Want To Shrink Formula

[tb]

I wrote the following formula which works but it is a mile long and
some of the functions are used more than once. Intuition tells me that
there should be a way to shorten it (i.e. make it more efficient).
I've been trying to do that for hours but my logic simply fails...

Anyone wants to give it a try?

=IF($BC15="PD",IF(OR($R15="INT",$R15="NEE",$R15="OBS"),$J15,IF(AND($E15=
"IT1L",OR($H15="RA",LEFT($D15,3)="1K0",LEFT($A15,3)="60W",LEFT($A15,3)="
70-")),IF(AND($AS15>=$BG$2,$AV15>=$BG$3),IF($K15>$J15,$K15,CEILING($AV15
*$BG$4,$J15)),MAX($J15,$K15)),MAX($J15,$K15))),IF($BC15="X1",IF(AND($F15
="F",$G15="V"),IF($AV15*$BG$5>MAX($J15,$K15),IF(H15<>"RA",CEILING($AV15*
$BG$5,$J15),IF(CEILING($AV15*$BG$5,$J15)<500,CEILING($AV15*$BG$5,$J15),I
F(CEILING($AV15*$BG$5,$J15)<=5000,IF(MOD(100,$J15)=0,CEILING($AV15*$BG$5
,MAX(100,$J15)),CEILING($AV15*$BG$5,LCM(100,$J15))),IF(CEILING($AV15*$BG
$5,$J15)<50000,IF(MOD(1000,$J15)=0,CEILING($AV15*$BG$5,MAX(1000,$J15)),C
EILING($AV15*$BG$5,LCM(1000,$J15))),CEILING($AV15*$BG$5,MAX(2000,$J15)))
))),MAX($J15,$K15)),IF(CEILING($AV15,$J15)<=1000,MROUND(CEILING($AV15,$J
15),$J15),MROUND(ROUNDUP($AV15,-2),$J15))),"problem!"))

 

[Walter Briscoe]

In message <[email protected]> of Mon, 19 Aug
2013 03:26:24 in microsoft.public.excel.worksheet.functions, tb

I wrote the following formula which works but it is a mile long and
some of the functions are used more than once. Intuition tells me that
there should be a way to shorten it (i.e. make it more efficient).

What do you mean by "more efficient"?

I've been trying to do that for hours but my logic simply fails...

What do you mean by "my logic simply fails".

Anyone wants to give it a try?

=IF($BC15="PD",IF(OR($R15="INT",$R15="NEE",$R15="OBS"),$J15,IF(AND($E15=
"IT1L",OR($H15="RA",LEFT($D15,3)="1K0",LEFT($A15,3)="60W",LEFT($A15,3)="
70-")),IF(AND($AS15>=$BG$2,$AV15>=$BG$3),IF($K15>$J15,$K15,CEILING($AV15
*$BG$4,$J15)),MAX($J15,$K15)),MAX($J15,$K15))),IF($BC15="X1",IF(AND($F15
="F",$G15="V"),IF($AV15*$BG$5>MAX($J15,$K15),IF(H15<>"RA",CEILING($AV15*
$BG$5,$J15),IF(CEILING($AV15*$BG$5,$J15)<500,CEILING($AV15*$BG$5,$J15),I
F(CEILING($AV15*$BG$5,$J15)<=5000,IF(MOD(100,$J15)=0,CEILING($AV15*$BG$5
,MAX(100,$J15)),CEILING($AV15*$BG$5,LCM(100,$J15))),IF(CEILING($AV15*$BG
$5,$J15)<50000,IF(MOD(1000,$J15)=0,CEILING($AV15*$BG$5,MAX(1000,$J15)),C
EILING($AV15*$BG$5,LCM(1000,$J15))),CEILING($AV15*$BG$5,MAX(2000,$J15)))
))),MAX($J15,$K15)),IF(CEILING($AV15,$J15)<=1000,MROUND(CEILING($AV15,$J
15),$J15),MROUND(ROUNDUP($AV15,-2),$J15))),"problem!"))

Where does this formula work?
I suggest it does not work in Excel 2003, which documents "Up to seven
IF functions can be nested as value_if_true and value_if_false arguments
to construct more elaborate tests." I reckon the nesting level you use
is about 9.
I suggest documenting it in some fashion. You might start by adding some
whitespace into it.
I tried:
<
=IF( $BC15="PD",
IF( OR($R15="INT",$R15="NEE",$R15="OBS"),
$J15,
IF( AND($E15="IT1L",OR($H15="RA",LEFT($D15,3)="1K0",LEFT($A15,3)="60W",LEFT($A15,3)="70-")),
IF( AND($AS15>=$BG$2,$AV15>=$BG$3),
IF( $K15>$J15,
$K15,
CEILING($AV15*$BG$4,$J15)
),
MAX($J15,$K15)
),
MAX($J15,$K15)
)
),
IF( $BC15="X1",
IF( AND($F15="F",$G15="V"),
IF( $AV15*$BG$5>MAX($J15,$K15),
IF( H15<>"RA",
CEILING($AV15*$BG$5,$J15),
IF( CEILING($AV15*$BG$5,$J15)<500,
CEILING($AV15*$BG$5,$J15),
IF( CEILING($AV15*$BG$5,$J15)<=5000,
IF( MOD(100,$J15)=0,
CEILING($AV15*$BG$5,MAX(100,$J15)),
CEILING($AV15*$BG$5,LCM(100,$J15))
),
IF( CEILING($AV15*$BG$5,$J15)<50000,
IF( MOD(1000,$J15)=0,
CEILING($AV15*$BG$5,MAX(1000,$J15)),
CEILING($AV15*$BG$5,LCM(1000,$J15)),
CEILING($AV15*$BG$5,MAX(2000,$J15))
)
)
)
)
),
MAX($J15,$K15)
),
IF( CEILING($AV15,$J15)<=1000,
MROUND(CEILING($AV15,$J15),$J15),
MROUND(ROUNDUP($AV15,-2),$J15)
)
),
"problem!"
)
)
That has an obvious syntax error in
IF( MOD(1000,$J15)=0,
CEILING($AV15*$BG$5,MAX(1000,$J15)),
CEILING($AV15*$BG$5,LCM(1000,$J15)),
CEILING($AV15*$BG$5,MAX(2000,$J15))
)

That fails for me with "Formula is too long". Before I added whitespace,
it showed the error I have identified in the call of the IF function.

I would factor out common parts. e.g.
Replace
IF( CEILING($AV15,$J15)<=1000,
MROUND(CEILING($AV15,$J15),$J15),
MROUND(ROUNDUP($AV15,-2),$J15)
)
with
MROUND( IF( CEILING($AV15,$J15)<=1000,
CEILING($AV15,$J15),
ROUNDUP($AV15,-2)
),
$J15
)

I would also use intermediate values. e.g. Put
=MROUND(IF(CEILING($AV15,$J15)<=1000,CEILING($AV15,$J15),ROUNDUP($AV15,2
)),$J15)
in a standalone cell which can then be read in the main formula.