# Multiple Tiered Calculations

#### alper

##### New Member
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!

Last edited by a moderator:

#### alper

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

T202105a.xlsm
EFGHIJKLMNOPQR
1Sale Price Additional Product EarningsData Validation Options to Select BelowFee payablePlan 1 * Fee RatesRate Differential
2300100Plan 3 aFromTo
3300100Plan1273605000.090.09
436CategoryFee %50010000.07-0.02
530010040036Plan 1 a150050000.02-0.05
630010040036Plan 1 b
730010040036Other Plan 15
1f
Cell Formulas
RangeFormula
I3I3=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:R5R3=Q3-N(Q2)
H5:H7H5=F5+G5
J3J3=SUMPRODUCT(--(F3+G3>aB_1),(F3+G3)-aB_1,aR_1)
J4J4=SUMPRODUCT(--(F3+G3>O3:O5),(F3+G3)-O3:O5,Q3:Q5-Q2:Q4)
J5J5=SUMPRODUCT(--(H5>{0;500;1500}),H5-{0;500;1500},{0.09;-0.02;-0.05})
J6J6=SUMPRODUCT(--(H6>{0;500;1500}),H6-{0;500;1500},R3:R5)
J7J7=SUMPRODUCT(--(H6>rB),H6-rB,rR)
Named Ranges
NameRefers ToCells
'1f'!rR='1f'!\$R\$3:\$R\$5J6:J7

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?

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Dave Patton

##### Well-known Member
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

##### New Member
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

##### New Member
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
FGHIJ
1Sale Price Additional Product EarningsData Validation Options to Select BelowFee payable
2300100Plan 3 a
3Total400Plan34848
4
1d
Cell Formulas
RangeFormula
G3G3=F2+G2
I3I3=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}))
J3J3=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

##### Well-known Member
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.

T202105a.xlsm
EFGHI
1Sale Price Additional EarningsTotalPlan for FeeFee payable
219001002000Other Plan 4180
319001002000Plan1125
419001002000Other Plan 6300
5
1dd
Cell Formulas
RangeFormula
G2:G4G2=E2+F2
I2I2=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:I4I3=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)))

Replies
3
Views
397
Replies
4
Views
556
Replies
34
Views
807
Replies
2
Views
141
Replies
6
Views
183

1,141,075
Messages
5,704,162
Members
421,331
Latest member
imdumb

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back