Excel formulae with three different conditons

[MickeyMouse]

Can someone assist in getting this formulae in one cell?

[#2]

@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.

[#3]

I got the solution using IF. See below the attachment of the formulae.

Lete also try the Xlookup.

[#4]

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)

[#5]

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

[#6]

@Peter Bartholomew 

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, @Peter Bartholomew!

[#7]

@mathetes 

It was the fact that it was a well-structured workbook and that you had used LET that temped me to try the LAMBDA to see what it offered.  I think the good thing about the LAMBDA concept is the way it breaks the problem into manageable parts.

1) Can I identify and return the correct table

2) Given the table, can I calculate the tax due

Most of the finance modellers I know let out a howl of agony if they think a solution falls short of being a perfect 'white box'.  In this case the solution can be validated simply by typing

= TAXTABLE(2021, FileStatus)

How it is achieved should not matter that much to the end user.  I have used a bottom-up strategy of building arrays of row and column indices but, equally, I could have returned the table by intersecting row ranges for the year and column ranges for the filing status.

I am pleased you found the use of LAMBDA interesting.

[#8]

@MickeyMouse 

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;