Hi Chandoo, that's a great tutorial and it worked perfectly for me. In my previous Risk Map in Excel I've only showed the Risk ID which made it more difficult to read since you always had to look up for the corresponding Risk title. With your solution, everything is visible at one spot. Great work!
Hi Chandoo, Please advise the formula by adding another column in the risk Register for the Risk Status - In Progress, Not Started, & Closing. Matrix should remove the Closed Risk.
I'd suggest that there be four risk levels, and if more, always an even number to prevent going to the middle. The middle level gets over-used. Plus this also avoids the huge error risk with three levels. Consider that the risks levels are really in diagonal bands, not stepped. The steps intrude into each other's error bands so can lead to huge errors where the top right corner of 'green' is within the error band of the bottom left corner of 'red'. A couple of errors of estimated probability of event and probability of damage can move a risk erroneously either from green to red, or red to green, resulting in misallocation of resources, or insufficient attention to a risk. That said a couple of observations. Cox has demonstrated the inherent error in a matrix approach. A 'effective value' calculation based on actuarial data of events in domains at a 95% confidence of each, or the expressed bands of each level of risk. But the fact that you are in effect multiplying PDF ( probability distribution functions) makes the whole exercise bogus. You need to use a Mellin transform. The effective cost also expresses the full risk, where as the matrix hides the details and inhibits good decision making.
This is an excellent piece. Thank you very much. For more than a decade now, I am learning from your short and crisp videos. 1. Can this be having a hyperlink to go to the particular risk. 2. If in data itself if we calculate the risks (L*I) and one column is given for that (1 to 5 green, and 16 to 25 red, etc. ) and then using if or vlookup, copy risk nos in the risk map? Thanks again for this.
Thank you this is very helpful. I'd like to ask, what if we use the risk ID and title for the sentence in the risk matrix? Do we re-use the textjoin formula to combine them?
Super helpful thank you so much! Also, I don't know if anyone else ran into this problem but I couldn't get char(10) to work unless I had "wrap text" turned on.
Hi, This is looking really great. Thanks for the tips. However the titles just show up randomly in the risk map, and do not correspond at all to the Impact and Likelihood numbers (1-5)... Why might this be?
Wow, this amazing, and I think instead of numerical we could pass the text parameter in 2D and this will work! ?, right. Thanks for sharing, great use of textjoin function . Regards
Hey Chandoo, Just wondering if you could also do this with a marker for residual, inherent and target impact and likelihood. How could you go about doing that? For example, could you each point on the matrix a different symbol based on whether it was res, inh, or target or could you have a drop down list that changes the variables in your formula to the rating? Any ideas are much appreciated!
Hi Heather... you can easily customize the Riskmap... Download the file and give it a try. If you need more help on TEXTJOIN, see this video - th-cam.com/video/LmHSluMsh-I/w-d-xo.html
Hi Chandoo... thanks this is very helpful.... I wanted to add one more condition (ex. Risk status - open/close).....only open risk should display in the heatmap...... can you please help
Refer to the comments on this video as well as here - chandoo.org/wp/excel-risk-map-template/ You might get some ideas. Also check the 9 box template thing as it uses same concept - chandoo.org/wp/9-box-talent-map-template/
Hi Denver... See my latest video on "9 box grid in Excel". It is a similar idea but shows how to get it with Pivot Tables & Measures (which are available in Excel 2016). th-cam.com/video/MnsQhba0V9A/w-d-xo.html
Hi Rajendra, You get NAME error for one of these reasons: 1. Your Excel doesn't have TEXTJOIN function (it is only available in Excel 365 / 2016 versions) 2. You had a typo in either table name or edited formulas. Check that you can use TEXTJOIN() and the formulas have no typos.
Unable to get the formula to work, using O365. No syntax errors, setup the variable names (Title, Impact, & Likelihood) in my source spreadsheet to map to the formula, tried CTRL-SHIFT-ENTER but cell is seen as a constant when tried to evaluate it. Appreciate some guidance :)
Tried another method, copied my data into the sample excel file, placed some of its data in the existing Data worksheet, works. Created new variable names by adding the number 2 to risks2 (consists of all data on worksheet added called data2), Title2 (represents all data in Column C) , Impact2 (all data in Col H), Likelihood2 (all data in Col G), updated formula on the Risk Map cell D3 with these minor updates and doesn't work, sees formula as text.
Hi Chandoo, Very good and thanks. I tried to create my own 6x6 risk matrix map by entering textjoin formulas. I tried to incorporate the following: * Ranges instead of 1,2,3,4,5,6. For x and y axes I entered desired range, eg., .75, 1.55, etc. In formula I used IF(AND(4 conditions)) instead of nested IF. Gave 2 conditions for x axis range and 2 conditions for y axis range based on range interval entered (instead of 1,2,..5). I only get blank cells (of course "* ") as a result. Does it mean the formula does not accept complex AND conditions A≤3.5, A>2.5 etc. Please help. Thanks. Harry
@@chandoo_ Thanks for the prompt answer. Amazing! Any particular reason why AND does not work here? Is it simply because of the way excel is programmed? Regards. Harry
AND function is designed to take a bunch of arguments and turn them in to a single true / false value. In this situation, we need a range of values to turn to another range. That is why AND doesn't work.
If you are getting #NAME error, it means TEXTJOIN is not available in your version of Excel. It works only in few latest versions (I know, MS has crazy licensing and roll out policies). If there is some other error, reply with that so I can help.
Unfortunately, this is a very odd and hacky way of doing this. But appreciate the effort. I implement much cleaner and more statistically accurate way of doing it and I hope real project managers learn the right way. Cheers.
@@chandoo_ Hi there, thanks for the reply. I don't think you need to color the cells first and then fill them in. The process should be to find the weighted scores first, and then plot them according to quartiles as appropriate for the project/organization. Perhaps I'll share more details in the future but that's how we do it at work up here. Keep posting your ideas however...always good to see others methods and perspectives.
@Tony R, r u pointing towards using scatter maps. Ultimately it would be based on impact n probability only. The impact value which Chandoo used could be the weighted score. Please correct me if m missing anything... happy to hear different idea!
How to understand If Formulas easy way. i always get confused with logical formulas. any trick to understand logical formulas at easy way. or it is not everyone cup of tea. but i have confidence in your teaching way so i am hoping you will make video for students like. thanking you in advance
Good question. Yes, IF formulas and business logic implementation takes a while to kick in. I suggest listening to this podcast I did few years back. chandoo.org/wp/cp028-how-to-tell-business-logic-rules-to-excel/ All the best.
I am unsure of your goal with this risk map - this does not give a risk rating, as it randomly generates numbers and then changes all items within the risk map. A risk map would update the matrix with the appropriate calculation between each risk. In fact, each written statement moves around the chart as you make changes - not in the area in which the should be, given a particular risk rating. This does not appear to work at all using the random generator, except to show things moving. Perhaps a Match / compare would be more useful
works great- if you change the assessment, it replots where the risk is on the heat map. Chandoo has done a great job here. You can always plot the before and after re-assessment and have a different color text or symbol to show last review assessment/ movement.
Hi Chandoo, that's a great tutorial and it worked perfectly for me. In my previous Risk Map in Excel I've only showed the Risk ID which made it more difficult to read since you always had to look up for the corresponding Risk title. With your solution, everything is visible at one spot. Great work!
Hi Chandoo, Please advise the formula by adding another column in the risk Register for the Risk Status - In Progress, Not Started, & Closing. Matrix should remove the Closed Risk.
If you are looking a *fully interactive Project Dashboard* with Excel, check out this video 👉 th-cam.com/video/FXnyKU6xZeI/w-d-xo.html
I'd suggest that there be four risk levels, and if more, always an even number to prevent going to the middle. The middle level gets over-used.
Plus this also avoids the huge error risk with three levels. Consider that the risks levels are really in diagonal bands, not stepped. The steps intrude into each other's error bands so can lead to huge errors where the top right corner of 'green' is within the error band of the bottom left corner of 'red'. A couple of errors of estimated probability of event and probability of damage can move a risk erroneously either from green to red, or red to green, resulting in misallocation of resources, or insufficient attention to a risk.
That said a couple of observations. Cox has demonstrated the inherent error in a matrix approach. A 'effective value' calculation based on actuarial data of events in domains at a 95% confidence of each, or the expressed bands of each level of risk. But the fact that you are in effect multiplying PDF ( probability distribution functions) makes the whole exercise bogus. You need to use a Mellin transform.
The effective cost also expresses the full risk, where as the matrix hides the details and inhibits good decision making.
This is just the video I needed to start an assignment in my Supply Chain Management class, thanks!
This is great stuff. Very simplified and straight to the point. Thank you so much
This is an excellent piece. Thank you very much. For more than a decade now, I am learning from your short and crisp videos.
1. Can this be having a hyperlink to go to the particular risk.
2. If in data itself if we calculate the risks (L*I) and one column is given for that (1 to 5 green, and 16 to 25 red, etc. ) and then using if or vlookup, copy risk nos in the risk map?
Thanks again for this.
Great work Chandoo, thanks for your videos
Thanks, Chandoo- really useful.
is there a way to format the mitigation column to output based on actual values instead of random?
Thank you this is very helpful. I'd like to ask, what if we use the risk ID and title for the sentence in the risk matrix? Do we re-use the textjoin formula to combine them?
Yes, correct
Great stuff Chandoo, I will find this extremely useful to visualise the number of Risks I am dealing with. Many thanks and good luck.
You are welcome Matt.
Great template, Thank you so much! It helped me out a lot with my project
Excellent. Thank you very much. I am currently this tool for my final assessment in Project management.
This is amazing!! Thank you !!😅
Thanks - Good simple plot for presentation/overview
(except I get a #VALUE! error with a wrong datatype somewhere in the table source)
Thank you very much for this video! It helped me a lot :)
Super helpful thank you so much! Also, I don't know if anyone else ran into this problem but I couldn't get char(10) to work unless I had "wrap text" turned on.
I am glad you liked it. Thanks for pointing out about "Wrap text". Yes, if you don't enable wrap, Excel ignores line breaks.
this is so helpful! thankyou so much
You're so welcome!
Need risk map for hospital management...to redefine strategies & to increase patient footfall.
Hi, if the risk title is too long it will give you an error.
Really cool table and features
Thanks Mathias
Thank you so much, this is exactly what i was looking for...not a risk matrix, but a matrix nonetheless, excellent tutorial
Glad it was helpful!
Very Good Presentation...
Thanks a lot
Esta genial, muchas gracias, lo adapte a mi trabajo de auditoria. !!!!
Thanks Chandoo. You taught me something today.
Thanks a Lot Chandoo!
Most welcome!
Hi,
This is looking really great. Thanks for the tips.
However the titles just show up randomly in the risk map, and do not correspond at all to the Impact and Likelihood numbers (1-5)... Why might this be?
hmm... they shouldn't. Did you download the sample file and test it with that?
Very helpful! Thanks!
You're welcome!
Thanks a lot Chandoo! Are you able to build the same Risk Matrix in Power BI?
Yes you can! I will make a video about it sometime.
Wow, this amazing, and I think instead of numerical we could pass the text parameter in 2D and this will work! ?, right. Thanks for sharing, great use of textjoin function . Regards
Hey Chandoo, Just wondering if you could also do this with a marker for residual, inherent and target impact and likelihood. How could you go about doing that? For example, could you each point on the matrix a different symbol based on whether it was res, inh, or target or could you have a drop down list that changes the variables in your formula to the rating? Any ideas are much appreciated!
Hi Heather... you can easily customize the Riskmap... Download the file and give it a try. If you need more help on TEXTJOIN, see this video - th-cam.com/video/LmHSluMsh-I/w-d-xo.html
Excellent. Where is the sample file?
Hi Chandoo... thanks this is very helpful.... I wanted to add one more condition (ex. Risk status - open/close).....only open risk should display in the heatmap...... can you please help
Refer to the comments on this video as well as here - chandoo.org/wp/excel-risk-map-template/ You might get some ideas.
Also check the 9 box template thing as it uses same concept - chandoo.org/wp/9-box-talent-map-template/
This is genuis. Thank you!
You're very welcome!
Can you accomplish the same thing if you don't have Office 365? Since you don't get TextJoin with MS Excel 2016
Hi Denver... See my latest video on "9 box grid in Excel". It is a similar idea but shows how to get it with Pivot Tables & Measures (which are available in Excel 2016).
th-cam.com/video/MnsQhba0V9A/w-d-xo.html
Excellent file!!! Nice Job
Thank you! Cheers!
If i may ask, why did chandoo use RANDBETWEEN function in the data sheet?
Because this is an example, so he's randomly assigning values to the cells.
this is awesome... thanks mate...
Glad you liked it!
Thank you so much! Great work with the video, helped alot.
You're welcome!
Thank you so much for this awesome knowledge...
Glad it was helpful!
Hi Chadoo, It is excellent one. how i can use your excel file which is showing NAME? in box. what to do please guide.
Hi Rajendra,
You get NAME error for one of these reasons:
1. Your Excel doesn't have TEXTJOIN function (it is only available in Excel 365 / 2016 versions)
2. You had a typo in either table name or edited formulas.
Check that you can use TEXTJOIN() and the formulas have no typos.
This is amazing!! Thank you !!
You're so welcome!
theres always a great indian guy helping my degree haha
Glad to be that guy... :D
@@chandoo_ haha thanks brooo 🙌🏼🙌🏼
Unable to get the formula to work, using O365. No syntax errors, setup the variable names (Title, Impact, & Likelihood) in my source spreadsheet to map to the formula, tried CTRL-SHIFT-ENTER but cell is seen as a constant when tried to evaluate it. Appreciate some guidance :)
Tried another method, copied my data into the sample excel file, placed some of its data in the existing Data worksheet, works. Created new variable names by adding the number 2 to risks2 (consists of all data on worksheet added called data2), Title2 (represents all data in Column C) , Impact2 (all data in Col H), Likelihood2 (all data in Col G), updated formula on the Risk Map cell D3 with these minor updates and doesn't work, sees formula as text.
Got the basic function working! Learned a lot today, thanks Chandoo!
Very informative
I can't get it to do work! I need help!
What is the error you are having? You can refer the example file link in video description and use it.
@@chandoo_ When I enter the formula, it doesn't do anything.
Good, and so fast.
Thanks!
Hi sir . I tried the first formula =TEXTJOIN
but after I follow your tutorial when I ctr+shift+enter it gives me a result ", #NAME?
Helpful! Many thanks.7
Glad it was helpful!
Good Stuff!
Thanks!
in your template, after downloading, when do Enable editing , there's a Invalid #NAME? error , I can't figure it out why
You need TEXTJOIN() to use this template. Are you using Excel 2013 or lower?
@@chandoo_ ah Yes thank you for the quick response. yes it's 2010 excel version :(
Hi Chandoo,
Very good and thanks.
I tried to create my own 6x6 risk matrix map by entering textjoin formulas.
I tried to incorporate the following:
* Ranges instead of 1,2,3,4,5,6. For x and y axes I entered desired range, eg., .75, 1.55, etc.
In formula I used IF(AND(4 conditions)) instead of nested IF. Gave 2 conditions for x axis range and 2 conditions for y axis range based on range interval entered (instead of 1,2,..5).
I only get blank cells (of course "* ") as a result.
Does it mean the formula does not accept complex AND conditions A≤3.5, A>2.5 etc.
Please help. Thanks.
Harry
Hi Harry, AND formula will not work with this. You need nested IF.
@@chandoo_ Thanks for the prompt answer. Amazing!
Any particular reason why AND does not work here? Is it simply because of the way excel is programmed?
Regards. Harry
AND function is designed to take a bunch of arguments and turn them in to a single true / false value. In this situation, we need a range of values to turn to another range. That is why AND doesn't work.
Great understanding and logic. Thanks.👍🙏
couldn't understand how the risks[Likelyhood] would work .. I mean the "risks" as an array
thats not covered in the video.
The TEXTJOIN formula is not working when I input into excel. Any tips?
If you are getting #NAME error, it means TEXTJOIN is not available in your version of Excel. It works only in few latest versions (I know, MS has crazy licensing and roll out policies). If there is some other error, reply with that so I can help.
@@chandoo_My error states that there is a problem with the formula. I copied the exact formula that you used in the video.
I also made sure that my charts aligned with yours.
It could be something else then. May be a bracket that is not closed properly or table name mismatch...?
@@chandoo_ Do you offer tutoring?
Nice wok, thanks!
Thank you so much
You're most welcome
Risk of Monster Attack :)
Thank you.
Unfortunately, this is a very odd and hacky way of doing this. But appreciate the effort. I implement much cleaner and more statistically accurate way of doing it and I hope real project managers learn the right way. Cheers.
Thanks for the comments Tony. Can you point to some resources about statistically correct ways to categorize and map risks?
@@chandoo_ Hi there, thanks for the reply. I don't think you need to color the cells first and then fill them in. The process should be to find the weighted scores first, and then plot them according to quartiles as appropriate for the project/organization. Perhaps I'll share more details in the future but that's how we do it at work up here. Keep posting your ideas however...always good to see others methods and perspectives.
@@flyingsalmon Sorry couldn't follow what you just said.Please name the software you use for this.
@Tony R, r u pointing towards using scatter maps. Ultimately it would be based on impact n probability only. The impact value which Chandoo used could be the weighted score. Please correct me if m missing anything... happy to hear different idea!
How to understand If Formulas easy way.
i always get confused with logical formulas.
any trick to understand logical formulas at easy way.
or it is not everyone cup of tea.
but i have confidence in your teaching way so i am hoping you will make video for students like.
thanking you in advance
Good question. Yes, IF formulas and business logic implementation takes a while to kick in. I suggest listening to this podcast I did few years back. chandoo.org/wp/cp028-how-to-tell-business-logic-rules-to-excel/
All the best.
@@chandoo_ highly appreciated your quick response. 👍
formula doesnt work in polish Excel
Hi Mona.. are you using Excel 2016 or 365? you need TEXTJOIN to use this template.
Thumbs up
i love you
Thank you. :)
@@chandoo_ You are welcome!
Eisai mhpws geros??
Why he sound like he has a nose block lol 💀💀.
I am practicing my _Himesh Reshmiya_ voice :D Nah, just have one of those allergy days.
I am unsure of your goal with this risk map - this does not give a risk rating, as it randomly generates numbers and then changes all items within the risk map. A risk map would update the matrix with the appropriate calculation between each risk. In fact, each written statement moves around the chart as you make changes - not in the area in which the should be, given a particular risk rating. This does not appear to work at all using the random generator, except to show things moving. Perhaps a Match / compare would be more useful
works great- if you change the assessment, it replots where the risk is on the heat map. Chandoo has done a great job here. You can always plot the before and after re-assessment and have a different color text or symbol to show last review assessment/ movement.
hi, i m following this method and facing some confusions. can I please contact you?