Calculating an Average excluding fields with null values

[Guest]

Hello,

I have a dollar spread of a period of 12 months, but only want to average
the month amounts that have numbers in them. I.e. We are in march and have
numbers in the january, february and march fields. I want these 3 averaged,
excluding April-December since they have null values. How can I do this in a
query?

Thank you!
MN

 

[Guest]

Your database should not have monthly fields but have a record per month.

 

[Michel Walsh]

Also, NULL (not zero, but the value NULL which appear as a blank, in
general, if you don't format it in a special way) is not include in the
computation of COUNT, and AVG. So, you don't have anything special to do.

To remove the records where a zero will be present, have a WHERE clause:


SELECT period, AVG(amount)
FROM myTable
WHERE amount <> 0
GROUP BY period



Sure, that assume your values to average are vertically, one per record, not
horizontally. I.e., that your table looks like:

Period Amount ' fields

Jan 10.10
Jan 12.12
Feb 12.16
..... ' data sample



and NOT like:


SomeId Jan Feb Mar .... 'fields name
---, 11.11 12.16 --- ' data


Vanderghast, Access MVP

 

[Guest]

Karl,

Well, it based off of a crosstab query.

Thanks,
MN

 

[John Spencer]

Your table structure should be something like
PeriodName
PeriodAmount

You would have a record for each Month. Your query would be straightforward
totals query.

With your current structure you need to use some VBA or some complex
calculated expressions in your query.


Paste the function below into a module and save the module with some other
name than fRowAverage. Perhaps Mod_Average

In your query add a calculated column that looks like the following.

Field: fRowAverage([Jan],[Feb],[Mar],[Apr],[May],...,{Dec]) as theAverage

'--------- Code follows --------------------------
Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
'returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fRowAverage = dblSum / intElementCount
'At least one number in the group of values
Else
fRowAverage = Null
'No number in the group of values
End If

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks for the info, but unfortunately, they ARE horizontal. (based on a
crosstab query)

 

[Guest]

I am getting the following error in my query when I try executing this line:

MyAverage:
Mod_Average([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])

The following is the error:

"Undefined function, 'Mod_Average' in expression."

I did paste the code into a module and renamed all the fRowAverage
occurrences in the code to Mod_Average.

Thanks!

Your table structure should be something like
PeriodName
PeriodAmount

You would have a record for each Month. Your query would be straightforward
totals query.

With your current structure you need to use some VBA or some complex
calculated expressions in your query.


Paste the function below into a module and save the module with some other
name than fRowAverage. Perhaps Mod_Average

In your query add a calculated column that looks like the following.

Field: fRowAverage([Jan],[Feb],[Mar],[Apr],[May],...,{Dec]) as theAverage

'--------- Code follows --------------------------
Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
'returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fRowAverage = dblSum / intElementCount
'At least one number in the group of values
Else
fRowAverage = Null
'No number in the group of values
End If

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I have a dollar spread of a period of 12 months, but only want to average
the month amounts that have numbers in them. I.e. We are in march and
have
numbers in the january, february and march fields. I want these 3
averaged,
excluding April-December since they have null values. How can I do this in
a
query?

Thank you!
MN

 

[Guest]

Do not use the crosstab query, backup one step and use the data that feeds
the crosstab.

 

[Michel Walsh]

There is a special syntax that allows you to do it, but that is a little bit
uncommon. I would have to like Karl said, personally, but you still can do:


TRANSFORM SUM(whatever) AS TheValue
SELECT client, AVG(TheValue) AS Mean
FROM myTable
GROUP BY client
PIVOT yourActualPivot


Note: I assumed your original query was:

TRANSFORM SUM(whatever)
SELECT client
FROM myTable
GROUP BY client
PIVOT yourActualPivot


so, you can spot the additions I brought: give an alias to the expression to
be tabulated, and use that alias, aggregated with AVG, in the SELECT clause.
Since not-existing month will get a NULL, the AVG will be just fine... or
should be just fine.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,
I do not get it to work as it seems you are using an alias (TheValue)
generated in this query to do further calculations.
I think you need to do it as below.

TRANSFORM SUM(whatever) AS TheValue
SELECT client, AVG(whatever) AS Mean
FROM myTable
GROUP BY client
PIVOT yourActualPivot

 

[John Spencer]

NO, DO NOT change the code in fRowAverage.

What I was trying to say is that the function and the module cannot have
the same name. If a module has the same name as a function then you
will get an error everytime you try to call the function.

So when you save the module, I was suggesting a possible name. You
could save the module as Module1 (the default) or This Is Magic or ...



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I am getting the following error in my query when I try executing this line:

MyAverage:
Mod_Average([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])

The following is the error:

"Undefined function, 'Mod_Average' in expression."

I did paste the code into a module and renamed all the fRowAverage
occurrences in the code to Mod_Average.

Thanks!

Your table structure should be something like
PeriodName
PeriodAmount

You would have a record for each Month. Your query would be straightforward
totals query.

With your current structure you need to use some VBA or some complex
calculated expressions in your query.


Paste the function below into a module and save the module with some other
name than fRowAverage. Perhaps Mod_Average

In your query add a calculated column that looks like the following.

Field: fRowAverage([Jan],[Feb],[Mar],[Apr],[May],...,{Dec]) as theAverage

'--------- Code follows --------------------------
Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
'returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fRowAverage = dblSum / intElementCount
'At least one number in the group of values
Else
fRowAverage = Null
'No number in the group of values
End If

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I have a dollar spread of a period of 12 months, but only want to average
the month amounts that have numbers in them. I.e. We are in march and
have
numbers in the january, february and march fields. I want these 3
averaged,
excluding April-December since they have null values. How can I do this in
a
query?

Thank you!
MN

 

[Michel Walsh]

Should word. Here an example using Northwind:



TRANSFORM Sum(Orders.Freight) AS c
SELECT Orders.EmployeeID, Avg([c]) AS Mean
FROM Orders
WHERE (((Orders.ShippedDate)<=#1/1/1997#))
GROUP BY Orders.EmployeeID
PIVOT Month(ShippedDate);



which returns (Access 2003), for me:



Employee Mean 1 7 8 9 10 11 12
Davolio, Nancy $263.13
$140.51 $240.30 $118.57 $289.17 $328.64 $461.60
Fuller, Andrew $147.34 $122.46
$181.05 $28.37 $105.58 $264.72 $181.84
Leverling, Janet $176.01
$139.21 $160.88
$116.32 $239.42 $224.20
Peacock, Margaret $328.10
$260.43 $300.77 $184.47 $602.00 $192.37 $428.55
Buchanan, Steven $217.52
$55.36 $4.56 $5.74 $35.16 $308.07 $896.22
Suyama, Michael $96.95
$11.61 $112.25 $174.74 $12.69 $78.66 $191.74
King, Robert $132.86

$22.77 $109.44 $122.52 $396.04 $13.55
Callahan, Laura $199.34
$48.29 $440.65 $142.30 $270.91 $53.80 $240.06
Dodsworth, Anne $177.61
$294.39

$224.46
$13.99






and indeed, the aggregate AVG operates over the data already aggregated in
the TRANSFORM clause, here, with the SUM.

If you average on on initial field, rather than on the value already
aggregated, you obtain the average of the group, wich is not always the same
as the average of the pivot. Example:

TRANSFORM SUM(f3) AS theValue
SELECT f1, AVG(f3), AVG(theValue)
FROM somewhere
GROUP BY f1
PIVOT f2


with

f1 f2 f3
Nancy A 1
Nancy A 2
Nancy B 3



then the average of f3 of the group Nancy is 2.

while averaging the pivoted summed values, for Nancy:

A B average =(A+B)/2
Nancy 3 3 3


produces 3.



Vanderghast, Access MVP.

 

[Michel Walsh]

Sorry for the mess with the query result, was much better when I inserted it
in the message, but I don't want to post in HTML just for that...


It was 'visible' that some 'cells' where null, and that the average of the
aggregated values did, indeed, worked as expected, in such cases.

As example,

Dodsworth, Anne, got $177.61 which is the average of the three values:
$294.39
$224.46
$13.99


Vanderghast, Access MVP.