Conditional formatting with icons using a formula

[Bob White]

I want to use the 3 flag icon set as a conditonal format based on a formalus relating to a value in another cell in my spreadsheet.

I am struggling with the formula.

I want a green flag in cell H1 if the value in cell H1 is in the range of .9 to1.1 times the value in cell A1.

If the value in H1 is zero I want a yellow flag in H1

If the value in H1 is neither green of yellow I want a red flag in H1

 

[Matt L]

According to the help file you can only use Icon Sets with rules that evaluate to a number. If I understand your question you need to apply a rule that evaluates to True/False:
=AND(H1>0.9*$A$1,H1<1.1*$A$1)

You can use this rule (and similar for the other conditions) and format the cell/font in red/yellow/green but cannot use the flags.

Matt
Useful as a short piece of string

 

[Peter_SSs]

Bob

My understanding of the icon sets is also that you cannot use them directly as you want. However, if you really want the flags and not just coloured cells as suggested by Matt, you could investigate a work-around something like this.

1. Use the fomula shown in G1 and copy it down.

2. Apply the flag icon set to the column G cells.

3. Select the column G cells then Conditional Formatting| Manage Rules...|select the rule|Edit Rule...|Show icon only|OK|Apply|OK

I can't show the flags with my screen shot so I have manually coloured the column G cells with the colour of the flag that appears after following the above steps.

Excel Workbook
	A	B	C	D	E	F	G	H
1	1							1.05
2	2							3
3	3							0
4	4							3.98
5	5							3
Flags

 

[Matt L]

Nice workaround Peter.

Bob, notice that the difference in referencing column A depending on whether you are referring to a fixed value in A1 ($A$1) or just the value in columnA for each row (A1).

Matt