IF conditional Help

[Bahaa]

I have rows and columns and need to insert the below in concept in formula

Columns are Major Minor and Insignificant
Rows are LIkely possible and Unlikely

=IF(AND(OR(H5="Likely",H5="Possible", H5="Unlikely"),AND(J5="Insignificant","Minor","Major ")),"X","")

If columns with a certain selection were equal to likely or possible or unlikely and at the same time rows were equal to insignificant or minor or major then insert x

Thanks in advance for all replies

 

[Bahaa]

The link requests permission to download.
Or share the file in dropbox.

Hello Dante
Sorry i can not share ot as i have certain restrictions in my laptop
I sent it to your email
Hope we can reach to a solution
Many thanks

 

[DanteAmor]

It can't be by mail, you have to put the link here.
Or copy the excel data and paste it here.

Check if you can use this tool to copy cells and paste here.

Upload an excel range:
XL2BB - Excel Range to BBCode

 

[jasonb75]

@Bahaa

Again, try reading and responding to post 25, if you read and followed all of the advice and suggestions provided instead of becoming fixated on attaching the file then the problem could have been solved by now.

In addition to the points already raised, are you entering the formula into the cell with the X in your screen capture, then copying / dragging it to the other red, yellow and green cells?
If you are doing that then the formula will not work! You will need a different formula for that, but you have not provided enough information for us to know what that formula would be.

 

[Bahaa]

@Bahaa

Again, try reading and responding to post 25, if you read and followed all of the advice and suggestions provided instead of becoming fixated on attaching the file then the problem could have been solved by now.

In addition to the points already raised, are you entering the formula into the cell with the X in your screen capture, then copying / dragging it to the other red, yellow and green cells?
If you are doing that then the formula will not work! You will need a different formula for that, but you have not provided enough information for us to know what that formula would be.

Hello Jason
No i am not entering the X in every screenshot, this is the formula
I ma trying to use the XL2BB and please do forgive me for any frustrations i have made, since i am new to this
BR

 

[DanteAmor]

Hi @Bahaa,

The problem is that the "Risk Matrix" sheet has a space to the right of the words:
"Insignificant "
"Minor "
"Major "
Eliminate that space in words.
It also removes the space of those words in all formulas:

I attach the correct formulas:

To red:
=SI(O(Y(H5="Likely",J5="Minor"),Y(H5="Likely",J5="Major"),Y(H5="Possible",J5="Major")),"X","")

To yellow box cell N5
=SI(O(Y(H5="Likely",J5="Minor"),Y(H5="Posible",J5="Insignificant")),"X","")

To yellow box cell O6
=SI(Y(H5="Possible",J5="Major"),"X","")

To green box
=SI(O(Y(H5="UnLikely",J5="Insignificant"),Y(H5="Possible",J5="Insignificant"),Y(H5="UnLikely",J5="Minor")),"X","")

________________________________

I understand perfectly that it is forbidden to share files by mail, in fact I mentioned it in post #32, here I put the file so that everyone can see it.
The file:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

________________________________

@Bahaa, Please, don't send files to my email in the future, I don't know how you got my email. You must put images here or copy ranges of cells and paste here.

 

[Bahaa]

It can't be by mail, you have to put the link here.
Or copy the excel data and paste it here.

Check if you can use this tool to copy cells and paste here.

Upload an excel range:
XL2BB - Excel Range to BBCode

Hello Dante
Many thanks
Hope i got you with this

Book1
	G	H	I	J	K	L	M	N	O	P
4	Likelihood Rating	Likelihood	Impact Rating	Impact	Risk Category	Risk Matrix
5	A	Likely	1	Insignificant	Medium	Likelihood	H
6							L	X
7								L	H
8							Impact
9	B	Possible	3	Major	High	Likelihood	H
10							L		X
11								L	H
12							Impact
13	A	Likely	2	Minor	High	Likelihood	H	X
14							L
15								L	H
16							Impact
Risk Register

