Averaging data from multiple months and data ID's

Rocku Aqua · Nov 6, 2023

I have the following data set;

Date	Time	DataID	Temp(C)	Pressure(kPa)	DO(ppm)	SAL(ppt)	pH	ORP(mV)
20/1/2023	13:41:27	Lake A	27.4	101	5.73	18	7.01	66.2
20/1/2023	14:19:12	Lake B	27	100.8	6.61	18.2	7.19	97.2
21/1/2023	8:58:50	Lake B	26.6	101.3	6.07	18.3	7.18	143.7
21/1/2023	10:10:49	Lake A	26.9	101.2	4.69	18.2	7.01	167.8
21/1/2023	10:23:57	Lake B	26.7	101.1	5.57	18.2	7.27	33.8
21/1/2023	13:04:22	Lake A	27.2	101.1	5.34	18.2	7.02	83
21/1/2023	13:08:25	Lake B	26.7	101.1	6.75	18.2	7.3	130.9
23/1/2023	5:50:04	Lake A	27	101.2	4.56	18.3	7.03	154.1
23/1/2023	5:55:35	Lake B	26.7	101.2	6.37	18.3	7.17	182.5
23/1/2023	13:36:51	Lake A	27.5	100.9	4.77	18.3	7	114.1
23/1/2023	13:54:41	Lake B	27.3	100.8	7.36	18.3	7.6	114.2
24/1/2023	6:31:18	Lake A	27.4	101.3	4.54	18.3	6.95	82.9
24/1/2023	6:36:50	Lake B	27.1	101.2	5.82	18.3	7.29	108.3
24/1/2023	13:51:09	Lake B	27.8	100.7	5.92	18.2	7.31	121.4
24/1/2023	14:32:36	Lake A	28.2	100.5	5.42	18.3	7.26	32.3
25/1/2023	5:54:16	Lake A	27.5	101.2	4.47	18.3	7.01	56.4
25/1/2023	6:01:00	Lake B	27.2	101.2	4.37	18.3	7.12	110.1
25/1/2023	13:40:48	Lake B	27.9	101	4.96	18.3	7.13	82.3
25/1/2023	13:44:07	Lake A	28	101	4.56	18.3	6.99	89.3
27/1/2023	6:02:52	Lake A	28.2	101.4	4.37	18.3	6.76	109
27/1/2023	6:09:36	Lake B	27.8	101.5	3.98	18.3	6.94	170.1
27/1/2023	13:43:26	Lake B	28.1	101	4.87	18.3	6.82	32.8
27/1/2023	13:46:05	Lake A	28.6	101	3.94	18.3	6.83	34.7
28/1/2023	10:08:02	Lake B	28.1	101.2	3.42	18.3	6.74	44.4
28/1/2023	10:11:28	Lake A	28.6	101.2	4.09	18.3	6.81	96.2
28/1/2023	13:39:57	Lake A	28.6	101.2	3.97	18.3	6.9	39.5
28/1/2023	13:40:17	Lake B	28.7	101.2	4.39	18.3	7.05	52.4
30/1/2023	15:18:12	Lake A	29.3	100.6	6.73	18.3	7.06	79
30/1/2023	15:20:12	Lake B	29.1	100.6	6.09	18.4	7.26	120.9
31/1/2023	6:05:12	Lake A	28.7	100.6	4.53	18.4	7.01	107.5
31/1/2023	6:36:54	Lake B	28.4	100.6	4.65	18.4	5.17	119.5
1/2/2023	5:48:50	Lake B	28.6	100.1	4.65	18.4	7.15	137.2
1/2/2023	5:52:47	Lake A	28.9	100.1	4.24	18.4	6.99	158.9
1/2/2023	13:45:35	Lake A	29.4	99.7	4.46	18.4	7.07	59.6
2/2/2023	6:02:11	Lake B	28.7	100.1	5.11	18.4	7.24	110.1
2/2/2023	6:07:44	Lake A	29.1	100.1	3.89	18.4	6.95	148.1
2/2/2023	13:41:36	Lake A	29.4	99.7	4.27	18.4	7.15	119.3
2/2/2023	13:48:25	Lake B	29	99.7	6.77	18.4	6.51	149.6
3/2/2023	5:47:21	Lake B	28.7	99.7	5.41	18.4	7.25	153.5
3/2/2023	5:49:22	Lake A	29	99.7	3.62	18.4	7.04	166.2
3/2/2023	12:50:36	Lake B	29.7	99.2	6.06	18.3	7.5	65.6
3/2/2023	13:06:54	Lake A	29.8	99.3	3.67	18.4	6.93	106.2
4/2/2023	13:11:02	Lake A	29.4	100.5	3.41	18.4	6.98	134.9
4/2/2023	13:17:32	Lake B	29	100.5	5.59	18.4	7.29	154.8
6/2/2023	5:44:35	Lake B	28.6	101.6	4.42	18.5	7.16	70
6/2/2023	5:48:46	Lake A	29	101.6	4.01	18.4	6.86	93.5
7/2/2023	5:58:30	Lake A	28.9	101.4	4.27	18.5	6.87	107.7
7/2/2023	6:13:40	Lake B	28.5	101.4	4.27	18.5	7.16	95
7/2/2023	14:16:04	Lake B	28.7	101.1	4.84	18.5	7.3	38.6
7/2/2023	14:23:08	Lake A	29.1	101.1	4.29	18.5	7.01	127.3
8/2/2023	6:36:53	Lake A	28.6	101.2	4.05	18.5	7.02	67.4
8/2/2023	6:54:35	Lake B	28.2	101.3	4.37	18.5	7.37	92.2
9/2/2023	14:55:50	Lake B	28.1	100.9	4.88	18.5	7.23	85.6
9/2/2023	14:58:13	Lake A	28.4	100.9	4.12	18.5	7.07	115.5
10/2/2023	5:47:43	Lake B	27.6	100.8	3.74	18.6	7.12	72.8
10/2/2023	5:50:32	Lake A	27.8	100.9	4.1	18.6	6.97	112.1
10/2/2023	13:52:31	Lake B	27.8	100.2	4.54	18.5	7.18	-5.7
10/2/2023	13:56:42	Lake A	28.4	100.2	4.72	18.5	6.78	85.3
11/2/2023	15:00:44	Lake B	27.9	100.4	4.82	18.5	7.09	0.5
11/2/2023	15:03:30	Lake A	28.3	100.4	4.14	18.6	6.96	42.6
13/2/2023	15:01:05	Lake B	28.5	100.2	5.74	18.6	7.21	21
13/2/2023	15:03:47	Lake A	29	100.2	4.65	18.6	6.98	20.6
14/2/2023	6:20:47	Lake A	28.5	100.7	4.25	18.6	6.9	-3.6
14/2/2023	6:26:56	Lake B	28.2	100.7	4.7	18.6	7.27	17.8
15/2/2023	5:59:58	Lake A	28.1	101.2	4.48	18.6	6.98	-4.4
15/2/2023	6:06:39	Lake B	27.8	101.3	4.24	18.6	7.3	23.7
15/2/2023	13:54:28	Lake B	27.9	101.3	4.05	18.6	7.01	50.7
16/2/2023	14:22:06	Lake B	27.9	101.3	5.55	18.6	7.47	42.9
16/2/2023	14:26:56	Lake A	28.2	101.3	4.73	18.6	7.7	60.3
17/2/2023	9:50:48	Lake B	27.5	101.3	4.52	18.6	7.27	65.6
17/2/2023	9:53:20	Lake A	27.9	101.3	4.15	18.6	7.16	93.2
17/2/2023	15:05:28	Lake B	28.5	101.3	4.19	18.6	6.92	61.1
17/2/2023	15:05:45	Lake A	28.5	101.2	4.43	18.7	5.58	67.5
18/2/2023	13:56:23	Lake B	28.1	101.2	5.54	18.6	7.28	-11.5
18/2/2023	14:28:00	Lake B	28.4	101.2	4.85	18.3	6.69	-56.8
18/2/2023	14:28:09	Lake A	28.4	101.2	4.98	18.6	6.75	-43
20/2/2023	13:25:39	Lake A	28.2	101.8	5.14	18.7	7.03	-13.2
21/2/2023	6:21:00	Lake A	27.8	102	5.15	18.7	6.99	-36.2
21/2/2023	6:28:18	Lake B	27.5	102	5.18	18.7	7.29	-18.3
21/2/2023	13:56:41	Lake B	27.9	101.7	7.21	18.6	7.03	-77.5
21/2/2023	14:06:05	Lake A	28.1	101.7	5.05	18.6	6.9	0.2
22/2/2023	6:00:02	Lake B	27.4	101.7	4.94	18.7	7.13	-83.4
22/2/2023	6:03:51	Lake A	27.6	101.8	4.8	18.7	6.87	-2.7
23/2/2023	13:25:54	Lake B	27.1	101.5	5.02	18.7	7.07	55.1
23/2/2023	13:53:55	Lake A	27.5	101.4	5.47	18.7	6.96	44.2
24/2/2023	5:54:35	Lake B	26.8	101.6	4.89	18.7	7.06	9.9
24/2/2023	5:59:23	Lake A	27	101.6	5.78	18.7	6.94	52.8
24/2/2023	13:05:16	Lake A	27	101.5	5.57	18.7	6.91	-32.6
24/2/2023	13:19:17	Lake B	26.8	101.6	4.8	18.7	7.19	-75.9
25/2/2023	9:04:36	Lake A	26.5	101.7	5.57	18.6	6.8	-114.2
25/2/2023	9:07:49	Lake B	26.4	101.7	5.24	18.7	6.85	-70.1
25/2/2023	13:30:55	Lake A	27	101.4	5.8	18.7	7.03	-63.6
25/2/2023	13:47:01	Lake B	26.8	101.3	6.26	18.7	7.22	-31.5
27/2/2023	14:29:25	Lake A	27.6	101	5.47	18.6	7.02	-27.9
27/2/2023	14:36:56	Lake B	27.7	100.9	13.44	18.7	8.09	36.3
28/2/2023	6:57:50	Lake B	26.9	101.3	7.92	18.8	7.41	7.2
28/2/2023	7:02:30	Lake A	27.1	101.3	5.56	18.7	6.96	41.1
28/2/2023	14:13:31	Lake A	28	100.7	6.41	18.7	7.18	55.5
28/2/2023	14:20:06	Lake B	27.9	100.7	11.81	18.7	7.92	62.5

