+ Reply to Thread
Results 1 to 8 of 8

Problem getting cents to round, Merged document

  1. #1
    Registered User
    Join Date
    04-13-2006
    Location
    Boston, MA
    Posts
    73

    Problem getting cents to round, Merged document

    Once again I am defeated by Excel. DRAT!!! AND CURSES!!

    Here's the seemingly simple problem: take a bunch o' numbers in a column, representing dollars, which have 4 decimal places for cents (in most cases - a few number are whole dollars). Get them to accurately round to the nearest cent, and then have it accurately display as whole (rounded) cents.

    Of course I tried ROUND, to no avail, got a number that displayed like it was correctly rounded, but when I merged it it displayed without $ oand with 4 digits after the decimal. This is a pretty short list, 26 items per column, and I could easily key in the correct dollars & cents, but I know there has to be a way to make this go faster with a function. Can you point me in the right direction? Any help is gratefully accepted. Thanks.

    Mark aka
    LTUser54

    Boston, MA

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    not sure what you mean by this:

    but when I merged it it displayed without $ oand with 4 digits after the decimal
    can you show an example
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-13-2006
    Location
    Boston, MA
    Posts
    73

    results w/ function & Paste Special: Value

    Thanks for the response. Here's an example:

    (as displayed in cell)
    Cell D2 # <sp> =Round (D2,7) <sp> Paste Special: Value
    $78,109.83 <sp> $78,109.83 <sp>78109.8288

    So, what I'm looking for is a result that shows as currency, accurately rounded to nearest cent. Can MROUND or some other function do this?

    Thanks.

    LTUser54
    Boston, MA
    Last edited by LTUser54; 03-11-2008 at 02:27 PM. Reason: display inaccurate

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Why is your round formula rounding to 7 decimal places...

    i.e. =Round (D2,7)

    why not =Round(D2,2).... then when you copy|paste special... you will get the 2 decimal places only.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    Looks like the problem is in your round function. You want to round to 2 decimal places, not 7. Then you should be able to copy>paste special values.

    ChemistB

  6. #6
    Registered User
    Join Date
    04-13-2006
    Location
    Boston, MA
    Posts
    73

    =ROUND(cell),2 Works

    Thanks for tha solution, it's effective.

    Yet, I still have a problem.

    When I use Sheet1 for the data to be merged into a Word document, I do not preserve the formatting in Excel, like dollar sign and comma separators. Any idea how to do that? Is it easier to make it an Excel/Excel merge rather than a Word/Excel merge?

    any replies or links are welcome. thanks.

    LTUser54
    Boston, MA

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    Yes, you will lose your formatting if you move the Excel values into Excel. Another option is to embed those cells in your Word document Paste as an Excel object or paste as a link.

    ChemistB

  8. #8
    Registered User
    Join Date
    04-13-2006
    Location
    Boston, MA
    Posts
    73

    really!

    no kidding, embed as Excel object or paste in the link, who woulda thot? I'm SO GLAD Microsoft makes this sooooo easy!!! (pardon my cynicism - you would think thay actually give a crap, unfortunately my experience says otherwise).

    Thanks for your comments. This is really helpful.

+ 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