szadroga

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?

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

ASKER

does this function, AVERAGEIF translate in PowerPivot? I am getting an error message...

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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...

ASKER

both worked, thx!

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