| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
Why does this formula not work? If I have a product named ABC, DEF, etc, I
want it to use the formula at the end HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for product OPQ. I think I have the parenthases in the wrong place, but I can't figure it out. =IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15)))))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
Simply put, because you do test for all the conditions, but does nothing when
a condition is true, except right at the end. =IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15)))))) Iow, you say that if U14 <ABC, then check if U14<DEF, then check, and so on. But what must it do if in fact U14 =ABC, or U14 = DEF, or U14 = GEH etc. Also, with so many checks, it is better to use VLOOKUP. =IF(ISNA(VLOOKUP(U14,FORMULAS!$T$3:$BF$119,9,0)),0 ,VLOOKUP(U14,FORMUALS!$T$3:$BF$119,9,0)*U15) is maybe what you are looking for. -- HTH Kassie Replace xxx with hotmail "klappin" wrote: Why does this formula not work? If I have a product named ABC, DEF, etc, I want it to use the formula at the end HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for product OPQ. I think I have the parenthases in the wrong place, but I can't figure it out. =IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15)))))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
Try it like this:
=IF(OR(U14={"ABC","DEF","GEH","IJK","LMN","OPQ"}), HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,0)*U15,0) -- Biff Microsoft Excel MVP "klappin" wrote in message ... Why does this formula not work? If I have a product named ABC, DEF, etc, I want it to use the formula at the end HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for product OPQ. I think I have the parenthases in the wrong place, but I can't figure it out. =IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15)))))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
This is perfect! I've never seen the OR{} before...
Thanks!!! "T. Valko" wrote: Try it like this: =IF(OR(U14={"ABC","DEF","GEH","IJK","LMN","OPQ"}), HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,0)*U15,0) -- Biff Microsoft Excel MVP "klappin" wrote in message ... Why does this formula not work? If I have a product named ABC, DEF, etc, I want it to use the formula at the end HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for product OPQ. I think I have the parenthases in the wrong place, but I can't figure it out. =IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15)))))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "klappin" wrote in message ... This is perfect! I've never seen the OR{} before... Thanks!!! "T. Valko" wrote: Try it like this: =IF(OR(U14={"ABC","DEF","GEH","IJK","LMN","OPQ"}), HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,0)*U15,0) -- Biff Microsoft Excel MVP "klappin" wrote in message ... Why does this formula not work? If I have a product named ABC, DEF, etc, I want it to use the formula at the end HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for product OPQ. I think I have the parenthases in the wrong place, but I can't figure it out. =IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15)))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
