Array average question

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

Guest

Hi there,

I am using the following formula to calculate the average of a row of cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<>"",E49:IV49)),AND(IF(E49:IV49<>"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??
 
See other post.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
What you need is =AVERAGE(IF(E49:IV49<>0,E49:IV49))
You must enter it as an array formula using CTRL+SHIFT+ENTER rather than
just ENTER. Excel will enclose the formula is curly braces {...}
You need not worry about blanks - AVERAGE is 'smart enough' to ignore them

Your formula has a number of errors
1) Wrong syntax fro AND
2) Boolean functions (AND, OR, NOT) do not work in array formulas
3) Wrong to put 0 is quotes - it is a number not text

best wishes
 
Hi,


Try this:

=AVERAGE(IF((E49:IV49<>"")*(E49:IV49<>0),E49:IV49))

but this should work as well I think:

=AVERAGE(IF(E49:IV49<>0,E49:IV49))

again enter using CSE


HTH
Jean-Guy

enter using Ctrl+Shift+Enter

HTH
Jean-Guy
 
Back
Top