IF statements...If something is greater than X, less than Y.

[DwyerJames]

I am an accountant and I'm creating a spreadsheet that will calculate your tax for you based on your earnings.

I'm having difficulty getting the IF statement to correctly calculate the tax on Non-Savings, Savings and Dividend income when it reaches the Higher and Additional rates of tax.

If anyone can help, that would be great!!

Thanks in advance

[Limor_OP]

How about posting a file for example?
The yellow banner at the top of the page gives instructions as for how to upload your file.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[TMS]

Something like

Please Login or Register  to view this content.

[DwyerJames]

I've attached the spreadsheet for you guys to take a look. The cells highlighted in orange are the formulas i need help with.

If someone could call me (on Microsoft Teams) to go through this that would be great.

Thanks

[AliGW]

No, that's not going to happen (the Teams bit) - sorry.

Please understand that this is not just a place for you to get help, but each thread is part of a permanent database that is extensively trawled, therefore ALL help has to be given within the thread. I am sure you will understand.

[DwyerJames]

Ok thanks, do I need to post each and every query through this forum then? May take a while

[AliGW]

It's what everybody has to do. We seem to get by just fine!

[MrShorty]

How much is trouble with the actual IF() or IFS() function, and how much is just trying to understand the logic that you are trying to apply? I'm not sure I understand the logic, so I cannot suggest a set of formulas.

As a guess, it looks like:
1) The first up to 37500 is taxed at the first rate -- A26=MIN(C22,C7)
2) Then next up to 112500 (up to a total of 150000) is taxed at the second rate -- A27 =MIN(F7-C7,MAX(C22-A26,0))
3) Anything over 150000 total is taxed at the third rate -- A28=MAX(C22-SUM(A26:A27),0)

I ended up not using any IF() functions for that. Did I guess right or even close?

[DwyerJames]

Ok, let's give this a go at explaining then.

These are all for the Interest section only.

A33 = that cell needs to calculate the remainder of whatever is in cell D22, less A32 and A29 up to the value of £37,500. However, if cell F22 is greater than £37,500, then it must be 0.

A34 = this must calculate the remainder of whatever is in cell D22, less A33, A32 and A29 up to the value of £150,000. However, if cell C22 is greater than £150,000 then it must be 0.

A35 = if cell C22 is greater than £150,000, then all of D22 must be included within this cell and none of the other cells (A34, A33 or A32)

[MrShorty]

I find that part of learning to program a spreadsheet is learning to solve small problems, and then combine them into something bigger. For example:

A33 = that cell needs to calculate the remainder of whatever is in cell D22, less A32 and A29 up to the value of £37,500.

Can you see how my MIN() function solves this part of the problem MIN(37500,D22-A32-A29)? Anywhere in your spreadsheet where you have a "perform calculation up to a specified maximum" problem, you should be able to use a MIN() function.

The other part of A33

However, if cell F22 is greater than £37,500, then it must be 0.

is a simple IF() where it will choose between returning 0 or returning the result of the MIN() function. IF(F22>37500,0,MIN(...))

Later, you add another condition to A33

if cell C22 is greater than £150,000, then all of D22 must be included within this cell and none of the other cells (A34, A33 or A32)

We can add another IF() layer to A33 =IF(C22>150000,0,IF(F22>37500,...))

A34 should be very similar to A33.
A35 appears to be a simple choice between 0 and D22, based on whether C22 is greater than 150000 -- another simple IF() function =IF(C22>150000,D22,0)