# Count unique values with criteria and blanks and an average of maximum consecutive values

1. ## Count unique values with criteria and blanks and an average of maximum consecutive values

Hi,

I have two problems.

1) I want to count the amount of times a unique serial value is used depending on what time it is. In this case if the time is below or above 12:00 hours, K2 (night shift and day shift). I've looked around for this and my answer is in H3 but that's obviously wrong

2) I want to count the average maximum consecutive value (column A) depending on whether it's done on night shift or day shift.

So for example it would be an average of the orange cells in column A depending if it was done on night or day shift. Is this even possible?

Thank you for any help!  Register To Reply

2. ## Re: Count unique values with criteria and blanks and an average of maximum consecutive val

I notice that times and serials are not contiguous. Are you tracking more than one day in this data? If so, then you also need a date. If not, then the data needs to be sorted by serial and time.  Register To Reply

3. ## Re: Count unique values with criteria and blanks and an average of maximum consecutive val

Here is my shot at it assuming that it is all in one day.

First I sorted by Serial and date so we can get the most consecutive values lined up.

Then I introduced a bunch of helper columns:
- shift =IF([@Time]>=0.5,"PM","AM") - 0.5 days is one half day or 12 hours so 0.5 is a shorthand way of telling Excel, Noon.
- composite =[@Serial]&":"&[@shift] - the combination of serial and shift make a record unqiue
- Unique =MATCH([@Composite],[Composite],0)=ROW()-1 - this formula is true for the first occurrence of a composite.
- Run =IF([@Unique]=TRUE,1,G1+1) - this is a running total of the number of records in the "run" of Serial + Time
- Max =MAXIFS([Run],[Composite],[@Composite]) - gets the maximum number in a run.

Then there are the results
Average Change (Total) =AVERAGE(Table1[MAX])
Average Change (PM) =AVERAGEIFS(Table1[MAX],Table1[shift],"PM")
Average Change(AM) =AVERAGEIFS(Table1[MAX],Table1[shift],"AM")

Unique Values (PM) =COUNTIFS(Table1[Unique],TRUE,Table1[shift],"PM")
Unique Values (AM) =COUNTIFS(Table1[Unique],TRUE,Table1[shift],"AM")  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 