Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If statements (of course) {running excel 2003}
There has to be an easier way to do this.
I have a commission worksheet. In column A I have the revenue. In column B I'd like to display the payout. For instance, if cell A1 is 0-14999, B1 should display A1*0% if cell A1 is 15-19999, B1 should display A1*10% and so on, including a total of 7 tiers or variables. Right now I have a terribly long 'If' statement and it is cumbersome to make any changes. I am -okay- at VB, but I know nothing about pivot tables, FYI. Thank you so much! Jacob |
#2
|
|||
|
|||
Create a 2xn table with the values 0,0;15000,10%;etc in say M1:N10
and then use =VLOOKUP(F1,M1:N10,2,FALSE) to get the percentage which you multiply by the amount. -- HTH Bob Phillips "jacob" wrote in message oups.com... There has to be an easier way to do this. I have a commission worksheet. In column A I have the revenue. In column B I'd like to display the payout. For instance, if cell A1 is 0-14999, B1 should display A1*0% if cell A1 is 15-19999, B1 should display A1*10% and so on, including a total of 7 tiers or variables. Right now I have a terribly long 'If' statement and it is cumbersome to make any changes. I am -okay- at VB, but I know nothing about pivot tables, FYI. Thank you so much! Jacob |
#3
|
|||
|
|||
You can use the VLOOKUP function to return the appropriate
percentage. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "jacob" wrote in message oups.com... There has to be an easier way to do this. I have a commission worksheet. In column A I have the revenue. In column B I'd like to display the payout. For instance, if cell A1 is 0-14999, B1 should display A1*0% if cell A1 is 15-19999, B1 should display A1*10% and so on, including a total of 7 tiers or variables. Right now I have a terribly long 'If' statement and it is cumbersome to make any changes. I am -okay- at VB, but I know nothing about pivot tables, FYI. Thank you so much! Jacob |
#4
|
|||
|
|||
Bob,
thank you for the reply. i can't get it to work however. In the M column, how do I enter the dollar range? 0-15000 or 0;15000 or exactly as you have it below: 0,0;15000? I've tried it all sorts of ways and all i get is "n/a" You're saying I need a two column table, first colum with the ranges, such as 0-15000 15001-20000 20001-30000, etc... and in the second column (n) i put the corresponding percent: 0% 10% 15%, etc... correct? If so, i must be physically entering something in the wrong format. Bob Phillips wrote: Create a 2xn table with the values 0,0;15000,10%;etc in say M1:N10 and then use =VLOOKUP(F1,M1:N10,2,FALSE) to get the percentage which you multiply by the amount. -- HTH Bob Phillips "jacob" wrote in message oups.com... There has to be an easier way to do this. I have a commission worksheet. In column A I have the revenue. In column B I'd like to display the payout. For instance, if cell A1 is 0-14999, B1 should display A1*0% if cell A1 is 15-19999, B1 should display A1*10% and so on, including a total of 7 tiers or variables. Right now I have a terribly long 'If' statement and it is cumbersome to make any changes. I am -okay- at VB, but I know nothing about pivot tables, FYI. Thank you so much! Jacob |
#5
|
|||
|
|||
Not quite, I am saying you need a two column table, first column
0 15000 20000 etc, second column 0% 10% 15% etc the first column values are the start point, the second is the percentage to apply from that start point. -- HTH Bob Phillips "jacob" wrote in message oups.com... Bob, thank you for the reply. i can't get it to work however. In the M column, how do I enter the dollar range? 0-15000 or 0;15000 or exactly as you have it below: 0,0;15000? I've tried it all sorts of ways and all i get is "n/a" You're saying I need a two column table, first colum with the ranges, such as 0-15000 15001-20000 20001-30000, etc... and in the second column (n) i put the corresponding percent: 0% 10% 15%, etc... correct? If so, i must be physically entering something in the wrong format. Bob Phillips wrote: Create a 2xn table with the values 0,0;15000,10%;etc in say M1:N10 and then use =VLOOKUP(F1,M1:N10,2,FALSE) to get the percentage which you multiply by the amount. -- HTH Bob Phillips "jacob" wrote in message oups.com... There has to be an easier way to do this. I have a commission worksheet. In column A I have the revenue. In column B I'd like to display the payout. For instance, if cell A1 is 0-14999, B1 should display A1*0% if cell A1 is 15-19999, B1 should display A1*10% and so on, including a total of 7 tiers or variables. Right now I have a terribly long 'If' statement and it is cumbersome to make any changes. I am -okay- at VB, but I know nothing about pivot tables, FYI. Thank you so much! Jacob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
quatro pro to excel 2003 | Excel Discussion (Misc queries) | |||
Problems using Excel 2000 to open/save file saved in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 crashes loading excel files created Excel 2000 | Excel Discussion (Misc queries) | |||
Microsoft Excel 2003 and Hyperion Retrieve with Excel 2000. | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) |