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
 

username_rand

New Member
Joined
Jan 27, 2019
Messages
19
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
Although it is a great solution but unfortunately i cannot download anything, it is out of my hands
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,887
Members
410,643
Latest member
sng
Top