Averageifs within a Table

[Richards440]

Hi All,

This has probably already been answered on here but I have tried a few different solutions and not managed to get what I am looking for.

I have a Table with Days in D1:AE1, Dates in D2:AE2 & Intervals in C3:C29. Then I have the relevant data in each cell based on that time and date.

I am trying to create an averageifs statement based on the Day of the week and the time interval. For example, it will look at each monday at 08:00 and find each of these instances and bring back the average.

Can Anybody help at all?

 

[pgc01]

I have a Table with Days in D1:AE1, Dates in D2:AE2

Hi
Welcome to the board

Sorry, I don't understand.

If you have the dates in D2:AE2 then you already have the days.
What's the data in D1:AE1?

 

[Richards440]

Hi
Welcome to the board

Sorry, I don't understand.

If you have the dates in D2:AE2 then you already have the days.
What's the data in D1:AE1?

Thank you!

In D1:AE2 it's just the days as text as I want to find an average for each week day interval

 

[pgc01]

I am trying to create an averageifs statement based on the Day of the week and the time interval. For example, it will look at each monday at 08:00 and find each of these instances and bring back the average.

Hi

This is a solution for the example that you posted.

You said you have the time in C3:C29. Is it not C3:C26 (24 hours in a day).
Next time please post a table to clarify these doubts.

I assumed the values in row 1 are text, like you posted.

Try in A9:

=AVERAGEIF(D1:V1,A3,INDEX(D3:V26,MATCH(A6,C3:C26,0),0))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1				Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday
2	Day			2021-01-01	2021-01-02	2021-01-03	2021-01-04	2021-01-05	2021-01-06	2021-01-07	2021-01-08	2021-01-09	2021-01-10	2021-01-11	2021-01-12	2021-01-13	2021-01-14	2021-01-15	2021-01-16	2021-01-17	2021-01-18	2021-01-19
3	Monday		0:00	38	23	52	6	84	40	49	5	80	33	10	14	3	5	67	9	25	35	49
4			1:00	28	47	87	18	50	22	34	62	31	33	60	15	99	45	53	59	61	33	11
5	Time		2:00	18	17	29	42	33	28	31	54	13	14	92	47	51	81	66	73	69	54	28
6	8:00		3:00	16	60	19	7	65	8	13	81	28	54	4	36	55	60	6	18	43	79	49
7			4:00	43	34	84	73	22	87	20	51	2	29	51	84	96	46	75	24	8	10	47
8	Result		5:00	99	8	78	76	87	64	70	72	41	90	15	52	98	8	72	57	80	49	62
9	61		6:00	67	71	36	33	60	71	18	8	77	55	9	4	58	5	37	53	44	90	58
10			7:00	47	19	26	55	7	62	83	49	0	16	50	90	84	2	25	90	90	67	99
11			8:00	83	27	25	81	81	14	42	57	50	70	34	55	9	31	66	22	4	68	65
12			9:00	80	84	12	72	5	50	80	21	33	15	50	7	43	29	40	25	17	45	86
13			10:00	48	5	86	30	26	47	18	83	43	28	4	35	73	48	65	82	72	98	73
14			11:00	74	74	72	51	30	77	3	82	31	41	72	42	70	95	37	99	9	26	3
15			12:00	96	30	88	93	49	97	5	44	66	60	43	26	15	73	85	6	36	23	92
16			13:00	61	34	26	7	71	92	22	64	66	79	53	46	33	78	78	29	92	44	92
17			14:00	71	35	99	35	91	33	18	96	34	97	32	48	34	33	7	31	31	34	43
18			15:00	20	11	23	44	0	51	61	48	77	24	3	88	43	30	7	19	94	65	44
19			16:00	27	66	92	31	63	42	44	84	5	64	36	34	96	65	21	33	40	56	28
20			17:00	28	85	8	3	63	60	36	67	12	16	66	71	20	10	80	63	56	39	13
21			18:00	86	29	59	55	34	48	77	1	45	38	95	82	93	96	5	79	8	10	70
22			19:00	11	77	73	82	90	72	89	62	83	94	63	92	92	5	62	32	15	53	79
23			20:00	99	43	26	42	48	15	58	82	52	18	92	65	56	13	25	94	67	60	90
24			21:00	59	38	18	74	64	69	23	51	76	89	57	29	43	91	56	8	90	50	64
25			22:00	23	33	55	16	28	60	79	50	66	7	33	67	99	6	66	10	65	3	52
26			23:00	56	32	16	52	66	79	93	94	27	65	94	15	66	1	88	13	7	79	29
Sheet4

 

[Richards440]

My apologies, I'm new to all this forum lark.

I will ensure I post a view of what I am trying to do next time.

Your formula helped me massively, it wasn't quite correct for what I needed (my own fault for not providing enough information). But I managed to work around what you gave me to get the figures I wasd looking for.

Thank you again!

 

[pgc01]

I'm glad it helped. Cheers!