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

#### chris_bcn

##### New Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
chris_bcn said:
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.

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!)

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.

Oops! A little late!

Replies
8
Views
175
Replies
3
Views
138
Replies
8
Views
222
Replies
1
Views
626
Replies
3
Views
147

1,203,313
Messages
6,054,696
Members
444,741
Latest member
MCB024

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back