Cell Formulas
Range	Formula
J5, J9, J13	J5	=IFERROR(IF(ISNUMBER(FIND($I5,"1 2 3 ",1)),VLOOKUP(I5,'Risk Codes'!$A$11:$B$13,2,FALSE),""),"")
K5, K9, K13	K5	=IF(ISNUMBER(FIND($G5&$I5,"A2 A3 B3",1)),"High",IF(ISNUMBER(FIND($G5&$I5,"A1 B1 B2 C2 C3",1)),"Medium",IF(ISNUMBER(FIND($G5&$I5,"C1",1)),"Low","")))
N5, N13, N9	N5	=IF(AND(OR(H5="Likely",H5="Possible"),AND(J5="Minor ")),"X","")
O5, O13, O9	O5	=IF(AND(OR(H5="Likely"),AND(J5="Major ")),"X","")
N6, N14, N10	N6	=IF(AND(OR(H5="Likely",H5="Possible"),AND(J5="Insignificant ")),"X","")
O6, O14, O10	O6	=IF(AND(OR(H5="Possible"),AND(J5="Major ")),"X","")
H5, H9, H13	H5	=IFERROR(IF(ISNUMBER(FIND($G5,"A B C ",1)),VLOOKUP(TRIM($G5),'Risk Codes'!$A$4:$B$6,2,FALSE),""),"")

 

[Bahaa]

Hi @Bahaa,

The problem is that the "Risk Matrix" sheet has a space to the right of the words:
"Insignificant "
"Minor "
"Major "
Eliminate that space in words.
It also removes the space of those words in all formulas:

I attach the correct formulas:

To red:
=SI(O(Y(H5="Likely",J5="Minor"),Y(H5="Likely",J5="Major"),Y(H5="Possible",J5="Major")),"X","")

To yellow box cell N5
=SI(O(Y(H5="Likely",J5="Minor"),Y(H5="Posible",J5="Insignificant")),"X","")

To yellow box cell O6
=SI(Y(H5="Possible",J5="Major"),"X","")

To green box
=SI(O(Y(H5="UnLikely",J5="Insignificant"),Y(H5="Possible",J5="Insignificant"),Y(H5="UnLikely",J5="Minor")),"X","")

________________________________

I understand perfectly that it is forbidden to share files by mail, in fact I mentioned it in post #32, here I put the file so that everyone can see it.
The file:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

________________________________

@Bahaa, Please, don't send files to my email in the future, I don't know how you got my email. You must put images here or copy ranges of cells and paste here.

Hello Dnate
Many thanks really
Can you share it on google drive as in my laptop there certain restrictions and i cant open the dropbox
Really appreciate all the help
BR

 

[Bahaa]

Hi @Bahaa,

The problem is that the "Risk Matrix" sheet has a space to the right of the words:
"Insignificant "
"Minor "
"Major "
Eliminate that space in words.
It also removes the space of those words in all formulas:

I attach the correct formulas:

To red:
=SI(O(Y(H5="Likely",J5="Minor"),Y(H5="Likely",J5="Major"),Y(H5="Possible",J5="Major")),"X","")

To yellow box cell N5
=SI(O(Y(H5="Likely",J5="Minor"),Y(H5="Posible",J5="Insignificant")),"X","")

To yellow box cell O6
=SI(Y(H5="Possible",J5="Major"),"X","")

To green box
=SI(O(Y(H5="UnLikely",J5="Insignificant"),Y(H5="Possible",J5="Insignificant"),Y(H5="UnLikely",J5="Minor")),"X","")

________________________________

I understand perfectly that it is forbidden to share files by mail, in fact I mentioned it in post #32, here I put the file so that everyone can see it.
The file:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

________________________________

@Bahaa, Please, don't send files to my email in the future, I don't know how you got my email. You must put images here or copy ranges of cells and paste here.

I tried your formulas and unfortunately still nothing is being shown
Anyway i am really thankful for all the help and if you were able to adjust ot on my excel sheet file , just send the file via google drive link

 

[DanteAmor]

In the post #35 I put the formulas and instructions to make them work.

But I put the file shared on google

Inspection plan revision.xls

drive.google.com

 

[DanteAmor]

I tried your formulas and unfortunately still nothing is being shown
Anyway i am really thankful for all the help and if you were able to adjust ot on my excel sheet file , just send the file via google drive link

I'm sorry, I forgot to translate the formulas

User this:

To red:
=IF(OR(AND(H5="Likely",J5="Minor"),AND(H5="Likely",J5="Major"),AND(H5="Possible",J5="Major")),"X","")

To yellow box cell N5
=IF(OR(AND(H5="Likely",J5="Minor"),AND(H5="Posible",J5="Insignificant")),"X","")

To yellow box cell O6
=IF(AND(H5="Possible",J5="Major"),"X","")

To green box
=IF(OR(AND(H5="UnLikely",J5="Insignificant"),AND(H5="Possible",J5="Insignificant"),AND(H5="UnLikely",J5="Minor")),"X","")