Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Simplifying array formula which evaluates as error.
Hi,
I have the array formula see below {=SUM((kpi_Code=$C11&L$15)*(kpi_CD0)*(kpi_AFD0)* (kpi_CD))*100} which is the divisor element within a larger array formula. When this subset divisor element evaluates to zero, obviously the cell returns the #Div/0! result. I know I could wrap the whole formula in an If Iserror to get round this, i.e. If(iserror(large_formula),"",(large_formula) but this makes the whole formula - well much larger. Is there a more elegant way of arranging this in order to keep the whole formula as small as possible? TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#2
|
|||
|
|||
Richard,
Well, no, not really. You need to use an IF somewhere. You could use =IF(SUM( formula below)=0,"",Large Formula). Or you could use a second cell that looks at the result of the first, and returns "" when it has an error. HTH, Bernie MS Excel MVP "Richard Buttrey" wrote in message ... Hi, I have the array formula see below {=SUM((kpi_Code=$C11&L$15)*(kpi_CD0)*(kpi_AFD0)* (kpi_CD))*100} which is the divisor element within a larger array formula. When this subset divisor element evaluates to zero, obviously the cell returns the #Div/0! result. I know I could wrap the whole formula in an If Iserror to get round this, i.e. If(iserror(large_formula),"",(large_formula) but this makes the whole formula - well much larger. Is there a more elegant way of arranging this in order to keep the whole formula as small as possible? TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
Hi
=IF(divisor=0,"",large_formula) Furthermore, you can define parts of your large_formula as names (named functions), so that the whole will look like =IF(Name2=0,"",Name1/Name2) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Richard Buttrey" wrote in message ... Hi, I have the array formula see below {=SUM((kpi_Code=$C11&L$15)*(kpi_CD0)*(kpi_AFD0)* (kpi_CD))*100} which is the divisor element within a larger array formula. When this subset divisor element evaluates to zero, obviously the cell returns the #Div/0! result. I know I could wrap the whole formula in an If Iserror to get round this, i.e. If(iserror(large_formula),"",(large_formula) but this makes the whole formula - well much larger. Is there a more elegant way of arranging this in order to keep the whole formula as small as possible? TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
On Thu, 29 Sep 2005 15:44:46 +0300, "Arvi Laanemets"
wrote: Hi =IF(divisor=0,"",large_formula) Furthermore, you can define parts of your large_formula as names (named functions), so that the whole will look like =IF(Name2=0,"",Name1/Name2) Arvi, That's an interesting idea, please tell me more. How do I name part of the formula. I tried highlighting the relevant part and going to Insert Name Define as you'd normally do, but the define bit is greyed out. The Excel help wasn't much help when I looked. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
|
|||
|
|||
Hi
When Define is greyed out, then probably you have the worksheet, from where you tried to open the Name Define Wizard, protected. Unprotect the sheet before, after that try again. And highlighting the range is not helping you in any way - you are creating a named formula (returns a value), not a range, so simply enter the formula into RefersTo field. Probably you have to use non-array SUMPRODUCT instead of SUM as array formula, because you can't enter formulas with Ctrl+Shift+Enter in Name Define Wizard (sometimes Excel can decide, that the formula must be an array one, and behaves accordingly, but it doesn't work 100%). Arvi Laanemets "Richard Buttrey" wrote in message ... On Thu, 29 Sep 2005 15:44:46 +0300, "Arvi Laanemets" wrote: Hi =IF(divisor=0,"",large_formula) Furthermore, you can define parts of your large_formula as names (named functions), so that the whole will look like =IF(Name2=0,"",Name1/Name2) Arvi, That's an interesting idea, please tell me more. How do I name part of the formula. I tried highlighting the relevant part and going to Insert Name Define as you'd normally do, but the define bit is greyed out. The Excel help wasn't much help when I looked. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
|
|||
|
|||
Hi,
While the array formula will remain as is, you can use conditinal formatting to hide the error value. In the cell which has the array formula, go to conditional formatting in the Format menu and in the drop down menu select - "Formula is". In the other box, type - =iserror(cell reference) Now click on the format tab and in Font colour, select white. Now click on OK and OK again. This should hide error values Regards, "Richard Buttrey" wrote: Hi, I have the array formula see below {=SUM((kpi_Code=$C11&L$15)*(kpi_CD0)*(kpi_AFD0)* (kpi_CD))*100} which is the divisor element within a larger array formula. When this subset divisor element evaluates to zero, obviously the cell returns the #Div/0! result. I know I could wrap the whole formula in an If Iserror to get round this, i.e. If(iserror(large_formula),"",(large_formula) but this makes the whole formula - well much larger. Is there a more elegant way of arranging this in order to keep the whole formula as small as possible? TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP !!! I have a ARRAY Formula HELP !!! | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Formula error... | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |