+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    104

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    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.
    Last edited by dflak; 07-30-2018 at 02:08 PM.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    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")
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find all unique values based on multiple criteria, excluding blanks
    By Tamarissa in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-29-2018, 08:12 PM
  2. [SOLVED] Count unique values for a particular criteria
    By cadineshkumar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2018, 10:36 AM
  3. [SOLVED] Count average number of times unique values appear
    By BWellman in forum Excel General
    Replies: 5
    Last Post: 08-09-2017, 10:47 AM
  4. Code selects first and last rows with consecutive values and with unique values
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2015, 08:38 PM
  5. Replies: 3
    Last Post: 06-25-2014, 03:28 AM
  6. Sum values from maximum consecutive string
    By kingjas in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2013, 08:12 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1