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 |
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 |