Heat Map

JamieP89

Board Regular
Joined
Mar 8, 2022
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
219/12/2022 00:0099.9
319/12/2022 01:0099.9
419/12/2022 02:0099.9
519/12/2022 03:0099.8
619/12/2022 04:0099.9
719/12/2022 05:0099.9
819/12/2022 06:0099.9
919/12/2022 07:0099.9
1019/12/2022 08:0099.9
1119/12/2022 09:0099.9
1219/12/2022 10:0099.9
1319/12/2022 11:0099.8
1419/12/2022 12:0099.8
1519/12/2022 13:0099.8
1619/12/2022 14:0099.8
1719/12/2022 15:0099.8
1819/12/2022 16:0099.8MondayTuesdayWednesdayThursdayFridaySaturdaySunday
1919/12/2022 17:0099.700:00
2019/12/2022 18:0099.301:00
2119/12/2022 19:0099.802:00
2219/12/2022 20:0099.803:00
2319/12/2022 21:0099.804:00
2419/12/2022 22:0099.805:00
2519/12/2022 23:0099.906:00
2620/12/2022 00:0099.807:00
2720/12/2022 01:0099.908:00
2820/12/2022 02:0099.909:00
2920/12/2022 03:0099.910:00
3020/12/2022 04:0099.911:00
3120/12/2022 05:0099.912:00
3220/12/2022 06:0099.913:00
3320/12/2022 07:0099.914:00
3420/12/2022 08:0099.915:00
3520/12/2022 09:0099.816:00
3620/12/2022 10:0099.917:00
3720/12/2022 11:0099.818:00
3820/12/2022 12:0099.819:00
3920/12/2022 13:0099.820:00
4020/12/2022 14:0099.821:00
4120/12/2022 15:0099.822:00
4220/12/2022 16:0099.823:00
4320/12/2022 17:0099.8
4420/12/2022 18:0099.9
4520/12/2022 19:0099.8
4620/12/2022 20:0099.8
4720/12/2022 21:0099.8
4820/12/2022 22:0099.9
4920/12/2022 23:0099.8
5021/12/2022 00:0099.8
5121/12/2022 01:0099.9
5221/12/2022 02:0099.9
5321/12/2022 03:0099.9
5421/12/2022 04:0099.9
5521/12/2022 05:0099.9
5621/12/2022 06:0099.9
5721/12/2022 07:0099.9
5821/12/2022 08:0099.9
5921/12/2022 09:0099.9
6021/12/2022 10:0099.9
6121/12/2022 11:0099.9
6221/12/2022 12:0099.8
6321/12/2022 13:0099.8
6421/12/2022 14:0099.8
6521/12/2022 15:0099.8
6621/12/2022 16:0099.8
6721/12/2022 17:0099.8
6821/12/2022 18:0099.8
6921/12/2022 19:0099.8
7021/12/2022 20:0099.8
7121/12/2022 21:0099.8
7221/12/2022 22:0099.8
7321/12/2022 23:0099.8
7422/12/2022 00:0099.9
7522/12/2022 01:0099.9
7622/12/2022 02:0099.9
7722/12/2022 03:0099.8
7822/12/2022 04:0099.9
7922/12/2022 05:0099.9
8022/12/2022 06:0099.9
8122/12/2022 07:0099.9
8222/12/2022 08:0099.9
8322/12/2022 09:0099.9
8422/12/2022 10:0099.9
8522/12/2022 11:0099.0
8622/12/2022 12:0099.4
8722/12/2022 13:0099.7
8822/12/2022 14:0099.4
8922/12/2022 15:0099.2
9022/12/2022 16:0099.5
9122/12/2022 17:0099.1
9222/12/2022 18:0099.3
9322/12/2022 19:0099.2
9422/12/2022 20:0099.2
9522/12/2022 21:0099.7
9622/12/2022 22:0099.9
9722/12/2022 23:0099.4
9823/12/2022 00:0099.8
9923/12/2022 01:0099.3
10023/12/2022 02:0099.5
10123/12/2022 03:0099.6
10223/12/2022 04:0099.1
10323/12/2022 05:0099.3
10423/12/2022 06:0099.4
10523/12/2022 07:0099.3
10623/12/2022 08:0099.4
10723/12/2022 09:0099.9
10823/12/2022 10:0099.5
10923/12/2022 11:0099.9
11023/12/2022 12:00100.0
11123/12/2022 13:0099.7
11223/12/2022 14:0099.2
11323/12/2022 15:0099.6
11423/12/2022 16:00100.0
11523/12/2022 17:0099.3
11623/12/2022 18:0099.7
11723/12/2022 19:0099.8
11823/12/2022 20:0099.8
11923/12/2022 21:0099.2
12023/12/2022 22:0099.7
12123/12/2022 23:0099.2
12224/12/2022 00:0099.7
12324/12/2022 01:0099.5
12424/12/2022 02:0099.0
12524/12/2022 03:0099.8
12624/12/2022 04:0099.9
12724/12/2022 05:0099.9
12824/12/2022 06:0099.2
12924/12/2022 07:0099.4
13024/12/2022 08:0099.9
13124/12/2022 09:0099.6
13224/12/2022 10:0099.9
13324/12/2022 11:0099.1
13424/12/2022 12:0099.0
13524/12/2022 13:0099.1
13624/12/2022 14:0099.2
13724/12/2022 15:0099.3
13824/12/2022 16:0099.2
13924/12/2022 17:0099.2
14024/12/2022 18:0099.4
14124/12/2022 19:0099.1
14224/12/2022 20:0099.2
14324/12/2022 21:0099.3
14424/12/2022 22:0099.6
14524/12/2022 23:0099.9
14625/12/2022 00:0099.9
14725/12/2022 01:0099.0
14825/12/2022 02:0099.1
14925/12/2022 03:0099.1
15025/12/2022 04:0099.0
15125/12/2022 05:0099.6
15225/12/2022 06:0099.8
15325/12/2022 07:0099.5
15425/12/2022 08:0099.0
15525/12/2022 09:00100.0
15625/12/2022 10:0099.3
15725/12/2022 11:0099.2
15825/12/2022 12:0099.4
15925/12/2022 13:0099.7
16025/12/2022 14:0099.1
16125/12/2022 15:0099.7
16225/12/2022 16:00100.0
16325/12/2022 17:0099.2
16425/12/2022 18:0099.8
16525/12/2022 19:0099.6
16625/12/2022 20:0099.2
16725/12/2022 21:0099.5
16825/12/2022 22:0099.7
16925/12/2022 23:0099.9
StackedColumn (4)
Cell Formulas
RangeFormula
B85:B169B85=RAND()*1+99
 
