Formulas and Conditional Formatting using icon sets

[simon76]

I have tried to find a solution to my problem in Excel 2010 but just can't!

I have a simple spreadsheet of 5 columns:

- B contains a procedure
- E contains a lookup returning a percentage for Jan 14
- F contains a lookup returning a percentage for Feb 14
- G contains a lookup returning a percentage for Mar 14
- H contains an IF forumula returning a score: =IF($G5<=$F5,"-10",IF($G5>=$F5,"10",IF($G5=$F5,"0","")))

I want column H to be formatted so it returns an arrow:
- Green arrow up if H is 10
- Amber arrow across if H is 0
- Red arrow down if H is -10

Any ideas?

[Saarang84]

Hi Simon,

Welcome to the forum.
Refer the attached workbook. Is this what you asked for?

[simon76]

There was an error in my earlier post which has now been corrected.

Procedure.xlsx

I want column H to be formatted so it returns an arrow:
- Green arrow up if H is 10
- Amber arrow across if H is 0
- Red arrow down if H is -10

p.s. - i have noticed that my IF formula in column F does not work correct as run and skip should return "0"

[gak67]

Your if statement doesn't work because you have <= (less than or equal to) and >= (greater than or equal to). Replace those with < and > respectively and it will work.

Why do you have the -10, 10 and 0 as text rather than numbers (if you want numbers don't have the quote marks (""))?

[gak67]

I opened your uploaded file, changed the IF formula to this and it worked fine:

Please Login or Register  to view this content.

[Saarang84]

I opened your uploaded file, changed the IF formula to this and it worked fine:

Please Login or Register  to view this content.

Workbook attached.

[simon76]

Excellent - works like a charm