IF Formula to calculate banding based on multiple criteria

Hi,

Please can someone help me with my spreadsheet. I have over 800 lines and have started checking by hand.

I need a formula to look at the country and depending on the taxable pay (various rates), return the tax banding. I have attached a small snippet which may help. I have popped a formula in column G, but this is merely for the line and not the entire range, please could you have a look and help.

Many thanks,
DChar1

OMG! That is amazing. I have not even come across Aggregate before. Thank you so much

I have re-attached the spreadsheet to see if you could help with another part of the same spreadsheet, again I have just attached a small snippet. I initially tried adding another column, to then do the formula, but wondered if you had a better idea?

CK76 thank you so, so much. You have taught me something new and taught me not to overthink. Cannot rate your reputation enough.

Thank you.

Just one thing.... sorry to bother you again.

Where I am using this as a template, on additional rows that have no data I am getting #Num. So, I don't have to delete the lines to get rid of the error and get my results, how can I tell it to return 0.00.

I have tried to attach a condensed version of the spreadsheet, but the file is too large still. I have therefore attached a picture.

Any help will be appreciated.

Amended; thank you

Of course.... That's me over thinking again. Thanks.

We are still testing the template, so I will leave the thread open, just in case I need your assistance with any other modifications before I close. I hope that is ok.

Hi CK76, I hope you are well.

You kindly helped me last year with this document and I'm afraid I need your assistance again

I have updated the bandings and noticed that some of the values are not pulling through. It seems to have a problem with the higher salaries. Either returning no value or 40% when it should be 45% or 46%.

I don't know whether I am going data blind, but I can't figure out what the error is.

Please can you help?

Hi dchar1,

You're returning 0 because the values in the range O2:O13 (in your formula) don't exceed the values in O21 and O22 and hence return FALSE (which Excel interprets as 0). It's a dirty solution, but can you change the upper values (the £125,140.01 value) to something they'll never hit (e.g. £999,999,999)?

I hope this helps,

Snook

Hi Snook,

Thanks ever so much for responding.

My values in O2:O13 do show £125,140.01, see highlighted image:
Screenshot 2024-09-05 193709.png

I did try £999,999,999.00 as suggested and it did work, so thank you. I am just confused as to why with the correct value in it won't work. Even, if I change to £125,141.00 it doesn't work.

#confused

The way the formula works, it's checking whether the values in the 'Upper' column are greater than the value you are looking up from the 'Taxable Pay' column. As £125,140.01 isn't greater than £128,561.44 (cell O21) it returns FALSE. When Excel multiplies that with the ($N$2:$N$13=L21) part of the formula, it treats FALSE as 0 and hence the output is 0. When it calculates the $P$2:$P$13/ part, it is dividing by 0 and the output is an error (#DIV/0). As you've wrapped the formula within an IFERROR statement it turns the resultant error into a 0.

Ah, OK I'm with you.

Thank you ever so much for help. Really appreciate it

You're welcome mate, I'm happy to help.