Using Excel’s LET, IFERROR and IFS functions to step up the “error trapping” game!
Use Excel for Financial Reporting? You want to know this!
Variance Calculation and Development
I recently developed a client’s financial management reporting package and implemented the LET function to simplify the formula development.
The best way to understand these new functions (LET is in MS 365 version of Excel only), is to see them used in real world business situations!
In the variance calculations, I want to do the following :
- If the % variance is zero, show a blank
- If the % variance is <> 300%, show a blank (I chose 300%, but whatever is meaningful to your reader)
- If the % variance is <> zero, calculate and return the variance %
- FINALLY, if any of those return an error, display a BLANK cell.
The initial formula to do this requires nesting the ABS and IFS functions in an IFERROR function:
Here it is:
=IFERROR(IFS(E11/C11=0,” ”,ABS(E11/C11)>3,” ”,ABS(E11/C11)>0,E11/C11),” ”)
Introducing the LET function
However, notice how frequently it was necessary to include the calculation of the variance (E11/C11)! Four times!!
The LET function eliminates that effort.
DEFINE the calculation — in this case, the calculation “name” is “var”, the calculation is E11/C11. Then, in the LET function formula, just use the variable name “var” in each instance where the calculation is required.
=LET(var,E11/C11,IFERROR(IFS(var=0,””,ABS(var)>3,””,ABS(var)>0,var),””))
Give it a try. It’s easier than you think!
This post was originally shared on LinkedIn. Check out #twinztalk and #twinztechtip.
