IF/THEN help
Posted by Mike on October 19, 2001 10:29 AM
I need help figuring this formula out.
If cell A is 90% - 97% then Cell B should populate 100%If cell A is 97.1% - 99.9% then Cell B should populate 125%
If cell A is 100% then Cell B should populate 150%
Posted by Mark W. on October 19, 2001 10:39 AM
=VLOOKUP(A1,{0.9,1;0.971,1.25;1,1.5},2)
Posted by Don C on October 19, 2001 10:42 AM
Try a lookup table
You could use some IF statements, but I'd just make a little lookup table with the upper values (97.0,97.9,100 in column D and 100, 125, and 150 in column E). Place the table on an unused section of the worksheet or on a separate worksheet and refer to it with VLOOKUP in your column B.
Posted by Don C on October 19, 2001 10:43 AM
Well, I learned something today! Did not know that you could "insert" a lookup table within the VLOOKUP function.
Posted by Mark W. on October 19, 2001 10:47 AM
Yep, in fact....
You could also assign ={0.9,1;0.971,1.25;1,1.5} to
a defined name (e.g., Lookup_Table) and use...
=VLOOKUP(A1,Lookup_Table,2)
This allows you to easily change the table
values in one place and know that all formulas
have been revised. This is a good configuation
managment (CM) practice.
