Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What if < with multiple variables and formulas
I am trying to calculate commission amount based on different gross sales
with different %s paying on increments. Also want to be able to change gross sales variables and %s. Example =IF(N5<=$A$2,N5*$D$2,IF(N5<=$A$3,(N5-$A$2)*$D$3+$F$2,IF(N5<=$A$4,(N5-$A$3)*$D$4+$F$3,IF(N5<=$A$5,(N5-$A$4)*$D$5+$F$4)))) Meaning - N5=gross sales; $A=sales increment; $D=%; $F=previous calc. added Of course, I can only put 8 if statements in the formula. How else can this be calculated with more variables? -- Anna |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What if < with multiple variables and formulas
this looks like a good setup fpr a index match
set up a table with first column =A4,=A3,=A2,0 second column ( N5-$A$4)*$D$5+$F$4,(N5-$A$3)*$D$4+$F$3,(N5-$A$2)*$D$3+$F$2,N5*$D$2 note this is inverted order to go with the less than or equal to aspect =index(second_column_new_table_range,match(N5,firs t_column_new_table_range,-1)) "Anna" wrote: I am trying to calculate commission amount based on different gross sales with different %s paying on increments. Also want to be able to change gross sales variables and %s. Example =IF(N5<=$A$2,N5*$D$2,IF(N5<=$A$3,(N5-$A$2)*$D$3+$F$2,IF(N5<=$A$4,(N5-$A$3)*$D$4+$F$3,IF(N5<=$A$5,(N5-$A$4)*$D$5+$F$4)))) Meaning - N5=gross sales; $A=sales increment; $D=%; $F=previous calc. added Of course, I can only put 8 if statements in the formula. How else can this be calculated with more variables? -- Anna |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What if < with multiple variables and formulas
=SUMPRODUCT( (N5<=$A$2:$A$11)*(N5$A$1:$A$10) * ((N5-$A$1:$A$10)*($D
$2:$D$11)+($F$1:$F$10)) ) I believe this structure is what you want. Please note: - The ranges start on different rows. - The parentheses are important. - A1 and F1 must be 0. (Move your data in A, D, and F downward if you have to, and adjust the formula above to compensate.) - If your final sales increment is in A11, then your final percent is in D11 and final F value is in F11. Each range in the formula must contain the same number of cells (here, 10, but you can extend it or contract it, which I think is what you were asking for.) Hope this helps! - David Hilberg On Jul 17, 7:58 am, Anna wrote: I am trying to calculate commission amount based on different gross sales with different %s paying on increments. Also want to be able to change gross sales variables and %s. Example =IF(N5<=$A$2,N5*$D$2,IF(N5<=$A$3,(N5-$A$2)*$D$3+$F$2,IF(N5<=$A$4,(N5-$A$3)*$D$4+$F$3,IF(N5<=$A$5,(N5-$A$4)*$D$5+$F$4)))) Meaning - N5=gross sales; $A=sales increment; $D=%; $F=previous calc. added Of course, I can only put 8 if statements in the formula. How else can this be calculated with more variables? -- Anna |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What if < with multiple variables and formulas
Anna,
=SUMPRODUCT( (N5<=$A$2:$A$11)*(N5$A$1:$A$10) * ((N5-$A$1:$A$10)*($D$2:$D$11)+($F$1:$F$10)) ) Each range must have the same number of cells. Here it is 10, but you can extend or contract the ranges, which I think is what you were asking for. Note: - A1 and F1 must be 0. - If A11 is your final sales increment, then D11 is your final %, and F10 is the final "extra." - The extra parentheses are important. (If you have column headers in A1 and F1, move your data downward and change the formula appropriately.) Hope this helps, - David Hilberg On Jul 17, 7:58 am, Anna wrote: I am trying to calculate commission amount based on different gross sales with different %s paying on increments. Also want to be able to change gross sales variables and %s. Example =IF(N5<=$A$2,N5*$D$2,IF(N5<=$A$3,(N5-$A$2)*$D$3+$F$2,IF(N5<=$A$4,(N5-$A$3)*$D$4+$F$3,IF(N5<=$A$5,(N5-$A$4)*$D$5+$F$4)))) Meaning - N5=gross sales; $A=sales increment; $D=%; $F=previous calc. added Of course, I can only put 8 if statements in the formula. How else can this be calculated with more variables? -- Anna |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions/Formulas to count multiple variables | Excel Worksheet Functions | |||
multiple variables in sumproduct or if/then formulas | Excel Worksheet Functions | |||
How do I string formulas together in Excel to display variables | New Users to Excel | |||
Using formulas to source a value based on 2 variables | Excel Worksheet Functions | |||
How can I use variables in formulas in VB? | Excel Discussion (Misc queries) |