Trying to figure this out and it’s probably something quite simple.
Layout:
I have a running log of information and events that are entered in with dates and times. From this I have a table using COUNTIFS formulas set to count the number of times certain events take place during each 8 hour shift between a selected start and end date. From this I have a SUM of specific significant events totaled in a list for each shift.
I’m using the formula of:
VLOOKUP(MAX(A1:A15),A1:B15,2,FALSE)
Where A1:A15 are the totals for each event
And B1:B15 are the names of each specific event
The idea is that the formula returns the event that happens the most during each shift during the selected start and end date.
Problem:
The formula does work. However, if there are no events logged, as in all the values are zero, the formula returns the very first event as the most common event for that shift even though the event never took place. Which makes the data a little skewed at first glance to someone that doesn’t know what is actually happening.
I’m trying to figure out a way to have the formula return which event is happening the most during each shift BUT have it return a “no record” or even just a blank cell if all the values are zero.
Any help or a point on the right direction would be greatly appreciated. Thanks in advance
Layout:
I have a running log of information and events that are entered in with dates and times. From this I have a table using COUNTIFS formulas set to count the number of times certain events take place during each 8 hour shift between a selected start and end date. From this I have a SUM of specific significant events totaled in a list for each shift.
I’m using the formula of:
VLOOKUP(MAX(A1:A15),A1:B15,2,FALSE)
Where A1:A15 are the totals for each event
And B1:B15 are the names of each specific event
The idea is that the formula returns the event that happens the most during each shift during the selected start and end date.
Problem:
The formula does work. However, if there are no events logged, as in all the values are zero, the formula returns the very first event as the most common event for that shift even though the event never took place. Which makes the data a little skewed at first glance to someone that doesn’t know what is actually happening.
I’m trying to figure out a way to have the formula return which event is happening the most during each shift BUT have it return a “no record” or even just a blank cell if all the values are zero.
Any help or a point on the right direction would be greatly appreciated. Thanks in advance
