UMBC Create a Formula that Will Get the Top Two Repeating Times from A List Excel Task

Description

Create an excel formula that will get the top two repeating times from a list, the excel sheet that I’m referring to has been provided below. 

Hi NVO24I hope you are well.Based on your question posted here “create an excel formula that will get the top two repeating times from a list” – I was able to create the two formulas you required.To get the top recurring time in the list (from a different worksheet – based on your excel workbook) the formula is::=INDEX(‘Weekly Incidents’!C5:C1000;MODE(IF(‘Weekly Incidents’!C5:C1000<>””;MATCH(‘Weekly Incidents’!C5:C1000;’Weekly Incidents’!C5:C1000;0))))To get the second most recurring time in the list (from a different worksheet – based on your excel workbook) the formula is::=INDEX(‘Weekly Incidents’!C5:C1000;MODE(IF((‘Weekly Incidents’!C5:C1000<>””)*(‘Weekly Incidents’!C5:C1000<>INDEX(‘Weekly Incidents’!C5:C1000;MODE(IF(‘Weekly Incidents’!C5:C1000<>””;MATCH(‘Weekly Incidents’!C5:C1000;’Weekly Incidents’!C5:C1000;0)))));MATCH(‘Weekly Incidents’!C5:C1000;’Weekly Incidents’!C5:C1000;0))))The trick is to make sure that when you have finished typing the complete function – that you press control + shift + enter together (and not just enter) – because this is an array formula.I have also demonstrated this in your workbook so that you can see that the functions work 100% – in the “Weekly Report” sheet – please see attached. I populated the ‘dataset of incidents’ so that there would be some test data to work with – in this way you can clearly see which time is repeated most often.Please contact me again if you have any other questions ? Warm regardsDoc_M