get-digital-help.com

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This kind of formula is often used to calculate commissions, bonuses, pricing, fees or charges, discounts, volume pricing, volume rebate, and performance incentives.

The table shows the different thresholds or levels that a specific percentage applies to, if an amount is larger than the first level then multiple calculations are necessary in order to calculate the total. The formula takes care of these additional calculations as well, no need for helper-columns.

Formula in cell C10:

There are four different values B10:B13 in order to demonstrate how the total changes based on the percentage and levels used, I will explain below how each value is calculated.

Explaining calculations

If $65 000 is used then the formula returns $2 925, here is how that number is calculated: 65 000 * 4.5% = 2 925

$125 000 returns $5 000. 100 000 * 4.5% = 4 500. 25 000 * 2% = 500. 4 500 + 500 = 5000.

$280 000 returns $7 860. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 30 000 * 1.2% = 360. 4 500 + 3 000 + 360 = 7 860

$540 000 returns $10 580. 100 000 * 4.5% = 4 500. 150 000 * 2% = 3 000. 250 000 * 1.2% = 3 000. 40 000 * 0.2% = 80. 4 500 + 3 000 + 3 000 + 80 = 10 580.

Explaining formula in cell C10

The formula contains two SUMPRODUCT functions, the first one calculates the result based on the amount that is above a specific level and the corresponding percentage.

The second SUMPRODUCT function calculates tiered values based on the amounts up to the reached level and their corresponding percentages. The formula then adds those numbers and returns the total.

Step 1 - First SUMPRODUCT function

The first two logical expressions determine which tier the amount in cell B10 reaches.

(B10<$C$4:$C$7)* (B10>$B$4:$B$7)

becomes

(65000<{100000; 250000; 500000; 999999})*(65000>{0; 100000; 250000; 500000})

becomes

({TRUE; TRUE; TRUE; TRUE})*({TRUE; FALSE; FALSE; FALSE})

and returns {1; 0; 0; 0}.

Step 2 - Subtract value with tier levels

The parentheses lets you determine the order of operation, we want to subtract before we multiply the arrays in order to get the correct result we are looking for.

(B10- $B$4:$B$7)

becomes

(65000 - {0; 100000; 250000; 500000})

and returns {65000; -35000; -185000; -435000}.

Step 3 - Multiply arrays

(B10<$C$4:$C$7)*(B10>$B$4:$B$7)*(B10- $B$4:$B$7)* $D$4:$D$7

becomes

{1; 0; 0; 0}*{65000; -35000; -185000; -435000}*{0.045;0.02;0.012;0.002}

and returns {2925; 0; 0; 0}.

Step 4 - Add values

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)

becomes

SUMPRODUCT({2925;0;0;0})

and returns 2925.

Step 5 - Second SUMPRODUCT function

((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7

becomes

(65000>{100000;250000;500000;999999})*({100000;150000;250000;499999})*{0.045;0.02;0.012;0.002}

and returns {0;0;0;0}.

Step 6 - Add values in array

SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

becomes

SUMPRODUCT({0;0;0;0})

and returns 0.

Step 7 - Add numbers

SUMPRODUCT((B10<$C$4:$C$7)* (B10>$B$4:$B$7)* (B10- $B$4:$B$7)* $D$4:$D$7)+ SUMPRODUCT(((B10>$C$4:$C$7)* ($C$4:$C$7- $B$4:$B$7))* $D$4:$D$7)

becomes

2925 + 0

and returns 2925 in cell C10.

Final thoughts

The formula can be simplified to:

but that formula is harder for me to explain.