Display formatted text based on result in adjacent cell.

[JimH]

In cell H3 I have a formula that calculates the average of cells C3:G3. If the value in the cell is the following, I would like the corresponding text displayed with the cell formatted in a specific color.

1-1.9 display MUST DO
2-2.9 display NEED TO DO
3-3.9 display SHOULD DO
4-5.0 display COULD DO

FacilitiesActionItems.xlsx
	B	C	D	E	F	G	H	I
2	Action Item	Strategic Value	Ease	Financial Benefit	Cost	Resource Impact	Overall Priority	Priority Rating
3							0.00
ProjectPrioritization

Cell Formulas
Range	Formula
H3	H3	=SUM(C3:G3)/5

 

[Peter_SSs]

Is this what you mean?

21 03 13.xlsm
	C	D	E	F	G	H	I
2	Strategic Value	Ease	Financial Benefit	Cost	Resource Impact	Overall Priority	Priority Rating
3	2	2.3	1	4	1.4	2.14	NEED TO DO
4	1.2	1.2	1.2	1.2	1.2	1.2	MUST DO
5	5	3.5	4	2	2.9	3.48	SHOULD DO
6	1	0.2	0.2	0.4		0.36
7	4.2	3.9	4.2	5	4.2	4.3	COULD DO
ProjectPrioritization

Cell Formulas
Range	Formula
H3:H7	H3	=SUM(C3:G3)/5
I3:I7	I3	=IFERROR(LOOKUP(H3,{1,2,3,4},{"MUST DO","NEED TO DO","SHOULD DO","COULD DO"}),"")

 

[JimH]

Thanks Peter. That did the trick. I just copied over my conditional formatting from another worksheet and it's perfect.

 

[Peter_SSs]

Thanks Peter. That did the trick.

Good news!

I just copied over my conditional formatting from another worksheet and it's perfect.

Sorry, I meant to include my CF but forgot. This is what I used.

21 03 13.xlsm
	C	D	E	F	G	H	I
2	Strategic Value	Ease	Financial Benefit	Cost	Resource Impact	Overall Priority	Priority Rating
3	2	2.3	1	4	1.4	2.14	NEED TO DO
4	1.2	1.2	1.2	1.2	1.2	1.2	MUST DO
5	5	3.5	4	2	2.9	3.48	SHOULD DO
6	1	0.2	0.2	0.4		0.36
7	4.2	3.9	4.2	5	4.2	4.3	COULD DO
ProjectPrioritization

Cell Formulas
Range	Formula
H3:H7	H3	=SUM(C3:G3)/5
I3:I7	I3	=IFERROR(LOOKUP(H3,{1,2,3,4},{"MUST DO","NEED TO DO","SHOULD DO","COULD DO"}),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:I7	Expression	=LEFT(I3,1)="C"	text	NO
I3:I7	Expression	=LEFT(I3,1)="S"	text	NO
I3:I7	Expression	=LEFT(I3,1)="N"	text	NO
I3:I7	Expression	=LEFT(I3,1)="M"	text	NO