Excel data with ROUND function retains decimals in Word Mail Merge

Question

BACKGROUND: I work at a school where we provide learners with a detailed list of how their personal marks makes up their term percentage in each subject. For this, each teacher uses their Excel spreadsheet with the marks and the Mail Merge function in Word. Lots of the marks need to be processed e.g. a 60 mark test will end up contributing only 10 marks towards the total - thus we use a lot of formulas.

When this data is used in Word's Mail Merge, it obviously shows these processed marks with loads of decimal places. To prevent that happening, the teachers use the =ROUND(number, num_digits) function in Excel. This works great for all the marks we round down to 0 decimal places - the merge fields in Word shows the whole number without decimals.

However, when we round to 2 decimal places, the merge field in Word shows the 9+ decimals of the original number (as if the ROUND function wasn't used at all). This even happens when one copies the column in Excel and pastes it as values, then use the values as the merge field! (e.g. I see 7.26 in the cell in Excel, but the merge field displays 7.26000000009).

To solve it, I have been formatting merge fields with the #0.0 switch in Word (which works), but that is definitely not an option for any other teachers - doing mail merge by themselves already took years to accomplish!

I want to find out why the Excel Round 0 decimals works, but not the 2 decimals?

Answer 1

The fact that you are seeing  7.26 in the cell in Excel, but the merge field displays 7.26000000009 means that you are running up against the encoding limitations of computers - the way that Excel stores the value as a binary is read by Word directly. Round 0 works because the binary representation of whole numbers is exact, but that of most decimal values is approximate, unless you can create the decimal part from a combination of the decimal values

0.5 0.25 0.125 0.0625 .03125 ....... (1/(2^n))

Perhaps you can create a text field for every more-than-zero-decimal field to merge, so that it is a string.

To convert, say, the values in column D:

=TEXT(D2,"0.00")

copied down. Then Excel will store that as a string, not a number, and Word should read it properly.