Nested IF Statements in Numbers

Hi Cody,

Nested IFs are fine up to a triad. Beyond that, they become cumbersome, error prone, and difficult to edit.

The Canadian tax form has a table very similar to the description you provided (but with fewer tax brackets). The same process can be constructed using a Lookup table containing the threshold amounts, amounts due at each threshold, and rates after each threshold.

Here's your list of rates and threshold amounts at which each rate applies, translated to a Rates table:

Column A contains a list of the threshold amounts at which each rate is applied. This is entered directly.

Column C contains the rate that applies to amounts from the threshold amount listed in 'this row' to the amount shown in the next row. These rates are entered directly.

Column B contains the amounts due at each threshold. The first of these, 0.00 (due on a taxable amount of $0) is entered directly. the rest are calculated by the formula shown below the table. The formula is entered in B3, then filled down to the last row of column A.

B3: IF(LEN(A3)<1,"",B2+C2×(A3−A2))

The IF part, shown in normal weight type, is a 'switch' that prevents calculation if there is no value entered in 'this row' of column A. The core formula that does the calculations is shown in bold.

In English, the core formula says: 'the amount due at the previous threshold (B2), plus the rate to 'this threshold' (C3) multiplied by (the amount at 'this threshold' (A3) minus the amount at the previous threshold (A2) )

The entered and constructed data in this table are used to calculate the amount due in the Calculations table:

The calculations table here has many rows to show the results obtained from several different input amounts. In practice, this would likely be a single pair of cells in a larger table.

What the formula does:

Each INDEX section uses the same MATCH statement: MATCH(A2,Rates::A,1)

This gets the value in Calculation::A2, searches for a match in Column A of Rates, accepts the largest value less than or equal to the value in A2 of Calculations, and returns a number indicating the position of tha value in the list in column A of Rates.

Each INDEX uses that number to determine the row from which it is to return the values requested below

INDEX(Rates::B,MATCH(A2,Rates::A,1))+ The amount due at the largest threshold amount less than or equal

to the Amount in Calculations::A2 plus

INDEX(Rates::C,MATCH(A2,Rates::A,1))× The the tax rate within the current bracket (tier), multiplied by

(A2−INDEX(Rates::A,MATCH(A2,Rates::A,1))) (The the Amount in Calculations::A2 minus the threshold amount

at the beginning of this bracket (tier) )

If rates change, a tier is added or removed, or the amounts at which each rate applies is changed, the only editing to be done is to enter the new threshold values in Column A of Rates and enter the new rates in column C of Rates (and fill the first forula into new rows if tiers have been added to the Rates table).

NO changes to either formula are required.

COPY/PASTE version of the full second formula:

INDEX(Rates::B,MATCH(A2,Rates::A,1))+INDEX(Rates::C,MATCH(A2,Rates::A,1))×(A2−IN DEX(Rates::A,MATCH(A2,Rates::A,1)))

Regards,

Barry

Though the formula is unusually long, I find the easiest solution to a "tiered rate" problem like this is the following:

If the pretax income is in A2, then I use a formula like this:

=MAX(0,(MIN(9525,A2)−0))*0.1
+MAX(0,(MIN(38700,A2)−9525))*0.12
+MAX(0,(MIN(82500,A2)−38700))*0.22
+MAX(0,(MIN(157500,A2)−82500))*0.24
+MAX(0,(MIN(200000,A2)−157500))*0.32
+MAX(0,(MIN(500000,A2)−200000))*0.35
+MAX(0,(MIN(1000000,A2)−500000))*0.37

As explained in the screenshot the formula, when broken down line by line, is much simpler than it looks and is easy to edit when the brackets change.

Substitute ; for , in the formula if your region uses , as a decimal separator.

SG