Average, if, and skipping cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get the following formula to work per the instructions in the help (Excel 2003). However, as soon as it hits the cell with a zero, rather than not including it in the calculation, it returns the dreaded #VALUE. Can you automatically skip cells by imbedding an if within an average?

=AVERAGE(IF(A2:A7<>0, A2:A7,""))

I've isolated the problem to the cell with zero by changing the ranges to be one cell at a time. Everything is fine until I hit A6 which has a value of 0 (zero, numeric)

I guess the real question is what is the purpose of the false argument in this situation

Thanks for any help.
 
Hi Sally
you nearly got it :-)
enter the formula
=AVERAGE(IF(A2:A7<>0, A2:A7))
as ARRAY formula (that is enter it with CTRL+SHIFT+ENTER instead of
only hitting ENTER)
 
Can come down smaller than that, as you don't need the <>0 bit

=AVERAGE(IF(A2:A7,A2:A7))

will skip blanks and 0s
 
LOL - Just love this copy and paste stuff - Don't know what I'd do if the others
didn't type this stuff out first so I could copy it :-)
 
Thank you to both of you. Both ways worked.

I wasn't getting it about the cntl+shift+enter. I think it was too late last night. I was reading it as cntl+alt+enter.
 
Back
Top