Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula vs. simpler
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",LE FT($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="1 21015"),"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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula vs. simpler
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. "GerryK" wrote: 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",LE FT($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="1 21015"),"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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula vs. simpler
Gerry, The complex formula is actually 7 nested IF formulas. The last piece of the complex formula (LEFT(D5,4)) is the value given if all of the conditions of the prior formulas are not met so. I assume that by getting the same result after entering in the simpler formula, =LEFT(D5,4), then the conditions of the previous 7 nested formulas of the complex formula are not being met. What you could do to confirm this is break the complex formula into it's 7 different parts and evaluate whether or not any of the conditions are being met. 1. 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",LE FT($D5,3)="629",LEFT($D5,3)="215",LEFT($D5,3)="221 ",LEFT($D5,3)="638",LEFT($D5,4)="1820",LEFT($D 5,4) ="1821",LEFT($D5,4)="1822",LEFT($D5,4)="1823",L EFT ($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="121006 ",$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 4 characters i.e. IF(OR($D5="651109",$D5="651110"), "6511CIO" so the formula returns "6511CIO" if D5 = either 651109 or 651110 whereas =LEFT(D5,4) would not return the desired value. It would return "6511". HTH Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=491071 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula vs. simpler
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","2 21","638","187"}),OR(LEFT($D5,4)={"1820","1821","1 822","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={"1 21008","121003","121004","121005","121006","121007 "}),"1210CC",IF(OR($D5={"121014","121015"}),"1210C F",IF(OR($D5={"651109","651110"}),"6511CIO",LEFT($ D5,4)))))))) NOT CLOSELY CHECKED "GerryK" wrote: 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",LE FT($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="1 21015"),"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help with complex formula | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
I Need Help with complex formula ? | Excel Worksheet Functions | |||
A SIMPLER FORMULA TO GET A REFERENCE OF WHICH COLUMN IS POPULATED | Excel Worksheet Functions | |||
Help with Complex SUMPRODUCT formula | Excel Worksheet Functions |