Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Font colour setting

Status
Not open for further replies.

xlbo

MIS
Mar 26, 2002
15,080
GB
Hi,
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)
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
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")
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top