Average of a number of fields

[JamesMcS]

Hi all - I'm almost certainly being an idiot, but it's been a long week....

I've got 12 fields that I need to take an average from. How do I do this in one field in a query? I get the feeling I'm going to be slapping my forehead and saying "D'oh" in a loud voice in the middle of the office.... I tried (field1+field2....)/12 but there are some zeroes in there which, if possible, I'd like to avoid.

Ta in advance!

 

[DCrake]

Getting the average of 12 values is not the same as getting the average of 4 values even if 8 of those values are 0.

If you wrap Nz([fld],0) around each field you will get 12 values to average

average:Avg(Nz([Field1],0)+Nz([Field2],0)+etc)

 

[JamesMcS]

OK. So I just made this, but how clunky??

Public Function Average_Of_Fields(field1, field2, Optional field3, Optional field4, Optional field5, Optional field6, Optional field7, Optional field8, Optional field9, Optional field10, Optional field11, Optional field12, Optional field13, Optional field14, Optional field15 As Integer)
Dim NonZeroFields As Integer
'Calculate number of non-zero fields
If field1 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field2 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field3 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field4 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field5 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field6 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field7 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field8 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field9 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field10 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field11 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field12 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field13 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field14 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
If field15 <> 0 Or Not Null Then
NonZeroFields = NonZeroFields + 1
End If
Average_Of_Fields = (field1 + field2 + field3 + field4 + field5 + field6 + field7 + field8 + field9 + field10 + field11 + field12 + field13 + field14 + field15) / NonZeroFields

End Function

A: Am I being a plonker of some sort by not knowing the obvious and simple method to do this?
B: If this is the way to do it, is there a way to say "For Each Argument in Me.Arguments" rather than having all these ifs?

I must be being an idiot!

 

[JamesMcS]

Thanks DC - what I've got is a table of sales margin for each of 12 countries. So the implication is that if there's a 0 in there, it hasn't sold, so I think I don't want to include them in the overall average, otherwise it brings it right down... (There would never be a 0% margin, by the way - salespeople like that don't last long in this company )

 

[DCrake]

As I said before to get an accurate average you need to include all the possible fields.

Think about it as a footballer who scores 10 goals in ten matches but only scored in 4 of the matches.

his true average is 1 goal per match. However the way you are doing means that his average is 2.5 goals per match, which is wrong.

You have to include the none scoring matches to get the true average.

 

[JamesMcS]

Good analogy - the thing is, even though a particular product is available for sale in the 12 countries, if it hasn't sold in them, I'd have a 0 in my sales margin table. Like the footballer being at the game, but on the bench, I suppose.

And when you're comparing that to the actual money-in-the-bank table and the cost of sale table, it's not going to match up.... see what I mean?

Anyway I've just asked one of the bosses and he says stick to the countries where it's sold - and what the customer wants, the customer gets right?!

So - is there a way to cycle through arguments in a for each statement? Or have I gone about it the right (if long winded) way?

 

[DCrake]

What you are saying is sometimes the country might sell them if they have any in stock but if they did not then don't include them?

Right?

What I would do is to split up the equasion into two parts.

Subtotal 1 - Countries that always have stock
subtotal 2 - optional countries

then add the 2 together and apply the average to that total

So if 8 outlets always sell them then you know at least the numerator is going to be at least 8

of the 4 other outlets calculate howmany have sales, say 2

Numerator = 8+2

So your average is going to be FactorAll Field values)/Numerator

You will not be able to use the built in Avg() in this case.

You so called long winded approach is doing what I suggest but if it works don't knock it.

As long as you are aware of the rules as to calculating the average and everyone else agrees to it then you can;t be blamed for false reporting.

 

[JamesMcS]

Almost - every country has the ability to sell it, stock comes from a warehouse in the UK - it's just a case of whether or not a customer in that country has made a purchase.

Sales.........Cost of Sales... Margin
UK - £100...£50.................50%
FR - £100...£50.................50%
DE - £0......£0...................0%
IT - £0.......£0..................0%
Total £200..£100...............50%

