find the max among a range of cells and repeat the search 365 times

[AmandaSS]

Hallo,

I have 8760 rows and one column with one value for each row.

row1	10
row2	23
row3	66
...	...
row8760	39

<tbody>
</tbody>

I need to check every 24 row what is the max value.
Can someone help?

Thanks
Amanda

 

[azumi]

Assumed your data in ranges A1:A48

Put in B1 or C1:
=MAX(OFFSET($A$1:$A$48,(ROWS(A$1:A1)-1)*24,,24))

Azumi

 

[AmandaSS]

thanks Azumi!

So this means that if my range is A1:A8760
i have to put
=MAX(OFFSET($A$1:$A$8760,(ROWS(A$1:A1)-1)*24,,8760))

?

 

[AmandaSS]

oh no, it is this the right formula for my case
=MAX(OFFSET($E$2:$E$8761,(ROWS(A$2:A8761)-1)*24,,24))

but it seems like excel only goes till A365

=MAX(OFFSET($E$2:$E$8761,(ROWS(A$2:A365)-1)*24,,24))

is there something that i can do to overcome this problem (and avoid copy and past each 365 rows up to 8760)?

 

[barry houdini]

Use this formula in G2 copied down

=MAX(OFFSET($E$2,(ROWS($G$2:G2)-1)*24,,24))

This assumes your data starts at E2 (in red - adjust as required) and that the first OFFSET formula is in G2 (in green - adjust as required)

 

[AmandaSS]

Hallo,
I would like to specify better the issue as below.

there are 8760 rows and one column with one value for each row.

row1	10
row2	23
row3	4
...
row 24	50
....	....
row8760	39

<tbody>
</tbody>

I need to check every 24 rows what is the max value (in 8760 rows I have to find 365 max values=8760/24). I.e. the max value in the first 24 rows, the max value in the second 24 rows, the max value in the 3rd 24 rows, and so on.
Can someone help?

Thanks
Amanda

 

[barry houdini]

I posted another version of the suggested OFFSET formula - all you need to do is adjust that formula depending on where your data starts and where you put the first formula, then you can drag it down for the required results

 

[AmandaSS]

Thanks Barry, I had seen your post only later.

I have done what you suggest, so: the data start in column E2 (down to E8761) and I want to get the result in cell AT2 Down to AT8761, so I do the following:

=MAX(OFFSET($E$2,(ROWS($AT$2:AT2)-1)*24,,24))

I then drag the formula down with adouble click but I get the result only up to cell 366 (..) and the rest of the cell till the 8761st are 0:00.
This is a mistery for me!

 

[barry houdini]

But you are averaging every 24 rows so AT2 will be the average of E2:E25, AT3 will be the average of E26:E49 etc.....so in AT366 that will be the last 24 cells E8738:E8761, after that there is no data, so you get zeroes

....or do you want to average E2:E25, E3:E26, E4:E27 etc.?

 

[AmandaSS]

ok, now I understand everything
What happens is exactly what I wanted and that's all..
I dont want/need to average E2:E25, E3:E26, E4:E27 etc.
Thanks so much Barry!