Heat Map

[JamieP89]

Hi there,

I'm having a slightly difficult time figuring out the best way to approach my Heat Map

The ask:

I am looking to use a set of data (which I will continue to add to each day) to populate the score being provided in column b. What I'm having a hard time processing is the best way to get the score for say two Mondays worth of to populate in "H18" (thinking it would be an average of the data in column b) and so on - This I am hoping will then provide incremental data over say a period of a month about what the average of each day and time looks like

Apologies the same is small as its dummy data at the moment.

I'll then use conditional formatting to show the heatmap.

I tried using a helper column initially to call turn column A into text and Monday to then do a =IF(H18=A1,B2) type calculation but realised while it will pull through the data it wasn't efficient and very manual. I'm guessing a lookup or vlookup would be better, but again just cant think how the lookup and average of x days will work!

Sorry I hope this makes sense....

StackedColumn (4).csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	19/12/2022 00:00	99.9
3	19/12/2022 01:00	99.9
4	19/12/2022 02:00	99.9
5	19/12/2022 03:00	99.8
6	19/12/2022 04:00	99.9
7	19/12/2022 05:00	99.9
8	19/12/2022 06:00	99.9
9	19/12/2022 07:00	99.9
10	19/12/2022 08:00	99.9
11	19/12/2022 09:00	99.9
12	19/12/2022 10:00	99.9
13	19/12/2022 11:00	99.8
14	19/12/2022 12:00	99.8
15	19/12/2022 13:00	99.8
16	19/12/2022 14:00	99.8
17	19/12/2022 15:00	99.8
18	19/12/2022 16:00	99.8						Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
19	19/12/2022 17:00	99.7					00:00
20	19/12/2022 18:00	99.3					01:00
21	19/12/2022 19:00	99.8					02:00
22	19/12/2022 20:00	99.8					03:00
23	19/12/2022 21:00	99.8					04:00
24	19/12/2022 22:00	99.8					05:00
25	19/12/2022 23:00	99.9					06:00
26	20/12/2022 00:00	99.8					07:00
27	20/12/2022 01:00	99.9					08:00
28	20/12/2022 02:00	99.9					09:00
29	20/12/2022 03:00	99.9					10:00
30	20/12/2022 04:00	99.9					11:00
31	20/12/2022 05:00	99.9					12:00
32	20/12/2022 06:00	99.9					13:00
33	20/12/2022 07:00	99.9					14:00
34	20/12/2022 08:00	99.9					15:00
35	20/12/2022 09:00	99.8					16:00
36	20/12/2022 10:00	99.9					17:00
37	20/12/2022 11:00	99.8					18:00
38	20/12/2022 12:00	99.8					19:00
39	20/12/2022 13:00	99.8					20:00
40	20/12/2022 14:00	99.8					21:00
41	20/12/2022 15:00	99.8					22:00
42	20/12/2022 16:00	99.8					23:00
43	20/12/2022 17:00	99.8
44	20/12/2022 18:00	99.9
45	20/12/2022 19:00	99.8
46	20/12/2022 20:00	99.8
47	20/12/2022 21:00	99.8
48	20/12/2022 22:00	99.9
49	20/12/2022 23:00	99.8
50	21/12/2022 00:00	99.8
51	21/12/2022 01:00	99.9
52	21/12/2022 02:00	99.9
53	21/12/2022 03:00	99.9
54	21/12/2022 04:00	99.9
55	21/12/2022 05:00	99.9
56	21/12/2022 06:00	99.9
57	21/12/2022 07:00	99.9
58	21/12/2022 08:00	99.9
59	21/12/2022 09:00	99.9
60	21/12/2022 10:00	99.9
61	21/12/2022 11:00	99.9
62	21/12/2022 12:00	99.8
63	21/12/2022 13:00	99.8
64	21/12/2022 14:00	99.8
65	21/12/2022 15:00	99.8
66	21/12/2022 16:00	99.8
67	21/12/2022 17:00	99.8
68	21/12/2022 18:00	99.8
69	21/12/2022 19:00	99.8
70	21/12/2022 20:00	99.8
71	21/12/2022 21:00	99.8
72	21/12/2022 22:00	99.8
73	21/12/2022 23:00	99.8
74	22/12/2022 00:00	99.9
75	22/12/2022 01:00	99.9
76	22/12/2022 02:00	99.9
77	22/12/2022 03:00	99.8
78	22/12/2022 04:00	99.9
79	22/12/2022 05:00	99.9
80	22/12/2022 06:00	99.9
81	22/12/2022 07:00	99.9
82	22/12/2022 08:00	99.9
83	22/12/2022 09:00	99.9
84	22/12/2022 10:00	99.9
85	22/12/2022 11:00	99.0
86	22/12/2022 12:00	99.4
87	22/12/2022 13:00	99.7
88	22/12/2022 14:00	99.4
89	22/12/2022 15:00	99.2
90	22/12/2022 16:00	99.5
91	22/12/2022 17:00	99.1
92	22/12/2022 18:00	99.3
93	22/12/2022 19:00	99.2
94	22/12/2022 20:00	99.2
95	22/12/2022 21:00	99.7
96	22/12/2022 22:00	99.9
97	22/12/2022 23:00	99.4
98	23/12/2022 00:00	99.8
99	23/12/2022 01:00	99.3
100	23/12/2022 02:00	99.5
101	23/12/2022 03:00	99.6
102	23/12/2022 04:00	99.1
103	23/12/2022 05:00	99.3
104	23/12/2022 06:00	99.4
105	23/12/2022 07:00	99.3
106	23/12/2022 08:00	99.4
107	23/12/2022 09:00	99.9
108	23/12/2022 10:00	99.5
109	23/12/2022 11:00	99.9
110	23/12/2022 12:00	100.0
111	23/12/2022 13:00	99.7
112	23/12/2022 14:00	99.2
113	23/12/2022 15:00	99.6
114	23/12/2022 16:00	100.0
115	23/12/2022 17:00	99.3
116	23/12/2022 18:00	99.7
117	23/12/2022 19:00	99.8
118	23/12/2022 20:00	99.8
119	23/12/2022 21:00	99.2
120	23/12/2022 22:00	99.7
121	23/12/2022 23:00	99.2
122	24/12/2022 00:00	99.7
123	24/12/2022 01:00	99.5
124	24/12/2022 02:00	99.0
125	24/12/2022 03:00	99.8
126	24/12/2022 04:00	99.9
127	24/12/2022 05:00	99.9
128	24/12/2022 06:00	99.2
129	24/12/2022 07:00	99.4
130	24/12/2022 08:00	99.9
131	24/12/2022 09:00	99.6
132	24/12/2022 10:00	99.9
133	24/12/2022 11:00	99.1
134	24/12/2022 12:00	99.0
135	24/12/2022 13:00	99.1
136	24/12/2022 14:00	99.2
137	24/12/2022 15:00	99.3
138	24/12/2022 16:00	99.2
139	24/12/2022 17:00	99.2
140	24/12/2022 18:00	99.4
141	24/12/2022 19:00	99.1
142	24/12/2022 20:00	99.2
143	24/12/2022 21:00	99.3
144	24/12/2022 22:00	99.6
145	24/12/2022 23:00	99.9
146	25/12/2022 00:00	99.9
147	25/12/2022 01:00	99.0
148	25/12/2022 02:00	99.1
149	25/12/2022 03:00	99.1
150	25/12/2022 04:00	99.0
151	25/12/2022 05:00	99.6
152	25/12/2022 06:00	99.8
153	25/12/2022 07:00	99.5
154	25/12/2022 08:00	99.0
155	25/12/2022 09:00	100.0
156	25/12/2022 10:00	99.3
157	25/12/2022 11:00	99.2
158	25/12/2022 12:00	99.4
159	25/12/2022 13:00	99.7
160	25/12/2022 14:00	99.1
161	25/12/2022 15:00	99.7
162	25/12/2022 16:00	100.0
163	25/12/2022 17:00	99.2
164	25/12/2022 18:00	99.8
165	25/12/2022 19:00	99.6
166	25/12/2022 20:00	99.2
167	25/12/2022 21:00	99.5
168	25/12/2022 22:00	99.7
169	25/12/2022 23:00	99.9
StackedColumn (4)

