Conditional Formatting - Icon Sets w/Formula

[bolwahnn]

Hey Guys,
Here's the situation: I have a list of clients that have satisfaction scores that I want to attach conditional formatting to (specifically a Icon set of Green, Yellow, Red). Simple enough right? The problem is, depending on the client, there's 2 different scales of score (a 10 point scale and 7 point scale). So naturally I'll need to add 2 different conditional formats scales. In addition the order of the list of clients can change so I need a way for the conditional format to say if its client X to use this conditional format, if not, use the other conditional format.

I was thinking I create a name range list for this but I cant get it to work....Any help would be greatly appreciated.

Thanks,
Zeke

 

[Eric W]

Excel has some restrictions on Icon Sets in Conditional Formatting. Specifically, you can't use relative referencing in the formulas. This limits some options. If you are willing to use plain highlighting instead of the icon sets, you can do something like this:

	A	B	C	D	E	F
1	Al	7			Al	Cal
2	Bob	7		Service	1	1
3	Cal	10		Speed	3	3
4	Don	10		Accuracy	4	4
5	Ed	7		Politeness	5	5
6				Overall	7	8

<tbody>
</tbody>

The CF formulas are:

Green format:
=(ISNUMBER(E1))*(E1 > VLOOKUP(E$1,$A$1:$B$5,2)*0.67)

Yellow format:
=(ISNUMBER(E1))*(E1 > VLOOKUP(E$1,$A$1:$B$5,2)*0.33)

Red format:
=(ISNUMBER(E1))*(E1 < VLOOKUP(E$1,$A$1:$B$5,2)*0.33)

These should be entered in this order, and the "Stop if True" box should be checked on them.

Let me know if that will work for you.

 

[bolwahnn]

Excel has some restrictions on Icon Sets in Conditional Formatting. Specifically, you can't use relative referencing in the formulas. This limits some options. If you are willing to use plain highlighting instead of the icon sets, you can do something like this:

	A	B	C	D	E	F
1	Al	7			Al	Cal
2	Bob	7		Service	1	1
3	Cal	10		Speed	3	3
4	Don	10		Accuracy	4	4
5	Ed	7		Politeness	5	5
6				Overall	7	8

<tbody>
</tbody>

The CF formulas are:

Green format:
=(ISNUMBER(E1))*(E1 > VLOOKUP(E$1,$A$1:$B$5,2)*0.67)

Yellow format:
=(ISNUMBER(E1))*(E1 > VLOOKUP(E$1,$A$1:$B$5,2)*0.33)

Red format:
=(ISNUMBER(E1))*(E1 < VLOOKUP(E$1,$A$1:$B$5,2)*0.33)

These should be entered in this order, and the "Stop if True" box should be checked on them.

Let me know if that will work for you.

Eric,
Thanks for the suggestion, but I really need this to work with the Icon Set that looks like a traffic light. The reason for that is because my data is just a small set of data for a report that we provide to the executive team and they really like this format. Any way to do this with the Traffic light Icon set?

 

[jtakw]

Hi,

Try using a helper column with your 7 or 10 point scale calculated, then apply the icon set formatting:

Excel 2010
	A	B	C	D
1	Company	Points	Scale	Result
2	A	5	7	0.714286
3	B	8	10	0.8
4	C	7	10	0.7
5	D	6	7	0.857143
6	E	4	7	0.571429
7	F	6	10	0.6
8	G	7	7	1
Sheet1

Cell Formulas
Range	Formula
D2	=B2/C2

In Column D, apply icon set formatting with green, yellow, red lights assigned to percentages, e.g. (>=67% green; >=33% yellow; red)

 

[Eric W]

The icon sets are nice, but they really do have limitations. In order to use the icon sets, here's my best stab at it, but it's not elegant.

Excel 2010

	A	B	C	D	E	F
1	Al	7			Al	Cal
2	Bob	7		Service	1	1
3	Cal	10		Speed	3	3
4	Don	10		Accuracy	4	4
5	Ed	7		Politeness	5	7
6				Overall	7	8
7
8
9					7	10

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E9 =VLOOKUP(E$1,$A$1:$B$5,2) <tbody> </tbody>

<tbody>
</tbody>

This extract doesn't show the icons, but on my sheet, Al has 1 red, 3 4 5 yellow, and 7 green. Cal has 1 3 red, 4 yellow, and 7 8 green.

I don't know how close this is to your sheet, but in order to get it to work, you have to create a CF rule on EVERY column. E and F here, and as far over as you have clients. Then you have a formula somewhere in the column that shows the maximum value in the range (E9 in this example). This formula, fortunately, can use relative references, and can be copied to the right. This is a simple version of the formula, you'd probably want to incorporate "not found" logic in it. Then you can hide that row. Once you have the sheet set up, you can change the order of the clients around as much as you'd like.

It would probably be possible to write a macro to create all those rules, but it would be dependent on how your sheet is designed.

If you can use a helper column like jtakw suggests, then his solution might work for you.

If I think of something more elegant, I'll let you know.

 

[bolwahnn]

Thanks jtakw!
I'm going to try an apply this suggestion. I think it'll work.

 

[bolwahnn]

Thanks for taking the time on this Eric! I'm going to apply jtakw suggestion as I believe that is the easiest way to get this resolved.

 

[jtakw]

Thanks jtakw!
I'm going to try an apply this suggestion. I think it'll work.

You're welcome, glad to help.