**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.

- Finding the sum of all numbers (checks)
- Finding how many numbers (checks) in the list (use counter)
- 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
() to*Private Sub cmdCalculate_Click**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** -T**he average value is updated after the first number is entered without clicking on any button**

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