Cell Formulas
Range	Formula
B85:B169	B85	=RAND()*1+99

 

[Legacy 143009]

H19:

=AVERAGEIFS(B:B,TEXT(A:A,"dddd"),H$18,TEXT(A:A,"hh:mm"),$G19)

 

[jdellasala]

I could not get the formula in response #2 to work. It gave this error:

In trying to work this out and in the past the "S" functions don't like getting an array of something to evaluate, and not have a criteria RANGE that matches in size (rows). The TEXT(A:A,"dddd") is scaler and cannot return an array. That's in 365 anyway. Maybe it works in 2019?
That said, I would recommend using an Excel Table for the data, especially since you said "which I will continue to add to each day". While a full column reference would compensate for that, using a table makes it easier to see what's happening.
ALSO, if you are using 365 and the formula above isn't working, I'd recommend adding helper columns which make the needed evaluations so that AVERAGEIFS can do it's job.
This is the table I recommend:

Book1
	A	B	C	D
1	Date / Time	Value	Weekday	Hour
2	12/19/2022 00:00	99.6	2	00:00
3	12/19/2022 01:00	99.6	2	01:00
4	12/19/2022 02:00	99.2	2	02:00
Sheet3

Cell Formulas
Range	Formula
B2:B4	B2	=ROUND(RAND()*1+99,1)
C2:C4	C2	=WEEKDAY([@[Date / Time]])
D2:D4	D2	=ROUND(TIME(HOUR([@[Date / Time]]),0,0),5)

