Averages by row 2

[Boneve]

I'm working with a gradebook. I am trying to make a query with Averages by row. Here are the columns

Name Test Test Rem1 TestRem2 Test Ave


I need an average of the test

I tried this, which did not work.

Test Ave: IIf ([Test]>1,[Test])

OR IIf([Test Rem1]>1,[Test]+[Test Rem1] / 2)
IIf([Test Rem2]>1,[Test]+[Test Rem1]+[Test Rem2] /3)
Any help would be greatly appreciated

Thank you
Boneve

 

[fneily]

First - the IIF takes 3 arguments. Highlight IIF and hit F1 to see an example.

 

[MichaelRed]

there are several routines posted in these fora which illustrate aggregate function techniques for rowset values. you should be able to find any / several of them using search / advanced search.

For the simple process of grade book average, the following should suffice, but please note that it has not been extensively tested

Public Function basAvg(ParamArray varMyVals()) As Variant


    'Michael Red 8/16/2007
    'To return the AVG of a series of values

    'Sample Usage:
    '? basAvg(1, 2, 3, 4, 5, ,6 -7)
    '2
    Dim Idx As Integer
    Dim Jdx As Integer
    Dim MySum As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (IsNumeric(varMyVals(Idx))) Then
            MySum = MySum + varMyVals(Idx)
            Jdx = Jdx + 1
        End If
NextVal:
    Next Idx

    If (Jdx > 0) Then
        basAvg = MySum / Jdx
     Else
        basAvg = 0
    End If

End Function

MichaelRed