Background color expression with a replacing value

[andymanu]

Hi All,

Apologies if the heading sounds awkward.

Actually, Ii want to apply an expression to a column named Variance (Budget Cost - Actual Cost). I am able to apply the expression correctly.

My background color  expression is,

=If(Sum([Current Month Actual Cost])/Sum([Current Month Budget Cost]) > 1.25, Red(),

If(Sum([Current Month Actual Cost])/Sum([Current Month Budget Cost]) > 1.00, LightRed(),

Green() ))

I am using as a fraction so that I could represent the magnitude of the variance as a proportion to the Budgeted cost.

Despite all works well, I came across the below issue,

There are certain budgeted cost values where the value is "0" (zero). In a so called situation, the calculation of the variance is correct (which is -9500) but, the background color should be "Red" instead of "Green" ( Because it divides the "9500" by "0").

May be an option to resolve this issue is to replace all the budgeted "0" values by "1" and performa the above said background expression.

However, I am not so sure how to perform the said task in the same background expression window (where I have written the above expression) so that, i will depict the correct variance values without harming the actual variance data along with the correct background color.

Could anyone help me in this regards?

Thank you in advance.

Kind regards,

Andrew

[Sergey_Shuklin · Accepted solution]

Hello!

You can increase your denominator by 0.0000001 for avoiding "division by zero" issues. Like that:

Sum([Current Month Actual Cost])

/

( Sum([Current Month Budget Cost]) + 0.0000001)

It won't harm your calculations much and a background color should be all right.

[andymanu]

Hi Sergey,

Thank you very much for your much valuable logic.

It's absolutely correct and got the desired results.

Thanks again.

Kind Regards,

Andy