| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sliding scale commission percentages formulas
How would I make this table apply to each cell that contains my different
sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sliding scale commission percentages formulas
Hi,
You need a table very similar to the one you posted below looking like this:- Col A Col B 0 1.75 31 3.5 61 12.5 81 17.5 101 21.7 105 23.45 109 25.2 113 25.9 117 27.3 As you will see I haven't replicated all your data but you get the idea. The lets say you are trying to the appropriate rate for a value in D1 you would use this formula:- =VLOOKUP(D1,A1:B9,2) Mike "Margs" wrote: How would I make this table apply to each cell that contains my different sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sliding scale commission percentages formulas
Hi Margs,
With your sales achieved in A1 put this in B1. =LOOKUP(A1,{0,31,61,81,101,105,109,113,117,121,125 ,131,141,151,161,171},{1.75,3.5,12.5,17.5,21.7,23. 45,25.2,25.9,27.3,29.75,32.2,36.4,37.8,42,46.2,51. 1}) Watch out for the line wrap if you copy from this post. HTH Martin "Margs" wrote in message ... How would I make this table apply to each cell that contains my different sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sliding scale commission percentages formulas
=IF(A1="new
business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,31,61, 81,101,105,109,113,117,121,124,131,141,151,161,171 },{1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27.3,29 .75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Margs" wrote: How would I make this table apply to each cell that contains my different sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sliding scale commission percentages formulas
Correction:
=IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,1,31,6 1,81,101,105,109,113,117,121,124,131,141,151,161,1 71},{0,1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27. 3,29.75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Teethless mama" wrote: =IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,31,61, 81,101,105,109,113,117,121,124,131,141,151,161,171 },{1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27.3,29 .75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Margs" wrote: How would I make this table apply to each cell that contains my different sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sliding scale commission percentages formulas
Hi Teethless mama,
Why would you divide by 100? Format the cell as custom 0.00"%" Regards Martin "Teethless mama" wrote in message ... Correction: =IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,1,31,6 1,81,101,105,109,113,117,121,124,131,141,151,161,1 71},{0,1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27. 3,29.75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Teethless mama" wrote: =IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,31,61, 81,101,105,109,113,117,121,124,131,141,151,161,171 },{1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27.3,29 .75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Margs" wrote: How would I make this table apply to each cell that contains my different sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sliding scale commission percentages formulas
Divide by 100 will give the right result when OP format as percentage
eg. 1.75/100 format as percentage the result 1.75% if you take 1.75 format as percentage the result 175.00% Not everyone knows how to do custom format 0.00"%" without providing the instruction. Thanks for your concern. "MartinW" wrote: Hi Teethless mama, Why would you divide by 100? Format the cell as custom 0.00"%" Regards Martin "Teethless mama" wrote in message ... Correction: =IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,1,31,6 1,81,101,105,109,113,117,121,124,131,141,151,161,1 71},{0,1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27. 3,29.75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Teethless mama" wrote: =IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,31,61, 81,101,105,109,113,117,121,124,131,141,151,161,171 },{1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27.3,29 .75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Margs" wrote: How would I make this table apply to each cell that contains my different sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sliding scale commission percentages formulas
Ah, now I see what you were doing. I never use the percentage format
in Excel, or the percentage key on a calculator for that matter. Regards Martin "Teethless mama" wrote in message ... Divide by 100 will give the right result when OP format as percentage eg. 1.75/100 format as percentage the result 1.75% if you take 1.75 format as percentage the result 175.00% Not everyone knows how to do custom format 0.00"%" without providing the instruction. Thanks for your concern. "MartinW" wrote: Hi Teethless mama, Why would you divide by 100? Format the cell as custom 0.00"%" Regards Martin "Teethless mama" wrote in message ... Correction: =IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,1,31,6 1,81,101,105,109,113,117,121,124,131,141,151,161,1 71},{0,1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27. 3,29.75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Teethless mama" wrote: =IF(A1="new business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,31,61, 81,101,105,109,113,117,121,124,131,141,151,161,171 },{1.75,3.5,12.5,17.5,21.7,23.45,25.2,25.9,27.3,29 .75,32.2,36.4,37.8,42,46.2,51.1}/100),"")) "Margs" wrote: How would I make this table apply to each cell that contains my different sales % figures in order to see what my Rate Earned would be? I am a total novice. Thank you in advance. Sales % Achieved Rate Earned 0 but < or = 30% 1.75% 30 but < or = 60% 3.50% 60 but< or = 80% 12.50% 80 but < or =100% 17.50% 100 but < or =104% 21.70% 104 but < or =108% 23.45% 108 but < or =112% 25.20% 112 but < or =116% 25.90% 116 but < or =120% 27.30% 120 but < or =124% 29.75% 124 but < or =130% 32.20% 130 but < or =140% 36.40% 140 but < or =150% 37.80% 150 but < or =160% 42.00% 160 but < or =170% 46.20% 170% 51.10% new business 84.00% |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sliding scale Question | Excel Worksheet Functions | |||
| Calculating a sliding scale | Excel Worksheet Functions | |||
| Sliding Scale Percentage via IF? | New Users to Excel | |||
| Percentage Commission on a sliding scale. | Excel Discussion (Misc queries) | |||
| calculating commission on sliding scale | New Users to Excel | |||
Linear Mode
