# How average up to 5 numbers on a form

I have a form with up to 5 number fields.  Some of them may have a value and some may not.  How can I average them no matter how many have a value?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Depends.  I use a function that will accept any number of parameters.  Are these fields in a table or unbound controls?  How do you want to handle the fields that have no data?  The function I use accepts an array and loops through the array

``````Public Function fnAvg(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim dblSum As Double, intCount As Integer

For intLoop = LBound(ValList) To UBound(ValList)

If Not IsNull(ValList(intLoop)) Then

dblSum = dblSum + ValList(intLoop)
intCount = intCount + 1

End If

Next

If intCount = 0 Then
fnAvg = Null
Else
fnAvg = dblSum / intCount
End If

End Function
``````
This returns a NULL value if there are no non-null values in the array.  If the 5 fields are in a table and bound to that table, then you could just bind your form to a query that includes a computed column and that column would update automatically when any of the other controls is changed.

If the controls are unbound, then you would set the Control Source of the Average field to: =fnAvg([Forms]![formName]!field1, [Forms]![formName]!field2, [Forms]![formName]!field3, [Forms]![formName]!field4, [Forms]![formName]!field5)
0
Author Commented:
The fields are bound to the table but probably aren't written to the table as the user enters them.  So I'd like the average to be calculated as they enter the values.

I'm using your code.  Plus I have this in the control source of the average field:

=fnAvg(Nz([txtSource1Rate],""),Nz([txtSource2Rate],""),Nz([txtSource3Rate],""),Nz([txtSource4Rate],""),Nz([txtSource5Rate],""))

But I get a type mismatch error on the line:

dblSum = dblSum + ValList(intLoop)

If it matters the values are currency numbers.
0
Commented:
If they are bound to the table and the form is bound to the table, then they will be written to the fields as the user enters them.  If you use a query for the recordsource of the form, then the computed column will automatically be updated as each entry is made.

Are you typing in the \$ or do you simply have the controls formatted as currency?
0
Author Commented:
The table is the recordsource for the form.  The controls are formatted as currency.  I am not typing the \$.
0
Author Commented:
And I put your code in a public module.  Correct?
0
Commented:
I tried entering values with the leading \$ and that worked fine.  And it even worked when I manually entered commas as thousands separators and it worked then too.
0
Commented:
You might try changing:

If Not IsNull(ValList(intLoop)) Then

to:

If Trim(ValList(intLoop) & "") <> "" Then

that will check for zero length strings as well as NULLs.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
I don't get it.

Again, I have this in a module:

``````Public Function fnAvg(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim dblSum As Double, intCount As Integer

For intLoop = LBound(ValList) To UBound(ValList)

If Not IsNull(ValList(intLoop)) Then

dblSum = dblSum + ValList(intLoop)
intCount = intCount + 1

End If

Next

If intCount = 0 Then
fnAvg = Null
Else
fnAvg = dblSum / intCount
End If

End Function
``````

And I have this in the control source for the field to be the calculated field:

=fnAvg(Nz([txtSource1Rate],""),Nz([txtSource2Rate],""),Nz([txtSource3Rate],""),Nz([txtSource4Rate],""),Nz([txtSource5Rate],""))

And the five fields a bound to the table.

What am I doing wrong?
0
Author Commented:
This did it:

You might try changing:

If Not IsNull(ValList(intLoop)) Then

to:

If Trim(ValList(intLoop) & "") <> "" Then

that will check for zero length strings as well as NULLs.
0
Author Commented:
Thank you!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.