PDA

View Full Version : Solved: Greater, Less than formula



klutz
09-17-2009, 10:10 AM
Hi,

I have this table with min and max amounts that requires a fixed amount when when the condition is met.

How do I write a formula for this. If result is >$0 but < $100 = $15 and so on...

I can't get it right..

Min Max Equals
$0 $100 = $15
$101 $200 = $20
$201 $1,000 = $ 30
$1,001 $3,500 = $50
$3,501 $10,000 = $75
$10,001 $50,000 = $120
$50,001 $100,000 = $180
$100,001 and over = $230

http://www.excelforum.com/images/misc/progress.gif

p45cal
09-17-2009, 10:49 AM
create a table:


0 15
101 20
201 30
1001 50
3501 75
10001 120
50001 180
100001 230


then the formula:
=VLOOKUP(D1,$A$1:$B$8,2)

where D1 was the value being looked up, $A$1:$B$8 is the location of the table. (this was the formula in cell E1 of the picture)1814