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

[UpbeatKid]

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

 

[GraH - Guido]

From the sample data, what would be a correct result for a given day?

 

[Chihiro]

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.

 

[vletm]

UpbeatKid
Without Your sample results ... check this

 

[Peter Bartholomew]

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 )

 

[Peter Bartholomew]

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.



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

 

[UpbeatKid]

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

 

[vletm]

UpbeatKid
You could get that eg with #4 Reply
without any formulas.

 

[Peter Bartholomew]

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.

 

[UpbeatKid]

vletm, Peter,

Thank you both for your responses. I will use pivot table as suggested by you both and then unpivot the sample data as sugested by Peter.