Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Form (Word 2003/2007)

    I need some help with a form. Clinicians are using this form but have a problem. There are instances where a particular field needs to be left blank. If you put a 0 (zero) in the field, it is calculated in the average and of course brings the average down. I do not know enough about field codes to help them. Is there a way to put a short if statement in the average field? For example if 0 do not include in average?? What they really would like to be able to do is enter N/A but I do not know how to accomplish this in a numeric field.
    Any suggestions would be greatly appreciated.
    Attached Files Attached Files

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

    Re: Calculating Form (Word 2003/2007)

    You can use the AVERAGE function - it will ignore blank cells. But you cannot use "above", for this will include only the filled cells immediately above the formula. So for the first average, you'd have to use the formula =AVERAGE(B2:B8), and similar for the others. You'll have to determine the cell references by counting carefully - each table starts with A1 in the upper left corner.

    Personally, I'd do this in Excel, not in Word. It's much easier to edit formulas in Excel.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Form (Word 2003/2007)

    Thank you Hans. They originally wanted it in Word and this week decided they would like it done in Excel. They copied and pasted into Excel and none of the fields worked and the fill in form fields for the evaluators did not wrap around. I do not have time right now to work on it in Excel. They need to distribute to the schools next week. So I was hoping to have an easy fix for their Word problems.

    Thank you so much for helping. I really appreciate it.
    Melanie

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Form (Word 2003/2007)

    Hello Hans, the clinicians like the changes I made to the form (thanks for your help) However, they are requesting another change and I am not sure how to accomplish this. In the last section "summary of competency scores" is there a way to calculate this section correctly if the cell it is referencing contains a 0? For example, if the one of the sections, let's say Therapy Planning has not been used and the average is 0, how can I get the 0 to not be included in the final summary?
    If this change cannot be made in Word, what is the easiest way to get these forms into Excel. There are several forms and they are approximately 5-10 pages long.

    Thanks for any help.

    Melanie

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

    Re: Calculating Form (Word 2003/2007)

    Perhaps macropod, our resident Word fields wizard, can tell you how to perform such calculations in Word.

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

    Re: Calculating Form (Word 2003/2007)

    Hi Melanie,

    Try the attached. I've done some serious restructuring, via the insertion of extra section breaks, so that the summary row for each category is now in a separate table, plus I've bookmarked these category tables. That has allowed the AVERAGE formula to be applied to the whole column for each category's 'sub-table', and also means the same formulae can be used for your 'SUMMARY OF COMPETENCY SCORES'.

    I think this gives the results you're after.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Form (Word 2003/2007)

    Thank you! I will try it out and send to the clinicians for feedback.


    I really appreciate it!

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Form (Word 2003/2007)

    The tables look great. The tricky part for me is inserting the rest of the sections. There are at least 5 more tables for one of the schools. I think I understand that you added the summary row as a separate table and then bookmarked the table? I am not exactly sure how to accomplish bookmarking and then referencing. I tried looking at your formulas but I was confused as to why sometimes the bookmark was tblA and the other time it was tblII? Is there a reason for using alpha and then roman numerals? Maybe I am way out of my league trying this but I will make an effort...

    Thank you!!!!
    Melanie

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

    Re: Calculating Form (Word 2003/2007)

    Hi Melanie,

    If you look at the document in 'Normal' view, you'll see more clearly how I used the Section breaks. Basically, they separate the data rows from the summary rows. Then, for each of the data tables, I Selected the whole table (you can do this via the Table menu) and added a bookmark. There's no special significance about the bookmark names, except that I chose names that related to the heading rows for each set of data.
    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
  •