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

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

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

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
---------------
Jacob Skaria


"Keith D.B." wrote:

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

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

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
--
Regards
Roger Govier

"Keith D.B." wrote in message
...
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

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

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
---------------
Jacob Skaria


"Keith D.B." wrote:

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

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

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.

"Roger Govier" wrote:

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
--
Regards
Roger Govier

"Keith D.B." wrote in message
...
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