Daily Statistics from a List

[Loofa]

Hello-

Newbie here. I am generating data from a small water supply meter every minute and want to get statistics from the data. There are 1440 lines every day and I would like to get a summary report for a month.

Ideally I would like Daily statistics. Like Min, Max and Average for each day. perhaps even Total flow for a day (this is a SUM for that day of the flow readings). What functions should I be looking at to generate this. Any assistance would be great.


[TABLE="width: 282"]
<tbody>[TR]
[TD]Time Stamp[/TD]
[TD]Flow GPM[/TD]
[TD]pH[/TD]
[TD]Temperature[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:00[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:01[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:02[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:03[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:04[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:05[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:06[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:07[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:08[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:09[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2014 0:11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]74.1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sheltonite]

Hi Loofa,

This sounds like a great project to use a pivot table on:
- Place all of your data on a single sheet (Only keep the column headers once at the top) - should have enough rows for 1 month's data.
- Highlight the data set (something like A1:C60000 - Don't highlight beyond the data you have, no blank rows).
- At the top, click Insert > Pivot Table (Your range should auto-populate with what you've already selected) > New worksheet > OK. You should see the pivot table field list appear on the side of your screen.
- Add (Click and drag) "TimeStamp" to the Rows section. You should see all of the entries show up in the table on your worksheet.
- Now, right-click on one of those date/time stamps and click "Group...". A "Grouping" box should appear.
- Next, select "Days" only in the "By" section and click "OK". This should now only show the dates of the days in your table. This also groups the data for the next steps.
- Now, Click on your table to see the Pivot Table Fields list appear on the side of your screen again. Add "Flow", "pH", "Temp" to the "Values" section. This should, by default, add a "sum of" column to your tables.
- From here, go back to the "Values" Section. Click on the menu triangle to the right of "Sum of Flow" and click Value Field Settings. Here is where you can choose Sum/Min/Max/Avg for the data.

Simply drag the "Flow" field down again to the "Values" section again to create a new column with the same data, but now you can select a different measurement type (Sum/Min/Max/Avg).

Hope this makes sense.

 

[AhoyNC]

May be example below will give you a start.

Note the formulas are array formulas and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Time Stamp	Flow GPM	pH	Temperature		Summary	Min			Max			Averge			Total
2	6/1/2014 0:00	8	7.6	74.1		Date	Flow GPM	pH	Temperature	Flow GPM	pH	Temperature	Flow GPM	pH	Temperature	Flow
3	6/1/2014 0:01	7	7.6	74.1		6/1/2014	4	7.6	74.1	9	7.6	74.1	7	7.6	74.1	84
4	6/1/2014 0:02	9	7.6	74.1		6/2/2014	4	7.5	74.1	8	7.7	75	6.6	7.58	74.38	33
5	6/1/2014 0:03	8	7.6	74.1
6	6/1/2014 0:04	6	7.6	74.1			Min			Max			Averge			Total
7	6/1/2014 0:05	4	7.6	74.1		Month	Flow GPM	pH	Temperature	Flow GPM	pH	Temperature	Flow GPM	pH	Temperature	Flow
8	6/1/2014 0:06	9	7.6	74.1		6	4	7.5	74.1	9	7.7	75	6.88	7.59	74.18	117
9	6/1/2014 0:07	4	7.6	74.1
10	6/1/2014 0:08	9	7.6	74.1
11	6/1/2014 0:09	9	7.6	74.1
12	6/1/2014 0:10	6	7.6	74.1
13	6/1/2014 0:11	5	7.6	74.1
14	6/2/2014 0:00	6	7.6	74.5
15	6/2/2014 0:01	4	7.5	74.1
16	6/2/2014 0:02	7	7.5	74.1
17	6/2/2014 0:03	8	7.7	74.2
18	6/2/2014 0:04	8	7.6	75
19
CTRL-SHIFT-ENTER.

 

[Loofa]

AhoyNC and Sheltonite-

Thank you very much. I have tried your suggestions and it looks like this will work.