Pie charts/graphs with embedded IF functions for risk and issue register

%3CLINGO-SUB%20id%3D%22lingo-sub-1429271%22%20slang%3D%22en-US%22%3EPie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429271%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I%20have%20created%20a%20risk%20and%20issue%20register%20within%20excel.%26nbsp%3B%20Within%20the%20impact%2Flikelihood%20column%20the%20cells%20change%20depending%20on%20High%2C%20Medium%2C%20Low%20(as%20shown%20in%20screenshot%20below).%26nbsp%3B%20What%20I%20am%20now%20trying%20to%20do%20is%20create%20a%20report%20to%20count%20up%20the%20amount%20of%20High%2C%20Medium%20%26amp%3B%20Low%20risks....%20however%20where%20there%20is%20a%20Impact%20that%20is%20High%20(red)%20and%20likelihood%20that%20is%20medium%20(amber)%20-%20I%20would%20count%20this%20as%20a%20high%20risk%20and%20vise%2Fversa%2C%20and%20same%20if%20there%20was%20a%20Low%2FMedium%20combo%20I%20would%20take%20this%20as%20a%20medium%20to%20keep%20it%20simple%20for%20now.%26nbsp%3B%20I%20need%20some%20guidance%20on%20how%20I%20can%20do%20this%20count%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1429271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1429451%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684608%22%20target%3D%22_blank%22%3E%40PW0105%3C%2FA%3E%26nbsp%3BPerhaps%20the%20attached%20file%20contains%20a%20workable%20solution.%20One%20option%20is%20to%20use%20nested%20IF%20statements%20(not%20my%20favourite)%20or%20simply%20change%20the%20risk%20ratings%20to%20numbers%20(0%3DLow%2C%201%3DMedium%2C%203%3DHigh).%20%26nbsp%3BThen%20you%20can%20use%20MAX%20to%20set%20the%20risk%20at%20the%20higher%20value%20of%20Impact%20and%20Likelihood.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1594935%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1594935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bthanks%20so%20much%20for%20this!%20Sorry%20it%20took%20me%20so%20long%20to%20get%20back%20to%20you%20this%20is%20great!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20there%20is%20also%20a%20formula%20I%20can%20write%20so%20put%20a%20bar%20chart%20together%20risk%20profile%20by%20project%2C%20I%20currently%20have%20Project%20Name%20as%20one%20of%20the%20fields%20so%20was%20thinking%20I%20could%20get%20it%20to%20reference%20the%20project%20name%2C%20look%20for%20the%20status%20of%20Open%20and%20insert%20how%20many%20High%2FMedium%2FLow%20issues%20per%20project%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1594957%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1594957%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684608%22%20target%3D%22_blank%22%3E%40PW0105%3C%2FA%3E%26nbsp%3BVery%20likely%20that%20it%20is%20possible%2C%20but%20it%20would%20help%20if%20you%20could%20upload%20your%20file%20(without%20any%20confidential%20info%20in%20it).%20Then%2C%20it%20will%20be%20much%20easier%20to%20grasp%20what%20you%20are%20trying%20to%20achieve.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1594999%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1594999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bsee%20attached%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595042%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684608%22%20target%3D%22_blank%22%3E%40PW0105%3C%2FA%3E%26nbsp%3BHave%20a%20look%20at%20the%20pivot_table%20tab%20in%20the%20attached%20sheet.%20Is%20that%20what%20you%20had%20in%20mind%3F%20I%20created%20a%20pivot%20table%20and%20pivot%20chart%20from%20the%20data%20in%20the%20risk%20register.%20If%20you%20are%20not%20familiar%20with%20pivot%20tables%2C%20check%20this%20out%20first.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20you'll%20see%20that%20I%20cleaned%20up%20the%20Risk%20Register.%20I'd%20recommend%20that%20you%20do%20not%20use%20merged%20cells.%20They%20totally%20screw%20up%20the%20structure%20of%20the%20data.%20Use%20the%20alignment%20option%20%22Center%20across%20selection%22%20in%20stead.%20Got%20rid%20of%20them%2C%20all%20the%20borders%20and%20all%20of%20the%20rows%20without%20a%20project%20name.%20Perhaps%2C%20you%20want%20to%20look%20into%20the%20use%20of%20structured%20tables%20as%20well.%20That%20way%2C%20you%20don't%20need%20to%20worry%20about%20updating%20data%20ranges%2C%20every%20time%20records%20get%20added.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595355%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595355%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20soo%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bthis%20is%20exactly%20what%20I%20wanted%20to%20achieve%2C%20(wish%20I%20thought%20of%20that!%20I'm%20a%20bit%20of%20a%20novice%20when%20it%20comes%20to%20excel%2C%20but%20always%20trying%20to%20learn)%20I've%20also%20replicated%20for%20the%20issues%20tab%20-%20only%20thing%20is%20I%20can't%20get%20the%20High%20Medium%20Low%20in%20the%20chart%20legend%20to%20display%20in%20that%20Order%20-%20will%20only%20let%20me%20filter%20it%20A-Z%20or%20Z-A...%20also%20noted%20about%20the%20merged%20cells%20and%20I'm%20glad%20you%20told%20me%20that%20because%20I've%20encountered%20issues%20when%20cutting%2Fpasting%20things%20across%20merged%20cells!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595393%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684608%22%20target%3D%22_blank%22%3E%40PW0105%3C%2FA%3E%26nbsp%3BYou%20can%20select%20a%20column%20in%20the%20pivot%20table%20(see%20picture)%20and%20drag%20it%20left%20or%20right%20to%20where%20you%20want%20it.%20This%20over-rides%20the%20standard%20alphabetical%20sort%20order.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-18%20at%2010.51.42.png%22%20style%3D%22width%3A%20146px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F213019iEA2C45D81FEE8F1C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-08-18%20at%2010.51.42.png%22%20alt%3D%22Screenshot%202020-08-18%20at%2010.51.42.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595419%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20charts%2Fgraphs%20with%20embedded%20IF%20functions%20for%20risk%20and%20issue%20register%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595419%22%20slang%3D%22en-US%22%3E%3CP%3EThankyou!!%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi all, I have created a risk and issue register within excel.  Within the impact/likelihood column the cells change depending on High, Medium, Low (as shown in screenshot below).  What I am now trying to do is create a report to count up the amount of High, Medium & Low risks.... however where there is a Impact that is High (red) and likelihood that is medium (amber) - I would count this as a high risk and vise/versa, and same if there was a Low/Medium combo I would take this as a medium to keep it simple for now.  I need some guidance on how I can do this count? 


