I need a formula.........

[Keith D.B.]

Im trying to create a worksheet to let me enter a dollar amount in a cell and
have Excel figure where it falls between and puts the correct dollar amont in
another cell. Basicaslly this is a commisson grid for my employees. I want to
take employee A's sheet and put a date and sales amount and Excel figure his
pay.
Here's an example:
$139.99 & up = $450.00
$119.99 – $139.98 = $370.00
$99.99 – $119.98 = $320.00
$70.01 – $99.98 = $315.00
$50.01 – $70 =$300.00
$40.01 – $50 =$275.00
$30.01 – $40 =$250.00
$0 – $30 =$190.0

 

[Jacob Skaria]

Suppose you have the below list in Sheet2. in Col A and ColB. In Sheet1 A1
you have the value to be looked up say 30.01
In Sheet1 B1 enter formula
=VLOOKUP(A1,Sheet2!$A$1:$B$8,2,TRUE)

Sheet2 ColA ColB
$0 $190.00
$30.01 $250.00
$40.01 $275.00
$50.01 $300.00
$70.01 $315.00
$99.99 $320.00
$119.99 $370.00
139.99 $450.00


If this post helps click Yes

 

[Roger Govier]

Hi Keith

Turn your table up the other way.
For example in Sheet2 enter in columns A and B
0 190
30.01 250
40.01 275
50.01 300
70.01 315
99.99 320
119.99 370
139.99 450

Then on sheet1 supposing you are entering the dollar amount in cell B2, in
cell C2 enter
=IF(B2="","",VLOOKUP(B2,Sheet2!$A$1:$B$8,2))
copy down as required

 

[Jacob Skaria]

You can also try the below using LOOKUP..(just a formula)

=IF(A1="","",LOOKUP(A1,{0,30.01,40.01,50.01,70.01,99.99,119.99,139.99},{190,250,275,300,315,320,370,450}))

If this post helps click Yes

 

[Keith D.B.]

Thank you both for your help. Jacobs lookup formula seems to be the only one
I could get to work. But thanks again to both.