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:

alper

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Dave Patton

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

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

Forum statistics

Threads
1,143,909
Messages
5,721,461
Members
422,363
Latest member
Bogus_Potatoes

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