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)

 

[gaz_chops]

What does the data look like, do the time slots just show as blanks?

Better if you can post a sample of your data using "XL2BB", it's in the right of the tool bar when posting a comment.

 

[gaz_chops]

If you're data is still 15 rows per day, then this may work to find the max value for each day

=MAX(OFFSET('Sheet1'!E1,ROW('Sheet1'!A1)*15,,16))

 

[mikerickson]

I wouldn't use OFFSET but
Given a date in F1, INDEX(E:E, MATCH(F1,A:A,0),1) is the first row for that day (E2 for 01/06/2020)
and INDEX(E:E, MATCH(F1,A:A,0)+16,1) is the last row for that day, so

=MAX(INDEX(E:E, MATCH(F1,A:A,0),1):INDEX(E:E, MATCH(F1,A:A,0)+16,1)) is the max of the column E entries for the day in F1

 

[username_rand]

If you're data is still 15 rows per day, then this may work to find the max value for each day

=MAX(OFFSET('Sheet1'!E1,ROW('Sheet1'!A1)*15,,16))

View attachment 17188

It is 15 rows for now, but it is not final, some dates will have 16 entries others will have 15 and it is all subject to regulations set from external parties so i was really looking for a solution that would work regardless of the number of entries which is why i thought i'd use the MAX function but it does not seem to work

 

[username_rand]

I wouldn't use OFFSET but
Given a date in F1, INDEX(E:E, MATCH(F1,A:A,0),1) is the first row for that day (E2 for 01/06/2020)
and INDEX(E:E, MATCH(F1,A:A,0)+16,1) is the last row for that day, so

=MAX(INDEX(E:E, MATCH(F1,A:A,0),1):INDEX(E:E, MATCH(F1,A:A,0)+16,1)) is the max of the column E entries for the day in F1

I'll try the formula you provided now

 

[gaz_chops]

Then @mikerickson 's solution is the best choice.

 

[mikerickson]

If your dates in column F are regular,
INDEX(E:E, MATCH(F2,A:A,0)-1,1) will be the last cell with F1 data
So
=MAX(INDEX(E:E, MATCH(F1,A:A,0),1):INDEX(E:E, MATCH(F2,A:A,0)-1,1))

Another approach would be to have the date in all of the rows for that day. (Conditional formatting could hide the unsightly entries) and
=MAXIFS(E:E, A:A, F1) would provide the answer.

 

[sandy666]

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

 

[username_rand]

If your dates in column F are regular,
INDEX(E:E, MATCH(F2,A:A,0)-1,1) will be the last cell with F1 data
So
=MAX(INDEX(E:E, MATCH(F1,A:A,0),1):INDEX(E:E, MATCH(F2,A:A,0)-1,1))

Another approach would be to have the date in all of the rows for that day. (Conditional formatting could hide the unsightly entries) and
=MAXIFS(E:E, A:A, F1) would provide the answer.

I wouldn't use OFFSET but
Given a date in F1, INDEX(E:E, MATCH(F1,A:A,0),1) is the first row for that day (E2 for 01/06/2020)
and INDEX(E:E, MATCH(F1,A:A,0)+16,1) is the last row for that day, so

=MAX(INDEX(E:E, MATCH(F1,A:A,0),1):INDEX(E:E, MATCH(F1,A:A,0)+16,1)) is the max of the column E entries for the day in F1

The formula works but there seems to be an issue with the first and second dates because they turn out to be the same, and after that the values for the dates are shifted