Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want To Shrink Formula
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="O BS"),$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$5MAX($J15,$K15),IF(H1 5<"RA",CEILING($AV15* $BG$5,$J15),IF(CEILING($AV15*$BG$5,$J15)<500,CEILI NG($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,M ROUND(CEILING($AV15,$J 15),$J15),MROUND(ROUNDUP($AV15,-2),$J15))),"problem!")) -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want To Shrink Formula
In message of Mon, 19 Aug
2013 03:26:24 in microsoft.public.excel.worksheet.functions, tb writes 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$5MAX($J15,$K15),IF(H 15<"RA",CEILING($AV15* $BG$5,$J15),IF(CEILING($AV15*$BG$5,$J15)<500,CEIL ING($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",LE FT($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$5MAX($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. -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in excel where it says a b c d on top how to shrink that | Excel Discussion (Misc queries) | |||
SHRINK AND WRAP | Excel Discussion (Misc queries) | |||
Shrink to Fit | Excel Discussion (Misc queries) | |||
Shrink to Fit | Excel Discussion (Misc queries) | |||
Shrink Formula | Excel Programming |