# Heat Map



## JamieP89 (Dec 22, 2022)

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).csvABCDEFGHIJKLMN219/12/2022 00:0099.9319/12/2022 01:0099.9419/12/2022 02:0099.9519/12/2022 03:0099.8619/12/2022 04:0099.9719/12/2022 05:0099.9819/12/2022 06:0099.9919/12/2022 07:0099.91019/12/2022 08:0099.91119/12/2022 09:0099.91219/12/2022 10:0099.91319/12/2022 11:0099.81419/12/2022 12:0099.81519/12/2022 13:0099.81619/12/2022 14:0099.81719/12/2022 15:0099.81819/12/2022 16:0099.8MondayTuesdayWednesdayThursdayFridaySaturdaySunday1919/12/2022 17:0099.700:002019/12/2022 18:0099.301:002119/12/2022 19:0099.802:002219/12/2022 20:0099.803:002319/12/2022 21:0099.804:002419/12/2022 22:0099.805:002519/12/2022 23:0099.906:002620/12/2022 00:0099.807:002720/12/2022 01:0099.908:002820/12/2022 02:0099.909:002920/12/2022 03:0099.910:003020/12/2022 04:0099.911:003120/12/2022 05:0099.912:003220/12/2022 06:0099.913:003320/12/2022 07:0099.914:003420/12/2022 08:0099.915:003520/12/2022 09:0099.816:003620/12/2022 10:0099.917:003720/12/2022 11:0099.818:003820/12/2022 12:0099.819:003920/12/2022 13:0099.820:004020/12/2022 14:0099.821:004120/12/2022 15:0099.822:004220/12/2022 16:0099.823:004320/12/2022 17:0099.84420/12/2022 18:0099.94520/12/2022 19:0099.84620/12/2022 20:0099.84720/12/2022 21:0099.84820/12/2022 22:0099.94920/12/2022 23:0099.85021/12/2022 00:0099.85121/12/2022 01:0099.95221/12/2022 02:0099.95321/12/2022 03:0099.95421/12/2022 04:0099.95521/12/2022 05:0099.95621/12/2022 06:0099.95721/12/2022 07:0099.95821/12/2022 08:0099.95921/12/2022 09:0099.96021/12/2022 10:0099.96121/12/2022 11:0099.96221/12/2022 12:0099.86321/12/2022 13:0099.86421/12/2022 14:0099.86521/12/2022 15:0099.86621/12/2022 16:0099.86721/12/2022 17:0099.86821/12/2022 18:0099.86921/12/2022 19:0099.87021/12/2022 20:0099.87121/12/2022 21:0099.87221/12/2022 22:0099.87321/12/2022 23:0099.87422/12/2022 00:0099.97522/12/2022 01:0099.97622/12/2022 02:0099.97722/12/2022 03:0099.87822/12/2022 04:0099.97922/12/2022 05:0099.98022/12/2022 06:0099.98122/12/2022 07:0099.98222/12/2022 08:0099.98322/12/2022 09:0099.98422/12/2022 10:0099.98522/12/2022 11:0099.08622/12/2022 12:0099.48722/12/2022 13:0099.78822/12/2022 14:0099.48922/12/2022 15:0099.29022/12/2022 16:0099.59122/12/2022 17:0099.19222/12/2022 18:0099.39322/12/2022 19:0099.29422/12/2022 20:0099.29522/12/2022 21:0099.79622/12/2022 22:0099.99722/12/2022 23:0099.49823/12/2022 00:0099.89923/12/2022 01:0099.310023/12/2022 02:0099.510123/12/2022 03:0099.610223/12/2022 04:0099.110323/12/2022 05:0099.310423/12/2022 06:0099.410523/12/2022 07:0099.310623/12/2022 08:0099.410723/12/2022 09:0099.910823/12/2022 10:0099.510923/12/2022 11:0099.911023/12/2022 12:00100.011123/12/2022 13:0099.711223/12/2022 14:0099.211323/12/2022 15:0099.611423/12/2022 16:00100.011523/12/2022 17:0099.311623/12/2022 18:0099.711723/12/2022 19:0099.811823/12/2022 20:0099.811923/12/2022 21:0099.212023/12/2022 22:0099.712123/12/2022 23:0099.212224/12/2022 00:0099.712324/12/2022 01:0099.512424/12/2022 02:0099.012524/12/2022 03:0099.812624/12/2022 04:0099.912724/12/2022 05:0099.912824/12/2022 06:0099.212924/12/2022 07:0099.413024/12/2022 08:0099.913124/12/2022 09:0099.613224/12/2022 10:0099.913324/12/2022 11:0099.113424/12/2022 12:0099.013524/12/2022 13:0099.113624/12/2022 14:0099.213724/12/2022 15:0099.313824/12/2022 16:0099.213924/12/2022 17:0099.214024/12/2022 18:0099.414124/12/2022 19:0099.114224/12/2022 20:0099.214324/12/2022 21:0099.314424/12/2022 22:0099.614524/12/2022 23:0099.914625/12/2022 00:0099.914725/12/2022 01:0099.014825/12/2022 02:0099.114925/12/2022 03:0099.115025/12/2022 04:0099.015125/12/2022 05:0099.615225/12/2022 06:0099.815325/12/2022 07:0099.515425/12/2022 08:0099.015525/12/2022 09:00100.015625/12/2022 10:0099.315725/12/2022 11:0099.215825/12/2022 12:0099.415925/12/2022 13:0099.716025/12/2022 14:0099.116125/12/2022 15:0099.716225/12/2022 16:00100.016325/12/2022 17:0099.216425/12/2022 18:0099.816525/12/2022 19:0099.616625/12/2022 20:0099.216725/12/2022 21:0099.516825/12/2022 22:0099.716925/12/2022 23:0099.9StackedColumn (4)Cell FormulasRangeFormulaB85:B169B85=RAND()*1+99


