# Using Offset with MAX

#### username_rand

##### New Member
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)

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### gaz_chops

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member

ADVERTISEMENT

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

##### New Member
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

##### Well-known Member

ADVERTISEMENT

Then @mikerickson 's solution is the best choice.

#### mikerickson

##### MrExcel MVP
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

##### Well-known Member
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

Rich (BB code):
``````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

##### New Member
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

Replies
2
Views
78
Replies
0
Views
63
Replies
9
Views
91
Replies
4
Views
82
Replies
9
Views
97

Threads
1,113,903
Messages
5,544,966
Members
410,646
Latest member
jojoseb