+ Reply to Thread
Results 1 to 11 of 11

Logical commands based on criteria

  1. 03-30-2014, 02:29 AM #1
    rtedesco
    rtedesco is offline
    Registered User
    Join Date
    03-19-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Logical commands based on criteria

    Hello everyone

    Thank you very much for helping me with my first post regarding index/match.

    Please view my attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands. I was wondering if you guys could think of any better way to do this. Please let me know your thoughts.

    Kind regards
    Ric
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-30-2014, 02:48 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Logical commands based on criteria

    Looking at the formula you have in column M, I dont think that is working for you.
    =VLOOKUP(IF(C5="H",1,IF(E5="H",2,IF(G5="H",3,IF(I5="H",6,IF(K5="H",8,IF(L5="High",9,IF(L5="Medium",10,IF(L5="Low",11,"Error")))))))),risk_rank,2,0)

    broken down, that is...
    =VLOOKUP(
    ...IF(C5="H",1,
    ...IF(E5="H",2,
    ...IF(G5="H",3,
    ...IF(I5="H",6,
    ...IF(K5="H",8,
    ...IF(L5="High",9,
    ...IF(L5="Medium",10,
    ...IF(L5="Low",11,"Error")))))))),risk_rank,2,0)

    All of your entries in row 5 are H, but your formula will stop looking after the very 1st time it returns TRUE - in this case "IF(C5="H",1," You also seem to be missing some ranges there too? (4, 5 - and 7 M/L)

    How about if, instead of letters, you gave a value? You could then add (or average) the values for an over-all rating?

    Another suggestion...instead of using all those nested IF's in teh rating columns, you could create a small(ish) table with all the rating categories and "scores", and then use a vlookup to find teh score. This table could contain all possible categories wand scored
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 03-30-2014, 02:59 AM #3
    rtedesco
    rtedesco is offline
    Registered User
    Join Date
    03-19-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Logical commands based on criteria

    Hi Ford

    Thank you for your prompt response. Is there any way to keep it using logical commands?

    Cheers
    Ric
    Reply With QuoteRegister To Reply
  4. 03-30-2014, 03:40 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Logical commands based on criteria

    With a small table like this....
    D
    E
    2
    		 Undefined H
    3
    		 Defined M/L
    4
    		 Repetative M/L
    5
    		 Short(tight) H
    6
    		 No M/L
    7
    		 Yes H
    8
    		 True H
    9
    		 False M/L


    You can then use 1 simple vlookup() for all the rating columns...
    =IF(B5="","",VLOOKUP(B5,Sheet1!$D$2:$E$9,2,0))

    Now, for the overall rating, what exactly are you trying to do there?
    Reply With QuoteRegister To Reply
  5. 03-30-2014, 04:18 AM #5
    rtedesco
    rtedesco is offline
    Registered User
    Join Date
    03-19-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Logical commands based on criteria

    That sounds like a much better idea, thanks for that.

    With the overall rating (col M), it takes into consideration cols C,E,G,I,K and L- if two or more have a rating that is H, then H is the overall rating. Also, where two elements within the cols C,E,G,I,K and L are H and the risk rating is Medium, then M/L is the overall rating.
    Reply With QuoteRegister To Reply
  6. 03-30-2014, 04:43 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Logical commands based on criteria

    This will give you the "H" for your 1st part...
    =IF(COUNTIF($C5:$L5,"H")>2,"H","")

    Buit Im a bit confused with the 2nd part....
    "where two elements within the cols C,E,G,I,K and L are H and the risk rating is Medium, then M/L is the overall rating."
    If it is H, how can it be Medium as well?
    Reply With QuoteRegister To Reply
  7. 03-30-2014, 04:58 AM #7
    rtedesco
    rtedesco is offline
    Registered User
    Join Date
    03-19-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Logical commands based on criteria

    Quote Originally Posted by FDibbins View Post
    This will give you the "H" for your 1st part...
    =IF(COUNTIF($C5:$L5,"H")>2,"H","")

    Buit Im a bit confused with the 2nd part....
    "where two elements within the cols C,E,G,I,K and L are H and the risk rating is Medium, then M/L is the overall rating."
    If it is H, how can it be Medium as well?


    Sorry mate, it should read "where less than 2 elements..."
    Reply With QuoteRegister To Reply
  8. 03-30-2014, 05:02 AM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Logical commands based on criteria

    ok then try this....
    =IF(COUNTIF($C5:$L5,"H")>=2,"H","M/L")
    Reply With QuoteRegister To Reply
  9. 04-01-2014, 12:10 AM #9
    rtedesco
    rtedesco is offline
    Registered User
    Join Date
    03-19-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Logical commands based on criteria

    Hi Ford

    Thank you for your suggestions. Please view them as attached. The only issue that I have is that if all the other ratings are M/L and one project risk is high, the overall rating must be high. Can you please help me with the overall risk rating?

    Kind regards
    Ric
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 04-01-2014, 12:15 AM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Logical commands based on criteria

    Tr this...
    =IF(OR(L2="High",COUNTIF($C2:$L2,"H")>=2),"H","M/L")
    Reply With QuoteRegister To Reply
  11. 04-01-2014, 12:41 AM #11
    rtedesco
    rtedesco is offline
    Registered User
    Join Date
    03-19-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Logical commands based on criteria

    Thank you very much
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. multiple criteria/logical tests required.
    By Imamu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-28-2013, 08:57 AM
  2. Macro Recording Chart Format commands missing all important commands!!!!
    By nounours in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-05-2013, 09:20 PM
  3. Base a logical test on a formatting criteria
    By shakes347 in forum Excel General
    Replies: 5
    Last Post: 09-08-2010, 02:24 AM
  4. If commands based on cell value
    By khalid79m in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-17-2007, 09:10 AM
  5. Logical Function for three criteria?
    By CardioGirl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-13-2006, 12:01 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1