Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|