Why Does Result Round Up/Down in Word Table?

[SilentAiche]

I have a very simple table in word - four columns, one header row, one data row. All four fields in the data row are FormFields.

User enters numeric figures in Columns A and B. A formula in Column C displays the difference between A and B and a formula in Column D gives the percentage the difference represents of Colunm A (i.e., =c2/a2).

When setting the number format in Column D I selected 0.00% from the drop down menu. However, all the percentages are rounding up or down. Example: 509/2325 should be 21.89%, but the result I get is 22%.

I tried replacing the form field with a field. When I selected the same 0.00% format, it gave me .22%.

I need the two decimal places due to this percentage being used in monetary calculations.

Thanks in advance!
Tim

[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[sawedoff]

I am not sure how it is intended to work, but if you multiply the cell (ie:C2/a2*100, then choose 0.00%, it seems to work fine.

2325 1816 509 21.89%


Sawedoff

 

[SilentAiche]

Sawedoff,

I really appreciate your response - thanks.

My actual numbers (very close to the example) were

2834 2325 509 22.00%

which should have been (at least, what I hoped for)

2834 2325 509 21.89%

I'll experiment with your suggestion tomorrow at work. It seems like it ought to be simple - why would MS offer a pre-packaged format that doesn't work? (y'all can stop laughing now...)

Thanks again,

Tim



[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[macropod]

Hi Sawedoff,

The reason that this doesn't work as expected is that the '%' symbol in the format isn't evaluated, any more than a '$' symbol is - inserting them just tells Word to output them in the result. This is quite unlike Excel's behaviour, but so too is the ability to nest 20 IF statements (Excel is limited to 7).

Cheers

 

[SilentAiche]

I fixed it manually, for lack of a better term. I changed the formula to =(c2/a2)*100 and simply inserted the "%" sign next to the form field. I changed the number format to #,##0.00.

Upon further experimentation, I discovered you can select the format above and append the % sign (#,##0.00%) and get correct results IF you remove the *100 portion of the formula. Strange - but doesn't this mean Word IS evaluating the percent sign?

BTW - Word 2002 on Win XP.

Thanks for your input.

Tim

[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[SilentAiche]

Okay, now Word is just screwing with me.

In another table in the same document, the #,##0.00% solution does not work. It still rounds up - 64.58% becomes 65.00%.

Table 2 is different in a few ways, but I'm not sure why any of them would effect the format/rounding issue. In Table 2:

1) The formula in the percent field is [blue]=if(c2<d2,e2/c2,e2/d2)[/blue]

2) All figures in C, D and E are input

3) C, D & E are all text form fields. In Table 1, where the forumla was [blue]=c2/a2[/blue], the figure in C was computed. Also, C was a Field instead of a Text Form Field.

Any ideas why #,##0.00% works in one table but not the other?

THanks,
Tim

[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[sawedoff]

You might want to put an Excel table into the Word doc. I think you would have better results than a Word table can deliver. Word was intended to do documents, and they added math as a convenience. Excel was designed to do math and you can add a small worksheet into a Word document when you need more math power. Just click the Excel button on the standard toolbar, or select Insert - Object - Microsoft Excel Worksheet.

Sawedoff

 

[SilentAiche]

Sawedoff,

My end users are not very computer savvy, so I would rather not have them keeping up with two files instead of one, or having to answer questions about updating links, etc.

I agree Excel is certainly the preferable application for calculations, but I'm afraid I'm stuck with this in Word. I have a workaround (mentioned above - format as #,##0.00, adjust the formula with *100, and insert the "%" next to the form field), but I was curious why the two tables behaved so differently.

I can accept my lack of knowledge when I don't know how to do something, but seemingly inconsistent behavior in software drives me nuts!

I appreciate your replies.

Tim

[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[sawedoff]

You don't need 2 files, it actually inserts a small table with Excel capabilities into the Word doc. No links, and it will look just like a Word table. If I were you, i'd try it, then decide if your users can handle it.

Sawedoff

 

[fumei]

OR; you could use BA to calculate the Results of your formfields.

With ActiveDocument
 .FormFields("Text3").Result = _
    ActiveDocument.FormFields("text1").Result - _
    ActiveDocument.FormFields("text2").Result

 .FormFields("Text4").Result = _
   CInt(ActiveDocument.FormFields("text3").Result) / _
   CInt(ActiveDocument.FormFields("text1").Result) * 100
End With

used as an OnExit macro for the SECOND formfield puts the difference into the third, and calculates a result for the fourth. Resukt = 21.8924731182796

Gerry

 

[SilentAiche]

Thank you both - I'll play with both ideas. Right now the users tab from form field to form field throughout the entire document, which is protected as a form. I'm not sure how an embedded worksheet will behave in this environment.

Thanks again!
Tim

[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[fumei]

If they Tab ing through a protected document, using formfields, then code to extract and fill in results (as per my last post) will work fine. You will have to unoprotect (by code) to do it though. Simply put an Unprotect line at the start, a Protect line at the end.

Gerry

 

[macropod]

Hi fumei,

Merely checking the 'calculate on exit' proprty of the form fields, combined with the existing formula field(s) would be simpler than invoking a macro to do the same job.

It also avoids macro warnings, which rightly enough cause concern about a document's safety.

Cheers