----------



## Flashbond (Dec 22, 2022)

H19:

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


----------



## jdellasala (Dec 22, 2022)

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:
Book1ABCD1Date / TimeValueWeekdayHour212/19/2022 00:0099.6200:00312/19/2022 01:0099.6201:00412/19/2022 02:0099.2202:00Sheet3Cell FormulasRangeFormulaB2:B4B2=ROUND(RAND()*1+99,1)C2:C4C2=WEEKDAY([@[Date / Time]])D2:D4D2=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:
Book1GHIJKLMN18MondayTuesdayWednesdayThursdayFridaySaturdaySunday190:0099.699.399.399.199.999.699.0201:0099.099.2100.099.999.199.299.8212:0099.599.399.799.599.299.799.7223:0099.499.899.1100.099.499.899.3234:0099.399.999.5100.099.899.499.7245:0099.399.599.799.799.599.699.1256:0099.799.699.799.3100.099.799.4267:0099.899.499.699.199.199.499.5278:0099.3100.099.799.299.399.499.2289:0099.2100.099.899.399.899.899.82910:0099.899.499.799.899.399.199.13011:0099.199.499.399.499.499.799.13112:0099.699.899.099.899.999.299.13213:0099.999.599.699.199.399.099.83314:0099.999.199.199.599.599.899.63415:0099.899.399.799.699.499.8100.03516:0099.199.199.699.999.899.499.73617:0099.8100.099.899.999.299.299.93718:0099.499.899.199.399.399.399.93819:0099.799.399.699.799.699.5100.03920:0099.499.299.499.299.199.399.04021:0099.799.699.599.799.999.399.84122:0099.099.699.599.399.799.899.84223:0099.399.399.599.299.299.399.1Sheet3Cell FormulasRangeFormulaG19:G42G19=ROUND(TIME(SEQUENCE(24,,0),0,0),5)H19:N42H19=AVERAGEIFS(Times[Value],Times[Hour],G19#,Times[Weekday],H18:N18)Dynamic array formulas.


----------



## Flashbond (Dec 22, 2022)

jdellasala said:


> 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.
> ...


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 (Dec 22, 2022)

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 (Dec 22, 2022)

Fluff said:


> Another option
> 
> 
> 
> ...



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 (Dec 22, 2022)

Glad we could help & thanks for the feedback.


----------



## JamieP89 (Dec 22, 2022)

Fluff said:


> 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 (Dec 22, 2022)

What do you mean by a count of days?


----------



## Flashbond (Dec 22, 2022)

I think he means something like:

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


----------



## JamieP89 (Dec 22, 2022)

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).csvABCDEFGHIJKLMN219/12/2022 00:0099.9319/12/2022 01:0099.9419/12/2022 02:0099.9519/12/2022 03:0099.8619/12/2022 04:0099.9719/12/2022 05:0099.9819/12/2022 06:0099.9919/12/2022 07:0099.91019/12/2022 08:0099.91119/12/2022 09:0099.91219/12/2022 10:0099.91319/12/2022 11:0099.81419/12/2022 12:0099.81519/12/2022 13:0099.81619/12/2022 14:0099.81719/12/2022 15:0099.81819/12/2022 16:0099.8MondayTuesdayWednesdayThursdayFridaySaturdaySunday1919/12/2022 17:0099.700:002019/12/2022 18:0099.301:002119/12/2022 19:0099.802:002219/12/2022 20:0099.803:002319/12/2022 21:0099.804:002419/12/2022 22:0099.805:002519/12/2022 23:0099.906:002620/12/2022 00:0099.807:002720/12/2022 01:0099.908:002820/12/2022 02:0099.909:002920/12/2022 03:0099.910:003020/12/2022 04:0099.911:003120/12/2022 05:0099.912:003220/12/2022 06:0099.913:003320/12/2022 07:0099.914:003420/12/2022 08:0099.915:003520/12/2022 09:0099.816:003620/12/2022 10:0099.917:003720/12/2022 11:0099.818:003820/12/2022 12:0099.819:003920/12/2022 13:0099.820:004020/12/2022 14:0099.821:004120/12/2022 15:0099.822:004220/12/2022 16:0099.823:004320/12/2022 17:0099.84420/12/2022 18:0099.94520/12/2022 19:0099.84620/12/2022 20:0099.84720/12/2022 21:0099.84820/12/2022 22:0099.94920/12/2022 23:0099.85021/12/2022 00:0099.85121/12/2022 01:0099.95221/12/2022 02:0099.95321/12/2022 03:0099.95421/12/2022 04:0099.95521/12/2022 05:0099.95621/12/2022 06:0099.95721/12/2022 07:0099.95821/12/2022 08:0099.95921/12/2022 09:0099.96021/12/2022 10:0099.96121/12/2022 11:0099.96221/12/2022 12:0099.86321/12/2022 13:0099.86421/12/2022 14:0099.86521/12/2022 15:0099.86621/12/2022 16:0099.86721/12/2022 17:0099.86821/12/2022 18:0099.86921/12/2022 19:0099.87021/12/2022 20:0099.87121/12/2022 21:0099.87221/12/2022 22:0099.87321/12/2022 23:0099.87422/12/2022 00:0099.97522/12/2022 01:0099.97622/12/2022 02:0099.97722/12/2022 03:0099.87822/12/2022 04:0099.97922/12/2022 05:0099.98022/12/2022 06:0099.98122/12/2022 07:0099.98222/12/2022 08:0099.98322/12/2022 09:0099.98422/12/2022 10:0099.98522/12/2022 11:0099.08622/12/2022 12:0099.48722/12/2022 13:0099.78822/12/2022 14:0099.48922/12/2022 15:0099.29022/12/2022 16:0099.59122/12/2022 17:0099.19222/12/2022 18:0099.39322/12/2022 19:0099.29422/12/2022 20:0099.29522/12/2022 21:0099.79622/12/2022 22:0099.99722/12/2022 23:0099.49823/12/2022 00:0099.89923/12/2022 01:0099.310023/12/2022 02:0099.510123/12/2022 03:0099.610223/12/2022 04:0099.110323/12/2022 05:0099.310423/12/2022 06:0099.410523/12/2022 07:0099.310623/12/2022 08:0099.410723/12/2022 09:0099.910823/12/2022 10:0099.510923/12/2022 11:0099.911023/12/2022 12:00100.011123/12/2022 13:0099.711223/12/2022 14:0099.211323/12/2022 15:0099.611423/12/2022 16:00100.011523/12/2022 17:0099.311623/12/2022 18:0099.711723/12/2022 19:0099.811823/12/2022 20:0099.811923/12/2022 21:0099.212023/12/2022 22:0099.712123/12/2022 23:0099.212224/12/2022 00:0099.712324/12/2022 01:0099.512424/12/2022 02:0099.012524/12/2022 03:0099.812624/12/2022 04:0099.912724/12/2022 05:0099.912824/12/2022 06:0099.212924/12/2022 07:0099.413024/12/2022 08:0099.913124/12/2022 09:0099.613224/12/2022 10:0099.913324/12/2022 11:0099.113424/12/2022 12:0099.013524/12/2022 13:0099.113624/12/2022 14:0099.213724/12/2022 15:0099.313824/12/2022 16:0099.213924/12/2022 17:0099.214024/12/2022 18:0099.414124/12/2022 19:0099.114224/12/2022 20:0099.214324/12/2022 21:0099.314424/12/2022 22:0099.614524/12/2022 23:0099.914625/12/2022 00:0099.914725/12/2022 01:0099.014825/12/2022 02:0099.114925/12/2022 03:0099.115025/12/2022 04:0099.015125/12/2022 05:0099.615225/12/2022 06:0099.815325/12/2022 07:0099.515425/12/2022 08:0099.015525/12/2022 09:00100.015625/12/2022 10:0099.315725/12/2022 11:0099.215825/12/2022 12:0099.415925/12/2022 13:0099.716025/12/2022 14:0099.116125/12/2022 15:0099.716225/12/2022 16:00100.016325/12/2022 17:0099.216425/12/2022 18:0099.816525/12/2022 19:0099.616625/12/2022 20:0099.216725/12/2022 21:0099.516825/12/2022 22:0099.716925/12/2022 23:0099.9StackedColumn (4)Cell FormulasRangeFormulaB85:B169B85=RAND()*1+99


