Calculate results of dropdown boxes in table

[Elaine]

I am working on a form (in Word 2002!) that has dropdown boxes in a column of
table cells where the user selects either 1, 2, 3 or leaves blank. At the
bottom of the column I'd like to have a calculation that averages the results
of the those dropdown boxes. Is this possible? Currently my formula is
=AVERAGE(H2:H5). It appears that the formula doesn't recognise whatever's in
the dropdown boxes.
Any help much appreciated.

 

[Graham Mayor]

You cannot have blanks in a dropdown field. You must either enter a zero or
a series of spaces for a blank entry. A zero would be better as it avoids
the need to error trap the spaces. Either way the average will be an average
of the four cells. You will also need to reference the bookmark names of
the dropdown fields (here Dropdown1 to Dropdown4) rather than the containing
cells. Check the calculate on exit check boxes of (at least) the last
contributing field and the use would have to tab out of that field to force
the calculation.

{ =Average ({ IF{ Dropdown1} <> " *" "{ Dropdown1 }" "0" }, { IF{ Dropdown2}
<> " *" "{ Dropdown2 }" "0" }, { IF{ Dropdown3} <> " *" "{ Dropdown3 }"
"0" }, { IF{ Dropdown4} <> " *" "{ Dropdown4 }" "0" }) \# "0" }

All the bracket pairs {} are inserted with Ctrl+F9

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Elaine]

Thanks very much for that. I have to have blanks (they are spaces) rather
than zeros, and if the user leaves a field blank then the average is not to
include that field. I'll have a play around with it and see how I get on.
Cheers.

 

[Graham Mayor]

If the average is not to include blank fields then it may not be possible to
do this without using macros - at least I cannot think of a way. However it
has to be said that mathematics was never my stongest subject and our
resident field expert is away on a trip and may not pop in to this forum for
a while.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

Thinking further on this, the following macro will apply the average of the
completed dropdown fields to a document variable varAverage. You can use a
docvariable field to reproduce the content of that variable.

Sub AverageOfDropdowns()
Dim strData As String
Dim strNum() As String
Dim i As Single, iNum As Single
Dim iSubT As Single, iAverage As Single
Dim oVars As Variables
Dim DD1 As FormField, DD2 As FormField
Dim DD3 As FormField, DD4 As FormField
strData = ""
With ActiveDocument
Set DD1 = .FormFields("Dropdown1")
Set DD2 = .FormFields("Dropdown2")
Set DD3 = .FormFields("Dropdown3")
Set DD4 = .FormFields("Dropdown4")
Set oVars = .Variables
If InStr(1, DD1.Result, Chr(32)) = False Then
strData = strData & DD1.Result & Chr(44)
End If
If InStr(1, DD2.Result, Chr(32)) = False Then
strData = strData & DD2.Result & Chr(44)
End If
If InStr(1, DD3.Result, Chr(32)) = False Then
strData = strData & DD3.Result & Chr(44)
End If
If InStr(1, DD4.Result, Chr(32)) = False Then
strData = strData & DD4.Result & Chr(44)
End If
If Right(strData, 1) = Chr(44) Then
strData = Left(strData, Len(strData) - 1)
End If
End With
If strData <> "" Then
strNum = Split(strData, Chr(44))
For i = 0 To UBound(strNum)
iNum = Val(strNum(i))
iSubT = iNum + iSubT
Next i
iAverage = iSubT / (UBound(strNum) + 1)
oVars("varAverage").Value = iAverage
Else
oVars("varAverage").Value = "No data"
End If
End Sub

http://www.gmayor.com/installing_macro.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

I expect there is a simpler formulation, but I think the following will
do it, assuming the possible values of dropdown1 to 4 are " ", 1, 2, 3.

