Hi Michael,
LOOKUP can be your friend here. What you have is a tiered rate calculation.
Taxes on amounts from 0 to 125000 are at a 0% rate.
Amounts in the range 125000 to 250000 are taxed at 2%
Amounts in the range 250000 to 925000 are taxed at 3%
Amounts in the range 925000 to 1500000 are taxed at 5%
Amounts greater than 1500000 are taxed at 2%
Placed on a table, that information looks like this:

The threshold amounts listed in column A are the amounts at which the tax rate changes.
The rates in column B show the tax rate on amounts between the threshold on that line and the next threshold on the line below.
The tax to threshold amount in column C is the total tax to be paid on amounts up to the threshold on that line.
For example, on an amount of 300000, the tax due would be 2500 + 3% of the 50000 above the 250000 threshold.
The column titles, all of the data in columns A and B, and the 0 in C2 are directly entered. The remaining four values in column C are calculated using the formula below, entered into C3 and filled down to C6:
C3: C2+(A3−A2)×B2
The Tax Rates table is used as a Lookup table by the formula in the Main table, shown below.

Column A contains demonstration amounts. The first four pairs are amounts at and one more than one of the threshold amounts. the final pair contain amounts that are above the highest threshold, and respectively five hundred thousand above the amount in row 8 and five million above the amount in row 10.
the formula is entered in cell C2, and filled down to the end of column C.
C2: LOOKUP(A2,Tax Rates::A,Tax Rates::C)+(A2−LOOKUP(A2,Tax Rates::A))×LOOKUP(A2,Tax Rates::A,Tax Rates::B)
The formula has three parts:
- LOOKUP(A2,Tax Rates::A,Tax Rates::C)+
- (A2−LOOKUP(A2,Tax Rates::A))×
- LOOKUP(A2,Tax Rates::A,Tax Rates::B)
Part 1 looks up the amount in A2 of 'this table' in column A of Tax Rates, finds the row of column A containing the largest value equal to or less than the search value, and returns the amount of tax due on that threshold amount from column C of Tax Rates.
Part 2 gets the value in A2 of 'this table'. The LOOKUP function then looks up the value in A2 in column A of Tax Rate, and because there is no third argument in this LOOKUP, returns the value it finds. The found value is subtracted from the value retrieved from A2, giving the amount subject to the marginal tax rate for amounts above that threshold.
Part 3 performs the same lookup, but returns the marginal tax rate from the same row of column B of the Tax Rates table.
The results of parts 2 and 3 are multiplied, and the result added to the result of part 1. The end result, the total tax due on the amount in A2, is returned to B2, the cell containing the formula.
Should tax rates change, the only changes needed in the tables are to the rates in column B of Tax Rates.
Should the threshold amounts change, the only changes needed in the tables are to the threshold amounts in column A of Tax Rates.
Should a new tier be added, the only changes needed in the tables are to add a row for the new tier, fill the formula into that row, and adjust the thresholds and rates to match the new tiers and rates.
Regards,
Barry