Visual Cues for Scorecard

[#1]

I am creating a score card that assigns a value of 1-5 based on the value of an expression. I would like to color code the cell or the text based on the value. The code below returns my 1-5 values, is there anyway to also color code? I would like 5 distinct colors.

IF(ISNULL([Prelm%])=true(),5,
IF([Prelm%]>.76,5,
IF([Prelm%]>.59 and [Prelm%]<=.76,4,
IF([Prelm%]>.36 and [Prelm%]<=.59,3,
IF([Prelm%]>.19 and [Prelm%]<=.36,2,
IF([Prelm%]<=.19,1))))))

[#2 · Accepted solution]

I don't think Visual Cues will give you enough colors. I think that only allows three states (below, normal, above).

On the Expressions tab, click on the plus next to your expression. Right click on Background Color and choose Edit expression, paste in your expression and then change the numbers to colors. Something like:

IF(ISNULL([Prelm%])=true(),RGB(0,255,255),
IF([Prelm%]>.76,RGB(0,255,255),
IF([Prelm%]>.59 and [Prelm%]<=.76,RGB(50,255,50),
IF([Prelm%]>.36 and [Prelm%]<=.59,RGB(0,50,255),
IF([Prelm%]>.19 and [Prelm%]<=.36,RGB(255,55,55),
IF([Prelm%]<=.19,RGB(255,0,0)))))))

All those color combos are completely made up, so don't blame me if they look hideous.

[#3]

Thanks, this will solve half of my problem. I need the if statment to return the value and color it based on the value of Reject%.

something like this syntax will not work
IF(ISNULL([Reject%])=true(),5 and red(0,0,255),

[#4]

You need two separate expressions. One to return the value, use that in the normal Expressions box. The other one would only return the color and that goes in the Background Color expression. I'm confused on the Prelm% versus the Reject%.

Normal Expression:

IF(ISNULL([Reject%])=true(),5)

Background Color Expression:

IF(ISNULL([Reject%])=true(),RGB(0,0,255))

[#5]

Sorry about that I coppied the wrong thing over reject% and prelm % are the names of my expressions which are being graded. The visual will clear it up. I will try out your suggestion.

[#6]

Thanks that was it, im sorry I missed your first post, didnt read it fully, got a lot going on here.

[#7]

Using this solution also changes the colour of the dimension name in a table. Having viewed a similar thread, it appears that the colour will change to match the first cell, which isn't good. I'm guessing this is a bug.........

I have tried Custom Format Cell but that doesn't make any difference.

Can anyone suggest a way to change the dimension name back to black?