Maximum in Regular Intervals

[drshkd]

I have values in 3000 cells in F column which I need to find maxiumum value at regular intervals. For ex, max(F4:F28) in G4 cell, max(F29:F99) in H4 cell, max(F100:F124) in G5, max(F125:F195) in H5 cell & continue. Basically I have to find the maximum value for 25 rows in G column & then next 71 rows in H column, then next 25 rows in G column & next 71 rows in H column. Kindly help.

 

[Peter_SSs]

Welcome to the MrExcel board!

Check the ranges in my formula as you said 3000 rows of data, but that is not a multiple of 96 which is your stated grouping.
Each formula copied down
G4: =MAX(INDEX(F$4:F$3003,ROWS(G$4:G4)*96-95):INDEX(F$4:F$3003,ROWS(G$4:G4)*96-71))
H4: =MAX(INDEX(F$4:F$3003,ROWS(G$4:G4)*96-70):INDEX(F$4:F$3003,ROWS(G$4:G4)*96))

 

[markmzz]

Hi!

Another way:

In G4 and copy down and to the right

=MAX(OFFSET($F$4,96*(ROWS(G$4:G4)-1)+25*(COLUMNS($G4:G4)-1),0,25+46*(COLUMNS($G4:G4)-1),1))

Markmzz

 

[Peter_SSs]

Comments:
- Since OFFSET is a volatile function, I generally try to avoid it where there is a feasible alternative. I do accept that with the data size stated in this problem, there is unlikely to be a performance issue.
- If OFFSET was to be used, I would still opt for separate formulas in each column to keep those formulas simpler & reduce calculation overheads. (It isn't much harder to copy/paste 2 formulas than 1)

G4: =MAX(OFFSET(F$4,(ROWS(G$4:G4)-1)*96,0,25,1))
H4: =MAX(OFFSET(F$4,(ROWS(G$4:G4)-1)*96+25,0,71,1))

 

[markmzz]

Hi!

Another way (without OFFSET function and with one formula):

In G4 and copy down and to the right

=MAX(INDEX($F:$F,4+25*(COLUMNS($G4:G4)-1)+96*(ROWS(G$4:G4)-1)):
INDEX($F:$F,28+71*(COLUMNS($G4:G4)-1)+96*(ROWS(G$4:G4)-1)))

Markmzz

 

[drshkd]

I thank you both Peter_SSs & markmzz for your prompt reply. I have checked both formulas & they are working like charm. Yeah, it is about 3000 cells not in particular. Who said that help is not available when you need it most. You just have to ask for it, some of World's greatest minds are there to help you out of any situation. I thank Mr.Excel team for this website.

 

[Peter_SSs]

I thank you both Peter_SSs & markmzz for your prompt reply. I have checked both formulas & they are working like charm. Yeah, it is about 3000 cells not in particular. Who said that help is not available when you need it most. You just have to ask for it, some of World's greatest minds are there to help you out of any situation. I thank Mr.Excel team for this website.

You are very welcome. Glad we were able to help.

 

[markmzz]

I thank you both Peter_SSs & markmzz for your prompt reply. I have checked both formulas & they are working like charm. Yeah, it is about 3000 cells not in particular. Who said that help is not available when you need it most. You just have to ask for it, some of World's greatest minds are there to help you out of any situation. I thank Mr.Excel team for this website.

You are welcome and I'm happy that your problem was solved.

Markmzz