Using all the records the margin would be 25%, which is wrong (in the bosses' eyes anyway)

Hope that makes sense.... I've got the function running in a make table query at the moment, we'll see what happens

 

[DCrake]

What you are really refering to is Consumption. Which is correct at time of going to print.

 

[JamesMcS]

Hi again - I've got an Overflow error for somereason - can you see why from the code?

Public Function Average_Of_Fields(field1 As Single, field2 As Single, Optional field3 As Single, Optional field4 As Single, Optional field5 As Single, Optional field6 As Single, Optional field7 As Single, Optional field8 As Single, Optional field9 As Single, Optional field10 As Single, Optional field11 As Single, Optional field12 As Single, Optional field13 As Single, Optional field14 As Single, Optional field15 As Single)
Dim NonZeroFields As Integer
'Calculate number of non-zero fields
If field1 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field2 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field3 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field4 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field5 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field6 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field7 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field8 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field9 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field10 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field11 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field12 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field13 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field14 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
If field15 <> 0 Then
NonZeroFields = NonZeroFields + 1
End If
Average_Of_Fields = ([field1] + [field2] + [field3] + [field4] + [field5] + [field6] + [field7] + [field8] + [field9] + [field10] + [field11] + [field12] + [field13] + [field14] + [field15]) / NonZeroFields
End Function

It's overflowing on the last line, where I return the result back to the function name. I've declared the arguments as Singles because the figures it's adding up are mostly fractions, is that right?

 

[JamesMcS]

What a pillock. I think it might be to do with me calling the function with cdbl(field1), cdbl(field2) and so on. I've changed the data types in the function, let's see what happens...

 

[DCrake]

I think you need to declare the type of variant being returned by the function. Try setting it to double

Function nnn(.....) As Double

 

[JamesMcS]

Tried that, and declaring all the arguments as doubles, and removing the cdbl() from each of the fields I'm arguing with. It's still hanging after a few minutes, and I get this frmo the immediate window:

print average_of_fields
-1.#IND

Have you seen this before? It's a first for me....

 

[JamesMcS]

Apparently it's an Indefinite, which according to a post on eggheadcafe is Not A Number?

 

[JamesMcS]

OK - I put an on error resume next in just to force the query to run. The field I'm calling this function from is full of "-1.#IND", and google is producing not much to describe what's happening... grrrr.....

 

[JamesMcS]

It's because Nonzerofields is coming out as 0. To be continued...

 

[vbaInet]

James, to simplify your function:

Public Function Average_Of_Fields(ParamArray flds() As Variant) As Double
    Dim i As Byte, sumFields As Long, numFields As Byte
    
    For i = LBound(flds) To UBound(flds)
        If Nz(flds(i), 0) <> 0 Then
            sumFields = sumFields + flds(i)
            numFields = numFields + 1
        End If
    Next
    
    If numFields <> 0 Then
        Average_Of_Fields = sumFields / numFields
    End If
End Function

To call the function:

Average_Of_Fields(Field1, Field2, Field3, Field4 ... etc)

 

[JamesMcS]

Excellent, thanks vba - that's what I was trying to figure out, how to cycle through the arguments without having all those fors, didn't know about paramarray.... Nice one!

 

[vbaInet]

No problemo!

 

[apr pillai]

Take a look at the following Blog Post:

http://www.msaccesstips.com/2008/11/sum-min-max-avg-paramarray/

You may modify the function SMMAvg() code to take a count of non-zero values and divide into the total value to find the average. The modified Code is given below:

Public Function SMMAvg(ByVal calcType As Integer, ParamArray InputArray() As Variant) As Double
'------------------------------------------------------------------------
'calType : 0 = Summary'        : 1 = Minimum
'        : 2 = Maximum'        : 3 = Average
'------------------------------------------------------------------------
'Author  : a.p.r. pillai'Date    : November 2008
'URL     : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------
Dim rtn, j As Integer, arrayLength As Integer
Dim NewValue As Variant, NonZeroCount as integer

On Error GoTo SMMAvg_Err

If calcType < 0 Or calcType > 3 Then
     MsgBox "Valid calcType Values 0 - 3 only", , "SMMAvg()"
     Exit Function
End If

arrayLength = UBound(InputArray())
For j = 0 To arrayLength
   InputArray(j) = Nz(InputArray(j), 0)
Next

Select Case calcType
    Case 1
        rtn = InputArray(0)
        rtn = IIf(rtn = 0, 9999999999#, rtn)
    Case 2
        rtn = InputArray(0)
    Case Else
        rtn = 0
End Select

NonZeroCount = 0

For j = 0 To arrayLength
    NewValue = InputArray(j)
    If NewValue = 0 Then
 GoTo nextitem
      NonZeroCount = NonZeroCount + 1
    Select Case calcType
        Case 0, 3
            rtn = rtn + NewValue
        Case 1
            rtn = IIf(NewValue < rtn, NewValue, rtn)
        Case 2
            rtn = IIf(NewValue > rtn, NewValue, rtn)
    End Select
nextitem:
Next

If calcType = 3 Then
   rtn = rtn / NonZeroCount
End If

SMMAvg = rtn

SMMAvg_Exit:
Exit Function

SMMAvg_Err:
MsgBox Err.Description, , "SMMAVG()"
SMMAvg = 0
Resume SMMAvg_Exit
End Function

You can call this function from a Query Column like : SMMAVG(3,Field1,Field2,Field3.....Field12)

The first parameter 3 denotes that you need an average of provided values (excluding zero values with the above modification).