How to convert excel formula to work in numbers app

Ah, yes, its tax season again! This is one of the rare cases where I find it simplest to "hard-code" the rate table right into a single formula, like this:

The formula in B2:

=MAX(0,(MIN(12500,A2)−0))×0
+MAX(0,(MIN(62500,A2)−12500))×0.2
+MAX(0,(MIN(150000,A2)−62500))×0.4

The formula in C2:

=MAX(0,(MIN(8632,A2)−0))×0
+MAX(0,(MIN(50000,A2)−8632))×0.09
+MAX(0,(MIN(150000,A2)−50000))×0.02

Here's what it looks like in my formula editor:

Those little arrows at the end of the lines denote line endings enter by option-return. I split the formula into lines to make it easier to visualize the pattern of the tax brackets when entering it in the editor. But that is purely cosmetic.

SG

Not sure your description of N ("used to exclude negative numbers") is what that rarely used function actually does in Excel.

What does your formula do? It appears to be an "array" form of SUMPRODUCT, which Numbers doesn't support. If we know what the formula does and the problem you are trying solve we can suggest a Numbers approach to accomplish that.

SG

Hi Daniel,

"if the income exceeds £150000 i would have to add another formula as all income in excess of this is charged at 45%"

Actually, you wouldn't. All you'd need to do is change the red 0% on the Tax Rates table to 40%.

Regards,

Barry

If 45% applies to Taxable Income above 150000, then your formula could look like this. You simply add another term to it at the end.

=MAX(0,(MIN(12500,A2)−0))*0
+MAX(0,(MIN(62500,A2)−12500))*0.2
+MAX(0,(MIN(150000,A2)−62500))*0.4
+MAX(0,(MIN(5000000,A2)−150000))*0.45

.... where the pre-tax income is in A2.

BTW, the MAX MIN approach is a good way to solve these kinds of "tiered rates" problems in Excel too.

SG

HI Daniel,

Your table suggests the calculation is similar to that on the Canadian forms, using a tiered rate that changes at specified threshold values.

If that is the case, and I'm reading your tables correctly, an individual in class 4 would owe

no tax on taxable income up to £12,500,

£10,000 on income of £62,500 (20% of 62,500-12,500), and

£45,000 on income of £150,000

What happens (in each classes listed) if taxable income exceeds £150,000?

Here's a pair of tables listing the tax rates at each threshold income value, and the amount of tax due on incomes at each threshold.

The second table looks up the amount due at the largest threshold value less than or equal to the taxable income entered in column A, then calculates the amount due on the part of the taxable income that is above that threshold value, multiplies that amount by the tax rate that is to be applied starting at that threshold, and adds the result to the tax due at that threshold.

Awaiting clarification on what happens when taxable income exceeds 150,000 quid?

Regards,

Barry