Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Word calculation of drop-down boxes

    Help!

    I have created a staff appraisal that includes scoring each individual between 1 and 4 in a number of different areas. I have created a drop down list of the scores (as my managers like to put in 2.5 etc) what I am trying to do is create an average score for each series of boxes. I have named each of the dropdowns so there is an individual name and ticked the calculate on exit. However the scores will not move from 0.00 (I have at least got the formatting of the number right.)

    I am using the following formula =average(manjc1,manjc2,manjc3,manjc4,manjc5) where manjc1 refers to the dropdown list

    What am I doing wrong?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,947
    Thanks
    0
    Thanked 204 Times in 185 Posts
    You need a formula field (not a formfield), coded along the lines of:
    {=average({REF manjc1},{REF manjc2},{REF manjc3},{REF manjc4},{REF manjc5})}

    Note: All the field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. The Following User Says Thank You to macropod For This Useful Post:

    Northy (2014-08-19)

  4. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Red face Thank You

    You sir are a star. I should have asked an MVP a couple of hours earlier. Thanks


    Quote Originally Posted by macropod View Post
    You need a formula field (not a formfield), coded along the lines of:
    {=average({REF manjc1},{REF manjc2},{REF manjc3},{REF manjc4},{REF manjc5})}

    Note: All the field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues.

  5. #4
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Formula Disappears

    Thanks to some great help on here I managed to create my form with the average scores being calculated properly. So far so good. As soon as I started protection though those formula disappear and when I turn off protection the field codes have gone as well. Is there something I havent ticked?

  6. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,947
    Thanks
    0
    Thanked 204 Times in 185 Posts
    Applying forms protection doesn't ordinarily impact any fields. Have you pressed Alt-F9 to toggle the field code view?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #6
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Unhappy Formulas not available when starting form protection

    Thanks Paul

    The formula is still there, here it is {{=average({REF empjc1},{REF empjc2},{REF empjc3}) \#"0.00"}} but when I click the begin protection the field disappears and if I change any of the numbers it doesnt show up in the average box. I suspect it is because word doesnt recognise that there is a "field" of some description in the box? Bit stumped and thanks for the help.



    Quote Originally Posted by macropod View Post
    Applying forms protection doesn't ordinarily impact any fields. Have you pressed Alt-F9 to toggle the field code view?

  8. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,947
    Thanks
    0
    Thanked 204 Times in 185 Posts
    Do all the dropdown formfields have their 'calculate on exit' property checked? Are you sure all the field braces in the code I posted were replicated in the document via Ctrl-F9?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #8
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the quick reply. I deleted the old ones and then retyped it. The form works absolutely fine and calculates the average. Happy days. This was exactly as you told me to do it in the first place, the syntax is obviously correct as the calculation now works. The problem only arises when I hit the start enforcement. I have put in editing restrictions to make it a form so people have text boxes they write about the person and then they select a score from the drop down list. That all works fine but the averages box simply goes blank. I have checked that the calculate on exit works and this is proven because it correctly calculated the average before I started the form protection. When I turn off the protection the field that used to be there and worked is now gone. if I hit alt F9 then I can still see the formula but it is no longer working and I have to delete it and start again. Odd really.

  10. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,947
    Thanks
    0
    Thanked 204 Times in 185 Posts
    Can you attach the document to a post with the relevant fields (delete anything sensitive) so can I diagnose the issue? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #10
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Template

    Thanks here you go, there is nothing sensitive on this form as it is the template.
    Attached Files Attached Files

  12. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,947
    Thanks
    0
    Thanked 204 Times in 185 Posts
    Your fields would work a lot better if you didn't insert the code inside yet another field. What you have is, for example:
    {{=average({REF manjc1},{REF manjc2},{REF manjc3},{REF manjc4},{REF manjc5})}}
    Compare that against what I said you should use...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. The Following User Says Thank You to macropod For This Useful Post:

    Northy (2014-08-20)

  14. #12
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Doh, Thanks again Paul.

Tags for this Thread

Posting Permissions

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