Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using the Average function (2002/SP2)

    We are running time tests and have row of numbers formated as time (cells are formatted:: HH:MM) and need an average of these times.
    I tried the =AVERAGE(cell range) and received an #div/0 error.
    We ended up having to put and =((TIMEVALUE(E14)+TIMEVALUE(F14)+etc... formula.
    Is there some reason why the AVERAGE function did not work? Does it have to do with the time format?

    We have the averages we need, but are curious as to why the average function did not work.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using the Average function (2002/SP2)

    First, you may want to change your cell format to [HH]:MM, with the brackets around the HH. HH:MM won't display times over 24 hours.

    I tried here, and average worked fine on time values. Could you upload an example of what failed?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using the Average function (2002/SP2)

    If any of the cells in the range have a #div/0 error, the average will result in that error.

    If none of the cells are values (that is they are all blank or all text) then you will get this error.

    Perhaps the times are not actually times but just look like times. This is supported by the fact that when you calc'd them manually you did not add them directly but used the timevalue function. You can convert all of them to values with the timevalue function and then average should work.

    Steve

Posting Permissions

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