Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging Problem (97)

    I would like this spreadsheet to add the hours in cell A3 and the minutes in Cell B3 then multilpy by 16.25 and round off to the nearest whole number.

    Sometimes the hours cell (B3) will be blank. Please see attached.

    =SUM(A3*16.25)+(B3*0.2708333)

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

    Re: Averaging Problem (97)

    Does the following do what you want?

    <code>=ROUND((A3+B3/60)*16.25,0)</code>

  3. #3
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Problem (97)

    Excellent. Thank You

  4. #4
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Problem (97)

    Can you please show me how to take cell C7 in the attached example and have it show time in hours and minutes?

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging Problem (97)

    Select cell C7 and choose format cells. Select the number tab and choose custom. Replace General with this : [hh]:mm . The square brackets allow you to calculate more that 24 hour periods!
    Regards,
    Rudi

  6. #6
    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: Averaging Problem (97)

    Assuming that the current value is in hours, convert it to days by dividing by 24 (excel formats all work in units of days), then format as:
    [h]:mm
    So the formula in the cell should be:
    <pre>=B7/16.25/24</pre>


    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging Problem (97)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    I'd agree. I noticed that I'd run the mile in 1H28min if I didn't divide by 24! It wouldn't be a run...it would be a crawl then!!
    Regards,
    Rudi

Posting Permissions

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