1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to dynamically group data by dates to calculate daily MIN MAX

Discussion in 'Ask an Excel Question' started by UpbeatKid, Jan 14, 2019.

  1. UpbeatKid

    UpbeatKid New Member

    Messages:
    3
    I would like to dynamically group the sample events of the same date and then applying that grouping for calculating daily MIN MAX values. Uploading a small sample data set as an example of the data format. The data spreads over 10 years in thousands of entries so cannot do the grouping manually.

    Events are not sampled on consecutive dates.

    Number of daily sample events can vary.

    Data is in columns labelled: Date | Time | Sample Value 1 | Sample Value 2 |... ...|Sample Value n |

    Date format is yyyy:mm:dd

    Time format is 00:00:00 (24 hrs)

    Sample event format: 1.123456

    I have minimal vba knowedge so would prefer formula if possible.

    I have asked this question at Stack Overflow with no response. Would appreciate your assistance.

    Thank you

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    905
    From the sample data, what would be a correct result for a given day?
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    Am not sure what result you are after. But try using Pivot Table to summarize your data. If necessary, first flatten your table from current structure.
    i.e. Data, Time, Sample# & Value column.
  4. vletm

    vletm Excel Ninja

    Messages:
    4,645
    UpbeatKid
    Without Your sample results ... check this

    Attached Files:

    Peter Bartholomew likes this.
  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    I approach this type of problem by first determining the range that contains the data you require to analyse, the final record of which being
    = INDEX( dataSamples, MATCH( Date, Table1[Date] ), 0 )

    The initial record requires a marginally more complicated MATCH
    = INDEX( dataSamples, MATCH( Date, Table1[Date] & "@") + 1, 0 )

    The range 'daySamples' is then
    = initialRecord : finalRecord

    Once the effort has gone into the setup the results are straightforward
    = MAX( daySamples )
    = MIN( daySamples )

    Attached Files:

  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    I have faint memories that, if a full set of summary data by date is required, the pivot table is about 30 times as fast. If, on the other hand, you only wish to query a single day's samples at a time, then calculating one result by formula rather than 3000 day's worth makes all the difference.

    vletm's pivot table summarised each sample number individually. To do that with the formulas I used, the zero/blank parameter in the INDEX that returned an entire record would need to be replaced by a named constant 'k' where
    k: = {1,2,3,4,5,6,7,8}
    This returns a set of 8 individual columns for aggregation.

    upload_2019-1-15_9-47-21.png

    The test that sample data exists on a given date 'dataExists?' is given by
    = LOOKUP(Date, Table1[Date] ) = Date
  7. UpbeatKid

    UpbeatKid New Member

    Messages:
    3
    Hi,

    Firstly my apologiess for not responding to your posts earlier. I have also learnt that in future if possible I should post a sample of the required output. However, all of you who have posted examples are correct. What I am looking for is to identify the MIN and MAX value for each sample set (Sample 1, Sample 2...) for each date for the whole dataset. As requested by some of you I am attaching here another sample spreadsheet as an example of the required output.

    I thank you for taking the time for responding. I'll go through your posts and inform you which one I use.

    Thank you

    Attached Files:

  8. vletm

    vletm Excel Ninja

    Messages:
    4,645
    UpbeatKid
    You could get that eg with #4 Reply
    without any formulas.
  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    You want results for all the data so I agree with vletm. The pivot table is a purpose written solution to your problem. The only step I would insert into #4 is to take the data into Power Query and unpivot the sample data.

    That way, when you come to apply a Pivot Table the sample just appears as a column heading rather than having to enter a separate value field for each sample.

    Attached Files:

Share This Page