----------



## JamieP89 (Dec 22, 2022)

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

StackedColumn (4).csvFGHIJKLMNOPQR23Row Labels4Sep523-Sep624-Sep725-Sep826-Sep927-Sep1028-Sep1129-Sep1230-Sep13Oct1401-Oct1502-Oct1603-Oct1704-Oct18MondayTuesdayWednesdayThursdayFridaySaturdaySunday05-Oct1900:0099.999.999.999.999.999.899.906-Oct2001:0099.799.899.999.999.899.899.907-Oct2102:0099.999.699.899.999.999.199.808-Oct2203:0099.999.999.599.599.699.499.809-Oct2304:0099.999.999.899.999.999.999.910-Oct2405:0099.999.999.499.999.999.999.911-Oct2506:0099.999.899.999.999.999.999.912-Oct2607:0099.999.899.799.999.999.999.913-Oct2708:0099.999.999.899.799.099.999.914-Oct2809:0099.999.699.599.997.699.999.915-Oct2910:0099.999.299.399.899.699.899.916-Oct3011:0099.899.899.699.899.599.799.917-Oct3112:0099.499.799.899.899.899.899.918-Oct3213:0098.999.799.699.899.499.899.619-Oct3314:0099.799.899.399.799.799.899.820-Oct3415:0099.899.899.899.799.899.899.821-Oct3516:0099.899.899.899.199.899.899.822-Oct3617:0099.899.899.899.899.799.899.723-Oct3718:0099.899.899.899.699.799.899.724-Oct3819:0099.899.799.899.899.899.899.825-Oct3920:0099.599.799.799.899.899.899.726-Oct4021:0099.899.899.899.899.899.899.827-Oct4122:0099.899.899.999.899.899.899.828-Oct4223:0099.999.999.899.899.899.899.929-Oct4330-Oct4431-Oct451234567Nov46Day Count111121101-NovStackedColumn (4)Cell FormulasRangeFormulaG19: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 FormattingCellConditionCell FormatStop If TrueG19:M42,G44Other TypeColor scaleNON19:N42Other TypeColor scaleNO


----------



## JamieP89 (Dec 22, 2022)

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

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


----------



## JamieP89 (Dec 22, 2022)

Flashbond said:


> 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 (Dec 22, 2022)

How about
	
	
	
	
	
	



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


----------



## JamieP89 (Dec 22, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


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 (Dec 22, 2022)

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


----------



## JamieP89 (Dec 22, 2022)

Fluff said:


> 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 (Dec 22, 2022)

Without seeing all the data, not really.


----------



## JamieP89 (Dec 22, 2022)

Fluff said:


> 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 (Dec 22, 2022)

Glad we could help & thanks for the feedback.


----------

