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

 

[XLfriend777]

Hi AmandaSS - Sorry I didn't see the previous posts before I developed this VBA macro. In column C it numbers 1-365 and in column D it lists the corresponding Max in the group of 24.

Hope you find it helpful.

Sub AmandaSS_Max_by_24rows()
Dim i, x As Integer
Dim MaxVal As Long
i = 1
x = 1
While i < 8761
    Set WorkRange = Range(Cells(i, 1), Cells(i + 23, 1))
    MaxVal = Application.WorksheetFunction.Max(WorkRange)
    Cells(x, 4).value = MaxVal
    Cells(x, 3).value = x
    i = i + 24
    x = x + 1
    Wend
End Sub

 

[XLfriend777]

Hi AmandaSS - Try the code:

Sub AmandaSS_Max_by_24rows()
Dim i, x As Integer
Dim MaxVal As Long
i = 1
x = 1
While i < 8761
    Set WorkRange = Range(Cells(i, 1), Cells(i + 23, 1))
    MaxVal = Application.WorksheetFunction.Max(WorkRange)
    Cells(x, 4).value = MaxVal
    Cells(x, 3).value = x
    i = i + 24
    x = x + 1
    Wend
End Sub

 

[AmandaSS]

Thanks Goesr, that looks fancy


how would the formula change if I wanted to calculate the mean of each 24 data?
can i still use a formula or do I have to go with VBA?

thanks,
Amanda