Simplifying array formula which evaluates as error.

Hi,

I have the array formula see below

{=SUM((kpi_Code=$C11&L$15)*(kpi_CD>0)*(kpi_AFD>0)*(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" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have the array formula see below
>
> {=SUM((kpi_Code=$C11&L$15)*(kpi_CD>0)*(kpi_AFD>0)*(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<at>tarkon.ee )



"Richard Buttrey" <[email protected]> wrote in
message news:[email protected]...
> Hi,
>
> I have the array formula see below
>
> {=SUM((kpi_Code=$C11&L$15)*(kpi_CD>0)*(kpi_AFD>0)*(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" <[email protected]>
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" <[email protected]> wrote in
message news:[email protected]...
> On Thu, 29 Sep 2005 15:44:46 +0300, "Arvi Laanemets" <[email protected]>
> 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_CD>0)*(kpi_AFD>0)*(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
> __________________________
>