Results 1 to 9 of 9

20070914, 15:54 #1
 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.

20070914, 16:12 #2
 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.

20070914, 16:50 #3
 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

20071015, 16:17 #4
 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 510 pages long.
Thanks for any help.
Melanie

20071015, 16:32 #5
 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.

20071016, 02:30 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 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 'subtable', and also means the same formulae can be used for your 'SUMMARY OF COMPETENCY SCORES'.
I think this gives the results you're after.Cheers,
Paul Edstein
[MS MVP  Word]

20071016, 17:29 #7
 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!

20071016, 19:19 #8
 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

20071017, 00:30 #9
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 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]