Multiple Tiered Calculations

A

alper

New Member
Joined
May 5, 2021
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Untitled.png


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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
You can post a concise example of your challenge with the forum's XL2BB.

Review Choose; it may help.
 
Upvote 0
Dave Patton said:
You can post a concise example of your challenge with the forum's XL2BB.

Review Choose; it may help.
Click to expand...
Thanks Dave,

I have created an example mini-sheet which will hopefully help explain the 'challenge'. Normally the 'Categories' and 'Fee rates' tables are on another sheet but I have compiled onto one sheet for the mini-sheet example.

MR_EXCEL_EXAMPLE.xlsx
FGHIJKLMNOPQ
1Sale Price Additional Product EarningsData Validation Options to Select BelowFee payablePlan 1 * Fee Rates
2£ 300.00£ 100.00Plan 3 a£ 48.00FromToRate
305009.0%
4CategoryFee %50010007.0%
5Plan 1 a150050002.0%
6Plan 1 b
7Other Plan 15.0
8Other Plan 26.0
9Other Plan 37.0
10Plan 2 aPlan 2 * Fee Rates
11Plan 2 bFromToRate
12Plan 2 c050011.0%
13Plan 2 d50010002.0%
14Plan 2 e
15Other Plan 49.0
16Other Plan 510.0
17Other Plan 615.0
18Other Plan 714.0
19Other Plan 813.0
20Plan 3 a
21Plan 3 bPlan 3 * Fee Rates
22Plan 3 cFromToRate
23Plan 3 d 060012.0%
24Plan 3 e60050002.0%
25Plan 3 f
26Plan 3 g
27Plan 4 h
Select
Cell Formulas
RangeFormula
I2I2=IF(AND(IFERROR(VLOOKUP(H2,$L$5:$M$27,2,FALSE),0)>0, F2>0), VLOOKUP(H2,$L$5:$M$27,2,FALSE)*(F2+G2) / 100, IF(AND(F2>0,OR(H2=$L$4,H2=$L$5,H2=$L$6)),SUMPRODUCT((F2+G2<=$P$3:$P$5)*(F2+G2>$O$3:$O$5)*(F2+G2-$O$3:$O$5)*$Q$3:$Q$5)+SUMPRODUCT(((F2+G2>$P$3:$P$5)*($P$3:$P$5-$O$3:$O$5))*$Q$3:$Q$5), IF(AND(F2>0,OR(H2=$L$10,H2=$L$11,H2=$L$12,H2=$L$13,H2=$L$14)),SUMPRODUCT((F2+G2<=$P$12:$P$13)*(F2+G2>$O$12:$O$13)*(F2+G2-$O$12:$O$13)*$Q$12:$Q$13)+SUMPRODUCT(((F2+G2>$P$12:$P$13)*($P$12:$P$13-$O$12:$O$13))*$Q$12:$Q$13), IF(AND(F2>0,OR(H2=$L$20,H2=$L$21,H2=$L$22,H2=$L$23,H2=$L$24,H2=$L$25,H2=$L$26,H2=$L$27)),SUMPRODUCT((F2+G2<=$P$23:$P$24)*(F2+G2>$O$23:$O$24)*(F2+G2-$O$23:$O$24)*$Q$23:$Q$24)+SUMPRODUCT(((F2+G2>$P$23:$P$24)*($P$23:$P$24-$O$23:$O$24))*$Q$23:$Q$24), 0))))
Cells with Data Validation
CellAllowCriteria
H2:H16List=$L$5:$L$27



To add a little more clarity:

- The vlookup statement takes care of any category which does NOT have a tiered fee rate. In this case those categories are named 'Other Plan *'.
- These were simple to do since the vlookup finds the corresponding fixed fee % in column 2 and calculates the required sum.
- I'm not able to figure out how to do that with the tiered fee rate categories since they are within the specific tables.
- All categories starting with ' Plan ...' are the tiered fee rates which currently use the SUMPRODUCT function to calculate the tiered fee rates.

- Id like to be able to reduce the number of IF statements for the tiered fee rate IF statements as my working sheet has many of these nested IF statements which is inefficient.
- In my view, it would have been nice to have 1 or two vlookups which would cover the requirements but I'm really not sure what's best to do here.

Thanks in advance,
 
Upvote 0
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))
 
Upvote 0
Solution
Dave Patton said:
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))
Click to expand...

I'm not sure I can get this to work. Especially with the array part. See XL2BB. I must be doing something wrong with the array part.
Ideally I would be able to update the category names in one place (like with the 'other plan' categories)

