AVERAGE CALCULATION

[thembela]

I've been using the following formula to calculate the Average, but i get an incorrect answer for each field.please tell me what could be wrong.This thing seems very easy to do on Excel.In some instances it returns an average of zero even when there's a value in any of these fields

(Nz([200407])+ Nz([200408])+ Nz([200409])).

 

[dhookom]

I prefer to always specify a second argument with Nz():
(Nz([200407],0)+ Nz([200408],0)+ Nz([200409],0))
I don't know how you expect to get an average when this is clearly just a sum.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

per Duane's comment, the NZ function can (will?) return a value of the type of the of the argument. If this (the argument) is non-numeric, you can (will?) get a non numeric return ... this may intergeer with tge calculation?



MichaelRed

 

[thembela]

Maybe i'll need 2 explain myself better.Because the solution u proposed hasn't worked.

I've calculated SUM for the following
fields: ([200407]+[200408]+[200409]+[200410]+[200411]) each of these date
fields have got numeric values.I then summed each field (i.e 200407) on the "Totals" row.

For Avg: Nz([200407],0)+Nz([200408],0)+Nz([200409],0). I then selected the
Average on "Totals" row.

The sum function seems to be working perfectly well and the Average one keeps
giving incorrect values.Yes I got zero values on some of my fields.Could that
be the reason??The funny thing is when i test the dynaset results on Excel ,i get the same values for both Sum and Average except for decimals

 

[dhookom]

The average should return the average of the sum across the fields. For instance:
[tt]
200407 200408 200409
4 5 12
10 0 8
[/tt]
You average would be ((4+5+12) + (10+0+8))/2

You have not yet provided some sample records with the output you are displaying.

Are you really storing values in "month" fields? Or have you properly normalized your tables?



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

Mayhap I'm missing something(s)?

You seem to be describing what would usually be TWO )seperate( operations. If you are using TWO queries. it can be as you say. If this only refers to a SIMGLE query, it may be a problem.




MichaelRed

 

[thembela]

Here's the query results:

200407 200408 200409 200410 Average
13968 0 14328 0 4716
36612 86868 38340 104520 1857.5

Yes numeric values are being stored in month fields? could that be the problem?

 

[MichaelRed]

I believe the issue is that you are trying to do it in a single operation. Average the columns and than average the rows. Ms. A. (SQL) is not equipped for this. You need to do the operations seperatly. Use the standard Aggregate to average the columns. Transpose the resultant ROW to a column. Another aggregate query to then aveerage that (result) column. An alternative would be to use a FUNCTION to obtain the rowset average (search herein (Tek-Tips) for basAvg or basAvgVal). Then generate the average for JUST the calculated column.



MichaelRed

 

[MichaelRed]

Also, be aware that these processes would (both / either) return the "Raw" average of the valu set(s). You have not indicated that the numbers represent anything, so "Counts of Occurances" of a singular event or the number of pounds of the same item processed would be reasonable objects for the calculation. The (Average) Price of the vehicles sold within an area would be a poor canidate for these procedures.




MichaelRed

 

[dhookom]

Using months as column/fields is not normalized. Consider reading up on normalization at

http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]