Icon sets not working with cells containing formulas

[msndrs]

I am a teacher making a spreadsheet so that I can input various bits of info about my classes throughout the week and then with a quick glance see for example what classes I'm teaching Tuesday or what topic my year 9s are doing Friday etc. I have several columns with conditional formatting for easy recognition of info, and one column I want to apply icon set conditional formatting to, but it will not work.

Essentially, I have a formula (=IF($G5="/","3",IF($G5>=2,"0","1"))) which gives a value of 2, 1 or 0 depending on if a lesson was started & completed or not. When I try to apply icon sets to this, to give me a green circle for 0, yellow circle for 1 and red circle for 2, it does not work. The formula works fine, and the icon set works in a way - if I delete the cell's formula and manually type a 0, 1 or 2 it works. However, the issue seems to be that the icon set doesn't recognise the formulas results (a single digit number) as the value which it represents (so even when =IF($G5="/","3",IF($G5>=2,"0","1")) gives a 2, excel doesn't see a 2). Is this the issue? And if so, is there a way around it? I would just replace the formula with the static values but the whole point of the spreadsheet is that I can input info day by day and have it change for me...


Thanks for any assistance

 

[Jerry Sullivan]

Hi, Your formulas are returning Text data types instead of numbers.

Remove the quote marks from around the numbers.

 

[danzon]

remove the quotation marks around your numbers. Those are forcing excel to treat those as TEXT.. thus the conditional formatting does not see a number.