Think i figured it out :) - google video somewhere helped with it

StackedColumn (4).csv
FGHIJKLMNOPQR
2
3Row Labels
4Sep
523-Sep
624-Sep
725-Sep
826-Sep
927-Sep
1028-Sep
1129-Sep
1230-Sep
13Oct
1401-Oct
1502-Oct
1603-Oct
1704-Oct
18MondayTuesdayWednesdayThursdayFridaySaturdaySunday05-Oct
1900:0099.999.999.999.999.999.899.906-Oct
2001:0099.799.899.999.999.899.899.907-Oct
2102:0099.999.699.899.999.999.199.808-Oct
2203:0099.999.999.599.599.699.499.809-Oct
2304:0099.999.999.899.999.999.999.910-Oct
2405:0099.999.999.499.999.999.999.911-Oct
2506:0099.999.899.999.999.999.999.912-Oct
2607:0099.999.899.799.999.999.999.913-Oct
2708:0099.999.999.899.799.099.999.914-Oct
2809:0099.999.699.599.997.699.999.915-Oct
2910:0099.999.299.399.899.699.899.916-Oct
3011:0099.899.899.699.899.599.799.917-Oct
3112:0099.499.799.899.899.899.899.918-Oct
3213:0098.999.799.699.899.499.899.619-Oct
3314:0099.799.899.399.799.799.899.820-Oct
3415:0099.899.899.899.799.899.899.821-Oct
3516:0099.899.899.899.199.899.899.822-Oct
3617:0099.899.899.899.899.799.899.723-Oct
3718:0099.899.899.899.699.799.899.724-Oct
3819:0099.899.799.899.899.899.899.825-Oct
3920:0099.599.799.799.899.899.899.726-Oct
4021:0099.899.899.899.899.899.899.827-Oct
4122:0099.899.899.999.899.899.899.828-Oct
4223:0099.999.999.899.899.899.899.929-Oct
4330-Oct
4431-Oct
451234567Nov
46Day Count111121101-Nov
StackedColumn (4)
Cell Formulas
RangeFormula
G19:M42G19=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:M46G46=SUMPRODUCT(--(WEEKDAY($R$5:$R$12,2)=G45))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19:M42,G44Other TypeColor scaleNO
N19:N42Other TypeColor scaleNO
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Maybe not.... the pivot months get in the way of it working...

**EDIT** just removed the "months" field from the pivot
 
Upvote 0
I think he means something like:
Excel Formula:
=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
 
Upvote 0
How about
Excel Formula:
=SUM(--(WEEKDAY(UNIQUE(FILTER(INT($A$2:$A$2000),$A$2:$A$2000<>"")),2)=COLUMNS($H45:H45)))
 
Upvote 0
How about
Excel Formula:
=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))
 
Upvote 0
The only real difference is you need the pivot for my formula.
 
Upvote 0
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?
 
Upvote 0
Without seeing all the data, not really.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top