Using Offset with MAX

[username_rand]

Hi, I have a workbook with data showing number of visitors at every hour for different dates. I have created another sheet which only shows the total number for each date. At first i used the following formula OFFSET('Sheet1'!$E$2,(ROW('Sheet1'!E1)*16-1),0) to copy the value in the 16th row which is the final count of visitors since the data is cumulative, but since then, working hours have changed so the formula returns blanks since the 16th row is no longer the final count. I can change it manually but the working hours are not yet final, is there a way to use OFFSET with MAX to get the maximum value from each 16 row without having to update the formula each time the working hours change? And it is worth mentioning that i have no control over how the data is filled in the sheet (see image of Sheet1 for a sample)

 

[username_rand]

maybe Power Query

Source						Result
Date	Hour	Gate 1	Gate 2	No. of Visitors		Date	No. of Visitors
01/06/2020	09:00:00	27	23	50		01/06/2020	4705
	10:00:00	98	36	134		02/06/2020	4728
	11:00:00	114	131	245		03/06/2020	3028
	12:00:00	610	320	930
	13:00:00	1106	306	1412
	14:00:00	1230	317	1547
	15:00:00	1345	353	1698
	16:00:00	1460	243	1703
	17:00:00	1575	355	1930
	18:00:00	1690	795	2485
	19:00:00	1805	1235	3040
	20:00:00	1920	1675	3595
	21:00:00	2035	2115	4150
	22:00:00	2150	2555	4705
	23:00:00	2265	2440	4705
02/06/2020	09:00:00	30	43	73
	10:00:00	100	57	157
	11:00:00	112	156	268
	12:00:00	630	323	953
	13:00:00	1204	231	1435
	14:00:00	1230	340	1570
	15:00:00	1256	465	1721
	16:00:00	1282	444	1726
	17:00:00	1308	645	1953
	18:00:00	1334	1174	2508
	19:00:00	1360	1703	3063
	20:00:00	1386	2232	3618
	21:00:00	1412	2761	4173
	22:00:00	1438	3290	4728
	23:00:00	1464	3264	4728
03/06/2020	09:00:00	16	22	38
	10:00:00	25	97	122
	11:00:00	61	172	233
	12:00:00	671	247	918
	13:00:00	1078	322	1400
	14:00:00	1138	397	1535
	15:00:00	1214	472	1686
	16:00:00	1144	547	1691
	17:00:00	1296	622	1918
	18:00:00	1776	697	2473
	19:00:00	2256	772	3028

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NotNull = Table.SelectRows(Source, each [Hour] <> null and [Hour] <> ""),
    FillD = Table.FillDown(NotNull,{"Date"}),
    Group = Table.Group(FillD, {"Date"}, {{"Count", each _, type table}}),
    Last = Table.AddColumn(Group, "Last", each Table.Last([Count])),
    Expand = Table.ExpandRecordColumn(Last, "Last", {"No. of Visitors"}, {"No. of Visitors"}),
    Type = Table.TransformColumnTypes(Expand,{{"Date", type date}})
in
    Type

Although it is a great solution but unfortunately i cannot download anything, it is out of my hands

 

[sandy666]

Although it is a great solution but unfortunately i cannot download anything, it is out of my hands

Could you update your profile (Account Details) about Excel version and OS .
Will be much easier for all around