Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    Gainesville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge w/ excel data (Office 2k/2003)

    We're attempting to create a mail merged letter to send grade results to students based on an Excel file that has a bunch of test results and a column that has a "Total Percentage" that is calculated based on all these tests. When this value is inserted into a Word mail merge document and the letters are previewed, we get extended decimal results like 68.99999994 and 70.0000000003. Initially we couldn't figure out why Word wasn't honoring the 1-decimal-place setting that Excel has on that column, so we copied and pasted values into another column. Same results. Then I manually typed the 1-decimal-place grades into another column, same results. What the heck?

    So I created a simple Excel test file with two columns (Name, Grade) and created a mail merge in Word that inserts these values into a Word letter (Hello <name>, Your final grade for the year was <grade>). For whatever reason, the grade sometimes still comes out with unexplained decimal numbers. E.g., the value in Excel is 89.4, yet the merged letter comes out with 89.4000000000003.

    In the original data file these values were calculated, but I've since been testing with calculated values, copy and paste "as values", values typed right in and in all cases, I get these extended decimal values. It seems like there's some kind of weird bug happening here. Is there any way to just format the inserted value so that it only shows one decimal place in the merged letter? I'll worry about the bug later.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    In the merge document, press Alt+F9 to see the field codes. Each merge field will look like this:

    { MERGEFIELD Grade }

    or

    { MERGEFIELD Grade * MERGEFORMAT }

    You can specify a display format by inserting # followed by the format between quotes after the field name. For example to get one decimal place:

    { MERGEFIELD Grade # "0.0" }

    or

    { MERGEFIELD Grade # "0.0" * MERGEFORMAT }

    When you're done, press Alt+F9 again to hide the field codes. When you merge to a new document or to the printer, the grades should be formatted correctly.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    I've seen the same kind of weirdness with whole numbers in Access. User enters 4, and who knows what is actually stored in the database?

    Anyway... I think I read in another thread that a merge can take account of the formatting you applied in Excel if you choose DDE as your data access method. However, I have never done it myself, so please don't quote me on it. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    A quick search turns up (among others) <post#=271064>post 271064</post#>.

    You must have ticked 'Confirm Conversion at Open' in the General tab of Tools | Options to get prompted for the way to connect to Excel.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    Thanks, Hans. I'll try not to be so lazy the next time.

  6. #6
    New Lounger
    Join Date
    May 2001
    Location
    Gainesville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    Of course, this worked. Dunno if there's any way to explain why this particular problem exists, but at least this is a very viable workaround, if not extremely unintuitive. Thanks.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    Hi,

    The reason this happens is that Excel doesn't always round values off the way one might expect. For example, =ROUND(A1,2) might appear to give a value that stops at the second decimal place, but if you copy the cell and paste its contents as a value, you might get a value that is out by +/- 1 or 2 at the 15th digit. This also explains why you might sometimes get a -0 result when you subtract one rounded value from another that was calculated differently.

    Using the numeric picture switch in Word cleans up the merged result.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Lounger
    Join Date
    May 2001
    Location
    Gainesville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    What this doesn't explain, however, is that when I type '89.2' into a cell in Excel and merge that into a Word document, I get 89.200000000000003.

    I can fully understand the rounding stuff and the other values that are calculated, but the above drives me crazy.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    Excel, like most computer programs, stores numbers in binary format (0s and 1s). Many decimal numbers cannot be represented exactly in binary format, just like a number such as 1/3 cannot be represented exactly in decimal notation. The number 0.2 = 1/5 is 0.0011001100110011... in binary notation. There is a very small rounding error in such numbers. Excel itself is programmed to hide such rounding errors from the user in most circumstances, but Word doesn't know about that.

  10. #10
    New Lounger
    Join Date
    May 2001
    Location
    Gainesville, Florida, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge w/ excel data (Office 2k/2003)

    Ahhh, ok. Thanks Hans. Good stuff as always.

Posting Permissions

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