Calculate an average percentage with IF

[leopardhawk]

Using Excel 2007, is there a simple formula to calculate the average percentage excluding any cells that are 0%?

Cells K5 through K22 have student percentages in each row based on test scores across multiple columns. I need cell K1 to calculate the class average which it does using this formula:

=AVERAGE(K5:K22)

The problem is that the class size is never the same and if there are less than 18 students, some of the cells in column K will have 0% in them which then throws off the class average in cell K1.

I need a formula that will only calculate the percentages if they are more than 0%.

Appreciate your help.

 

[barry houdini]

Try AVERAGEIF like this

=AVERAGEIF(K5:K22,">0")

 

[leopardhawk]

Thanks Barry, that worked perfectly...!!