#1
|
|||
|
|||
Word Form -- numeric fields miscalculate
I have 3 columns in a word doc of numeric calculations. I'm using the legacy form for the fields. The first column calculates. However, when I try to populate the other two columns the whole thing fails to calculate correct sums.
3 attachments provided, one of which is the whole form. |
#2
|
||||
|
||||
The problem is that you're using a calculation formfield to calculate results based on other calculation formfields. This bug has been around forever. You should instead use formula fields for the calculations. To see how to do this and a wide range of other calculations in Word, check out my Microsoft Word Field Maths Tutorial:
https://www.msofficeforums.com/word/...-tutorial.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Using formulas on a PROTECTED form
Thank you for the information regarding the bug. I downloaded the tutorial via the link you provided which, unfortunately, turned out to be above my skill set. Found the formula tool, but not sure how that will work on a protected form. I get a syntax error when I insert the formula into the legacy total field. I need something that auto calculates for the end user, since he/she will likely not know how to insert a formula into the total field.
|
#4
|
|||
|
|||
You can put an ordinary (non-form) calculation field in a protected document. So long as the formfields on which it is based have "calculate on exit" checked. Note that you may have to tab out of the last field in which you enter data to trigger this.
That is, your Totals and Variance fields could all be ordinary calculation fields. You would need to assign bookmarks to the variance fields. |
#5
|
|||
|
|||
It seems to have worked by making each column's expressions and bookmarks unique.
|
#6
|
|||
|
|||
Quote:
|
#7
|
||||
|
||||
Tabbing out of any formfield with the 'calculate on exit' set will cause all the calculations to update. That's far simpler and more reliable than trying to have the users do their own (possibly errant) calculations. You might consider making the last field on the document a checkbox to be checked to say all data have been input. It wouldn't matter whether the user exits that field...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
Quote:
|
#9
|
||||
|
||||
Cross-posted at: http://answers.microsoft.com/en-us/o...0-02eaf752b581
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Spoke too soon -- I found another glitch: if ALL numeric fields are NOT filled (which is often the case), then the form miscalculates again. Sigh -- guess it's back to letting the end user fill in their own data and thus, their totals.
|
#11
|
||||
|
||||
Without actually seeing the problem document, it can be difficult for anyone to diagnose the issue and advise of how you might deal with it. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Student_Financial_Forum docx
Form uploaded per request of macropod. Thank you for your assistance. The form will only calculate if you tab across the columns horizontally. If the end user wants to enter data vertically in one column at a time, it's not going to calculate in all fields.
|
#13
|
||||
|
||||
That would only be the case if you don't have the 'calculate on exit' property set for all of the cells involved in the calculations. Since your document is password-protected, I can't really see what you have there. And, given the structure of your form (which has a bunch of other formfields after the data to be calculated), if you had the 'calculate on exit' property set for all of the cells involved in the calculations, you probably wouldn't need the 'Click to insure all totals calculate' checkbox.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Workaround for FormField Calculation Doubling
I was having the same problem with the formfield for totalling multiple amounts doubling. I calculated the three bookmarked fields as normal then divided it by 2 and it worked (a suggestion from my son)
Example: (AmountA+AmountB+AmountB)/2 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Legacy Form Fields Miscalculate | LauraL | Word | 2 | 09-21-2015 08:47 PM |
Macro to keep formatted form fields after mail merge or replace text with formatted form fields | jer85 | Word VBA | 2 | 04-05-2015 10:00 PM |
Extract form fields to Word Document | RonNCmale | Word VBA | 22 | 01-11-2014 05:06 AM |
Form Fields in Word | jwm1346 | Word | 1 | 04-17-2012 07:02 PM |
Form fields in Word messed up | mba | Word VBA | 0 | 02-07-2010 09:54 PM |