Consulting

Results 1 to 10 of 10

Thread: Create a Loop - Help!

  1. #1

    Create a Loop - Help!

    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!
    Attached Files Attached Files

  2. #2
    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.

  3. #3
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    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.
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

  4. #4
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    Further Addition

    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.
    Last edited by Daxton A.; 01-07-2014 at 07:11 PM. Reason: Additional Info
    “All right now ya wise guy … Dance!”

  5. #5
    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.

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Quote Originally Posted by psctornado View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

    Cheers!

  9. #9
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    Or ?

    In L7:

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

  10. #10
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    Quote Originally Posted by psctornado View Post
    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 .
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •