Let’s Excel
Published in

Let’s Excel

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!

Percentage Variance Calculations Using LET, IFERROR, and IFS Functions

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!

About Don

“It’s time for different”

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

--

--

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Don Tomoff

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics