Display formatted text based on result in adjacent cell.

JimH

Board Regular
Joined
Apr 21, 2002
Messages
94
Office Version
  1. 2010
Platform
  1. Windows
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
BCDEFGHI
2Action ItemStrategic ValueEaseFinancial BenefitCostResource ImpactOverall PriorityPriority Rating
30.00
ProjectPrioritization
Cell Formulas
RangeFormula
H3H3=SUM(C3:G3)/5
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,633
Office Version
  1. 365
Platform
  1. Windows
Is this what you mean?

21 03 13.xlsm
CDEFGHI
2Strategic ValueEaseFinancial BenefitCostResource ImpactOverall PriorityPriority Rating
322.3141.42.14NEED TO DO
41.21.21.21.21.21.2MUST DO
553.5422.93.48SHOULD DO
610.20.20.40.36 
74.23.94.254.24.3COULD DO
ProjectPrioritization
Cell Formulas
RangeFormula
H3:H7H3=SUM(C3:G3)/5
I3:I7I3=IFERROR(LOOKUP(H3,{1,2,3,4},{"MUST DO","NEED TO DO","SHOULD DO","COULD DO"}),"")
 
Solution

JimH

Board Regular
Joined
Apr 21, 2002
Messages
94
Office Version
  1. 2010
Platform
  1. Windows
Thanks Peter. That did the trick. I just copied over my conditional formatting from another worksheet and it's perfect.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,633
Office Version
  1. 365
Platform
  1. Windows
Thanks Peter. That did the trick.
Good news! (y)

I just copied over my conditional formatting from another worksheet and it's perfect.
:oops: Sorry, I meant to include my CF but forgot. This is what I used.

21 03 13.xlsm
CDEFGHI
2Strategic ValueEaseFinancial BenefitCostResource ImpactOverall PriorityPriority Rating
322.3141.42.14NEED TO DO
41.21.21.21.21.21.2MUST DO
553.5422.93.48SHOULD DO
610.20.20.40.36 
74.23.94.254.24.3COULD DO
ProjectPrioritization
Cell Formulas
RangeFormula
H3:H7H3=SUM(C3:G3)/5
I3:I7I3=IFERROR(LOOKUP(H3,{1,2,3,4},{"MUST DO","NEED TO DO","SHOULD DO","COULD DO"}),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:I7Expression=LEFT(I3,1)="C"textNO
I3:I7Expression=LEFT(I3,1)="S"textNO
I3:I7Expression=LEFT(I3,1)="N"textNO
I3:I7Expression=LEFT(I3,1)="M"textNO
 

Forum statistics

Threads
1,141,498
Messages
5,706,695
Members
421,465
Latest member
Dunners

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top