Insert smiley face dependent upon a value in another cell

[MsTwiitch]

Hi all. I'm trying to set up conditional formatting on a budget spreadsheet and I'm not sure if what I'm looking to do is possible.

Objectives:
1. Compare values in Column N to values in Column F
2. If values in Column N are within 2% of the values in column F, fill the cell in Column N "green"
3. If values in Column N are between 2% and 4% of the values in column F, fill the cell in Column N "yellow"
4. If the values in Column N are not within 4% of the values in column F, fill the cell in Column N "red"

If Possible:
Add a smiley face to the end of the figure in Column N if the cell is colored "green"
Add a meh face to the end of the figure in Column N if the cell is colored "yellow"
Add a frowney face to the end of the figure in Column N if the cell is colored "red"

If the above isn't possible:
Add a smiley face to the cell in Column O if the cell next to it in Column N is "green"
Add a meh face to the cell in Column O if the cell next to it in Column N is "yellow"
Add a frowney face to the cell in Column O if the cell next to it in Column N is "red"

*Column N already has a formula (cell in Column K divided by cell in Column L*

 

[DRSteele]

Welcome to the Forum.

I mocked up an example spreadsheet for you. If you prefer different emoticons, this website lists lost of them.

Note that the emoticons here in this HTML website are all yellow, but in Excel they will be properly coloured.

MrExcel posts19.xlsx
	A	B	C	D	E
1	https://www.alt-codes.net/smiley_alt_codes.php
2
3
4		code	128578	128528	128577
5		result	?	?	?
6		confirmation	128578	128528	128577
7
8
9			hurdles
10			2%
11			4%
12
13		sample data
14		target	observation	accuracy
15		6.0	6.8	13.3%	?
16		79.0	78.0	1.3%	?
17		28.0	27.5	1.8%	?
18		12.0	11.5	4.2%	?
19		27.0	27.8	3.0%	?
20		60.0	60.5	0.8%	?
21		75.0	75.2	0.3%	?
22		52.0	52.5	1.0%	?
23		12.0	13.0	8.3%	?
24		34.0	34.9	2.6%	?
25		62.0	62.4	0.6%	?
26		62.0	62.5	0.8%	?
27		37.0	38.0	2.7%	?
Sheet5

Cell Formulas
Range	Formula
C5:E5	C5	=UNICHAR(C4)
C6:E6	C6	=UNICODE(C5)
D15:D27	D15	=ABS(C15/B15-1)
E15:E27	E15	=IF(D15>$C$11,UNICHAR(128577),IF(D15<$C$10,UNICHAR(128578),UNICHAR(128528)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E15:E27	Expression	=D15>$C$11	text	NO
E15:E27	Expression	=D15<=$C$10	text	NO
E15:E27	Expression	=D15<=$C$11	text	NO
C15:C27	Expression	=D15>$C$11	text	NO
C15:C27	Expression	=D15<=$C$10	text	NO
C15:C27	Expression	=D15<=$C$11	text	NO

 

[MsTwiitch]

Welcome to the Forum.

I mocked up an example spreadsheet for you. If you prefer different emoticons, this website lists lost of them.

Note that the emoticons here in this HTML website are all yellow, but in Excel they will be properly coloured.

MrExcel posts19.xlsx
	A	B	C	D	E
1	https://www.alt-codes.net/smiley_alt_codes.php
2
3
4		code	128578	128528	128577
5		result	?	?	?
6		confirmation	128578	128528	128577
7
8
9			hurdles
10			2%
11			4%
12
13		sample data
14		target	observation	accuracy
15		6.0	6.8	13.3%	?
16		79.0	78.0	1.3%	?
17		28.0	27.5	1.8%	?
18		12.0	11.5	4.2%	?
19		27.0	27.8	3.0%	?
20		60.0	60.5	0.8%	?
21		75.0	75.2	0.3%	?
22		52.0	52.5	1.0%	?
23		12.0	13.0	8.3%	?
24		34.0	34.9	2.6%	?
25		62.0	62.4	0.6%	?
26		62.0	62.5	0.8%	?
27		37.0	38.0	2.7%	?
Sheet5

Cell Formulas
Range	Formula
C5:E5	C5	=UNICHAR(C4)
C6:E6	C6	=UNICODE(C5)
D15:D27	D15	=ABS(C15/B15-1)
E15:E27	E15	=IF(D15>$C$11,UNICHAR(128577),IF(D15<$C$10,UNICHAR(128578),UNICHAR(128528)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E15:E27	Expression	=D15>$C$11	text	NO
E15:E27	Expression	=D15<=$C$10	text	NO
E15:E27	Expression	=D15<=$C$11	text	NO
C15:C27	Expression	=D15>$C$11	text	NO
C15:C27	Expression	=D15<=$C$10	text	NO
C15:C27	Expression	=D15<=$C$11	text	NO

Thanks for the welcome, and the solution! It's much simpler than the formulas I was thinking I was going to end up using.

 

[DRSteele]

You're welcome. Glad to have helped.