Multiple Tiered Calculations

[alper]

Hi,

I have been using SUMPRODUCT to achieve 'tiered' calculations to calculate fees depending on how much has been spent. This works great for me right now however its a very static formula and I have several tables in which I refer to in several nested IF statements which is clunky and Excel file is growing in size

I currently have a cell with data validation drop down list of the different categories (which determines the fee % and ranges) and I would like it that when you select a category from the data validation options, it then looks at the correct fee table and calculates the correct fees.

Currently, I have lots of these nested IF statements in place that look at sheet 'X' for the category list and fee tables:

IF(AND(H7=X!$A$38,F7>0), SUMPRODUCT((F2+G2<=X!$E$39:$E$40)*(F2+G2>X!$D$39:$D$40)*(F2+G2-X!$D$39:$D$40)*X!$F$39:$F$40)+SUMPRODUCT(((F2+G2>X!$E$39:$E$40)*(X!$E$39:$E$40-X!$D$39:$D$40))*X!$F$39:$F$40),

Where X is the sheet name which contains:
- A list of fee categories i.e. Plan A, Plan B etc (e.g. A38 is one of those categories)
- Several tables (like below) each with different tiered fee % and ranges (in picture)
H7 is the cell which contains the drop-down list of options.
F7 Contains the sale cost

What I'd like to be able to do is reduce the number of nested IF Statements I have (over 20) and instead, perhaps use something like VLOOKUP (or some other function), so that when I select the category from the drop-down list, it looks at the selected option and runs the SUMPRODUCT calculation from the relevant fee table.

I hope this makes sense, I've been trying to figure a way to do this but no luck so far.

Thanks!

 

[alper]

The following shows a variety of ways of building the Sumproduct formula.
Review the alternatives and use the alternative that you prefer.

T202105a.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Sale Price	Additional Product Earnings	Data Validation Options to Select Below	Fee payable						Plan 1 * Fee Rates			Rate Differential
2		300	100	Plan 3 a							From	To
3		300	100	Plan1	27	36					0	500	0.09	0.09
4						36		Category	Fee %		500	1000	0.07	-0.02
5		300	100	400		36		Plan 1 a			1500	5000	0.02	-0.05
6		300	100	400		36		Plan 1 b
7		300	100	400		36		Other Plan 1	5
1f

Cell Formulas
Range	Formula
I3	I3	=CHOOSE(MATCH(H3,{"Plan1";"Plan2";"Plan3"},0),SUMPRODUCT(--(F3>aB_1),F3-aB_1,aR_1),SUMPRODUCT(--(F3>aB_2),F3-aB_2,ar_2),SUMPRODUCT(--(F3>aB_3),F3-aB_3,aR_3))
R3:R5	R3	=Q3-N(Q2)
H5:H7	H5	=F5+G5
J3	J3	=SUMPRODUCT(--(F3+G3>aB_1),(F3+G3)-aB_1,aR_1)
J4	J4	=SUMPRODUCT(--(F3+G3>O3:O5),(F3+G3)-O3:O5,Q3:Q5-Q2:Q4)
J5	J5	=SUMPRODUCT(--(H5>{0;500;1500}),H5-{0;500;1500},{0.09;-0.02;-0.05})
J6	J6	=SUMPRODUCT(--(H6>{0;500;1500}),H6-{0;500;1500},R3:R5)
J7	J7	=SUMPRODUCT(--(H6>rB),H6-rB,rR)

Named Ranges
Name	Refers To	Cells
'1f'!rR	='1f'!$R$3:$R$5	J6:J7

Thanks for your response,

Apologies I think I am now becoming confused with the named arrays part. I think the problem I am likely to still face is that my working sheet has many different fee brackets and rates.

Are you saying in order for J3 (in your example that is) to work, I3 is required, or is this simply another way to achieve it?

 

[Dave Patton]

I3 just includes the income form F3.
J3 and the other alternatives includes F3 and G3

The 5 alternatives just show different ways of including the relevant data for the SumProduct.

Did you review the formulas with Excel's Formula Evaluate?

 

[alper]

I3 just includes the income form F3.
J3 and the other alternatives includes F3 and G3

The 5 alternatives just show different ways of including the relevant data for the SumProduct.

Did you review the formulas with Excel's Formula Evaluate?

Thanks Dave,

I will have a look over the next few days.

Just to be sure, this will also take into account the 'fixed' rate categories (which do not have tiered calculations). In my example these are named Other Plan X?

 

[alper]

consider the following
With J3, the arrays of Brackets data are named like aB_1 and the arrays of rate differentials are named like aR_1

T202105a.xlsm
	F	G	H	I	J
1	Sale Price	Additional Product Earnings	Data Validation Options to Select Below	Fee payable
2	300	100	Plan 3 a
3	Total	400	Plan3	48	48
4
1d

Cell Formulas
Range	Formula
G3	G3	=F2+G2
I3	I3	=CHOOSE(MATCH(H3,{"Plan1";"Plan2";"Plan3"},0),SUMPRODUCT(--(G3>{0;500;1500}),G3-{0;500;1500},{0.09;-0.02;-0.05}),SUMPRODUCT(--(G3>{0;500}),G3-{0;500},{0.11;-0.09}),SUMPRODUCT(--(G3>{0;600}),G3-{0;600},{0.12;-0.1}))
J3	J3	=CHOOSE(MATCH(H3,{"Plan1";"Plan2";"Plan3"},0),SUMPRODUCT(--(G3>aB_1),G3-aB_1,aR_1),SUMPRODUCT(--(G3>aB_2),G3-aB_2,aR_2),SUMPRODUCT(--(G3>AB_3),G3-AB_3,aR_3))

Hi Dave,

I've some more time to tinker with the formula and use formula evaluate to help understand it more and It appears to be a solution to the challenge at hand here.

Thanks again for your time and expertise

 

[Dave Patton]

Thanks for the feedback.
The following points depend on your data.

With appropriate edits, the match part will work. You will have to determine if the results are accurate with your data.
You can use Vlookup and Match formulas with exact match. Ensure that you use the appropriate functions and parameters for your data.

Additional examples are shown below

T202105a.xlsm
	E	F	G	H	I
1	Sale Price	Additional Earnings	Total	Plan for Fee	Fee payable
2	1900	100	2000	Other Plan 4	180
3	1900	100	2000	Plan1	125
4	1900	100	2000	Other Plan 6	300
5
1dd

Cell Formulas
Range	Formula
G2:G4	G2	=E2+F2
I2	I2	=CHOOSE(MATCH(H2,{"Other Plan";"Plan1";"Plan2";"Plan3"},1),LOOKUP(H2,aL)*G2,SUMPRODUCT(--(G2>aB_1),G2-aB_1,aR_1),SUMPRODUCT(--(G2>aB_2),G2-aB_2,ar_2),SUMPRODUCT(--(G2>aB_3),G2-aB_3,aR_3))
I3:I4	I3	=IF(LEFT(H3,10)="Other Plan",LOOKUP(H3,aL)*G3,CHOOSE(MATCH(H3,{"Plan1";"Plan2";"Plan3"},0),SUMPRODUCT(--(G3>aB_1),G3-aB_1,aR_1),SUMPRODUCT(--(G3>aB_2),G3-aB_2,ar_2),SUMPRODUCT(--(G3>aB_3),G3-aB_3,aR_3)))