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

 

[JamieP89]

Think i figured it out - google video somewhere helped with it

StackedColumn (4).csv
	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3													Row Labels
4													Sep
5													23-Sep
6													24-Sep
7													25-Sep
8													26-Sep
9													27-Sep
10													28-Sep
11													29-Sep
12													30-Sep
13													Oct
14													01-Oct
15													02-Oct
16													03-Oct
17													04-Oct
18		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday					05-Oct
19	00:00	99.9	99.9	99.9	99.9	99.9	99.8	99.9					06-Oct
20	01:00	99.7	99.8	99.9	99.9	99.8	99.8	99.9					07-Oct
21	02:00	99.9	99.6	99.8	99.9	99.9	99.1	99.8					08-Oct
22	03:00	99.9	99.9	99.5	99.5	99.6	99.4	99.8					09-Oct
23	04:00	99.9	99.9	99.8	99.9	99.9	99.9	99.9					10-Oct
24	05:00	99.9	99.9	99.4	99.9	99.9	99.9	99.9					11-Oct
25	06:00	99.9	99.8	99.9	99.9	99.9	99.9	99.9					12-Oct
26	07:00	99.9	99.8	99.7	99.9	99.9	99.9	99.9					13-Oct
27	08:00	99.9	99.9	99.8	99.7	99.0	99.9	99.9					14-Oct
28	09:00	99.9	99.6	99.5	99.9	97.6	99.9	99.9					15-Oct
29	10:00	99.9	99.2	99.3	99.8	99.6	99.8	99.9					16-Oct
30	11:00	99.8	99.8	99.6	99.8	99.5	99.7	99.9					17-Oct
31	12:00	99.4	99.7	99.8	99.8	99.8	99.8	99.9					18-Oct
32	13:00	98.9	99.7	99.6	99.8	99.4	99.8	99.6					19-Oct
33	14:00	99.7	99.8	99.3	99.7	99.7	99.8	99.8					20-Oct
34	15:00	99.8	99.8	99.8	99.7	99.8	99.8	99.8					21-Oct
35	16:00	99.8	99.8	99.8	99.1	99.8	99.8	99.8					22-Oct
36	17:00	99.8	99.8	99.8	99.8	99.7	99.8	99.7					23-Oct
37	18:00	99.8	99.8	99.8	99.6	99.7	99.8	99.7					24-Oct
38	19:00	99.8	99.7	99.8	99.8	99.8	99.8	99.8					25-Oct
39	20:00	99.5	99.7	99.7	99.8	99.8	99.8	99.7					26-Oct
40	21:00	99.8	99.8	99.8	99.8	99.8	99.8	99.8					27-Oct
41	22:00	99.8	99.8	99.9	99.8	99.8	99.8	99.8					28-Oct
42	23:00	99.9	99.9	99.8	99.8	99.8	99.8	99.9					29-Oct
43													30-Oct
44													31-Oct
45		1	2	3	4	5	6	7					Nov
46	Day Count	1	1	1	1	2	1	1					01-Nov
StackedColumn (4)

Cell Formulas
Range	Formula
G19:M42	G19	=AVERAGE(FILTER($B$2:$B$2000,(TEXT($A$2:$A$2000,"dddd")=G$18)*(TEXT($A$2:$A$2000,"hh:mm")=TEXT($F19,"hh:mm"))))
G46:M46	G46	=SUMPRODUCT(--(WEEKDAY($R$5:$R$12,2)=G45))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G19:M42,G44	Other Type	Color scale		NO
N19:N42	Other Type	Color scale		NO

 

[JamieP89]

Maybe not.... the pivot months get in the way of it working...

**EDIT** just removed the "months" field from the pivot

 

[JamieP89]

I think he means something like:

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

Something like this - but I need a unique value for each "day" instead of it counting every instance for say the 24/09/2022

 

[Fluff]

How about

=SUM(--(WEEKDAY(UNIQUE(FILTER(INT($A$2:$A$2000),$A$2:$A$2000<>"")),2)=COLUMNS($H45:H45)))

 

[JamieP89]

How about

=SUM(--(WEEKDAY(UNIQUE(FILTER(INT($A$2:$A$2000),$A$2:$A$2000<>"")),2)=COLUMNS($H45:H45)))

I ended up with something similar to this went for this - would you say there would be any additional benefit over using yours ? =SUMPRODUCT(--(WEEKDAY($R$5:$R$94,2)=G27))

 

[Fluff]

The only real difference is you need the pivot for my formula.

 

[JamieP89]

The only real difference is you need the pivot for my formula.

AH right so yours requires less faffing around in that sense, I guess for me it's just understanding every part of your formula. I did notice when I pasted yours in I seem to get a day less in count for say Monday (Mines counting 13 and yours was showing 12)

Any ideas why?

 

[Fluff]

Without seeing all the data, not really.

 

[JamieP89]

Without seeing all the data, not really.

No probs - I won't bother you with it as I'm sure it's nothing major. Thank you again all for the input and support on this, massively helpful as always!

 

[Fluff]

Glad we could help & thanks for the feedback.