Forum Discussion

MickeyMouse's avatar
MickeyMouse
Copper Contributor
Jan 21, 2021

Excel formulae with three different conditons

Can someone assist in getting this formulae in one cell?
Formulas and Functions
mathetes's avatar
mathetes
Gold Contributor
Jan 21, 2021

MickeyMouse 

 

 

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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 22, 2021

mathetes 

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))
   )

 

Resources