I rounded the value in column B just because it was annoying me! The Time value is rounded because there are known anomalies which can come into play when TIME is involved, and although I don't think that would matter here, it means knowing what the value is.
And here's the heat map:

Book1
	G	H	I	J	K	L	M	N
18		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
19	0:00	99.6	99.3	99.3	99.1	99.9	99.6	99.0
20	1:00	99.0	99.2	100.0	99.9	99.1	99.2	99.8
21	2:00	99.5	99.3	99.7	99.5	99.2	99.7	99.7
22	3:00	99.4	99.8	99.1	100.0	99.4	99.8	99.3
23	4:00	99.3	99.9	99.5	100.0	99.8	99.4	99.7
24	5:00	99.3	99.5	99.7	99.7	99.5	99.6	99.1
25	6:00	99.7	99.6	99.7	99.3	100.0	99.7	99.4
26	7:00	99.8	99.4	99.6	99.1	99.1	99.4	99.5
27	8:00	99.3	100.0	99.7	99.2	99.3	99.4	99.2
28	9:00	99.2	100.0	99.8	99.3	99.8	99.8	99.8
29	10:00	99.8	99.4	99.7	99.8	99.3	99.1	99.1
30	11:00	99.1	99.4	99.3	99.4	99.4	99.7	99.1
31	12:00	99.6	99.8	99.0	99.8	99.9	99.2	99.1
32	13:00	99.9	99.5	99.6	99.1	99.3	99.0	99.8
33	14:00	99.9	99.1	99.1	99.5	99.5	99.8	99.6
34	15:00	99.8	99.3	99.7	99.6	99.4	99.8	100.0
35	16:00	99.1	99.1	99.6	99.9	99.8	99.4	99.7
36	17:00	99.8	100.0	99.8	99.9	99.2	99.2	99.9
37	18:00	99.4	99.8	99.1	99.3	99.3	99.3	99.9
38	19:00	99.7	99.3	99.6	99.7	99.6	99.5	100.0
39	20:00	99.4	99.2	99.4	99.2	99.1	99.3	99.0
40	21:00	99.7	99.6	99.5	99.7	99.9	99.3	99.8
41	22:00	99.0	99.6	99.5	99.3	99.7	99.8	99.8
42	23:00	99.3	99.3	99.5	99.2	99.2	99.3	99.1
Sheet3

