Month-over-Month Variance Calc

[Goran]

Hi everyone,

I have 3 columns: Months, Current Value, Previous Value

Current Value is lined up with the most recent month and Previous Value is lined up to the previous month

All I'm trying to do calculate the variance:

(Current Value - Previous Value)/Previous Value

However, I keep getting an error on this calc because its trying to divide by 0.  I believe this is because Previous Value results are 0 in the Current Value month.  

Example:  Current Value   Previous Value

March            10                         0

Feb                 0                          8

I'd rather not bring Current Value results into Previous value as a find and replace if Previous Value is 0 as that would inflate the results if they'd all be mashed up in a single column, but perhaps I'm wrong?

Any ideas how I can get around this?  Thanks!!!

[AngelosPachis]

Hi @Goran ,

You are correct, the error is because you are trying to divide certain values by 0.

In the formula tool you can include an IF statement that checks if the previous value is 0 or not. If it isn't then you can proceed with the calculation, otherwise you can return a null/empty value.

IF [Previous Value]!=0 THEN [Current Value]-[Previous Value]/[Previous Value]
ELSE ""
ENDIF

Hope that helps,

Angelos

[AngelosPachis · Accepted solution]

Any updates on this @Goran ? Did it work for you?