Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging field values in a Word document (Word 2000)

    I'm trying to modify a document we use for employee performance evaluations so that the employee's score will be computed for the user after all the scores have been entered. The problem is that the form uses check boxes for each of 5 (1-5) scoring level in all of the graded areas. How can I get the document to assign a value, based upon the boxes checked by the user, so they may be averaged? I think I know how to make each selection a field and to assign each a value but do not know how to get Word to disregard the unchecked boxes so that only those boxes that are checked are used in the averaging calculation. A plus would be to have the document not allow more than one box to be checked in each grading area. At present, the form is pretty basic and allows any number of boxes to be checked in any given grading area. It's up to the user to uncheck/check the appropriate box.

    I have included a copy of the document file for your reference.

    Thank you WOPR crew!!!!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Averaging field values in a Word document (Word 2000)

    I'd use Excel, Access or a dedicated survey tool for this, not Word.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Averaging field values in a Word document (Word 2000)

    Hi Steve,

    Rather than using a series of check-boxes for each measure, I'd suggest using a single drop-down box. This has a number of advantages, including:
    . Allowing only one value per measure
    . Setting values that can be tested via the 'calculate on exit' property for generating the averages. This last bit can be done with a (relatively) simple formula field, especially if the first character for each choice is the numeric value.
    If you do go down this path, an issue you'll have to work around is the limited number of chacters (50) each drop-down choice can support.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging field values in a Word document (Word 2000)

    Thanks for responding to my post.

    I'd love to do it in Excel BUT it's an established form, in Word, and do not think Adminstration will go for it. I will defintely keep it in mind though and bring it up when I see an opportunity to do so.

    Thanks again.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging field values in a Word document (Word 2000)

    Thanks for taking the time to respond.

    I will give it a go and see how it works. I did not even think of the drop-down route. Hans had a good idea too, about using another application like Excel or Access but I do not think our Administration will go for that (though I guess I could re-do the form in Excel for my use in my Department and anyone else who cares to do so and just leave the higher ups out of it).

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging field values in a Word document (Word 2000)

    I am trying out your suggestion on the evaluation document we use at work. I changed each checkbox selection to a drop-down menu with a blank, 1, 2, 3, 4, 5 in the selection list (so the blank would show up when the document is first viewed) and left the number entry fields elsewherein the document as they were. I named each of the bookmarks that are performance scores and set the "Overall Evaluation" field to be a calculation. I set the calculation to be the AVERAGE() of the many bookmark fields I just mentioned. I cannot get the evaluation score to display in the "Overall Evaluation" field, however. I've attached a copy of the revised document for your reference.
    Attached Files Attached Files

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Averaging field values in a Word document (Word 2000)

    Hi Steve,

    As I said in my previous reply, calculating the averages from the formfields is 'relatively' easy - not absolutely so.

    In this case, an added complication is that you've got formfields that permit empty results, as indicated by the space character. Word has real trouble doing maths with anything other than digits. But, then, so do I. No matter, we can work around that by using embedded formula fields to reinterpret the spaces as 0s.

    Attached is a revised copy of your document, with the necessary formula fields for calculating the averages on your 'Overall Evaluation' line. There are two of these, either of which you could use according to your requirements:
    . The first one simply uses Word's in-built AVERAGE function to calculate the average of all the drop-down bookmark values. The averages returned by this assume all fields have been updated.
    . The second one uses a more complicated formula to calculate the average of all the drop-down bookmarks that have numeric values, in effect only averaging those that have been updated.
    Both of these set a bookmark named 'Score'. Delete whichever set of averaging fields you don't want.

    To simulate the completion of the overall ranking check boxes, I've used another set of formula fields that reference to 'Score' bookmark and put an 'X' inside a set of boxed-in characters. Since the averages aren't necessarily integer values, I've made rounding some assumptions. Also, since there are two fields setting the 'Score' bookmark, this last set of fields currently work off the second set averaging fields.

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Bartlesville, Oklahoma
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging field values in a Word document (Word 2000)

    My apology but I'm a bit confused by your response. I thought that the document that I attached had been set up to average the fields containing evaluation scores, similar to the "first" example or field set to which you refer in your reply. Wouldn't the scoring fields I had set up all have contained numeric values (other than zero) once the form/document was completed by a user? Wouldn't the "Overall Evaluation" field in my document update itself each time a user selected a score value for each field? Would it make a difference if I removed the "blank" list selections in all the fields and left the default entry selection in Properties blank? When I tested my document (before making my submittal through WOPR) all the fields had numeric values (>0) assigned to them and I still received no result in the "Overall Evaluation" field. I'm obviously missing something you've explained in your response.

    Also, when I tried to check the properties of the "Overall Evaluation" field in the document you sent to me, that selection (properties) was not available ("greyed out"). I was attempting to see the calculation formula you had placed there. I could only find one (1) file or only one file opened when I selected the file link though you mention there being two.

    Did you ever read something and just know that the answer you are seeking is in the text but you just cannot see it? I have a feeling that I'm about to receive a double dose of chagrin when you school me on this matter.

    Thank you for your time and patience.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Averaging field values in a Word document (Word 2000)

    Hi Steve,

    When I look in your document, all I can see in the properties for the field next to 'Overall Evaluation' is that is was a calculation field, and the calculation has a '=' sign. No formula is apparent. That may explan why you're getting no result. It's also largely why I set about creating my own formula fields. Plus, you don't need a form field to do the calculation. Indeed, using form fields to do calculations based on the results of other form fields can give erroneous results.

    It is true that the 'Overall Evaluation' field in your document will update itself each time a user selected a score value for each field (subject to a formula depending on the other fields being present and, in this case, except for the 'Flexibility' form field, which wasn't set to calculate on exit). However, a standard AVERAGE function in the 'Overall Evaluation' field would return an syntax error if any score was blank (actually a space character in your form fields). That's why I used a more complicated formula field (which you haven't yet seen) to turn your 'blanks' into 0s. If you replaced the permitted blanks in your form fields with 0s, standard AVERAGE function would work correctly, but this would still return a potentially misleading 'low' average if any fields are left with the 0 defaults. Which is why I've provided two averaging solutions - they're both on the same line, next to 'Overall Evaluation'.

    You can't access the formula fields I created via the field 'properties' option - that only works with form fields. For formula fields, select the field (or even the whole paragraph) and press Shift-F9. Alternatively, use Alt-F9 to toggle the display of all field codes on/off.

    Cheers
    PS: The learning curve with using fields for anything beyond the basics is short and steep (maybe even has overhangs).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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