Question How Omit Zeros From The Average Function Formula

[calucho]

Hello;
Some body can tell me how Omit the zeros from the average function formula so I can get the accurate average of my values.

Thank you for you help

Carlos

 

[John Big Booty]

Welcome to the forum.

You could create a query with the Criteria <>0 and put those results into the Average function

 

[calucho]

hi;
Thank you for you answers; how use a IIf condition for the same problem a report;
=AVG(IIF[accept]=0, here what will be the Thrue condition;

Thank you

 

[John Big Booty]

I'm not quiet sure why the same approach would work for your report as well

 

[calucho]

hi;
sorry may be I can express my self well; I create a reports. I use in the report the Average formula; but I have some values with 0.00 and when I aplly the formula of average it count the 0.00 values and the average is not accurate because the average count the 0.00 like a value and it is the problem that i have. It is the reason that I think to solve this problems I have to use the IIF function or there is another approach for this issue.
it is my problems(0.00, 0.00, 1.00, 5.00 0.00) the average suppose to be (6.00/2= 3.00) but in my report average ( 6.00/5 = 1.2) and it is wrong.. can you advise me what is the way to fix these..

Tank you very much for you help..

Carlos

 

[John Big Booty]

You should be able to do it using the DAvg() function and using the Criteria <>0

 

[Dairy Farmer]

Why not use median rather than average. It is more representative of the "average" as it tends to "ignore" those numbers that are way out.

Now why Microsoft chose to not include this function that is used in Excel is a mystery. I think you could write a book on the functions in Excel that did not make it across to Access that we could use everyday.

Here is a link to MSDN

http://msdn.microsoft.com/en-us/library/dd789431(v=office.12).aspx

 

[calucho]

hi,
thank you for you help; I find another way to do these:
convert the value of 0.00 to NULL
=AVG(IIF([Accept]=0,NULL,[Accept]))
it work fine.
Thank you for all you replay.

Carlos