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

Question

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

riny_van_eekelen · Answer

PW0105&nbsp;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). &nbsp;Then you can use MAX to set the risk at the higher value of Impact and Likelihood.&nbsp;

pw0105 · Answer

Riny_van_Eekelen&nbsp;thanks so much for this! Sorry it took me so long to get back to you this is great!!&nbsp;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&nbsp;

riny_van_eekelen · Answer

PW0105&nbsp;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.

pw0105 · Answer

Riny_van_Eekelen&nbsp;see attached&nbsp;

riny_van_eekelen · Answer

PW0105&nbsp;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-bfe9-40a9-a8e9-f99134456576&nbsp;&nbsp;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.&nbsp;&nbsp;

Forum Discussion

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

8 Replies

Resources