You have a 5x5 Risk and Severity Matrix. Separately, there is a database of events. Each is assigned a risk and severity. Place the record number from each event in the proper box. If more than one record falls in a cell, join them with commas in between. After consulting with ExcelisFun, I went with a custom function in VBA. Can you think of a formula that wouldn't be repetitively silly?
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1773.
Join Answers in a Matrix.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question, this is a doozy I sent in by Tom and actually I wanted to do this on Friday.
Wanted this to be a dueling Excel podcast because my solution involves some VBA.
I sent this off to Mike Excel Is Fun Girvin. I said Mike can you come up with a formula.
He says, oh the formula would just be ridiculously long it would be silly and there's no way we could fit it in the amount of time.
So, I think we're going to have the VBA solution here, here's what we have.
We have a matrix a Risk Matrix severity 1, 2, 3, 4, 5.
Probability 1, 2, 3, 4, 5 and in the intersection of each of these cells.
We want to find all of the rows all of the records that have that probability All right! So, here's record number 1 probability is 5 severity is 5.
All right! So, probability 5 severity 5, so there's that one that one means that it's record number one is falling there and then there's, there's a whole bunch of different records down here right and so, we need to concatenate all of the answers and sometimes there's not going to be any answers at all that's, that'll be the blank cells and you know this is, this would be a really, really tough formula if you think about it how would you start to build this, at least I couldn't come up with anything. So, what I did is you know sometimes we'll create custom functions in Excel and I said wouldn't be nice if we had a function, where we could give it the probability, give it the severity and then give it this range here.
The range of record numbers, knowing that Tom is always gonna have the probability in the next column and severity in the next column and you know see if we could just loop through, all of the rows.
So, here let me show you what we came up with. I'll do Alt+F11, do insert module and we type this new module function instead of sub I call it prob sub score probability severity score we're going to pass it the probability as an integer pass of the severity as an integer and then number NBR as range.
All right, we wanna make sure this function is volatile because I will calculate even if any of those other items out there change because we might be changing things to the right of the number range or two columns to the right of the number range.
So, that's why we have to make it volatile, dimension cell is a range stand then start out initialize this result as quote, quote.
If we don't find any matches at all we're just going to return a quote quote for each cell in number. I figure out the peace core.
So, it's the cell offset zero rows, down one column over get that rightmost column add zero to convert it to from text to a number and then the severity score same thing here.
All right, then if the peace core is equal to the probability that they passed, and the S core is equal to the severity that they passed then if this is not the first one for this particular square that was a match we're going to add whatever we had before and a comma and then the probabilities vary score is equal to the cell dot value that's the record number record number all right so here's how we actually build this in Excel so it's an actual function and I'll just build a new one equal prob see it comes up in the list and first thing we do is the probability so that's this answer here I always want to make sure that we lock this down to come back to the column so I'm going to press f4 one two three times to put the dollar sign before the C 26 comma and then the severity up here we'll press f4 one two times to put the dollar sign before the twenty five comma and then my range of numbers we'll do control shift down arrow press f4 just once to lock that down and press ENTER and it comes up with number five all right so let's do a test here we're gonna take this record number one which you see is currently being reported right there and we'll change the severity to from critical five insignificant one and sure enough it flew from this cell back to that cell isn't that cool now it's too bad we have to do VBA there's is really no well the formula would just be incredibly long but in this case VBA actually solves the problem I'm sure that tom is going to be think this this has to be easier but unfortunately at least as far as I can tell and I'm sure you'll let me know if there's something easier down in the YouTube comments this seems to be the best way to go by the way I sent this workbook back to Tom so he doesn't have to try and type all this in and hey not to beat a dead horse yesterday I talked about merging cells right here here's an example where it in my opinion is acceptable to merge cells these cells up here you see are not merged those are just single cells we select the whole range and control one and center across selection that way I'm allowed to just kind of cruise through here and when I touch that cell it doesn't expand my range or I said merge cells are generally bad you want to use center cross selection as much as you can you see it just expanded the range there I don't have a good way to do the vertical except for merging cells so reluctantly I'll let that one in but again center cross selection better than merge cells all the time hey I want to thank Tom for saying that question in and I want to thank you for stopping by we'll see you next time for another net cast from mr. Excel there's a lot of different ways to connect with me and social media of course Facebook and Twitter have been around for a long time but Google+ I'm now on Google+ so you can add me to your circle I'll add you back to my excel circle if I get any good Excel tips put those out on Google+ either build yelling or mr. Excel comm we have two different pages out there on Google+
Learn Excel form MrExcel podcast episode 1773.
Join Answers in a Matrix.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question, this is a doozy I sent in by Tom and actually I wanted to do this on Friday.
Wanted this to be a dueling Excel podcast because my solution involves some VBA.
I sent this off to Mike Excel Is Fun Girvin. I said Mike can you come up with a formula.
He says, oh the formula would just be ridiculously long it would be silly and there's no way we could fit it in the amount of time.
So, I think we're going to have the VBA solution here, here's what we have.
We have a matrix a Risk Matrix severity 1, 2, 3, 4, 5.
Probability 1, 2, 3, 4, 5 and in the intersection of each of these cells.
We want to find all of the rows all of the records that have that probability All right! So, here's record number 1 probability is 5 severity is 5.
All right! So, probability 5 severity 5, so there's that one that one means that it's record number one is falling there and then there's, there's a whole bunch of different records down here right and so, we need to concatenate all of the answers and sometimes there's not going to be any answers at all that's, that'll be the blank cells and you know this is, this would be a really, really tough formula if you think about it how would you start to build this, at least I couldn't come up with anything. So, what I did is you know sometimes we'll create custom functions in Excel and I said wouldn't be nice if we had a function, where we could give it the probability, give it the severity and then give it this range here.
The range of record numbers, knowing that Tom is always gonna have the probability in the next column and severity in the next column and you know see if we could just loop through, all of the rows.
So, here let me show you what we came up with. I'll do Alt+F11, do insert module and we type this new module function instead of sub I call it prob sub score probability severity score we're going to pass it the probability as an integer pass of the severity as an integer and then number NBR as range.
All right, we wanna make sure this function is volatile because I will calculate even if any of those other items out there change because we might be changing things to the right of the number range or two columns to the right of the number range.
So, that's why we have to make it volatile, dimension cell is a range stand then start out initialize this result as quote, quote.
If we don't find any matches at all we're just going to return a quote quote for each cell in number. I figure out the peace core.
So, it's the cell offset zero rows, down one column over get that rightmost column add zero to convert it to from text to a number and then the severity score same thing here.
All right, then if the peace core is equal to the probability that they passed, and the S core is equal to the severity that they passed then if this is not the first one for this particular square that was a match we're going to add whatever we had before and a comma and then the probabilities vary score is equal to the cell dot value that's the record number record number all right so here's how we actually build this in Excel so it's an actual function and I'll just build a new one equal prob see it comes up in the list and first thing we do is the probability so that's this answer here I always want to make sure that we lock this down to come back to the column so I'm going to press f4 one two three times to put the dollar sign before the C 26 comma and then the severity up here we'll press f4 one two times to put the dollar sign before the twenty five comma and then my range of numbers we'll do control shift down arrow press f4 just once to lock that down and press ENTER and it comes up with number five all right so let's do a test here we're gonna take this record number one which you see is currently being reported right there and we'll change the severity to from critical five insignificant one and sure enough it flew from this cell back to that cell isn't that cool now it's too bad we have to do VBA there's is really no well the formula would just be incredibly long but in this case VBA actually solves the problem I'm sure that tom is going to be think this this has to be easier but unfortunately at least as far as I can tell and I'm sure you'll let me know if there's something easier down in the YouTube comments this seems to be the best way to go by the way I sent this workbook back to Tom so he doesn't have to try and type all this in and hey not to beat a dead horse yesterday I talked about merging cells right here here's an example where it in my opinion is acceptable to merge cells these cells up here you see are not merged those are just single cells we select the whole range and control one and center across selection that way I'm allowed to just kind of cruise through here and when I touch that cell it doesn't expand my range or I said merge cells are generally bad you want to use center cross selection as much as you can you see it just expanded the range there I don't have a good way to do the vertical except for merging cells so reluctantly I'll let that one in but again center cross selection better than merge cells all the time hey I want to thank Tom for saying that question in and I want to thank you for stopping by we'll see you next time for another net cast from mr. Excel there's a lot of different ways to connect with me and social media of course Facebook and Twitter have been around for a long time but Google+ I'm now on Google+ so you can add me to your circle I'll add you back to my excel circle if I get any good Excel tips put those out on Google+ either build yelling or mr. Excel comm we have two different pages out there on Google+