From this data i want to find the average for each month and each Lake. I want this data table to be in a seperate worksheet.

Fluff · Nov 6, 2023

Hi & welcome to MrExcel.
How about

Fluff.xlsm

A

B

C

D

E

F

G

H

1

Date

DataID

Temp(C)

Pressure(kPa)

DO(ppm)

SAL(ppt)

pH

ORP(mV)

2

Lake A

3

01/01/2023

Lake B

4

01/02/2023

Lake B

5

01/02/2023

Lake A

Sheet2

Cell Formulas
Range	Formula
A2:B5	A2	=UNIQUE(HSTACK(EOMONTH(+Sheet1!A2:A100,-1)+1,Sheet1!C2:C100))
C2:H5	C2	=AVERAGE(FILTER(Sheet1!D$2:D$100,(Sheet1!$C$2:$C$100=$B2)(Sheet1!$A$2:$A$100>=$A2)(Sheet1!$A$2:$A$100<=EOMONTH($A2,0))))
Dynamic array formulas.

steve the fish · Nov 6, 2023

Use a pivot table

Averaging data from multiple months and data ID's

Rocku Aqua

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

steve the fish

Well-known Member

Similar threads

Forum statistics

Share this page

Averaging data from multiple months and data ID's

Rocku Aqua

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

steve the fish

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock