Having problem with 'if' in what should be a simple formula

[Meenie]

I have Excel 2003
All I want to do is make a formula to show the Average of a range of cells
BUT some of the cells are have #DIV/0 (I don't have the numbers for those
right now) and I want to ignore those cells. I'm trying to do that with an
"if" formula, but can't seem to get it to come out right. I keep getting
?Name.

 

[Gary''s Student]

=AVERAGE(IF(ISNUMBER(E1:E100),E1:E100,""))

this is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just he ENTER key.

 

[Gord Dibben]

Error-check your cells' formulas to prevent the #DIV/0 to start with is the
best solution.

e.g. formulas in A1:A10

=IF(OR(D1="",D1=0),"",C1/D1)

=AVERAGE(A1:A10) will ignore the "" cells


Gord Dibben MS Excel MVP

 

[Meenie]

I tried this and got back #value!

=AVERAGE(IF(ISNUMBER(E1:E100),E1:E100,""))

this is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just he ENTER key.

 

[Meenie]

Tried this one but still got the #DIV/0 (I did enter with control shift enter

 

[John]

Hi Meenie
It's important that you press Ctrl>Shift>Enter not just enter.
If you do it right, you will get curly bracket around your formula.
e.g.{=AVERAGE(IF(ISNUMBER(E1:E100),E1:E100,""))}
Make a small correction on your formula and press Ctrl>Shift>Enter , you should
see the curly brackets, don't type them in yourself, it won't work.
HTH
John