Thanks

 

 

 

8 Replies
Highlighted

@PW0105 Perhaps the attached file contains a workable solution. One option is to use nested IF statements (not my favourite) or simply change the risk ratings to numbers (0=Low, 1=Medium, 3=High).  Then you can use MAX to set the risk at the higher value of Impact and Likelihood.

 

Highlighted

@Riny_van_Eekelen thanks so much for this! Sorry it took me so long to get back to you this is great!!

 

I was wondering if there is also a formula I can write so put a bar chart together risk profile by project, I currently have Project Name as one of the fields so was thinking I could get it to reference the project name, look for the status of Open and insert how many High/Medium/Low issues per project

 

Highlighted

@PW0105 Very likely that it is possible, but it would help if you could upload your file (without any confidential info in it). Then, it will be much easier to grasp what you are trying to achieve.

Highlighted
Highlighted

@PW0105 Have a look at the pivot_table tab in the attached sheet. Is that what you had in mind? I created a pivot table and pivot chart from the data in the risk register. If you are not familiar with pivot tables, check this out first.

https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bf... 

 

By the way, you'll see that I cleaned up the Risk Register. I'd recommend that you do not use merged cells. They totally screw up the structure of the data. Use the alignment option "Center across selection" in stead. Got rid of them, all the borders and all of the rows without a project name. Perhaps, you want to look into the use of structured tables as well. That way, you don't need to worry about updating data ranges, every time records get added.

 

 

Highlighted

Thanks soo much @Riny_van_Eekelen this is exactly what I wanted to achieve, (wish I thought of that! I'm a bit of a novice when it comes to excel, but always trying to learn) I've also replicated for the issues tab - only thing is I can't get the High Medium Low in the chart legend to display in that Order - will only let me filter it A-Z or Z-A... also noted about the merged cells and I'm glad you told me that because I've encountered issues when cutting/pasting things across merged cells!

Highlighted

@PW0105 You can select a column in the pivot table (see picture) and drag it left or right to where you want it. This over-rides the standard alphabetical sort order.

Screenshot 2020-08-18 at 10.51.42.png

Highlighted