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?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Dale FyeOwner, Developing Solutions LLCCommented:
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

Open in new window

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)
SteveL13Author 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.
Dale FyeOwner, Developing Solutions LLCCommented:
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?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

SteveL13Author Commented:
The table is the recordsource for the form.  The controls are formatted as currency.  I am not typing the $.
SteveL13Author Commented:
And I put your code in a public module.  Correct?
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
SteveL13Author 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

Open in new window


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?
SteveL13Author 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.
SteveL13Author Commented:
Thank you!
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.