+ Reply to Thread
Results 1 to 7 of 7

Risk matrix table with multiple returns in single cell

  1. 11-13-2018, 02:12 AM #1
    GreatGizmo
    GreatGizmo is offline
    Registered User
    Join Date
    11-13-2018
    Location
    New Zealand
    MS-Off Ver
    MS Office 365
    Posts
    2

    Risk matrix table with multiple returns in single cell

    "Hi,

    I am using Office for 365 with Windows 10.

    I am trying to create a risk matrix table where the cells are automatically populated with inputs using data validation. The table will have Likelihood on the Y-axis and Consequences on the X-axis. My approach was to use IF/AND statements but I am limited to one return per cell, where there may be multiple returns required. There will be two drop downs for each event: "Likelihood" and "Consequences". For example, if I select "Likely" and "Minor" for "event 1", then I want "event 1" to appear in B3. Additionally, if I select "Likely" and "Minor" for "event 2", then I want "event 2" to also appear in B3 (in a bullet list etc).

    Any advice on how to achieve this will be appreciated.
    Reply With QuoteRegister To Reply
  2. 11-13-2018, 02:15 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Risk matrix table with multiple returns in single cell

    Welcome to the forum!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 11-13-2018, 02:23 AM #3
    GreatGizmo
    GreatGizmo is offline
    Registered User
    Join Date
    11-13-2018
    Location
    New Zealand
    MS-Off Ver
    MS Office 365
    Posts
    2

    Re: Risk matrix table with multiple returns in single cell

    Thank you, please see the attached file which shows a mock-up of what I want to achieve.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 11-18-2018, 10:19 AM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Risk matrix table with multiple returns in single cell

    This proposal employs a helper column (D8:D10) which may be moved and/or hidden for aesthetic purposes.
    The helper column is populated using: =IF(AND(B8=B7,C8=C7),D7&CHAR(10)&"-"&A8,"-"&A8)
    The matrix cells (B2:D4) are populated using:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Note that the matrix will need to be formatted to Wrap Text.
    Note: some terms in E8:F10 contained extra spaces.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 11-18-2018, 01:18 PM #5
    GeoffW283
    GeoffW283 is offline
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Risk matrix table with multiple returns in single cell

    JeteMc, I think there may be a problem with the helper column formula. As far as I can see it seems to assume that events with the same likelihood and consequence are in consecutive rows which I don't think has to be the case. For example, change Event 1 to "Likely" / "Moderate" and now Event-1 does not appear anywhere in the matrix.
    Reply With QuoteRegister To Reply
  6. 11-18-2018, 02:20 PM #6
    Bosco
    Bosco is offline
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Risk matrix table with multiple returns in single cell

    Try................

    1] In "Helper" D8, copied down :

    ="-"&A8&IFERROR(CHAR(10)&INDEX(D9:D$12,MATCH(1,INDEX((B9:$B$12=B8)*(C9:$C$12=C8),0),0)),"")

    2] In "Output table" B2, copied across to D2 and down :

    =IFERROR(INDEX($D$8:$D$10,MATCH(1,INDEX(($B$8:$B$10=$A2)*($C$8:$C$10=B$1),0),0)),"")

    Regards
    Bosco
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 11-19-2018, 12:57 AM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Risk matrix table with multiple returns in single cell

    Since you have Office 365 this is another way.

    First there are trailing spaces in the Data Validation lists. Those need to be removed or they will not always match the headers in row 1.

    Then array enter this in B2, fill down and across D4.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by FlameRetired; 11-19-2018 at 01:00 AM.
    Dave
    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. [SOLVED] Risk Matrix Conditional Formatting
    By heepo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-22-2021, 09:12 PM
  2. [SOLVED] How to create risk matrix chart in Excel?
    By davorin in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-28-2015, 05:08 PM
  3. Trying to create a Risk Matrix
    By jmcnair in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-01-2014, 08:29 PM
  4. [SOLVED] Multiple IF Functions and help with risk matrix?
    By PJ2012 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2012, 10:38 PM
  5. Risk Matrix
    By BAW331 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2012, 04:30 PM
  6. [SOLVED] Risk Matrix/Decision table
    By mikmak in forum Excel General
    Replies: 4
    Last Post: 04-23-2012, 11:31 PM
  7. Risk Matrix
    By Stoffer in forum Excel General
    Replies: 4
    Last Post: 01-24-2012, 05:53 AM

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