Using Offset with MAX

username_rand

New Member
Joined
Jan 27, 2019
Messages
19
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)

Untitled.png
 

Some videos you may like

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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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))

1593433927761.png
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
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
Joined
Jan 27, 2019
Messages
19

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
Joined
Jan 27, 2019
Messages
19
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

Then @mikerickson 's solution is the best choice.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
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
Joined
Oct 24, 2015
Messages
7,075
maybe Power Query
SourceResult
DateHourGate 1Gate 2No. of VisitorsDateNo. of Visitors
01/06/202009:00:0027235001/06/20204705
10:00:00983613402/06/20204728
11:00:0011413124503/06/20203028
12:00:00610320930
13:00:0011063061412
14:00:0012303171547
15:00:0013453531698
16:00:0014602431703
17:00:0015753551930
18:00:0016907952485
19:00:00180512353040
20:00:00192016753595
21:00:00203521154150
22:00:00215025554705
23:00:00226524404705
02/06/202009:00:00304373
10:00:0010057157
11:00:00112156268
12:00:00630323953
13:00:0012042311435
14:00:0012303401570
15:00:0012564651721
16:00:0012824441726
17:00:0013086451953
18:00:00133411742508
19:00:00136017033063
20:00:00138622323618
21:00:00141227614173
22:00:00143832904728
23:00:00146432644728
03/06/202009:00:00162238
10:00:002597122
11:00:0061172233
12:00:00671247918
13:00:0010783221400
14:00:0011383971535
15:00:0012144721686
16:00:0011445471691
17:00:0012966221918
18:00:0017766972473
19:00:0022567723028

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
Joined
Jan 27, 2019
Messages
19
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
Untitled2.png
 

Watch MrExcel Video

Forum statistics

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