+ Reply to Thread
Results 1 to 7 of 7

Excel 2011 for mac rounding numbers

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Kent
    MS-Off Ver
    excel 2011 for mac
    Posts
    3

    Excel 2011 for mac rounding numbers

    My problem is that the sum results are rounded up please see example below
    (I have used the exact figures I am having a problem with as an example)

    cell f33, Invoice total 2,134.33

    cell f35, less 2%=42.68 (but my excel keeps rounding it up to 42.69) and obviously this then alters the rest of the invoice figures.

    I have read and tried clicking on "set precision as display" and also tried formating the cell to all different things including moving the decimal point and have had no luck what so ever and its driving me crazy ! I have resorted in doing the calculation on a calculator and then added the amount as text.

    my contractors and accountant will not except the rounded up amounts on my invoices ?

    if anyone could help please let me know cause I'm loosing sleep over it now lol

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel 2011 for mac rounding numbers

    Without specific instructions to the contrary, most excel formulas will do standard rounding of fractions based on the display format.

    For me, I never use formulas that multiply percentages without also building into the formula hardcoded rounding, so insure the fractional pennies aren't added up into visual errors over multiple cells. Even if you want standard rounding, do the rounding IN the equation to make sure the extended decimal values are gone and can't cause grief.


    So some of the options for you are are ROUND(), MROUND(), FLOOR(), CEILING()...

    For instance, to strip off all partial pennies and always round down:

    =FLOOR(F33*2%, 0.01)

    This will absolutely result in 42.68 and remain so in all further calculations against F35.
    Last edited by JBeaucaire; 11-10-2014 at 01:45 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Excel 2011 for mac rounding numbers

    if you never want to round a number up - you could always round down

    =ROUNDDOWN(A1*0.02,2)
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    11-10-2014
    Location
    Kent
    MS-Off Ver
    excel 2011 for mac
    Posts
    3

    Re: Excel 2011 for mac rounding numbers

    Hi guys thankyou for a quick response and things to try but this would only work with numbers that require rounding down I need excel just to display the exact figure with 2 decimal points. for example if i use the figure 1,000.87 and added the fomula =FLOOR(F33*2%, 0.01) or ROUNDDOWN it would result in 20.01 when in this case it needs to be 20.02 ? its very frustrating as i dont want to be typing in different formula's each time when i invoice ? I get a statement from my clients to invoice to and I have to have the exact figure they have (i believe they use sage) but as i get my wife to do most of the invoices and my template is in excel i thought it would be easy to formulate it to make it easy instead of just typing it all in using text?

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Excel 2011 for mac rounding numbers

    so why are you rounding up
    1,000.87 - 2% = 20.0174
    and NOT
    42.7866 ?

  6. #6
    Registered User
    Join Date
    11-10-2014
    Location
    Kent
    MS-Off Ver
    excel 2011 for mac
    Posts
    3

    Re: Excel 2011 for mac rounding numbers

    etaf you have made me look into past invoices from this contractor and i am convinced its their figures that are not adding up ? I will get back to you soon on this guys thank you so far thou...

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Excel 2011 for mac rounding numbers

    your 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. 2011 Excel for Mac: Numbers will not display in cell
    By paulgarett in forum Excel General
    Replies: 1
    Last Post: 09-25-2013, 06:19 PM
  2. Replies: 8
    Last Post: 06-07-2012, 09:14 AM
  3. Excel Rounding off numbers
    By vikas.bhandari in forum Excel General
    Replies: 3
    Last Post: 08-10-2010, 03:49 PM
  4. [SOLVED] rounding off numbers with excel
    By Tom C in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-04-2005, 07:10 PM
  5. [SOLVED] Rounding off numbers in Excel 2003
    By Damon Longworth in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 06:05 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