Hi,
trying to set the font colour for a formula based on its result:
The formula is:
which is a % variance to Last Year calculation.
If last year's value is 0 then this will produce an error so I am testing for that 1st and then testing for +ve / -ve
The really irritating thisng about this is that if I just use:
it works fine and hides any error results by making the font white. It doesn't show -ve results in red however. If I then add the 2nd part of the check (the nested iif), the -ve results go red & the +ve results go black but the errors are also in black & therefore visible. I am used to the premis that once a test has been made and the result is true, it cannot be overruled so I am at a loss to explain why this formula will hide the errors in Example2 but not in Example 1.....
Any ideas ???
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
trying to set the font colour for a formula based on its result:
The formula is:
Code:
=sum( Fields!PD_Sales_Var_LY.Value)/sum( Fields!PD_Sales_LY.Value)
If last year's value is 0 then this will produce an error so I am testing for that 1st and then testing for +ve / -ve
Code:
Example 1
=iif( Fields!PD_Sales_LY.Value = 0 , "White", iif(sum( Fields!PD_Sales_Var_LY.Value)/sum( Fields!PD_Sales_LY.Value) < 0 , "Red","Black"))
The really irritating thisng about this is that if I just use:
Code:
Example 2
=iif( Fields!PD_Sales_LY.Value = 0 , "White", "Black")
Any ideas ???
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question