Average Calculation in a Protected Form - Word 2003

[Linda Aldrich]

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,intPMBPD5,intPMBPD6,intPMBPD7)

Thank you for any help you can provide!

Linda

 

[Doug Robbins - Word MVP]

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]

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]

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]

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]

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]

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]

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