Published in

Let’s Excel

# 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!

“It’s time for different”

# Connect with Don!

--

--

## More from Let’s Excel

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

## Get the Medium app

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