#### Mac XL

This site will look better if you upgrade to a browser that supports web standards.

## Using SUMPRODUCT() to calculate variable rates and commissions

Often commission schedules, tax rates, or schedules of discounts will have rates that vary depending on the income or transaction value.

### Calculating total tax in one cell:

Say that the following marginal income tax rates applied:

```        Taxable Income                Marginal
Low Threshold    High Threshold     tax rate
\$     0             \$ 6,000           0%
\$ 6,001             \$20,000          17%
\$20,001             \$50,000          30%
\$50,001             \$60,000          42%
\$60,001             and over         47%
```

and you wanted to find the total tax for a net income. Note that only the amount between \$6,001 and \$20,000 (inclusive) is taxed at 17%. Only the amount between \$20,001 and \$50,000 is taxed at 30%.

One way to do it would be to use 4 different cells and calculate the tax owed for each marginal tax band, up to the income value (since the lowest band is taxed at 0%, we can ignore its contribution).

However, the SUMPRODUCT() function allows a little legerdemain to calculate the value in a single cell:

Assume that the taxable income is in cell A1. The total tax can be calculated using the formula

```   =SUMPRODUCT(--(A1>{6000;20000;50000;60000}), (A1-{6000;20000;50000;60000}), {0.17;0.13;0.12;0.05})

= \$13,480```

Note that the tax rates, in the last set of brackets, are differential rates - the amount above or below the previous rate.

```  Marginal Rate        Difference       Differential rate
17%              17% - 0%          17%, or 0.17
30%              30% - 17%         13%, or 0.13
42%              42% - 30%         12%, or 0.12
47%              47% - 42%          5%, or 0.05
```

How it works...

### Using tables to make things easier

This method is even easier using tables. Set up a table, say in J1:L5:

```        J           K             L
1  Threshold   Marginal Rate  Diff. Rate
2       6000       17%          = K2          which returns:    17%
3      20000       30%          = K3 - K2     which returns:    13%
4      50000       42%          = K4 - K3     which returns:    12%
5      60000       47%          = K5 - K4     which returns:     5%
```

Then the formula becomes:

`     =SUMPRODUCT(--(A1>\$J\$2:\$J\$5), (A1-\$J\$2:\$J\$5), \$L\$2:\$L\$5)`

Now you can change the marginal rate without having to change the formula.

If you name the ranges, say, threshold for J2:J5 and dRate for L2:L5, then the formula becomes

`     =SUMPRODUCT(--(A1>threshold), (A1-threshold), dRate)`

and adding another tax bracket is as simple as extending the list and redefining the names - no formulae need to be changed. If you use dynamic named ranges, you don't even need to redefine!

### Another example: commissions

Say a salesperson had the following commission schedule:

```              Sales                    Marginal
Low Threshold    High Threshold   commission rate
\$        0       \$  100,000          10%
\$  100,001       \$1,000,000           8%
\$1,000,001       and over            12%   ```

We again calculate the differential rates,

```       J                K               L
1   Threshold       Marginal Rate    Diff. rate
2         0              10%           = K2         which returns:  10%, or  0.10
3    100000               8%           = K3 - K2    which returns:  -2%, or -0.02
4   1000000              12%           = K4 - K3    which returns:   4%, or  0.04
```

then construct a SUMPRODUCT() formula that calculates the commissions in one step:

`     =SUMPRODUCT(--(A1>\$J\$2:\$J\$4),(A1-\$J\$2:\$J\$4), \$L\$2:\$L\$4)`

Note that unlike the tax example above, since the commission applies to all sales, we need to include the bottom of the lowest band.

For sales of \$500,000, the formula returns \$42,000:

```    Sum(--{TRUE;TRUE;FALSE} * {500,000;400,000;0} * {0.10;-0.02;0.04}), or
Sum({50,000;-80,00;0}), or
\$42,000```

For sales of \$2,500,000, the formula returns \$262,000:

```    Sum(--{TRUE;TRUE;TRUE} * {2,500,000;2,400,000;1,500,000} * {0.10;-0.02;0.04}), or
Sum({250,000;-48,000;60,000}), or
\$262,000```