Multiple Tiered Calculations

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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can post a concise example of your challenge with the forum's XL2BB.

Review Choose; it may help.
 

alper

New Member
Joined
May 5, 2021
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You can post a concise example of your challenge with the forum's XL2BB.

Review Choose; it may help.
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,
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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))
 
Solution

alper

New Member
Joined
May 5, 2021
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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))

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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
- 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))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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))
 

alper

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

I beleive I created named ranges (see above table) for each of your examples. Is there another way to do this?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,143,907
Messages
5,721,436
Members
422,362
Latest member
elliotpat

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
Top