Help! Need to ignore 0s and blank cells in average formula!

  • Thread starter Thread starter sandres74
  • Start date Start date
S

sandres74

I have the following formula - which works - to calculate an average of
numbers that are listed in 3 separate columns:
=AVERAGE(E7:E126,M7:M129,U7:U128)

The problem is that I need the formula to ignore blank cells and cells that
contain a zero. I've tried many different things but just can't get it to
work!

Anyone have any suggestions?
Thanks in advance for your help!
 
sandres74 said:
=AVERAGE(E7:E126,M7:M129,U7:U128) [....]
The problem is that I need the formula to ignore
blank cells and cells that contain a zero.
Click to expand...

If read look at Excel Help, you would see that AVERAGE already ignores blank
cells, even cells that contain null and blank strings as well as other text.
So the only thing you need to worry about are cells whose value is zero.

Try the following array formula [*]:

=AVERAGE(IF(E7:E126<>0,E7:E126),
IF(M7:M129<>0,M7:M129),
IF(U7:U128<>0,U7:U128))

[*] An array formula is commited by pressing ctrl+shift+Enter instead of
just Enter. You will see the entire formula surrounded by curly braces in
the Formula Bar, viz. {=formula}. If you make a mistake, press F2, edit the
formula if necessary, then press ctrl+shift+Enter.


----- original message ----
 
What =average does is to add all of the cells with a numerical value
(including 0) and then divide that result by the number of cells containing a
numerical value.
=Average will skip blank cells and non-numerical cells, but it does not
ignore 0.
Try using =SUM function to get the total and divide by =COUNTIF if to ignore
blank and 0 value cells
 
Back
Top