I am trying to analyze hazards for a project. I have a table of potential hazards, where the risks are reduced to a hazard number:
Task Hazard H# Consequence Level Likelihood Level Build base Insufficient pylons BB-1 3 2 Build base Require more Vespian gas BB-2 2 5 Launch Missiles Target lock fail LM-1 5 2
I want to display those on what is essentially a re-formatted sub-table, like this:
L 5 BB-2 I 4 K 3 E 2 BB-1 LM-1 L 1 I 1 2 3 4 5 C O N S E Q
I have some 60 hazards, so each hazard square will have multiple H#'s on it. For example, if I had an LM-2 hazard with the same likelihood/consequence vales as BB-1, then the cell for (2,3) would need to contain the string "BB-1, LM-2", or at least both the "BB-1" and "LM-2" strings so they are both readable. I have tried two approaches:
- A scatter plot. I couldn't figure out how to label the points with the text of the H# cell, and because the points overlap exactly I can only ever see the first or last point plotted there.
- I tried to build a string using some fancy INDEX() non-sense, but best result with INDEX was an array formula. As the arrays are of unknown/changing size, I couldn't really build a table based on that approach.
Any thoughts on how to do this?
Bookmarks