| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with Copying a Simple formula | Excel Worksheet Functions | |||
| IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
| Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
| simple formula problem | Excel Discussion (Misc queries) | |||
| another simple problem... | Excel Worksheet Functions | |||
Linear Mode
