Forum Discussion
Excel formulae with three different conditons
Hi, Mickey
I'm going to give you more than you asked for. Probably quite a bit more. As it happens, I was working on a tax estimating spreadsheet for the US tax tables applying to 2020 and 2021. I'm attaching the result. There are really only two distinct formulas on this (with variations to refer to the tables that apply to different filing statuses).
But to do the kind of thing you are asking, although a deep IF or IFS function is possible, the tax calculations are done using the XLOOKUP function and referring to tax tables to get those different cut-off levels. This is far better than hard-coding the values in the formulas, because you can basically update the tables (which happens every year) and check and validate those static tables against the official IRS tables...and leave the formulas themselves unchanged.
Lete also try the Xlookup.
It's definitely possible to do that with IF. But you don't really want to; it's not a good long term solution for a variety of reasons. First, you're only dealing with two levels of the tax tables, so it's limited that way; second, the longer the nested set of IF conditions gets, the harder it is to follow them; IFS is an option, but it too is prey to the next reason not to approach it this way: third, it's really not good practice to hard code numbers into formulas when those numbers can change, as tax related numbers WILL do;
- One other option, but if it grows to more conditions I would definitely look at setting up a lookup table.
=MEDIAN(0,8233,G2-24000)
