Forum Discussion
Excel IF Formula
ash_m200 Actually, similar questions have come up here twice the past week, though applied in different contexts.
https://techcommunity.microsoft.com/t5/excel/band-billing/m-p/1632141#M73901
Attached is the same type of solution that I provided then. I've adapted it to your specific situation. I trust you can apply the method demonstrated into your own file. If not, come back here.
PeterBartholomew1 True, sometimes I'm still a bit into the old way of doing things. A habit I'm trying to get over, though.
But in this case, I believe your formula doesn't give the right answer as the rate should be tiered. For instance, a quantity of 500000 should return a rate of 0.04% i.e. (100000 x 0% + 400000 x 0.05%)/ 500000.
Your formula picks-up the rate that is valid as from 500000. But perhaps I'm mistaken.
Please accept my apologies. I had misread the original question and not realised it was a banded tax type of challenge.
I normally try to get the solution as a single array formula but, on this occasion, I have fallen back to using a Table to propagate a relative reference formula. Maybe a transpose will sort it?