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.
Excel LET Function
The LET function lets you define named variables in a formula. There are two primary reasons you might want to do this…
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:
Excel ABS Function
The Excel ABS function returns the absolute value of a number. ABS converts negative numbers to positive numbers, and…
Excel IFS Function
The Excel IFS function can run multiple tests and return a value corresponding to the first TRUE result. Use the IFS…
Excel IFERROR Function
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no…
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.
Give it a try. It’s easier than you think!