Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-22-2016, 11:00 AM
snowboarder2 snowboarder2 is offline Word Form -- numeric fields miscalculate Windows XP Word Form -- numeric fields miscalculate Office 2010 32bit
Novice
Word Form -- numeric fields miscalculate
 
Join Date: Aug 2016
Posts: 26
snowboarder2 is on a distinguished road
Default 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.
Attached Images
File Type: jpg StudentReport.JPG (41.1 KB, 33 views)
File Type: jpg VarianceTotals.JPG (32.9 KB, 32 views)
Attached Files
File Type: docx Student_Financial.docx (30.0 KB, 8 views)
Reply With Quote
  #2  
Old 08-22-2016, 05:23 PM
macropod's Avatar
macropod macropod is offline Word Form -- numeric fields miscalculate Windows 7 64bit Word Form -- numeric fields miscalculate Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 08-23-2016, 07:02 AM
snowboarder2 snowboarder2 is offline Word Form -- numeric fields miscalculate Windows XP Word Form -- numeric fields miscalculate Office 2010 32bit
Novice
Word Form -- numeric fields miscalculate
 
Join Date: Aug 2016
Posts: 26
snowboarder2 is on a distinguished road
Default 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.
Reply With Quote
  #4  
Old 08-23-2016, 07:27 AM
Charles Kenyon Charles Kenyon is offline Word Form -- numeric fields miscalculate Windows 8 Word Form -- numeric fields miscalculate Office 2013
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

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.
Reply With Quote
  #5  
Old 08-23-2016, 07:58 AM
snowboarder2 snowboarder2 is offline Word Form -- numeric fields miscalculate Windows XP Word Form -- numeric fields miscalculate Office 2010 32bit
Novice
Word Form -- numeric fields miscalculate
 
Join Date: Aug 2016
Posts: 26
snowboarder2 is on a distinguished road
Default

It seems to have worked by making each column's expressions and bookmarks unique.
Attached Images
File Type: jpg AllCategories_Unique.jpg (131.7 KB, 31 views)
Reply With Quote
  #6  
Old 08-23-2016, 08:12 AM
snowboarder2 snowboarder2 is offline Word Form -- numeric fields miscalculate Windows XP Word Form -- numeric fields miscalculate Office 2010 32bit
Novice
Word Form -- numeric fields miscalculate
 
Join Date: Aug 2016
Posts: 26
snowboarder2 is on a distinguished road
Default

Quote:
Originally Posted by Charles Kenyon View Post
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.
Thanks for the tip about tabbing out of the last field to to trigger the calculation. It was giving me problems. I'm afraid this is going to be problematic for the end user. Ultimately, I'm afraid I'll just have to ditch the auto calculation or formula options and have the end user enter their own sums.
Reply With Quote
  #7  
Old 08-23-2016, 02:38 PM
macropod's Avatar
macropod macropod is offline Word Form -- numeric fields miscalculate Windows 7 64bit Word Form -- numeric fields miscalculate Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #8  
Old 08-23-2016, 03:18 PM
snowboarder2 snowboarder2 is offline Word Form -- numeric fields miscalculate Windows XP Word Form -- numeric fields miscalculate Office 2010 32bit
Novice
Word Form -- numeric fields miscalculate
 
Join Date: Aug 2016
Posts: 26
snowboarder2 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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...
The checkbox option is a brilliant solution. I just now inserted it into the form and it resolves the issue of getting the last column to total. Thank you so much for the suggestion. That's a great user-friendly technique for the end-user.
Reply With Quote
  #9  
Old 08-23-2016, 07:47 PM
macropod's Avatar
macropod macropod is offline Word Form -- numeric fields miscalculate Windows 7 64bit Word Form -- numeric fields miscalculate Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #10  
Old 08-24-2016, 11:28 AM
snowboarder2 snowboarder2 is offline Word Form -- numeric fields miscalculate Windows XP Word Form -- numeric fields miscalculate Office 2010 32bit
Novice
Word Form -- numeric fields miscalculate
 
Join Date: Aug 2016
Posts: 26
snowboarder2 is on a distinguished road
Default

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.
Reply With Quote
  #11  
Old 08-24-2016, 02:42 PM
macropod's Avatar
macropod macropod is offline Word Form -- numeric fields miscalculate Windows 7 64bit Word Form -- numeric fields miscalculate Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #12  
Old 08-25-2016, 07:35 AM
snowboarder2 snowboarder2 is offline Word Form -- numeric fields miscalculate Windows XP Word Form -- numeric fields miscalculate Office 2010 32bit
Novice
Word Form -- numeric fields miscalculate
 
Join Date: Aug 2016
Posts: 26
snowboarder2 is on a distinguished road
Default 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.
Attached Files
File Type: docx Student_Financial_Forum.docx (31.3 KB, 13 views)
Reply With Quote
  #13  
Old 08-25-2016, 03:44 PM
macropod's Avatar
macropod macropod is offline Word Form -- numeric fields miscalculate Windows 7 64bit Word Form -- numeric fields miscalculate Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by snowboarder2 View Post
If the end user wants to enter data vertically in one column at a time, it's not going to calculate in all fields.
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]
Reply With Quote
  #14  
Old 12-13-2020, 10:52 PM
lboogy202 lboogy202 is offline Word Form -- numeric fields miscalculate Windows 10 Word Form -- numeric fields miscalculate Office 2019
Novice
 
Join Date: Dec 2020
Posts: 1
lboogy202 is on a distinguished road
Thumbs up 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
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft