A Rep Comission when the percentage is is splied by scales

[drom]

Hi and Thanks in advance

I do know how to do this using a UDF and/or a Macro, I would like to know if is possible using maeby a Sumproduct or...


I have a Table named Table1
with 2 Columns

• A1: Amount
• B1 Percentage

This table has 6rows, in Column A

• A1: Amount
• A2: 0
• A3: 4000
• A4: 10000
• A5: 45000
• A6: 60000

This table has 6rows, in Column B

• B1: Percentage
• B2: 20%
• B3: 22%
• B4: 31%
• B5: 36%
• B6: 45%

At present the Table has 5 different Percentages, but who knows maeby next month I may have 4 and/or 7, this is why I cant use nested Ifs

Now my question:

If a Salesman does a Sale of 100.000
I would like to know how much is it going to get if he gets for the first 4000 a 20%
for the next 6000 22%
for the next 35000 31%
and so on

So I would like to have a formula base on the Tables Amount and Percentages and the Reps Sales Amount

The Table now is not dinamic, but could have more rows

ps: I do not want to use a UDF and/or a Macro

 

[AlanY]

is this work for you? you can adjust the table/formula to suit

Book1
	A	B	C	D	E	F
1	Amount		Percentage		Amount	Payout
2	0	20%	20%		100,000	36,370
3	4001	22%	2%
4	10001	31%	9%
5	45001	36%	5%
6	60001	45%	9%
167

Cell Formulas
Range	Formula
C2	=B2-B1
F2	=SUMPRODUCT((E2 > A2:A6) * (E2 - A2:A6) * C2:C6)