Creating a Form

[natalie1980]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

I am trying to create a form for my colleague to fill in when he goes to site's to evaluate there feasibility. I have used combo boxes as most of the questions he will need to answer require a selection from the list I have created however I thought you could average all the combo boxes to give you an answer. Basically he will mark them 1-100 on various things then I need to add in a box that works out the average so that we can see if it is feasible or not to go ahead. Any help would be appreciated. I'm sure I did it the other day but can't remember how.

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

I am trying to create a form for my colleague to fill in when he goes to
site's to evaluate there feasibility. I have used combo boxes as most of the
questions he will need to answer require a selection from the list I have
created however I thought you could average all the combo boxes to give you
an answer. Basically he will mark them 1-100 on various things then I need to
add in a box that works out the average so that we can see if it is feasible
or not to go ahead. Any help would be appreciated. I'm sure I did it the
other day but can't remember how.

One way (that perhaps sounds more complicated than it is):

Format the Comboboxes Input range to your list, and Cell LInk (Format
Control dialog, Control pane) to cells in a range (which you can then
hide, if desired). Note that the value in the linked cell will be the
*index* number of the list (the first item will be 1, the 10th item will
be 10, regardless of the format or numeric value in the cells.

For instance, say your input range is:

J
1 100
2 80
3 60
4 40
5 20

If the selected value in the combobox is 60, the value returned to the
linked cell will be 3.

Use INDEX() to generate the actual value from the list. For instance,
with three comboboxes, the Input Range would be J1:J5, and the linked
cells, say, Y1, Y2, and Y3:

Y Z
1 <combobox1 value> =INDEX($J$1:$J$5,Y1)
2 <combobox2 value> =INDEX($J$1:$J$5,Y2)
3 <combobox3 value> =INDEX($J$1:$J$5,Y3)
4 =AVERAGE(Z1:Z3)

Then use Z4 as your input range to your summary combobox.