What if <> with multiple variables and formulas

[Guest]

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?

 

[Guest]

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,first_column_new_table_range,-1))

 

[D Hilberg]

=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

 

[D Hilberg]

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