Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form fields not totalling correctly (Word 2000 SR1)

    I was wondering if anyone could check out a form I created. The form fields are totalling correctly but the results of the totals are to be displayed on the first page. In every instance - the amounts are off by a few cents. I cannot figure it out. The last row on the first page is also supposed to average the above figures. I haven't figured that out yet so if anyone wants to - I would appreciate it. Thank you.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    1. Are the fields being refreshed every time a value changes?
    2. What are you using to total the fieds?
    3. (This might answer 2.) Could you attach a sample? (It looks as if you wanted to but it didn't work. This can be if you previewed the message with an attachment before posting- that loses the attachment).
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    I am not sure of the refreshed question. I am just using sum above for the fields. I did try to attach but it didn't work. I will try again.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Melanie,

    First, "Sum(Above)" can be a problem. If there's a blank row in the middle, that can throw out the result. It's better to use an Excel-type sum, referencing cells in (what I assume is) a table. eg, if you are summing column 3 rows 1 to 3, use "Sum(B1:B3)" instead of sum(above) (Sorry, I don't have the exact syntax avaialable.).

    Second, if you populate a form field, and you have a field which is a formula, the value does not automatically chnage as it does in Excel. You may have to refresh the document fields to see the newly calculated value.

    From the MS help:
    <hr>Update fields to display the latest information
    If new information becomes available, you can update a field to produce new field results.

    To update a single field, click the field or the field results, and then press F9.
    To update all fields in a document, click Select All on the Edit menu, and then press F9.

    Tip You can update all fields in a document each time you print it. Click Options on the Tools menu, click the Print tab, and then select the Update fields check box<hr>
    But be careful of the tip. If a user unprotects a document which is protected for fields, and prints with this option set, values entered into the fields are cleared.

    I have a macro which is run on exit from every form field which refreshes all fields in a document.



    Secondly, irf you enter
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Can I send you the form at another email address? I am having trouble explaining what is going on. My totals are fine in the different tables in the document but I am trying to put the results from one of the fields into another field on the first page and then I need to average the final grades.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Can you attach to document?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    I will try just sending the first two pages. Thank you.
    Attached Files Attached Files

  8. #8
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Hi Melanie,

    First, consider using Excel for this. Table math in Word is quirky, at best.

    Is your form a locked "online" form with fields inserted using the Forms toolbar?

    If you have a correct result in one spot that you want to have appear in another spot, use a REF field for spot #2, don't try to rerun the calculation.
    Charles Kyle Kenyon
    Madison, Wisconsin

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Excel would be better - but the user wanted Word. Yes it is a form with locked fields. How do you use the ref field? If I use it -will I be able to total the results? It all works fine now except for the strange difference in the amounts. I am trying to say that the result of one form field =text134 (it puts the amount in but it is off by a few cents in every instance).

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    I made some minor changes...
    1) In the recap table, you will want references to the information from the other page (I changed the first entry as an example);

    2) to make it work, you will need to plot in your mind the the cell addresses (columns are labeled A, B, C... and Rows are numeric... including the table heading row) the same way Excel does.

    3) I agree with all who said this should be done in Excel, simply because that's what it's made for... 3 months down the road, when you add a row to the table on p2 (or beyond) good luck figuring out why all your numbers are off... Excel will automatically adjust them.
    Attached Files Attached Files
    Karen

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Thank you - I will take a look at it. Believe me - I know it should have been done in Excel. I did not create it - a faculty member did. I am just pleased that some of the faculty and staff here have thrown their typewriters out!! Word is a start! thanks again.

  12. #12
    BAM
    Guest

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Hi Melanie,

    Your =Average(Above) formula is off because it is including all empty cells in the average. An average is created by taking the sum of all cells that contain an entry and dividing by the count of those cells.

    Here is an example of what Word is doing: In the I. CLINICAL SKILLS section for example, there are 6 cells to be averaged. If only 2 contain an entry then the calculation Word is using is adding all 6 cells together and dividing by 6. So if the two cells each contained a 1, the formula results in .33 rather than the correct result: 1.

    As Geoff pointed out, you should be using =Average(B1:B6)

    Now, here is where a possible big problem comes in. The form was created using several different tables, thus Text 132 may appear to be in cell B3, is really B1. I'm wondering if this was done in an effort to use =Average(Above)??

    You would need to obtain Macros8.dot and use the Table Cell Helper macro to help you determine the cell addresses that you need to use in your formulas. You can obtain this from <A target="_blank" HREF=http://www.mvps.org/word/Downloads/index.html>http://www.mvps.org/word/Downloads/index.html</A> (I believe this macro will work with Word 2000)

    Place the template it in your WordStartup folder to load it as a global template to make the macro available to any document. The location of your WordStartup folder can be obtained from Tools/Options/File Locations.

    Note that you need to restart Word for the template to be loaded.

    There is also another item that I believe may cause you problems down the road. Please note this is not to criticize you but to help you learn. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Using the default tab stops instead of setting manual ones or placing the information in a borderless Word table may cause the printout to be off when printed on different printers. What can happen is that the printed version may be slightly larger than what you see on the screen and the tab stop could get pushed to the right and throw the everything out of whack.

    If the text takes up exactly 1" in width and it only has 1" to span, should the printed copy need 1.2" to print the text then it pushes the next tab stop to the right and everything else is thrown off. But with a 1 1/2" of "breathing room", this doesn't happen.

    The reason for this is that every time the tab key is pressed it is taking up a certain amount of space. If using the default tab stops, this would be 1/2 inch for every tab character.

    To get a better idea of what happens try this:
    - Create a document and press the tab key 6 times to move to the 3" mark and type some text about 1" in width.
    - Press the tab key 2 times to move to the 4.5" mark and type some text
    - Now add a few characters to the text at the 3" mark.

    Result: the text at the 4.5" mark gets pushed to the right.

    - Now on another line, click under the 3" mark to set a manual tab and do the same at the 4.5" mark.
    - Press tab once to move to the 3" mark and type the same text as you did before.
    - Press tab once to move to the 4.5" mark and type some text.
    - Edit the text at the 3" mark and add a few characters.

    Result: You can now add text up to the 4.5" mark before the 4.5" tab stop gets pushed to the right.

    Since you are already familiar with using Word tables personally I think that is the best route to take with a form such as this.
    ~~~~~~~~~~~~
    Cheers!

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Thanks - I see what you mean about the tab stops...that is a great tip - I may include it in a future newsletter if that is ok with you. so you are saying the reason my amounts are slightly off is because of the empty cells. That makes sense. thanks again.

  14. #14
    BAM
    Guest

    Re: Form fields not totalling correctly (Word 2000 SR1)

    Hi Melanie,

    Yes the averages are off due to the empty cells and the use of =Average(Above). If you use =Average(B1:B6) then they would be correct.

    I have sent you a PM regarding the newsletter request since it isn't necessarily Word related. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    ~~~~~~~~~~~~~
    Cheers! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form fields not totalling correctly (Word 2000 SR1)

    OK - this is really strange - I did change the form to read =average(B1:B6) and it still doesn't not work. I guess I will have to use the ref field. Thanks to all who helped me.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •