Link to home
Start Free TrialLog in
Avatar of szadroga
szadrogaFlag for United States of America

asked on

Calculate Average excluding zero values in cells

I need to calculate the average for a set of numbers, but I do not want to include the zero values in my calculation.  For instance, in the month of July i have values of 8.4 and 5.2, their average would be 6.8 but my table has multiple rows for all the days in July and when i calculate the average it brings all those zero value in and makes the average extremely low.  

I am using PowerPivot and Access to get the data.  Is the a function i can use to calculate this average?
Avatar of Professor J
Professor J

you can do that with =AVERAGE(IF(A1:A10<>0,A1:A10))   old excel

and for new excel =AVERAGEIF(A1:A10,"<>0")
Avatar of szadroga

ASKER

does this function, AVERAGEIF translate in PowerPivot?   I am getting an error message...
SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i downloaded your xlsx file and went into PowerPivot > Manage, but its empty?  am i looking in the wrong place
Check at the bottom of this or create a pivot table of the data you will see avg calculated field their...
both worked, thx!