Find the average of a column, ignoring values that are 0

[chris_bcn]

if that makes sense!

I have a column of data, and i want to find the average, but I need to ignore all values that are 0

I ccame up with =AVERAGE(IF(G3:G72>=0,G3:G72,"")) - but that's clearly rubbish!

Any help would be much appreciated!

Cheers

 

[Aladin Akyurek]

if that makes sense!

I have a column of data, and i want to find the average, but I need to ignore all values that are 0

I ccame up with =AVERAGE(IF(G3:G72>=0,G3:G72,"")) - but that's clearly rubbish!

Any help would be much appreciated!

Cheers

=AVERAGE(IF(G3:G72 > 0,G3:G72,""))

which, when confirmed with control+shift+enter, would average non-zero positive values.

 

[chris_bcn]

I was kinda close, but I was never going ot get it - thanks a million - you're a star, it works like a charm (as you knew it would!)

 

[dpofarre]

Is there any way to perform some upstream processing on this data, to format the cells with "0" to "". For instance, if G3 is the sum of F3 and E3, then G3 would be:

=IF(SUM(E3:F3)=0,"",SUM(E3:F3))

Then the average function would take care of itself. This of course assumes that the "0" character is not necessary, and that the values in G column are the result of a previous expression, and not entered.

Hey, it's probably not the best way, but it should work.

 

[dpofarre]

Oops! A little late!