Calculate results of dropdown boxes in table

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

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



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



.

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.


--
Cheers
macropod
[Microsoft MVP - Word]


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



.