Cell Formulas
Range	Formula
G19:G42	G19	=ROUND(TIME(SEQUENCE(24,,0),0,0),5)
H19:N42	H19	=AVERAGEIFS(Times[Value],Times[Hour],G19#,Times[Weekday],H18:N18)
Dynamic array formulas.

 

[Legacy 143009]

I could not get the formula in response #2 to work. It gave this error:
View attachment 81412
In trying to work this out and in the past the "S" functions don't like getting an array of something to evaluate, and not have a criteria RANGE that matches in size (rows). The TEXT(A:A,"dddd") is scaler and cannot return an array. That's in 365 anyway. Maybe it works in 2019?
That said, I would recommend using an Excel Table for the data, especially since you said "which I will continue to add to each day". While a full column reference would compensate for that, using a table makes it easier to see what's happening.
ALSO, if you are using 365 and the formula above isn't working, I'd recommend adding helper columns which make the needed evaluations so that AVERAGEIFS can do it's job.
This is the table I recommend:

Book1
	A	B	C	D
1	Date / Time	Value	Weekday	Hour
2	12/19/2022 00:00	99.6	2	00:00
3	12/19/2022 01:00	99.6	2	01:00
4	12/19/2022 02:00	99.2	2	02:00
Sheet3

Cell Formulas
Range	Formula
B2:B4	B2	=ROUND(RAND()*1+99,1)
C2:C4	C2	=WEEKDAY([@[Date / Time]])
D2:D4	D2	=ROUND(TIME(HOUR([@[Date / Time]]),0,0),5)

I rounded the value in column B just because it was annoying me! The Time value is rounded because there are known anomalies which can come into play when TIME is involved, and although I don't think that would matter here, it means knowing what the value is.
And here's the heat map:

Book1
	G	H	I	J	K	L	M	N
18		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
19	0:00	99.6	99.3	99.3	99.1	99.9	99.6	99.0
20	1:00	99.0	99.2	100.0	99.9	99.1	99.2	99.8
21	2:00	99.5	99.3	99.7	99.5	99.2	99.7	99.7
22	3:00	99.4	99.8	99.1	100.0	99.4	99.8	99.3
23	4:00	99.3	99.9	99.5	100.0	99.8	99.4	99.7
24	5:00	99.3	99.5	99.7	99.7	99.5	99.6	99.1
25	6:00	99.7	99.6	99.7	99.3	100.0	99.7	99.4
26	7:00	99.8	99.4	99.6	99.1	99.1	99.4	99.5
27	8:00	99.3	100.0	99.7	99.2	99.3	99.4	99.2
28	9:00	99.2	100.0	99.8	99.3	99.8	99.8	99.8
29	10:00	99.8	99.4	99.7	99.8	99.3	99.1	99.1
30	11:00	99.1	99.4	99.3	99.4	99.4	99.7	99.1
31	12:00	99.6	99.8	99.0	99.8	99.9	99.2	99.1
32	13:00	99.9	99.5	99.6	99.1	99.3	99.0	99.8
33	14:00	99.9	99.1	99.1	99.5	99.5	99.8	99.6
34	15:00	99.8	99.3	99.7	99.6	99.4	99.8	100.0
35	16:00	99.1	99.1	99.6	99.9	99.8	99.4	99.7
36	17:00	99.8	100.0	99.8	99.9	99.2	99.2	99.9
37	18:00	99.4	99.8	99.1	99.3	99.3	99.3	99.9
38	19:00	99.7	99.3	99.6	99.7	99.6	99.5	100.0
39	20:00	99.4	99.2	99.4	99.2	99.1	99.3	99.0
40	21:00	99.7	99.6	99.5	99.7	99.9	99.3	99.8
41	22:00	99.0	99.6	99.5	99.3	99.7	99.8	99.8
42	23:00	99.3	99.3	99.5	99.2	99.2	99.3	99.1
Sheet3

Cell Formulas
Range	Formula
G19:G42	G19	=ROUND(TIME(SEQUENCE(24,,0),0,0),5)
H19:N42	H19	=AVERAGEIFS(Times[Value],Times[Hour],G19#,Times[Weekday],H18:N18)
Dynamic array formulas.

I hadn't tested the formula and you are right.
Luckly, I discovered that O365 has ARRAYTOTEXT function H19:

=AVERAGEIFS(B:B,ARRAYTOTEXT(A:A,"dddd"),H$17,ARRAYTOTEXT(A:A,"hh:mm"),$G18)

I have no O365 to test but hopefully it will work.

 

[Fluff]

Another option

=AVERAGE(FILTER($B$2:$B$2000,(TEXT($A$2:$A$2000,"dddd")=H$18)*(TEXT($A$2:$A$2000,"hh:mm")=TEXT($G19,"hh:mm"))))

 

[JamieP89]

Another option

=AVERAGE(FILTER($B$2:$B$2000,(TEXT($A$2:$A$2000,"dddd")=H$18)*(TEXT($A$2:$A$2000,"hh:mm")=TEXT($G19,"hh:mm"))))

Thanks for the replies on this one all - I decided to go with Fluff's recommendation purely because I was able to quickly understand the logic

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JamieP89]

Glad we could help & thanks for the feedback.

No probs - I should have added I was looking for a count of how many days under each day of the heat map as well as cant seem to get anything to work without spill...

 

[Fluff]

What do you mean by a count of days?

 

[Legacy 143009]

I think he means something like:

=COUNT(FILTER($B$2:$B$2000,(TEXT($A$2:$A$2000,"dddd")=H$18)))