Complex formula vs. simpler

G

GerryK

Could someone tell me if running this formula
IF(OR(LEFT($D5,2)="27",LEFT($D5,2)="02",LEFT($D5,3)="621",LEFT($D5,3)="623",LEFT($D5,3)="624",LEFT($D5,3)="626",LEFT($D5,3)="627",LEFT($D5,3)="628",LEFT($D5,3)="629",LEFT($D5,3)="215",LEFT($D5,3)="221",LEFT($D5,3)="638",LEFT($D5,4)="1820",LEFT($D5,4)="1821",LEFT($D5,4)="1822",LEFT($D5,4)="1823",LEFT($D5,4)="1824",LEFT($D5,4)="1825",LEFT($D5,3)="187"),LEFT($D5,3),IF(LEFT($D5,2)="22",LEFT($D5,5),IF(OR($D5="121020",$D5="122101",$D5="122102"),"1221",IF(OR($D5="121001",$D5="121002",$D5="121009",$D5="121010",$D5="121012"),"1210CT",IF(OR($D5="121008",$D5="121003",$D5="121004",$D5="121005",$D5="121006",$D5="121007"),"1210CC",IF(OR($D5="121014",$D5="121015"),"1210CF",IF(OR($D5="651109",$D5="651110"),"6511CIO",LEFT($D5,4))))))))

along side:
=LEFT(D5,4) is any different?

I get the same result and am not quite sure why someone would write the
first. Before I change it to the simpler one I would appreciate any advice as
to what the first may be calculating that I am missing?

TIA
 
S

Sloth

It is only equal to =LEFT(D5,4) if it doesn't meat any previous requirments.
For instance, if the number starts with 27, then the formula only takes the
left 3 digits.
 
S

SteveG

Gerry,

The complex formula is actually 7 nested IF formulas. The last piec
of the complex formula (LEFT(D5,4)) is the value given if all of th
conditions of the prior formulas are not met so. I assume that b
getting the same result after entering in the simpler formula
=LEFT(D5,4), then the conditions of the previous 7 nested formulas o
the complex formula are not being met. What you could do to confir
this is break the complex formula into it's 7 different parts an
evaluate whether or not any of the conditions are being met.

1.
IF(OR(LEFT($D5,2)="27",LEFT($D5,2)="02",LEFT($D5,
)="621",LEFT($D5,3)="623",LEFT($D5,3)="624",LEFT(
D5,3)="626",LEFT($D5,3)="627",LEFT($D5,3)="628",LEFT($D5,3)="629",LEFT($D5,3)="215",LEFT($D5,3)="22
",LEFT($D5,3)="638",LEFT($D5,4)="1820",LEFT($D5,4
="1821",LEFT($D5,4)="1822",LEFT($D5,4)="1823",LEF
($D5,4)="1824",LEFT($D5,4)="1825",LEFT($D5,3)="187 "),LEFT($D5,3)
2.
IF(LEFT($D5,2)="22",LEFT($D5,5)
3.
IF( OR($D5="121020",$D5="122101",$D5="122102"),"1221"
4.
IF(OR($D5="121001",$D5="121002",$D5="121009",$D5=
121010",$D5="121012"),"1210CT"
5.
IF(OR($D5="121008", $D5="121003",$D5="121004",$D5="121005",$D5="12100
",$D5="121007"),"1210CC"
6.
IF(OR($D5="121014",$D5="1 21015"),"1210CF"
7.
IF(OR($D5="651109",$D5="651110"), "6511CIO"

Result if none of the above are met =LEFT($D5,4).

Some of the values to be returned in the nested functions exceed
characters i.e. IF(OR($D5="651109",$D5="651110"), "6511CIO" so th
formula returns "6511CIO" if D5 = either 651109 or 651110 wherea
=LEFT(D5,4) would not return the desired value. It would retur
"6511".


HTH


Cheers,

Stev
 
D

Duke Carey

Others have already commented on why you can't use the simple formula you
offered.

Here's a slimmed down version of what you posted

IF(OR(OR(LEFT($D5,2)={"27","02"}),OR(LEFT($D5,3)={"621","623","624","626","627","628","629","215","221","638","187"}),OR(LEFT($D5,4)={"1820","1821","1822","1823","1824","1825"})),LEFT($D5,3),IF(LEFT($D5,2)="22",LEFT($D5,5),IF(OR($D5={"121020","122101","122102"}),"1221",IF(OR($D5={"121001","121002","121009","121010","121012"}),"1210CT",IF(OR($D5={"121008","121003","121004","121005","121006","121007"}),"1210CC",IF(OR($D5={"121014","121015"}),"1210CF",IF(OR($D5={"651109","651110"}),"6511CIO",LEFT($D5,4))))))))

NOT CLOSELY CHECKED
 
Top