Conditional Formatting and Smileys!

Hi

Can you someone help me solve this problem. I have a list of numbers in one column and the column to the right contains a smiley I generate using wingdings.

I need formula that will

1) If value is less than 10 it will show a sad face with red background.
2) If value is greater than 10 it will show straight smile and orange background
3) If value greater than 20 it will be happy face with green background.

I can get the green and red but the orange is confusing me. I am using straightforward if function but I want to be able to use excels way of display greater than but less than.

Thanks forum!

birdoo2,

In wingdings:
Happy face = capital j = J
Straight face = capital k = K
Sad face = capital l = L

So instead of manually entering the smileyfaces, just put a formula next to your numbers. Assuming the numbers start in cell A1, cell B1 would contain the following:
=IF(A1="","",IF(A1<10,"L",IF(A1<20,"K","J")))

Then copy down and set the font for that range to wingdings.

Once that's done, select the cells that contain the formula (the ones showing the smiley/straight/sad faces), and create the following three conditional formats
Rule 1: If value = J then green background
Rule 2: If value = K then orange background
Rule 3: If value = L then red background

Hope this helps,
~tigeravatar

Thanks,

I had formatted the smileys so their was no manual input

If I wanted to add another greater than less than to that formula how would it look?

=IF(A1="","",IF(A1<10,"L",IF((A1<20,"K","J"),IF(A1<40,"K","J")))

This gives me errors.

Also are their another smiley for Excel otherr than 3 in wingdings?

birdoo2,

Sample formula (just keep chaining ifs):
=IF(A1<10,"less than 10",IF(A1<20,"between 10 and 20",IF(A1<40,"between 20 and 40","greater than 40")))

As far as other smiley faces go, I don't know of any, sorry

~tigeravatar

Your formula shows 2 brackets after the third if statement.
No more smileys but lots of other colours to use.

Hi Tigeravatar and Barrytsl,

This is still not working for me. In the formula Tiger presents the "K" is not accounted for.

I need to run the If function but instead or true or false values being applied I need 3 outcomes.

Thanks

birdoo2

birdoo2,

The K is being accounted for, but it will only display if the number is between 10 and 20:
=IF(A1="","",IF(A1<10,"L",IF(A1<20,"K","J")))

So if A1 is blank, the formula result is blank, and formula exits
If A1 is not blank and below 10, then the formula result is L and formula exits
If A1 is not below 10, but below 20, then the formula result is K and formula exits
Else formula result is J

~tigeravatar