+ Reply to Thread
Results 1 to 8 of 8

Decimals in calculations - not just rounding off

  1. #1
    Registered User
    Join Date
    03-10-2019
    Location
    Ottawa, Caanada
    MS-Off Ver
    2013
    Posts
    2

    Decimals in calculations - not just rounding off

    Hi . . . first, thanks for this forum and thanks for anyone who can help me solve this problem.

    1. I am using MS Excel 2013.

    2. I have a calculated cell. Example a formula that looks like: (C12/C6). Using example numbers: (= 0.185/76.2).

    3. This gives the answer: .002427822

    4. I know that I can show as many decimal places as I want, but when I reduce the number of decimals, I end up with .00243. But what I want to do is the following:

    * I do not want this number to be rounded off to .00243.

    * I want the number in this cell to have the value of .00242 for use in future calculations. In other words, I do not want the "7" to have an impact on the "2" by rounding it up to "3".

    I have TRUNC but it generates an error for me when I use it before a formula. I have tried ROUNDDOWN but that does not seem to work. I may be using it improperly.

    Any suggestions will be greatly appreciated.

    Best wishes, KC

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Decimals in calculations - not just rounding off

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Decimals in calculations - not just rounding off

    Without seeing exactly how you are using the TRUNC() and ROUNDDOWN() functions, it is difficult to know what you are doing wrong. I would expect either function to work for you. Something like =TRUNC(formula,5) or =ROUNDDOWN(formula,5) should return the desired value to 5 digits past the decimal point. What are you doing differently?

    If needed, you might review the help files:
    https://support.office.com/en-us/art...4-aa5ceb292721
    https://support.office.com/en-us/art...f-17e6d7968f53
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Decimals in calculations - not just rounding off

    Hello kconnors100. Welcome to the forum.

    How exactly are you using the TRUNC function.

    This formula

    =TRUNC(0.185/76.2,5)

    returns 0.00242 at my end.

    With the formula

    =0.185/76.2

    in cell A1 and this =TRUNC(A1,5) I also get 0.00242

    What am I missing?
    Dave

  5. #5
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Decimals in calculations - not just rounding off

    Quote Originally Posted by kconnors100 View Post
    ... (C12/C6). Using example numbers: (= 0.185/76.2) ...
    ... .002427822 ... tried ROUNDDOWN but that does not seem to work ...
    ... I want the number in this cell to have the value of .00242 ...
    Does it really not work for you ? Then try:

    =VALUE(LEFT(TEXT(C12/C6,"0,000000"),7))

  6. #6
    Registered User
    Join Date
    03-10-2019
    Location
    Ottawa, Caanada
    MS-Off Ver
    2013
    Posts
    2

    Re: Decimals in calculations - not just rounding off

    Hello . . .not only am I having issues with Excel, it seems as if my reply got sent to who knows where . . .

    Another try at replying . . .Wow! what a great forum and thanks so much for the suggestions. Now, for follow-up:

    1. the Rounddown works . . . my fault as I did not use the drop down list but typed in Rounddown and i used only one "d" . . . I know, every time a key is hit it has the potential for an error . . .

    2. the Trunc also works . . .the actual cell has a combination of math operations on cells each with other formulas . . . again, there was an error in one of the referenced cells . . .I think I became a bit frustrated with the process and just assumed the error was with the Trunc . . .

    3. as for the =VALUE(LEFT(TEXT(C12/C6,"0,000000"),7)) ---- I have never used this but I am intrigued. I am going to try this out and, if nothing else, it encourages me to go deeper into Excel.

    In any case, I wanted to thank each of you for taking the time to respond. I sincerely appreciate the value of a forum like this one, but also how it is the people who take the time to respond that makes it such a great resource.

    Best wishes, Karen

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Decimals in calculations - not just rounding off

    You are welcome. Glad to help. Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Decimals in calculations - not just rounding off

    You're welcome.

+ 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. Rounding 2 decimals to specific 2 decimals
    By danyel191 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2016, 01:41 PM
  2. Rounding Decimals
    By MR-77 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-21-2015, 03:18 AM
  3. Rounding decimals below and above 0,50
    By Hitch75 in forum Excel General
    Replies: 4
    Last Post: 09-23-2010, 08:15 AM
  4. Rounding decimals
    By Russell719 in forum Excel General
    Replies: 2
    Last Post: 08-24-2007, 12:09 PM
  5. [SOLVED] Rounding Up Decimals
    By Lilbit in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-04-2006, 04:45 PM
  6. Rounding Decimals
    By Jim May in forum Excel General
    Replies: 8
    Last Post: 11-07-2005, 02:10 PM
  7. [SOLVED] Re: Rounding with no Decimals...
    By Karthik in forum Excel General
    Replies: 1
    Last Post: 07-29-2005, 02:06 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