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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________