I have a huge data to calculate its average 59 values in one cell then 59 values next to previous

Hello there muhammadwaseem!

I hope the day finds you well. It Looks like you still need an answer so let me try and help you. First, we need a base understanding of some parameters and set

standards for our communication.
I've noticed a couple of points so we can make a good start 😌

1. We need the workbook to be in the same edition, e.g. latest. I'll provide you with an example just in case.

2. We need to have the same formatted time. I suggest having a look into it by checking this URL .

a good read and there is a example workbook.

3. Looks like you have alot of differently formatted cells, clean those up to having the same format. Sometimes this causes errors in your desired result.

EDIT: I didn't see the last posts but there is still good info to be had here so i'll leave it there. Good luck in your journey 

muhammadwaseem 

In N4:

=AVERAGE(IF(HOUR($B$4:$B$1443)=J4-1, $E$4:$E$1443))

Fill down

muhammadwaseem 

It seems you want to average the values for each hour. But why do you average 59 values instead of 60?

The easiest way for averaging values per hour is a pivot table.

muhammadwaseem 

In N4

=AVERAGE( INDEX($E$4:$E$100000, (J4-1)*59+1):INDEX($E$4:$E$100000, J4*59) )

and drag it down