Forum Discussion
Excel formulae with three different conditons
7 Replies
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.
I do not know whether this is of interest to you. Basically, I have used your workbook as a test case for developing strategies for using the Lambda function TAXDUE.
= LAMBDA(year,status,taxable, LET( Tbl, TAXTABLE(year, status), Thresholds, INDEX(Tbl, {1;2;3;4;5;6;7}, 1), RowNum, MATCH(taxable, Thresholds, 1), threshold, INDEX(Thresholds, RowNum), accumulated, INDEX(Tbl, RowNum, 2), taxband, INDEX(Tbl, RowNum, 3), accumulated + taxband*(taxable-threshold) ) )which, in turn, calls a Lambda TAXTABLE
= LAMBDA(Year,Status, LET( StatusOptions, {"Single","Married Separate","Married Joint","Surviving Spouse","Head of Household"}, TableNumber, QUOTIENT( 1+XMATCH(FileStatus, StatusOptions), 2 ), Offset, 5*(TableNumber-1), YearTaxTable, SWITCH(Year, 2020, Tables2020, 2021, Tables2021, NA() ), INDEX(YearTaxTable, {1;2;3;4;5;6;7}, {1,2,3}+Offset)) )Thank you very much. Prior to this, I've not even looked at the LAMBDA information. I've seen it here, of course, but have simply assumed it was for the truly advanced.
On the other hand, I did employ the LET function in my own solution, and was feeling proud of THAT as a test case for the original poster. It was only my second time using LET.
Lambda, here I come, thanks to you, PeterBartholomew1!
- I got the solution using IF. See below the attachment of the formulae.
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;
