Calculate results of dropdown boxes in table

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.

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Elaine" wrote in message
news
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.

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" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Elaine" wrote in message
news
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.


.

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Elaine" wrote in message
...
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" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Elaine" wrote in message
news
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.


.

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Graham Mayor" wrote in message
...
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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Elaine" wrote in message
...
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" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Elaine" wrote in message
news 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.


.

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

On 21/04/2010 08:53, Graham Mayor wrote:
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.

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Graham Mayor" wrote in message
...
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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Graham Mayor" wrote in message
...
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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Elaine" wrote in message
...
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" wrote:

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"Elaine" wrote in message
news 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.


.

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

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Peter Jamieson" wrote in message
...
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

On 21/04/2010 08:53, Graham Mayor wrote:
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.

Yes, plenty of options there!

Peter Jamieson

http://tips.pjmsn.me.uk

On 21/04/2010 15:43, Graham Mayor wrote:
Peter
Your solution crossed with my latest revision, which adopts a slightly
different approach

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" wrote:

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

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Peter Jamieson" wrote in message
...
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

On 21/04/2010 08:53, Graham Mayor wrote:
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.



.