Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
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. |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#8
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. |
#9
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. |
#10
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#11
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#12
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. |
#13
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. |
#14
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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 |
#15
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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 |
#16
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#17
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#18
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
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. . |
#19
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
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. . |
#20
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
#21
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Calculate results of dropdown boxes in table
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. . |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown Boxes: More than 25 entries? | Microsoft Word Help | |||
Why doesn't Word show dropdown box results in form when emailed? | Microsoft Word Help | |||
Tutorial for Dropdown boxes in a Word table on the Web? | New Users | |||
inserting text from the results of a dropdown box | Mailmerge | |||
Hyperlinks in Word Dropdown Boxes | Microsoft Word Help |