Problem with percentage when using Excel for MailMerge

**Shirley Munro** · 12-09-2008, 07:25 PM

Hi

I am using Excel to calculate student results. The first screenshot shows an example of the data that I am using. Cell G3 contains the total marks available and cells G6:G9 contain the marks achieved. In cell H6 I have generate a formula to work out the percentage =G6/$G3. I then format this to a percentage style of 0 decimal place. When I use this as a data source in Word, it then displays the results as numbers, eg 92% becomes 0.92 or even 0.666666673. I can get around the problem but using this function =ROUND(G6/$G$3*100,0) and simply inserting the % sign into the Word document but I feel there must be a better way of doing this. I have also tried using Paste Special to replace the formula with values but this is not ideal as the result sometimes change so I need to remember to fix it all up.

If anyone can come up with a really good fix for this problem I would be most grateful.

Shirley Munro

**teylyn** · 12-09-2008, 08:13 PM

You need to work on the merge field in Word

say your field is called "percentage". In Word you see something like

{ MERGEFIELD percentage} or <<percentage>> when you toggle field codes.

What you have to do is add a calculation field that will multiply "percentage" with 100 and then add the % sign.

I ran into a few error messages when trying it out, but found this trick to nest a mergefield in a calculation field:

- toggle the field codes to display { MERGEFIELD percentage}
- add a few blanks behind it, then click Insert - Field and click the "Formula" button. We'll enter a dummy formula to make sure we get the syntax right:
- enter a formula like 10*100 and choose a number format from the drop down, e.g. 0.00%
- Click OK and you'll see the field calculation result. Toggle the field code to display

{ =10*100 \# "0.00%" }

- Highlight your merge field { MERGEFIELD percentage} including the brackets, then highlight the number 10 in the calculation and paste, thus replacing the 10 with the mergefield. It will show up as <<percentage>> with the chevrons.

- Now update the field and VOILA! The percentage is displayed correctly.

- delete the mergefield that is not showing correctly.

Let me know if this works for you.

cheers

**Shirley Munro** · 12-09-2008, 10:51 PM

Hi

I am nearly there but not quite. I have put in the following code in the merge field in Word:

{MERGEFIELD "Percentage" =percentage*100\# "0.00%"}

but it now displays the value as 0.90%. Have tried different setting, eg "0%" but it then rounds it up to 1%

Any more help would be great.

Shirley

**teylyn** · 12-09-2008, 11:23 PM

You work nights?? Scotland??

Have you followed my step by step?

Your

{MERGEFIELD "Percentage" =percentage*100\# "0.00%"}

looks different from what I have in my field, and it does not seem to be doing the multiplication with 100, which is why it still shows a 0.9.

You need to start with a calculation field and then paste the mergefield into the calculation. In my tests, the merge field then was converted to a value that seems to be an absolute, but it changes with every data record. My field looks like this for a record with a percentage value of 33%:

{ =0.33000000000000002*100 \# "0.00%" }

The red bit is what Word converted my <<percentage>> field to

Let me know how you get on

**Shirley Munro** · 12-10-2008, 12:15 AM

Hi

Pretty much got it working although it doesn't show as <<percentage>> when I toggle the field codes but this may be something I need to fix up in Word.

Thanks for your help.

Oh, I live in Australia now. Emigrated here 2 years ago.

Shirley

**martindwilson** · 12-10-2008, 06:24 AM

strang that , i just built a mail merge put =c1/c2 into cell a2 excel formated % called column (a1)percentage
then put various numbers to generate percentages in a2
then tried a merge on that and my word returned whatever percentage was in a2 as 17%, 60% or whatever no problem

**Shirley Munro** · 12-10-2008, 07:28 PM

Would be interested in seeing a screenshot of both the word and excel data as I cannot get this to work using your method. What data did you put in C1 and C2 and how was your mailmerge constructed? I have found that it doesn't make any difference whether I format the cells in Excel to a percentage, it still appears in its raw state in Word so if you have got a way around using formulas in fields in Word that would be great.

Shirley