MR_EXCEL_EXAMPLE.xlsx
EFGHIJKLMNOPQ
1Sale Price Additional Product EarningsData Validation Options to Select BelowFee payablePlan 1 * Fee Rates
2£ 300.00£ 100.00Plan 3 a£ 48.00FromToRate
3£ 300.00£ 100.00Plan 2 b#N/A05009.0%
4CategoryFee %50010007.0%
5Plan 1 a150050002.0%
6Plan 1 b
7Other Plan 15.0
8Other Plan 26.0
9Other Plan 37.0
10Plan 2 aPlan 2 * Fee Rates
11Plan 2 bFromToRate
12Plan 2 c050011.0%
13Plan 2 d50010002.0%
14Plan 2 e
15Other Plan 49.0
16Other Plan 510.0
17Other Plan 615.0
18Other Plan 714.0
19Sale Price Additional Product EarningsData Validation Options to Select BelowFee payableOther Plan 813.0
203002000Plan 3 aPlan 3 a
21Total£ 2,000.00Plan1125#VALUE!Plan 3 bPlan 3 * Fee Rates
22Plan 3 cFromToRate
23Plan 3 d 060012.0%
24Plan 3 e60050002.0%
25Plan 3 f
26Plan 3 g
27Plan 4 h
Select
Cell Formulas
RangeFormula
I2I2=IF(AND(IFERROR(VLOOKUP(H2,$L$5:$M$27,2,FALSE),0)>0, F2>0), VLOOKUP(H2,$L$5:$M$27,2,FALSE)*(F2+G2) / 100, IF(AND(F2>0,OR(H2=$L$4,H2=$L$5,H2=$L$6)),SUMPRODUCT((F2+G2<=$P$3:$P$5)*(F2+G2>$O$3:$O$5)*(F2+G2-$O$3:$O$5)*$Q$3:$Q$5)+SUMPRODUCT(((F2+G2>$P$3:$P$5)*($P$3:$P$5-$O$3:$O$5))*$Q$3:$Q$5), IF(AND(F2>0,OR(H2=$L$10,H2=$L$11,H2=$L$12,H2=$L$13,H2=$L$14)),SUMPRODUCT((F2+G2<=$P$12:$P$13)*(F2+G2>$O$12:$O$13)*(F2+G2-$O$12:$O$13)*$Q$12:$Q$13)+SUMPRODUCT(((F2+G2>$P$12:$P$13)*($P$12:$P$13-$O$12:$O$13))*$Q$12:$Q$13), IF(AND(F2>0,OR(H2=$L$20,H2=$L$21,H2=$L$22,H2=$L$23,H2=$L$24,H2=$L$25,H2=$L$26,H2=$L$27)),SUMPRODUCT((F2+G2<=$P$23:$P$24)*(F2+G2>$O$23:$O$24)*(F2+G2-$O$23:$O$24)*$Q$23:$Q$24)+SUMPRODUCT(((F2+G2>$P$23:$P$24)*($P$23:$P$24-$O$23:$O$24))*$Q$23:$Q$24), 0))))
I3I3=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))
G21G21=F20:G20
I21I21=CHOOSE(MATCH(H21,{"Plan1";"Plan2";"Plan3"},0),SUMPRODUCT(--(G21>{0;500;1500}),G21-{0;500;1500},{0.09;-0.02;-0.05}),SUMPRODUCT(--(G21>{0;500}),G21-{0;500},{0.11;-0.09}),SUMPRODUCT(--(G21>{0;600}),G21-{0;600},{0.12;-0.1}))
J21J21=CHOOSE(MATCH(H21,{"Plan1";"Plan2";"Plan3"},0),SUMPRODUCT(--(G21>aB_1),G21-aB_1,ar_1),SUMPRODUCT(--(G21>aB_2),G21-aB_2,ar_2),SUMPRODUCT(--(G21>aB_3),G21-aB_3,aR_3))
Named Ranges
NameRefers ToCells
aB_1=Select!$O$3:$P$5J21, I2:I3
aB_2=Select!$O$12:$P$13J21, I2:I3
aB_3=Select!$O$23:$P$24J21, I2:I3
ar_1=Select!$Q$3:$Q$5J21, I2:I3
ar_2=Select!$Q$12:$Q$13J21, I2:I3
aR_3=Select!$Q$23:$Q$24J21, I2:I3
Cells with Data Validation
CellAllowCriteria
H2:H16List=$L$5:$L$27
 
Upvote 0
- try pasting my post on a clean sheet and review the formula with Excel's Formula Evaluate
- try working with a simple model and follow the logic and then build up

Your post
Match H3 should be match I3

It is not clear if you named the Brackets and Rate Differentials
I named the actual arrays for Plan1 aB_1 ={0;500;1500} aR_1={0.09;-0.02;-0.05}
You reference G3 so the amount is the 300 and the formula yields 27

T202105a.xlsm
GHIJ
1Sale Price Additional Product EarningsData Validation Options to Select BelowFee payable
2300100Plan 3 a
3300100Plan127
1f
Cell Formulas
RangeFormula
J3J3=CHOOSE(MATCH(I3,{"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))
 
Upvote 0
Ignore the comment about H3 reference; see below.

T202105a.xlsm
FGHI
1Sale Price Additional Product EarningsData Validation Options to Select BelowFee payable
2300100Plan 3 a
3300100Plan127
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))
 
Upvote 0
Dave Patton said:
Ignore the comment about H3 reference; see below.

T202105a.xlsm
FGHI
1Sale Price Additional Product EarningsData Validation Options to Select BelowFee payable
2300100Plan 3 a
3300100Plan127
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))
Click to expand...

I beleive I created named ranges (see above table) for each of your examples. Is there another way to do this?
 
Upvote 0
You do not appear to have defined the correct rate differentials for the SumProduct.
check post #4. the long post in I3 shows the arrays. The named arrays are used in J3.

Try your SumProduct formula and/or check the formula with Excel's Formula Evaluate.
Try the suggestions.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
424
MissDB101
M
Sangar_338
  • Solved
Methods for Quartile Calculation with Multiple Criteria
Replies
5
Views
310
Fluff
Fluff
K
  • Solved
Custom Formula with Specific Functions
Replies
8
Views
156
dreid1011
dreid1011
A
  • Solved
Problem with multiple IFS, IF and AND in long formula
Replies
2
Views
121
arendberg
A
B
  • Question
Auto calculation of Percentage, Position and Grade
Replies
1
Views
329
awoohaw
awoohaw

Forum statistics

Threads
1,214,583
Messages
6,120,377
Members
448,955
Latest member
BatCoder

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top