1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to repeat a formula every X rows

Discussion in 'Ask an Excel Question' started by ad.dias, Jun 19, 2017.

  1. ad.dias

    ad.dias New Member

    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

    Attached Files:

  2. Mike86

    Mike86 Active Member

    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.

    Attached Files:

    Last edited: Jun 19, 2017
    Thomas Kuriakose likes this.
  3. ad.dias

    ad.dias New Member

    That's it! Thanks a bunch !
  4. SirJB7

    SirJB7 Excel Rōnin

    Hi, Mike86!

    Am I wrong or the average for Jan 1st are:

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

    Last edited: Jun 19, 2017
  5. Mike86

    Mike86 Active Member

    @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.

    Attached Files:

    Thomas Kuriakose likes this.
  6. r2c2

    r2c2 Active Member


    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
    Thomas Kuriakose likes this.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    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.


Share This Page