Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average temperature (2003)

    Hi. I have a column Date listing hourly sequences from '30/10/08 00:00' to '05/11/08 00:00' and a column Temp of temperatures. I want to find the average temperature during 30th October.
    If I use DAVERAGE, how do I enter the date criteria so it is recognised correctly? Alternatively, if I use an array formula, how do I enter the date/time format correctly within the formula? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average temperature (2003)

    Let's say the dates/times are in A2:A146 and the temperatures in B2:B146.
    Enter the date for which you want to average the temperatures in a cell, say F2.
    Use the following array formula to calculate the average (confirm with Ctrl+Shift+Enter):

    =AVERAGE(IF(INT(A2:A146)=F2,B2:B146))

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average temperature (2003)

    That's great (and simpler than my attempt). How can I put the date directly in the formula - without reference to F2? Thanks, Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average temperature (2003)

    You could replace F2 with DATE(2008,10,30) or with DATEVALUE("30-Oct-2008")

    Note: you can't simply enter 30/10/2008 in the formula because that would be interpreted as "divide 30 by 10 and divide the result by 2008" (or similarly with 10/30/2008 for those in the USA).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •