Average calculation when a field is left blank

[theroundpen]

Good afternoon,

I have created an employee evaluation form using a text field with the
"Number" type. Each field has been given a bookmark name. In the "Total
Score" field is a "Calculation" type text field with the following formula:

=Average(Values01,Values02,Values03,Values04,Values05,Values06,Values07,Values08)

I really have two questions:

1) Can the acceptable values be limited to .5, 1, 1.5, 2, 2.5, and 3? (I
tried using a drop down list, but couldn't get the calulation to view the
result as a number instead of text, thereby not getting a calculated total).

2) Can a formula be created that will only average the fields that have a
value in them? In other words, there are some questions that might not apply
to an individual and therefore no score would be given. As my form currently
stands, a blank field calculates in the average as though it were a zero.

Any help will be GREATLY appreciated!

Carla McDonald

 

[Doug Robbins - Word MVP on news.microsoft.com]

You would need to run a macro on exit from the formfields to calculate the
result and display it in the formfield where you want it to be displayed

Dim i as long, j as long
Dim Total as Double
Dim ffname as string
Total = 0
i = 0
With ActiveDocument
For j = 1 to 8
ffname = "Values0" & j
If .FormFields(ffname).Result <> 0 then
Total = Total + .FormFields(ffname).Result
i = i + 1
End if
Next j
.FormFields("Average").Result = Total/i
End With

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[TheRoundPen]

Thank you, Doug, for your quick response. I have attempted your suggestion
and get a "Type Mismatch" error on this line:

If .FormFields(ffname).Result <> 0 Then

 

[Doug Robbins - Word MVP on news.microsoft.com]

That was untested. The following works:

Dim i As Long, j As Long
Dim Total As Double
Dim ffname As String
Total = 0
i = 0
With ActiveDocument
For j = 1 To 8
ffname = "Values0" & j
If Val(.FormFields(ffname).result) <> 0 Then
Total = Total + .FormFields(ffname).result
i = i + 1
End If
Next j
.FormFields("Average").result = Total / i
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[macropod]

Hi theroundpen,

With Dropdown formfields, you can certainly limit the possible choices to 0.5, 1.0, 1.5, 2.5, 2.5, and 3.0.

As for the averaging formula field code, you can then use:
{=Average({REF Value01},{REF Value02},{REF Value03},{REF Value04},{REF Value05},{REF Value06},{REF Value07},{REF Value08})}

No vba required.

Note that Dropdown formfields require a default value to be set - which is consistent with your desire to limit the possible
choices. However, if you want to make the default 0 or blank (either of which then becomes a 'valid' choice) but exclude those
results from the average, a more complex field code would be needed

 

[TheRoundPen]

Thanks again! That worked perfectly except for one small issue. If the
first field is left blank the code stops with the following error:

Run Time Error '6':
Overflow

(obviously because of a divide by zero condition) Is there any way to
capture the error and continue to the next field?

 

[Doug Robbins - Word MVP on news.microsoft.com]

Dim i As Long, j As Long
Dim Total As Double
Dim ffname As String
Total = 0
i = 0
With ActiveDocument
For j = 1 To 8
ffname = "Values0" & j
If Val(.FormFields(ffname).result) <> 0 Then
Total = Total + .FormFields(ffname).result
i = i + 1
End If
Next j
If i > 0 then
.FormFields("Average").result = Total / i
Else
.FormFields("Average").result = 0
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[TheRoundPen]

PERFECTION! Thank you, Doug, you're the best!

Dim i As Long, j As Long
Dim Total As Double
Dim ffname As String
Total = 0
i = 0
With ActiveDocument
For j = 1 To 8
ffname = "Values0" & j
If Val(.FormFields(ffname).result) <> 0 Then
Total = Total + .FormFields(ffname).result
i = i + 1
End If
Next j
If i > 0 then
.FormFields("Average").result = Total / i
Else
.FormFields("Average").result = 0
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com