How to Find the Average of Numbers

http://www.iaccessworld.com/find-average-numbers/

How to Find the Average from Numbers in a List

Per previous How To, I already demonstrated how to create the total (sum) of numbers (checks). In this How To, I will use the previous form to show how to find the average of numbers (checks). The Average Formula (Mean or Arithmetic Mean) is the sum of all of the numbers in a list divided by the numbers of items in that list. For example, the average of the numbers 2,3,7 is 4 since 2+3+7 =12 and 12 divided by 3 is 4.

Creating Form

• I use the previous form on how to calculate the total or you can create one similar to this.
• Change the label of Total to Average
• Change name of the unbound textbox from txtTotal to txtAverage

• Place the VB code under the On Click Event Procedure of the Calculate button

Code to Find the Average

There are three different steps to create VBA to the find the Average.

1. Finding the sum of all numbers (checks)
2. Finding how many numbers (checks) in the list (use counter)
3. Divided the sum by the numbers from the list

Private Sub cmdCalculate_Click()
Dim Total As Double
Dim Counter as integer
‘Initialize variable Counter and Total = 0 to be ready to add to another amount
Counter =0
Total =0
‘ User If Not Isnull statement to check if there is a number entered in the textbox.
If Not IsNull(Me.txtChk1) Then
       Total = Total + Me.txtChk1.Value
       Counter = Counter +1
End If
If Not IsNull(Me.txtChk2) Then
       Total = total + Me.txtChk2.Value
       Counter = Counter +1
End If
If Not IsNull(Me.txtChk3) Then
       Total = total + Me.txtChk3.Value
       Counter = Counter +1
End If
Me.txtAverage = Total/Counter
End Sub

Example #1 Calculate the average from 3 numbers

Example #2 Calculate the average from 2 numbers at the beginning

Example #3 Calculate the average from 2 numbers at the end

Calculate the Average after the Amount is entered

• Delete the Calculate button
• Select the Event Procedure on After Update event on each unbound textbox

• Call Sub FindAverage() function under the AfterUpdate event of each unbound textbox as shown in the picture below.

• Create the FindAverage() function below by changing the Private Sub cmdCalculate_Click() to Sub FindAverage()

Sub FindAverage()
Dim Total As Double
Dim Counter as integer
‘Initialize variable Counter and Total = 0 to be ready to add to another amount
Counter =0
Total =0
‘ User If Not Isnull statement to check if there is a number entered in the textbox.
If Not IsNull(Me.txtChk1) Then
       Total = Total + Me.txtChk1.Value
       Counter = Counter +1
End If
If Not IsNull(Me.txtChk2) Then
       Total = total + Me.txtChk2.Value
       Counter = Counter +1
End If
If Not IsNull(Me.txtChk3) Then
       Total = total + Me.txtChk3.Value
       Counter = Counter +1
End If
Me.txtAverage = Total/Counter
End Sub

Example #1 -The average value is updated after the first number is entered without clicking on any button

Example #2 -The average value is changed after the second number is entered and cursor moves the next field.