Forum Discussion
Excel formulae with three different conditons
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!
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.