How to repeat a formula every X rows

[ad.dias]

Greetings everyone,
I have an Excel file with 3 columns and 8738 rows.
These are the hourly values of the temperature and relative humidity on a wall's surface.
I need to convert the time format from hour to day, and calculate the daily average values of the temperature and relative humidity, so i'll end up with 365 rows.
Does anyone know how to do this?
The file is uplodad in this post.

Kind regards
André

 

[Mike86]

Brute force, but here's a way to do it. You don't always have the same number of data points per day, so using the average if and adding a Date column seemed a simple way to do it.

 

[ad.dias]

That's it! Thanks a bunch !

 

[SirJB7]

Hi, Mike86!

Am I wrong or the average for Jan 1st are:
77,56398454
-0,114208187

And you can add 1 to the date cells from the 2nd to the last one.

Regards!

 

[Mike86]

@ad.dias @SirJB7 Damn. OK, round did a funny thing around Noon on each day. Was trying to get away from doing the Date statement. Shows what corner cutting gets you. Updated sheet attached.

 

[r2c2]

@ad.dias

You can also use a pivot table to quickly get the result. Use below instructions

1. Add a title to the first column, like time
2. Select all 3 columns of data and insert a pivot
3. Drop time to row labels area
4. Right click on time and choose group (steps 4 and 5 are not necessary in Excel 2016 as it auto groups date time values in pivots)
5. Set grouping by day, month and year
6. Remove month and year from the pivot fields as we just need daily averages
7. Drop temperature and humidity to values area
8. Summarize them by average
9. Done

 

[NARAYANK991]

@ad.dias @SirJB7 Damn. OK, round did a funny thing around Noon on each day. Was trying to get away from doing the Date statement. Shows what corner cutting gets you. Updated sheet attached.

Hi ,

It has nothing to do with corner cutting ; it is the data which is at fault.

If you see all the values at midnight , they are not real integers , which they should have been.

01-01-2024 is a correct integer , and is equal to 45292.

02-01-2024 00:00:00 is not a correct integer 45293 , but is actually 45292.9999999999

As we go down , this problem becomes more and more acute , till the last entry in the table , which is shown as 30-12-2024 00:00:00 is actually 45655.9999999788 , instead of 45656.

It is this problem with the data which is causing your formula to fail. If the data were correct , your original formula would work correctly.

Narayan