Mail merge decimal woes

January 30, 2012 2:44 PM

Mail merge woes - how can I get these decimals to display properly?

I teach middle-school.

I am trying to use mail-merge to take students' test scores from a spreadhseet and put them into a letter to send home to families. I am having some trouble.

Because of some craziness in the standardized test we use and the way it calculates grade-level-equivalents, the numbers are stored with a silly number of places after the decimal, i.e. Student A reads a 4.321579 grade level. I want to say, "Student A's reading level is 4.3"

I know how to run the mail merge. I know how to round the decimals in the excel spreadsheet so they show up with only 1 place after the decimal. In the spreadsheet everything looks fine.

But! In the merged file, they show up weird, i.e. instead of Student A having a 4.3, they have a 4.30000000002, where each number is super close to the rounded value in the spreadhseet but just a tiny bit off.

Why does it do this? How do I fix it?

posted by mai to Computers & Internet (8 answers total)

Instead of formatting the column to show only 1 place after the decimal, try creating another column and using the =ROUND() formula.
posted by Perplexity at 2:48 PM on January 30, 2012

Are you actually rounding the numbers with the round() formula, or are you just using the decrease decimal? Try making a new column using the round formula (=round(A1,2)), and then you can copy and paste values, and then you'll have the rounded values actually in there, instead of it just displaying them.
posted by brainmouse at 2:50 PM on January 30, 2012

Best answer: Assuming you're merging into Word, check this article on "Changing How Word Displays Numbers in Mail Merge"
posted by bcwinters at 2:52 PM on January 30, 2012

Response by poster: I did in fact use the round function. It isn't displaying the decimals the way they were before they rounded, it is displaying them a new, different, weird wrong way, i.e. with lots of zeroes and then a spurious 2. Sorry if that was not clear.
posted by mai at 2:52 PM on January 30, 2012

Adding onto the ROUND() suggestions, after using that to convert the numbers, copy the entire column, then paste it right back in as values (using PASTE SPECIAL). That will clear the formula that may be throwing off the merge.

Shortcut: CTRL-C, then ALT-E,S,V
posted by lampshade at 2:59 PM on January 30, 2012

Response by poster: Thanks, lampshade, I did paste them back in as values. It still had a problem, not sure why. The advice in bcwinters' article worked.
posted by mai at 3:04 PM on January 30, 2012

Best answer: I'd use TEXT(), so that the value stored is actually what you want displayed.

For one decimal place:

=TEXT(A1,"0.0")


(as to the why: it's some artefact of the storage of values in particular binary datatypes, but representing them on the screen as decimal, that I've never really understood. Or, shorter: Office can be stupid sometimes)
posted by pompomtom at 3:04 PM on January 30, 2012

Best answer: To elaborate on pompomtom's response, it is impossible to store a number like 0.1 precisely in a binary representation, because numbers to the right of the binary point are negative powers of 2, just as in decimal notation the numbers to the right of the decimal point are negative powers of 10. So, 0.1 binary is equivalent to 1/2 or 0.5 decimal; 0.01₂ is 1/4 or 0.25₁₀, 0.001₂ is 1/8 or 0.125₁₀, and so on. There is no finite series of negative powers of two that add up to exactly 0.1, or 0.2, or 0.3, or any other fractions that seem "round" and "nice" to us accustomed to thinking in base ten. Excel "fixes" these errors, meaning that it displays perfectly valid numbers as other numbers on the assumption that you would never actually need the number 4.30000000002 for anything. Word apparently does not.
posted by kindall at 3:24 PM on January 30, 2012