+ Reply to Thread
Results 1 to 9 of 9

Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

  1. #1
    Registered User
    Join Date
    05-22-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    Hello Everyone,
    Would appreciate your assistance on the following problem I have.
    I may be going about this the wrong way but hopefully somebody can point me in the right direction?

    So here is my problem, I have made a table in excel (2010) for calculating divers umbilical lengths (see attached)


    Bailout Pressure - (Abs. Bar + Reg Bar) x BOB Litres Capacity = 1281 Litres

    Breathing Rate (45lts/min) x Abs. Bar = 90 Litres per minute
    45*2 = 90 Litres per minute

    So using the figures above dividing capacity by requirement gives me a dive time
    1281/90 = 14.23 minutes

    14.23 decimal minutes (this is where i'm stuck) -
    14.23 decimal minutes equate to 14 minutes 13.8 seconds
    The Diver has 14 mins and 14 seconds available at 10 metres

    Now the final calculation:
    The divers needs to be able to travel at 10 metres every 1 minute
    therefore 14.14 minutes x 10 metres = 141 metres

    So what i would like is to show is this final 141 metres figure in my cell
    I've managed to get to the 14.23 decimal minutes by doing this:


    =SUM((C4)-(B4+L2))*(D3)/(B4*45)

    =SUM((200)-(2+15))*(7)/(2*45)
    =SUM(1281/90)
    =14.23 minutes

    How can i change 14.23 decimal minutes into actual minutes and seconds (14 mins 14 secs) then times by 10 to equal 141 metres

    Preferably all in one formula? If possible....
    or does this need other cells to make the calculations

    Thanks in advance for any advice
    Christian
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    I don't think you need that conversion. If you get a result of 14.23 decimal minutes and he can travel at 10 metres a minute then the number of metres is just 14.23*10 = 142.3
    Audere est facere

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    preferably all in one cell
    So you want the single cell to show both 14 minutes 14 seconds and 141 metres?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    05-22-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    daddylonglegs - Using the decimal minutes when finally transferring back to metres gives a incorrect length due to time and distance being at a different exponentiation
    ChemistB - I would like the cell just to show 141 metres however do all the calculation if possible?

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    DDL is correct, Excel just displays the results differently. 14.23 is actually 14 minutes and 13.8 seconds which is why it comes to 142 metres and not 141. Do you want to round it down to whole seconds?

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    Quote Originally Posted by sparkyliveson View Post
    daddylonglegs - Using the decimal minutes when finally transferring back to metres gives a incorrect length due to time and distance being at a different exponentiation
    Sorry, I may be missing something but if the result was 14 minutes exactly then wouldn't you want the final answer to be 140, and if the minutes came to exactly 15 you'd want the answer to be 150, so on that basis shouldn't 14.5 decimal minutes give you an answer half way between those, i.e. 145?

    The way you are going you will convert 14.5 to 14.30 and then get a result of 143

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    I have been trying to get the 14 minutes, 13.8 seconds to round to 14:13 but haven't been able to accomplish it.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    Quote Originally Posted by ChemistB View Post
    I have been trying to get the 14 minutes, 13.8 seconds to round to 14:13 but haven't been able to accomplish it.
    but why would you want to?

    That would be a case of double rounding giving the wrong result - the calculation 1281/90 actually gives you 14.233333... which is an exact number of seconds - 854 or 14:14 exactly

  9. #9
    Registered User
    Join Date
    05-22-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Converting a sum from decimal minutes to minutes & seconds then multiplying by 10

    Thanks for your quick responses - i agree

    =ROUNDDOWN(SUM((C4)-(B4+$L$2))*($D$3)/(B4*45),1)*10
    =142

    I think this is how it should be shown

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Converting decimal to Month:Day:Hours:Minutes:Seconds
    By KellyK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-20-2013, 03:34 AM
  2. Replies: 13
    Last Post: 05-03-2013, 08:42 PM
  3. RE: Converting decimal minutes to hours, minutes and seconds
    By mills49 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-14-2013, 09:14 AM
  4. Converting hours:minutes:seconds to just minutes
    By Dan Vagle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2006, 06:25 PM
  5. [SOLVED] Converting Minutes:Seconds to Minutes
    By NickC in forum Excel General
    Replies: 9
    Last Post: 06-05-2005, 09:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1