If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average Calculation when zeroes are present
I am trying to do a calculation to get an average of the number data in 5
fields on a report. However, there is not always data in all 5 fields, so the average will sometimes use 3 of them, 4 of them, etc. I have no trouble getting the correct result when all 5 fields are greater than zero, but I can't figure out how to ignore the zeroes (I converted the null values in those fields to zero so the calculation would work, but I'm stumped on how to do the average on a variable number of fields). Help! |
#2
|
|||
|
|||
Average Calculation when zeroes are present
The real problem here is that you have repeating fields. In a relational
database, these values should be in one column, in a related table, so that one of these records can have many values in the related table. It would then be a really simple matter to average them. If you don't want to do that, you will be writing some crazy, convoluted expressions like this: =IIf([A] Is Null AND [b] Is Null AND [C] Is Null AND [D] Is Null AND [E] Is Null, Null, (Nz([A],0) + Nz([b],0) + Nz([C],0) + Nz([D],0) + Nz([E],0)) / -(([A] Is Not Null) + ([b] Is Not Null) + ([C] Is Not Null) + ([D] Is Not Null) + ([E] Is Not Null))) Substitute yoru field names for A - E. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "KarenB" wrote in message news I am trying to do a calculation to get an average of the number data in 5 fields on a report. However, there is not always data in all 5 fields, so the average will sometimes use 3 of them, 4 of them, etc. I have no trouble getting the correct result when all 5 fields are greater than zero, but I can't figure out how to ignore the zeroes (I converted the null values in those fields to zero so the calculation would work, but I'm stumped on how to do the average on a variable number of fields). Help! |
#3
|
|||
|
|||
Average Calculation when zeroes are present
Karen
If you have "five fields" with numbers in some/all, you have ... a spreadsheet! Have you considered exporting the data to excel, then using a couple functions (Sum() & Count()) to compute "average"? There are multiple problems (in Access) with doing what you described. First, changing nulls to zeros doesn't help, since "0" is a number, too. You could average 0,1,2 and get "1". Also, Access' aggregation functions (e.g., Avg()) work DOWN, not across. Across is what you do with spreadsheets. If you absolutely, positively must work in Access, consider converting your existing table structure to one that is well-normalized (more down than across). That way, the Access Avg() function will automatically only include the non-null values (hint, send those 0's back to null, unless, of course, your numbers also include REAL zeros?!). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "KarenB" wrote in message news I am trying to do a calculation to get an average of the number data in 5 fields on a report. However, there is not always data in all 5 fields, so the average will sometimes use 3 of them, 4 of them, etc. I have no trouble getting the correct result when all 5 fields are greater than zero, but I can't figure out how to ignore the zeroes (I converted the null values in those fields to zero so the calculation would work, but I'm stumped on how to do the average on a variable number of fields). Help! |
#4
|
|||
|
|||
Average Calculation when zeroes are present
Hi Karen,
Paste the following code into a module. ================================================== = Public Const strError As String = "Error" Public Function AverageAny(ParamArray var() As Variant) As Variant ' Average function that accepts any number of parameters. ' Author: Clifford Bass ' Parameters: Any number accepted, but only numeric values will be used ' Returns: The average of all the numerical values, if there are any, otherwise Null Dim i As Integer Dim intCount As Integer Dim dblSumValues As Double On Error GoTo AverageAny_Error intCount = 0 dblSumValues = 0 For i = LBound(var()) To UBound(var()) Step 1 SumAndCount var(i), dblSumValues, intCount Next i If intCount 0 Then AverageAny = dblSumValues / intCount Else AverageAny = Null End If AverageAny_Exit: Exit Function AverageAny_Error: AverageAny = strError Resume AverageAny_Exit End Function Private Sub SumAndCount(ByVal varItem As Variant, ByRef dblSumValues As Double, _ ByRef intCount As Integer) ' Helper routine for AverageAny Dim i As Integer If Not IsNull(varItem) Then If IsArray(varItem) Then For i = LBound(varItem) To UBound(varItem) Step 1 SumAndCount varItem(i), dblSumValues, intCount Next i Else If IsNumeric(varItem) Then dblSumValues = dblSumValues + varItem intCount = intCount + 1 End If End If End If End Sub ================================================== = Use it as follows: Private Sub SomeSub() Dim varResult As Variant varResult = AverageAny(Field1, Field2, Field3, Field4, Field5) If IsNull(varResult) Then ' No values found ..... Else If varResult = "Error" Then ' An error occurred .... Else ' An average has been calculated successfully ........ End If End If End Sub In theory you can specify an unlimited number of fields. The only exception is if you are using it directly in a query, in which case, if I recall corrrectly, you are limited to 29. When you are calling it from code you can actually pass multiple arrays of values in as the parameters. Hope this helps, Clifford Bass "KarenB" wrote: I am trying to do a calculation to get an average of the number data in 5 fields on a report. However, there is not always data in all 5 fields, so the average will sometimes use 3 of them, 4 of them, etc. I have no trouble getting the correct result when all 5 fields are greater than zero, but I can't figure out how to ignore the zeroes (I converted the null values in those fields to zero so the calculation would work, but I'm stumped on how to do the average on a variable number of fields). Help! |
#5
|
|||
|
|||
Average Calculation when zeroes are present
Thanks everyone for your help. I learned enough from each of you to muddle
through and get this to work - I appreciate your input! "Clifford Bass" wrote: Hi Karen, Paste the following code into a module. ================================================== = Public Const strError As String = "Error" Public Function AverageAny(ParamArray var() As Variant) As Variant ' Average function that accepts any number of parameters. ' Author: Clifford Bass ' Parameters: Any number accepted, but only numeric values will be used ' Returns: The average of all the numerical values, if there are any, otherwise Null Dim i As Integer Dim intCount As Integer Dim dblSumValues As Double On Error GoTo AverageAny_Error intCount = 0 dblSumValues = 0 For i = LBound(var()) To UBound(var()) Step 1 SumAndCount var(i), dblSumValues, intCount Next i If intCount 0 Then AverageAny = dblSumValues / intCount Else AverageAny = Null End If AverageAny_Exit: Exit Function AverageAny_Error: AverageAny = strError Resume AverageAny_Exit End Function Private Sub SumAndCount(ByVal varItem As Variant, ByRef dblSumValues As Double, _ ByRef intCount As Integer) ' Helper routine for AverageAny Dim i As Integer If Not IsNull(varItem) Then If IsArray(varItem) Then For i = LBound(varItem) To UBound(varItem) Step 1 SumAndCount varItem(i), dblSumValues, intCount Next i Else If IsNumeric(varItem) Then dblSumValues = dblSumValues + varItem intCount = intCount + 1 End If End If End If End Sub ================================================== = Use it as follows: Private Sub SomeSub() Dim varResult As Variant varResult = AverageAny(Field1, Field2, Field3, Field4, Field5) If IsNull(varResult) Then ' No values found ..... Else If varResult = "Error" Then ' An error occurred .... Else ' An average has been calculated successfully ........ End If End If End Sub In theory you can specify an unlimited number of fields. The only exception is if you are using it directly in a query, in which case, if I recall corrrectly, you are limited to 29. When you are calling it from code you can actually pass multiple arrays of values in as the parameters. Hope this helps, Clifford Bass "KarenB" wrote: I am trying to do a calculation to get an average of the number data in 5 fields on a report. However, there is not always data in all 5 fields, so the average will sometimes use 3 of them, 4 of them, etc. I have no trouble getting the correct result when all 5 fields are greater than zero, but I can't figure out how to ignore the zeroes (I converted the null values in those fields to zero so the calculation would work, but I'm stumped on how to do the average on a variable number of fields). Help! |
#6
|
|||
|
|||
Average Calculation when zeroes are present
"Clifford Bass" wrote in
message ... code snipped In theory you can specify an unlimited number of fields. The only exception is if you are using it directly in a query, in which case, if I recall corrrectly, you are limited to 29. When you are calling it from code you can actually pass multiple arrays of values in as the parameters. Hope this helps, Clifford Bass Clifford, I like your thinking! Thanks for sharing; this one is going into my useful examples folder, for sure and certain! -- Clif Still learning Access 2003 |
#7
|
|||
|
|||
Average Calculation when zeroes are present
Hi Clif,
You are welcome! I wrote a number of other similar functions that do medians, maximums, minimums, standard deviations, modes, counts and sums. They use essentially all the same/similar concepts as you see in the AverageAny function. Clifford Bass |
Thread Tools | |
Display Modes | |
|
|