Assign a Numerical Value to Text

[Tigeraif]

Hi.

I have 3 columns. The first has a dropdown to include the values Very High, High, Significant, Medium, Low.

The second and third have a text result from another formula.

I would like to conditional format the second and third so that if they are equal to or greater than the first column they for red fill.

To do this, do I need to assign a value to all 3 cells/columns to do this comparison such as:

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=246><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" span=2 width=82><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; WIDTH: 62pt; HEIGHT: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=40 width=82>Acceptable Risk</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82>Risk Before Treatments</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82>Risk After Treatments</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Medium</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Very High</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Significant</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Medium</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Very High</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #92d050; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>Low</TD></TR></TBODY></TABLE>

I have set the acceptable risk to medium. As Very High and Significant are higher than Medium then they are Red. As Low is valued lower than Medium it is Green. Medium in this case would also be green as it matches the acceptable risk.

I need to covertly assign a numercial value to each to undertake the conditional format but still show the text instead of the numercal value. I hope this isn't too confusing.

 

[T. Valko]

Hi.

I have 3 columns. The first has a dropdown to include the values Very High, High, Significant, Medium, Low.

The second and third have a text result from another formula.

I would like to conditional format the second and third so that if they are equal to or greater than the first column they for red fill.

To do this, do I need to assign a value to all 3 cells/columns to do this comparison such as:

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=246 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" span=2 width=82><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 30pt" height=40><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: gray" width=82 height=40>Acceptable Risk</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=82>Risk Before Treatments</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: gray" width=82>Risk After Treatments</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Medium</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red">Very High</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red">Significant</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Medium</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red">Very High</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050">Low</TD></TR></TBODY></TABLE>

I have set the acceptable risk to medium. As Very High and Significant are higher than Medium then they are Red. As Low is valued lower than Medium it is Green. Medium in this case would also be green as it matches the acceptable risk.

I need to covertly assign a numercial value to each to undertake the conditional format but still show the text instead of the numercal value. I hope this isn't too confusing.

What version of Excel are you using?

 

[Tigeraif]

I am using 2010

 

[T. Valko]

I am using 2010

Try this...

List the classifications in a range of cells in this specific order:

Low
Medium
Significant
High
Very High

Let's assume that list is in the range H1:H5.

Setup the formatting...

Let's assume the range to format is B2:C10.

Select the *entire* range B2:C10 starting from cell B2.
Cell B2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=MATCH(B2,$H$1:$H$5,0)>MATCH($A2,$H$1:$H$5,0)

Click the Format button
Select the Fill tab
Select a shade of RED
Click OK twice

New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=MATCH(B2,$H$1:$H$5,0)>MATCH($A2,$H$1:$H$5,0)=FALSE

Click the Format button
Select the Fill tab
Select a shade of GREEN
OK out

 

[Tigeraif]

My apologise I wasn't entirely accurate. The first cell will be either Low, Medium, Significant, High or Very High. The other two cells will be populated as the result of a formula and will be assigned a priority depending on the consequence and likelihood to allow us to prioritise mitigation.

So the text in the second and third columns will look like this:

Very High (23)
Very High (21)
Significant (17)
Medium (11)
and so on..

I want to exlude the numerical value and just assess the text against column 1 and format if greater then column 1. Does this make sense?

 

[T. Valko]

My apologise I wasn't entirely accurate. The first cell will be either Low, Medium, Significant, High or Very High. The other two cells will be populated as the result of a formula and will be assigned a priority depending on the consequence and likelihood to allow us to prioritise mitigation.

So the text in the second and third columns will look like this:

Very High (23)
Very High (21)
Significant (17)
Medium (11)
and so on..

I want to exlude the numerical value and just assess the text against column 1 and format if greater then column 1. Does this make sense?

Ok, try this:

List the classifications in a range of cells in this specific order:

Low
Medium
Significant
High
Very High

Let's assume that list is in the range H1:H5.

Setup the formatting...

Let's assume the range to format is B2:C10.

Select the *entire* range B2:C10 starting from cell B2.
Cell B2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=MATCH(LEFT(B2,FIND(" (",B2)-1),$H$1:$H$5,0)>MATCH($A2,$H$1:$H$5,0)

Click the Format button
Select the Fill tab
Select a shade of RED
OK out

 

[Tigeraif]

Thank you very much it worked a treat.

 

[T. Valko]

Thank you very much it worked a treat.

You're welcome. Thanks for the feedback!