Calculation in Word 2007

[Melanie]

I need some assistance in Word 2007 using calculating form fields in a table. The final table references other tables in the document. The problem is when one of the cells from the other tables has a 0 zero it brings the average down in the final table. How would I average the cells and not count the 0?
I have tried =sum(b1:b8)/countif(b1:b8,">0") but I keep receiving a syntax error. I am really not sure how to use countif or sumif but someone on the programming staff suggested it may be possible.

Thank you for any assistance

[macropod]

Hi Melanie,

SUMIF isn't a valid function in Word. For what you're after, you could use a formula field (not a formfield) coded as:
{=SUM(C1:C8)/{=(C1<>0)+(C2<>0)+(C3<>0)+(C4<>0)+(C5<>0)+(C6<>0)+ (C7<>0)+(C8<>0)}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.

[Melanie]

Thank you so much for your reply. I am having a problem trying to get the formula field to stay after I lock the form in the developer tab. When I lock it, the formula disappears. The issue appears on the last page in the summary of competency scores.
I have attached the file for your review. Thank you for any suggestions.

[macropod]

Hi Melanie,

It works for me! All you need to do is to tab out of any of the data entry fields contributing to the calculation to get it to update. It would also have calculated had you selected the formula field before protecting the document and pressed F9.

Having said that, I think your form's logic might need a bit more work. Where you've got the formula I gave you, what it is calculating is the average of a number of averages, which is a rather meaningless statistic given that the various 'average' figures are comprised of different numbers of elements some of which may or may not have been filled in. If you want a true average of all scores, you need a formula that sums all the scores, then divides that sum by the count of all the scores. See attached. Note the changes I've made to the individual average calculations and the table bookmarks I've used.

[Melanie]

Thank you so much! This looks great. I was a bit confused at first because when I entered a 0 in one of the cells, it still brought the average down. The professors have stated that there are occasions when they may not be grading a particular area of assessment and they would like to be able to leave it blank or just use a 0. If I use a 0 in your formula, the average is lowered, but if I just tab and leave it blank or use delete if there is already a number entered, it works fine and does not lower the average.

I am still having issues with Section I. A. - I cannot get an average. Also, at the end of the form, I cannot get the summary of competencies to calculate correctly. the first row and the last two rows are not included. I will try and figure out what is going on. I have attached as your knowledge exceeds mine and you would have an answer much quicker than I.

Thanks again
Melanie

[macropod]

Hi Melanie,

Yes, the AVERAGE formula treats blanks and zeros differently - this would allow an assessor to award a 0 where warranted (though I note you don't provide for it in the scoring instructions - a 0 might say "does more harm than good"). Do note that your formfields also allow negative values to be input! If there is any concern about numbers outside the valid range (or non-numeric strings) being input, you could use an on-exit macro attached to each formfield to validate the input.

There were a few problems:
1. the empty paragraph separating table I.A from the one above had been deleted, with the result that the two tables became joined. That put all the cell references off.
2. the averaging formula for Table II spanned too few rows (2-4 instead of 2-6)
3. the results for the 'Counselling' and 'Professional/Interpersonal Skills' scores in the 'Summary of Competency Scores' table were swapped.
Fixed in the attachment. I've also modified the formula fields in the 'Summary of Competency Scores' table slightly, to show where no score has been recorded and, in case you believe you need it, this allows the kind of calculation of the average of the comptency averages for which you originally sought help - the simpler form of that calculation which is now possible is in Column C.

[Gary Frieder]

Melanie,

Just tacking on one other suggestion, to Paul's amazingly helpful advice:

Not only will the text formfields permit negative values to be entered but they will also allow non-integer values like "3.22".

If you want to enforce that only the numbers 1 - 7 can get entered, a better option might be to use drop-down fields throughout, rather than text fields. You can make a single empty space the first entry in the list, to allow for a field to be left empty.

Gary

[macropod]

Melanie,

Just tacking on one other suggestion, to Paul's amazingly helpful advice:

Not only will the text formfields permit negative values to be entered but they will also allow non-integer values like "3.22".

If you want to enforce that only the numbers 1 - 7 can get entered, a better option might be to use drop-down fields throughout, rather than text fields. You can make a single empty space the first entry in the list, to allow for a field to be left empty.

Gary

Hi Gary,

A significant problem with using dropdown formfields is that you can't then sum their values using table formulae (eg =SUM(B3:B6) - each dropdown field needs to have a (unique) bookmark name and the formulae then need to refer to those names. As for the whole-number issue, that can easily enough be handled with a text formfield by setting the number length & format in the text formfields (eg set Type to 'number', the Maximum Length to '1' and the Number Format to '0'). The same solution effectively prevents negative numbers from being input.

[Gary Frieder]

Paul,

Thanks for pointing this out. When I've done scoring templates like these, I've used dropdowns combined with macros (triggered either OnExit or sometimes with an overall "Update Calculations" button or menu item) to perform and update calculations, so I wasn't aware of the limitation with regard to SUM.

Gary

[Melanie]

Thank you both for your timely responses. I have forwarded Paul's template to the professors and I am waiting to hear their response.

Thank you again.

Melanie