| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Average Calculation in a Protected Form - Word 2003
Hi,
I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
|
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
|
Average Calculation in a Protected Form - Word 2003
You are probably going to have to use a macro
Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("bookmarknameforaverage").Result = j/i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Hi, I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
|
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
|
Average Calculation in a Protected Form - Word 2003
Thank you, Doug for the response. However, when I try to run that macro on
entry of the 'Total' cell, I get an error message: "The collection that your expression refers to does not exist". "Doug Robbins - Word MVP" wrote in message ... You are probably going to have to use a macro Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("bookmarknameforaverage").Result = j/i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Hi, I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
|
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
|
Average Calculation in a Protected Form - Word 2003
Did you replace
.bookmarknameforaverage in ..Formfields("bookmarknameforaverage").Result = j/i with the name of the bookmark assigned to the formfield in which you want the average displayed -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug for the response. However, when I try to run that macro on entry of the 'Total' cell, I get an error message: "The collection that your expression refers to does not exist". "Doug Robbins - Word MVP" wrote in message ... You are probably going to have to use a macro Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("bookmarknameforaverage").Result = j/i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Hi, I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
|
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
|
Average Calculation in a Protected Form - Word 2003
Thank you, Doug. Changing the 'bookmarknameforaverage' was part of the
solution. The other part of the solution was to revise the field names in your code below for the labels to be correct --the numbers were of for each If statement (i've include the revised code below)... Great tool! Thanks again! Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("intAverPMBPD").Result = j/i End With "Doug Robbins - Word MVP" wrote in message ... Did you replace .bookmarknameforaverage in .Formfields("bookmarknameforaverage").Result = j/i with the name of the bookmark assigned to the formfield in which you want the average displayed -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug for the response. However, when I try to run that macro on entry of the 'Total' cell, I get an error message: "The collection that your expression refers to does not exist". "Doug Robbins - Word MVP" wrote in message ... You are probably going to have to use a macro Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("bookmarknameforaverage").Result = j/i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Hi, I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
|
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
|
Average Calculation in a Protected Form - Word 2003
That's what comes of copying and pasting and failing to edit the pasted
result correctly. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug. Changing the 'bookmarknameforaverage' was part of the solution. The other part of the solution was to revise the field names in your code below for the labels to be correct --the numbers were of for each If statement (i've include the revised code below)... Great tool! Thanks again! Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("intAverPMBPD").Result = j/i End With "Doug Robbins - Word MVP" wrote in message ... Did you replace .bookmarknameforaverage in .Formfields("bookmarknameforaverage").Result = j/i with the name of the bookmark assigned to the formfield in which you want the average displayed -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug for the response. However, when I try to run that macro on entry of the 'Total' cell, I get an error message: "The collection that your expression refers to does not exist". "Doug Robbins - Word MVP" wrote in message ... You are probably going to have to use a macro Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("bookmarknameforaverage").Result = j/i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Hi, I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
|
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
|
Average Calculation in a Protected Form - Word 2003
Well, this worked when I tested it on the LAN using Microsoft Word 2003.
However, when I sent it to my hotmail account at home and then opened it using Microsoft Word 2007, the averaging function stopped working. Also, the patients attempting to fill the form out from their home computers get the same thing and other odd things like not being able to fill out some fields. Does anyone have any ideas? Thanks! Linda "Doug Robbins - Word MVP" wrote in message ... That's what comes of copying and pasting and failing to edit the pasted result correctly. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug. Changing the 'bookmarknameforaverage' was part of the solution. The other part of the solution was to revise the field names in your code below for the labels to be correct --the numbers were of for each If statement (i've include the revised code below)... Great tool! Thanks again! Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("intAverPMBPD").Result = j/i End With "Doug Robbins - Word MVP" wrote in message ... Did you replace .bookmarknameforaverage in .Formfields("bookmarknameforaverage").Result = j/i with the name of the bookmark assigned to the formfield in which you want the average displayed -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug for the response. However, when I try to run that macro on entry of the 'Total' cell, I get an error message: "The collection that your expression refers to does not exist". "Doug Robbins - Word MVP" wrote in message ... You are probably going to have to use a macro Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("bookmarknameforaverage").Result = j/i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Hi, I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
|
#8
Posted to microsoft.public.word.tables
|
|||
|
|||
|
Average Calculation in a Protected Form - Word 2003
Where did you create the macro? That is, in what template is it? I would
be fairly certain that it is in a template that is available to you on the network, but is not available when you open the document containing the form on a computer that is not attached to the network. To be able to use the macro from a computer that is not connected to the network, you will have to do one of two things: 1. Create the form as a template and have the users save it in their templates folder and then use FileNew and select that template as the basis for the document (form) that they want to create. As the users template folder is considered by Word to be a trusted location, then the macro in the template will be allowed to run. 2. Create the macro in the document. With this approach however if the users have the macro security level set to high, the macro will be disabled without any warning and it will not then work. If they have it set to medium, they will get a warning and the option to enable the macro (which may freak them out - or should that be them out freak) -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Well, this worked when I tested it on the LAN using Microsoft Word 2003. However, when I sent it to my hotmail account at home and then opened it using Microsoft Word 2007, the averaging function stopped working. Also, the patients attempting to fill the form out from their home computers get the same thing and other odd things like not being able to fill out some fields. Does anyone have any ideas? Thanks! Linda "Doug Robbins - Word MVP" wrote in message ... That's what comes of copying and pasting and failing to edit the pasted result correctly. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug. Changing the 'bookmarknameforaverage' was part of the solution. The other part of the solution was to revise the field names in your code below for the labels to be correct --the numbers were of for each If statement (i've include the revised code below)... Great tool! Thanks again! Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("intAverPMBPD").Result = j/i End With "Doug Robbins - Word MVP" wrote in message ... Did you replace .bookmarknameforaverage in .Formfields("bookmarknameforaverage").Result = j/i with the name of the bookmark assigned to the formfield in which you want the average displayed -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Thank you, Doug for the response. However, when I try to run that macro on entry of the 'Total' cell, I get an error message: "The collection that your expression refers to does not exist". "Doug Robbins - Word MVP" wrote in message ... You are probably going to have to use a macro Dim i as Long, j as Long i = 0 j = 0 With ActiveDocument If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD1").Result End If If .Formfields("intPMBPD1").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD2").Result End If If .Formfields("intPMBPD2").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD3").Result End If If .Formfields("intPMBPD3").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD4").Result End If If .Formfields("intPMBPD4").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD5").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD5").Result End If If .Formfields("intPMBPD6").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD6").Result End If If .Formfields("intPMBPD7").Result 0 then i = i + 1 j = j + .Formfields("intPMBPD7").Result End If .Formfields("bookmarknameforaverage").Result = j/i End With -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Linda Aldrich" wrote in message ... Hi, I have used the calcuation expression within a table form field, using the bookmarks for each of the fields. If the fields have a '0' value they get averaged into the final figure. How do I dis-include the '0' values from the average? Below is the expression for the Form Field "intAverPMBPD": =AVERAGE(intPMBPD1,intPMBPD2,intPMBPD3,intPMBPD4,i ntPMBPD5,intPMBPD6,intPMBPD7) Thank you for any help you can provide! Linda |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Word 2003 - Protected form will not allow hyperlink to be clicked | Microsoft Word Help | |||
| Average calculation | Microsoft Word Help | |||
| Calculation in a table in a protected form | Tables | |||
| How do I create a multiplication calculation in a protected form? | Microsoft Word Help | |||
| Automatic update of calculation fields in a protected form | Tables | |||
Linear Mode
