+ Reply to Thread
Results 1 to 6 of 6

Calculating decimal places in a non decimal format (ie sixes not tens)

Hybrid View

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Calculating decimal places in a non decimal format (ie sixes not tens)

    Hello all,
    I am trying to add numbers where the decimal places equal a whole number when they reach 6.
    For example:
    Where 1.3 + 1.3 = 3
    Or 1.3 + 2 = 3.3
    Or 2 + 0.2 = 2.2
    Or 21.5 + 4.2 = 26.1

    Is there a way to make this work in excel. Any help greatly appreciated. Thanks.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Calculating decimal places in a non decimal format (ie sixes not tens)

    With your numbers in A1 and B1, put this formula in C1:

    =INT(A1)+INT(B1)+INT((MOD(A1,1)*10+MOD(B1,1)*10)/6)+MOD((MOD(A1,1)*10+MOD(B1,1)*10),6)/10

    then copy down as required.

    Hope this helps.

    Pete

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Calculating decimal places in a non decimal format (ie sixes not tens)

    with numbers in a1 and a2
    =DOLLARFR(DOLLARDE(A1,6)+DOLLARDE(A2,6),6)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: Calculating decimal places in a non decimal format (ie sixes not tens)

    Thank you to both and each solution works, if however I wanted to add numbers over a range of cells, say A1:A7 how would I express it to get the result? Thank you

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Calculating decimal places in a non decimal format (ie sixes not tens)

    with numbers in col A starting A1 in b1 put =DOLLARDE(A1,6) fill down
    then you can use =DOLLARFR(SUM(B:B),6) to get the total

  6. #6
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: Calculating decimal places in a non decimal format (ie sixes not tens)

    Thank you very much for your assistance

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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