if function with exceptions to the rule

Question

Hi again folks.  I have a 4x4 risk matrix and have an if statement to classify  a 4x4 calculation as low, med, high or very high.

the statement is ( as usefully guided by yourselves !) 

=IFS(M3<=2;"Low";M3<=6;"Medium";M3<=9;"High";M3>9;"Very High"

 i need to create an exception to the above rule for 2 intersects on the matrix  i.e.   x- axis value ( 1 ) x  y- axis value (4)  = (4) should be " High" 

and x-axis value (2) x y -axis value (3) = (6) should be " High" 

Please could you advise...

Many thanks

Answer 1

If you were to use just the matrix, won't you get the correct value each time?

I think using the numbers as a factor is introducing the problem, because a 4 can be the result of three different constellations and only two of them are medium, according to the matrix. Therefore, the IF formula does no good.

So, use the Index/Match formula to query the matrix based on the text values for likelihood and impact, and the result should be correct. You can still calculate and show the number, but the number alone will not give the correct risk rating.

Answer 2

If you want to get the exposure text from the matrix, then let's assume the cell with the word "Certain" is B1 on Sheet2. 

Assuming the cell M3 is the one that has the 8 in the first screenshot, you could get the matrix value like this:

=index (sheet2!$C$1:$G$4,match(I3,Sheet2!$B$1:$B$4,0),match(K4,Sheet2!$C$5:$G$5,0))

In words, find the row in sheet2 that has the word from cell I3 in column B, find the column in sheet2 that has the word from cell K2 in row 5. Return the value where that row and that column cross.

This will return the l, m, h, vh values from the matrix. You may want to change the text in the matrix to be "High", "Very high", etc. 

I don't quite understand in which situation you want to use the IF statement for the result and in which situation you want to use the matrix instead. 

Can you explain that logic with a few more words?

Answer 3

Hi All ,   assistance please - 

I am trying to create  an exception to an IF statement / rule previously you have ablely an expeditiously assisted with... ...i.e converting values to " text" .  the IF statement to which i want to append the exceptions to is...

=IFS(M3<=2;"Low";M3<=6;"Medium";M3<=9;"High";M3>9;"Very High")

example first line below, the IF the formula converts the exposure nr of 8 to a rating of " High" - this holds true for the entire matrix (see further below), but i want to amend the rule in the  instance of the 2nd line, i.e. 1X4 to reflect a " High" narrative - irrespective of the >=<  if statement). Similarly for the third item below which i also want to have reflect as " High"   (The actual number is not that important).  

make sense? Please advise how i should adjust this formula...

many thanks 

Answer 4

Hi teyln -  apologies for the late reply,see below for context ( i see in retrospect this would not have made a lot of sense to someone else.....) 

example first line below, the IF the formula converts the exposure nr of 8 to a rating of " High" - this holds true for the entire matrix (see further below), but i want to amend the rule in the  instance of the 2nd line, i.e. 1X4 to reflect a " High" narrative

• irrespective of the >=<  if statement). Similarly for the third item below which i also want to have reflect as " High"   (The actual number is not that important).  

makes sense? 

many thanks 

Answer 5

Hello,

Your formula looks only at one cell. How does the 4x4 matrix come in? In which cells is the matrix? Which cell has the value that you want to look up in the matrix?

Can you post a screenshot and explain in context?