Conditional Formatting Formula Help

[mpeterson1227]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

0down votefavorite ​

I'm trying to create a formula for conditional formatting that will identify two words and create an output of a word. See example below: Right now I'm having to manually put in the Risk Type. I want it to output the risk type based on the likelihood and impact.

<tbody style="margin: 0px; padding: 0px; border: 0px;">
</tbody>

</body>

down votefavorite ​

I'm trying to create a formula for conditional formatting that will identify two words and create an output of a word. See example below: Right now I'm having to manually put in the Risk Type. I want it to output the risk type based on the likelihood and impact.

<tbody style="margin: 0px; padding: 0px; border: 0px;">
</tbody>

Please help and thank you in advance!

 

[FDibbins]

Hi, welcome to the board

Your post came through a bit weird, but based on this part...

I'm trying to create a formula for conditional formatting that will identify two words and create an output of a word.

Conditional Formatting is only used to change the appearance of a cell, it cannot add/change/put anything into a cell.

I cannot see your images, but can you explain what you have and what you want?

 

[mpeterson1227]

Oh wow, I'm sorry. Thanks for the welcome!

So I'm creating a risk register but it is very basic. I have a Likelihood column, an Impact column and a Risk column. I wanted to find a formula to where it would input the type of risk based on likelihood and impact.

Likelihood + Impact (without numbers) = Risk. I'm having difficulties because it's using text and not numbers. Numbers just makes it too complicated.

Can you see this picture?

 

[mpeterson1227]

Here is the chart


https://www.google.com/search?q=ris...Kzkk_1OjRRuEWmFkiVSqCCH&imgrc=bzMHDCqwqujpZM:

 

[FDibbins]

Still cannot see images (I mostly cannot), but based on your description, see if this this is what you want...

	A​	B​	C​	D​	E​	F​
2​	Likelihood	Impact	Risk		Likelihood	Low
3​	low	none	1​		Impact	small
4​	Medium	none	2​		Risk	4​
5​	High	none	3​
6​	low	small	4​
7​	Medium	small	5​
8​	High	small	6​

F2 and F3 are your criteria
F4=INDEX($C$3:$C$8,MATCH(F2&" "&F3,INDEX($A$3:$A$8&" "&$B$3:$B$8,0),0))

 

[Guru008]

Hi

Try this

=IF(AND(A1="Very Likely",B1="minor"),"Medium",
IF(AND(A1="Very Likely",B1="Moderate"),"High",
IF(AND(A1="Very Likely",B1="Major"),"Extreme",
IF(AND(A1="Likely",B1="minor"),"Low",
IF(AND(A1="Likely",B1="Moderate"),"Medium",
IF(AND(A1="Likely",B1="Major"),"High",
IF(AND(A1="Unlikely",B1="minor"),"Low",
IF(AND(A1="Unlikely",B1="Moderate"),"Low",
IF(AND(A1="Unlikely",B1="Major"),"Medium", "")))))))))

I am able to response in real time to you until its solve!

 

[mpeterson1227]

Very Likely	Acceptable Risk Medium 2	Unacceptable Risk High 3	Unacceptable Risk Extreme 5
Likely	Acceptable Risk Low 1	Acceptable Risk Medium 2	Unacceptable Risk High 3
Unlikely	Acceptable Risk Low 1	Acceptable Risk Low 1	Acceptable Risk Medium 2
	Minor	Moderate	Major

<colgroup><col span="4"></colgroup><tbody>
</tbody>

 

[mpeterson1227]

Wow, I think that worked!!!!! I just adjusted the cell #s and it changed. Then I was able to put a conditional formatting to colors based on the text in that column. Thank you soo soo much!!

Thank you FDibbins and Guru008

 

[FDibbins]

Not sure what that sample is supposed to represent, you said you had 3 columns - Likelihood, Impact and Risk (where you wanted the answer)?

Did you see my suggested method in post #5?

 

[mpeterson1227]

Yes I did but I was trying to stay with the same format that I had and I didn't want to utilize numbers.

This issues has been solved by using the formula listed above.


Thanks for your help!!