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

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.

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

=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.
--
Gary''s Student - gsnu200829


"Meenie" wrote:

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.

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

As an array formula (entered with Control Shift Enter)
=SUM(IF(ISERROR(K1:K11),"",K1:K11))/COUNT(K1:K11)
--
David Biddulph

"Meenie" wrote in message
...
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.

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

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

On Mon, 26 Jan 2009 12:38:35 -0800, Meenie
wrote:

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.

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

I tried this and got back #value!

"Gary''s Student" wrote:

=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.
--
Gary''s Student - gsnu200829


"Meenie" wrote:

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.

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

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

"David Biddulph" wrote:

As an array formula (entered with Control Shift Enter)
=SUM(IF(ISERROR(K1:K11),"",K1:K11))/COUNT(K1:K11)
--
David Biddulph

"Meenie" wrote in message
...
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.

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

Hi Meenie
It's important that you press CtrlShiftEnter 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 CtrlShiftEnter , you should
see the curly brackets, don't type them in yourself, it won't work.
HTH
John
"Meenie" wrote in message
...
I tried this and got back #value!

"Gary''s Student" wrote:

=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.
--
Gary''s Student - gsnu200829


"Meenie" wrote:

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.