Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with Formulae

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Hello,

    I need my fellow WOPR Friend's help again. As per the attached XL file, I want to fill out Availability Values (e.g. for a Server) for a 28 day month i.e. 28x24= 672 hours.
    Thus for example 1% of 672 hours = 6.72 hours and based upon this, the:
    EQUIVALENT DOWNTIME = 665h 16m 47s (Time Notation) and 665.28 h (Decimal Notation).
    The CORRESPONDING UPTIME = 6h 43m 11s (Time Notation) and 6.72 h (Decimal Notation).
    The website http://www.springfrog.com/converter/decimal-time.htm has a very useful decimal/time converter. But I want to get Excel to do the same thing. Please advise me on how I can do this?! Thanks again WOPRs

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

    Re: Need help with Formulae

    See attached.

  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: Need help with Formulae

    Excel can format it directly.
    Excel stores dates and times as days. So take
    EQUIVALENT DOWNTIME = 28 (days) *99%=27.72 days
    CORRESPONDING UPTIME = 28(days) * 1% = 0.28 days

    Now just format them to display the desired "result"
    Format -cells - custom:
    <pre>[h]"h" m"m" s"s" </pre>



    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with Formulae

    Thanks again guys.
    So just for me to be lucid on this

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with Formulae

    Dear Steve,

    I want to try your solution but I may need a bit of help here.
    Please see the attached file

  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: Need help with Formulae

    Availability for the month = (No. of Days in that Month) * (Availability Percentage Level).
    The hours should not enter into it. You will calculate the time in days. Excel "builtin" routine will do the converting to whatever format you desire:
    [h]"h" m"m" s"s" 10h 15m 3s
    or
    [h]:mm:ss.00 876:54:32.11

    or whatever.
    Steve

  7. #7
    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: Need help with Formulae

    There is no file attached.

    Make sure you
    <hr><font color=red>* DO This LAST - AFTER Previewing and AFTER Spell-Checking - OR you will lose your attachment! * </font color=red><hr>

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with Formulae

    Here's the file sorry.

    Bob.

  9. #9
    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: Need help with Formulae

    If you want the 3 (hrs/mins/secs) separate just use Hans' formulae.

    If you want 1 value and have it displayed via a custom format use the formula:
    <pre>=F5/24</pre>

    Which takes your hours and converts to days
    Then format as I described earlier

    You could also just use the percentage directly (as mentioned earlier)
    <pre>=B5*28</pre>


    Note: I assume you got the error since you copied the F5 formula to C5 and the relative reference to a column 4 columns to right is not valid, since you are at the 2nd column, giving the #err in the formula.

    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
  •