Background color expression with a replacing value
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(),
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.