Create a Loop - Help!

[psctornado]

Hi All,

Hope everyone had a great New Years! I am looking for a bit of help in setting up a loop. Attached is a sample .xlsx in which my goal is to automate the result so that it is one of the following : Negligible, Low, Medium, High, Very High.

Currently I set up a table in columns S:V in which has all the possible scenarios (1 severity, 1 occurence = Low) etc.

What I would like to do is that based on the values placed in F & H, I would like it to automatically produce 1 of the 5 options I noted above (Neg, Low, Med, High, Very High) in column K.

Would anyone be able to assist me in creating a loop as I'm guessing this is probably the most effecient way of handling this?

Thanks!

[ashleyuk1984]

I presume the best option for this would be an IF statement.
It's 00:12, so I don't really have the brain power to generate a simple If statement right now, but if it's not resolved by the morning, I'll write one.

[Teeroy]

Try the following array formula.

L7: =INDEX(V$3:V$27,MATCH(1,(F7=S$3:S$27)*(H7=T$3:T$27)*1,0))

Confirm the formula with CTRL + SHIFT +ENTER (not just enter). If you confirm correctly you'll see {} around the formula.
Fill this formula down as required.

[Daxton A.]

I would use an automatic tactic of nested loops.

=IF(AND(F7=1,H7=0) = TRUE,"Negligible",IF(AND(F7=1,H7=1) = TRUE,"Low",IF(AND(F7=2,H7=0) = TRUE,"Negligable",IF(AND(F7=2,H7=1) = TRUE,"Low",IF(AND(F7=3,H7=0) = TRUE,"Negligible",IF(AND(F7=3,H7=1) = TRUE,"Medium",X3))))))
then continue this loop into x3,y3,and then z3.

It is a lot of writing but it works. The reason that the formula is extended is because there can only be 7 nested IF statements per cell.

[psctornado]

Hey Daxton,

I had thought about nested loops, but they're limited aren't they? I thought the max was 9? I did use the formula provided by Teeroy, but I have always struggled with Index / Match functions.

Could anyone provide a simplified explanation of the formula?

The Daxton formula I understand completely, its the Index that I'm a bit puzzled by. So really there isn't a sense of creating VBA code if the Index function can just be dragged down, but was still curious if this is something I could do still.

[Aussiebear]

My understanding of the formula is:

That you are trying to place one of the values contained in the Result range (V$3:V$27) based on the relationship between the value in F7 (Sev) and the value in H7(Occ) by using an array formula. The value in f7 is trying to be matched to the range S$3:S$27 (Severity) and the value in H7 is being matched to the range T$3:T$27 (Occurrence). The *1 appears to be to overcome the issue of multiplication by zero (Value for Negligible), and zero asks for an exact match

[Bob Phillips]

I did use the formula provided by Teeroy, but I have always struggled with Index / Match functions.

Could anyone provide a simplified explanation of the formula?

It isn't too hard if you break it down.

(F7=S$3:S$27) looks up the value in F7 in the range S$3:S$27, which will return an array of TRUE/FALSE values depending upon whether those values match F7 or not.

Similarly, (H7=T$3:T$27) looks up H7 in T$3:T$27 and returns a separate array of TRUE/FALSe values.

Multiply them, (F7=S$3:S$27)*(H7=T$3:T$27), and you get a single array of 1/0 values, 1 where both conditions are met, 0 for anything else (TRUE*TRUE=1, any other combination =0).

You can then use MATCH looking up a value of 1 within this array, in other words find the first item in the 1/0 array where both conditions are met.

As MATCH returns the index in the lookup array, this can be passed to a similar array in the INDEX function, =INDEX(V$3:V$27,result_from_MATCH), to get the value in the range V$3:V$27 where both conditions are met.

BTW, the *1 that Teeroy provided is superfluous, multiplying the two TRUE/FALSE arrays will coerce the values into an array of 1/0 so that MATCH can work, so need to do it again with *1.

Working through the example

(F7=S$3:S$27) returns {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE ;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
(H7=T$3:T$27) returns {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;F ALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
(F7=S$3:S$27)*(H7=T$3:T$27) becomes {0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
MATCH(1,(F7=S$3:S$27)*(H7=T$3:T$27),0) resolves to MATCH(1,{0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0), which returns 4 - you should be able to work out why
=INDEX(V$3:V$27,MATCH(1,(F7=S$3:S$27)*(H7=T$3:T$27),0)) resolves =INDEX({"Negligble";"Low";"Negligble";"Low";"Negligble";"Medium";"Negligble ";"Medium";"Low";"Medium";"Low";"Medium";"High";"High";"High";"Medium";"Med ium";"High";"High";"High";"Medium";"High";"Very High";"Very High";"Very High"},MATCH(1,(F7=S$3:S$27)*(H7=T$3:T$27),0)) which further resolves to =INDEX({"Negligble";"Low";"Negligble";"Low";"Negligble";"Medium";"Negligble ";"Medium";"Low";"Medium";"Low";"Medium";"High";"High";"High";"Medium";"Med ium";"High";"High";"High";"Medium";"High";"Very High";"Very High";"Very High"},4) which then returns Low.

[psctornado]

Excellent! That makes great sense. I appreciate the help from all who chimed in.

Cheers!

[snb]

Or ?

In L7:

=CHOOSE(J7;"Negligible";"Low";"Medium";"High")

[Teeroy]

Could anyone provide a simplified explanation of the formula?

Hi psctornado,

Sorry I didn't explain the formula, for some reason I'm not getting email notification from subscribed threads. Luckily XLD has explained the formula in a much more comprehensive manner than I could have .