{ SET c { = { QUOTE "sign(0{ dropdown1 })+sign(0{ dropdown2 }+sign(0{
dropdown3 }+sign(0{ dropdown4 }" } } }{ IF c = 0 "" "{ ={ QUOTE "(0{
dropdown1 }+0{ dropdown2 }+0{ dropdown3 }+0{ dropdown4 })/c" } }" }

The problem with using any of the = field functions like AVERAGE and SUM
is that they require more than one item in the list - i.e. { =SUM(1,2) }
is OK, but {=SUM(1) } and {=SUM(,2) } are not.

By prepending all the dropdown results with "0" we end up with "0 ",
"01", "02", and "04" which can more easily be plugged into {=} field
calculations.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Graham Mayor]

I believe I have resolved the problem using fields. The construction is
complex, but seems to do the business

{ If { Dropdown1 } <> " *" "{ Set A 1 }{ Set W { Dropdown1 } }" "{ Set A
0 }{ Set W 0 }" }{ If { Dropdown2 } <> " *" "{ Set B 1 }{ Set X {
Dropdown2 } }" "{ Set B 0 }{ Set X 0 }" }{ If { Dropdown3 } <> " *" "{ Set C
1 }{ Set Y { Dropdown3 } }" "{ Set C 0 }{ Set Y 0 }" }{ If { Dropdown4 } <>
" *" "{ Set D 1 }{ Set Z { Dropdown4 } }" "{ Set D 0 }{ Set Z 0 }" }{ =
({W } + { X } + { Y } + { Z }) / ({ A } + { B } + { C } + { D })}

It works on the principle of establishing whether the field has a value or
not (assuming at least one space for a blank entry) and setting two
bookmarks for each field thus

{ If { Dropdown1 } <> " *" "{ Set A 1 }{ Set W { Dropdown1 } }" "{ Set A
0 }{ Set W 0 }" }

You can then add the bookmarks W,X,Y & Z and the bookmarks A, B, C & D and
divide the former by the latter to get the average of the completed fields.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

Peter
Your solution crossed with my latest revision, which adopts a slightly
different approach

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

Yes, plenty of options there!

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Elaine]

Thank you so much Graham and Peter. I appreciate all the thought that's gone
into this, but I forgot to mention ... I need the Average result to be
rounded up or down to the nearest 0.5 so anything >.5 rounds up and anything
<.5 rounds down. I'm not sure how to do this. Can you help?

 

[Graham Mayor]

I'm glad I kept the test document

The following should round to the nearest .5

Basically you wrap the field construction from yesterday in a QUOTE field,
then wrap the Qupte field in a set field to create a new bookmark AVG that
contains the average of the fields. Then you use a conditional field to
evaluate whether the decimal is .5. If it is you add a switch to display 1
place of decimals \# 0.0
If not you apply a switch with no decimals \# 0 which will round to the
nearest whole number.

{ Set AVG { QUOTE{ If { Dropdown1 } <> " *" "{ Set A 1 }{ Set W {
Dropdown1 } }" "{ Set A 0 }{ Set W 0 }" }{ If { Dropdown2 } <> " *" "{ Set B
1 }{ Set X { Dropdown2 } }" "{ Set B 0 }{ Set X 0 }" }{ If { Dropdown3 } <>
" *" "{ Set C 1 }{ Set Y { Dropdown3 } }" "{ Set C 0 }{ Set Y 0 }" }{ If {
Dropdown4 } <> " *" "{ Set D 1 }{ Set Z { Dropdown4 } }" "{ Set D 0 }{ Set Z
0 }" }{ = ({W } + { X } + { Y } + { Z }) / ({ A } + { B } + { C } + {
D })} } }{ IF { =MOD({ AVG }, { =INT({ AVG }) }) } = .5 "{ AVG \# 0.0 }"
"{ AVG \# 0 }" }

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[macropod]

Hi Elaine,

Here's a different approach:
1. In each cell, insert a manual line break, followed by a REF field in the form of {REF Dropdown#}, where # is the Dropdown's
bookmark number.
2. Format each cell's row height (or the paragraph formatting of each cell) so that REF field doesn't show.
3. In row 6, use a formula like: {IF{=AVERAGE(H2:H5)}<> "!*" {=ROUND(AVERAGE(H2:H5)*2,0)/2} \# 0.0}
4. Ensure each dropdown formfield has the 'calculate on exit' property set.