Hi,

I am creating a model that has 4 inputs (input 1, input 2, etc). On
the column next to each input, I have set up check boxes. What I would
like to do is: Depending on what combination of boxes are checked or
unchecked, I would like like the next column to show a predetermined
weighting figure.

For example, if all 4 boxes are checked off, I would like the
respective weightings on the next column to show 15,30,35,20. If say,
only box one and box three was checked off, it would show 20, 80. If
only Box one and two were checked off, it would show 25,75. And so
on..

With all the potential permutations, what would be the best way to do
this? I've set up a grid on a separate worksheet to show what
weightings are to be assigned depending on what items are checked off
but I'm not sure how I can tie this in ..

Hope its not too confusing!

Thanks


--
inoexcel
------------------------------------------------------------------------
inoexcel's Profile: http://www.excelforum.com/member.php...o&userid=33055
View this thread: http://www.excelforum.com/showthread...hreadid=528699

You only have 16 possible permutations of 4 checkboxes, so it would be
quite easy to set up a table to show these. Using the examples you
quote, you have:

1 1 1 1 (15) 20 35 30 15
0 1 0 1 ( 5) 80 20
0 0 1 1 ( 3) 75 25

The numbers in brackets indicate that you can treat the settings in the
checkboxes as bits and therefore convert them collectively into a
number. It would then be quite easy to use this as a normal lookup
table with 16 rows which can return 4 different input values.

Hope this helps.

Pete

Hi Pete,

Thanks for the reply. It certainly helps in pointing a newbie in the
right direction. I'm a little confused on how to treat the settings in
the
checkboxes as bits and converting them collectively into a
number. Will I have to enter a formula for each of the cells beside
the respective checkboxes?

Thanks again


--
inoexcel
------------------------------------------------------------------------
inoexcel's Profile: http://www.excelforum.com/member.php...o&userid=33055
View this thread: http://www.excelforum.com/showthread...hreadid=528699

You should check out Excel Help for more information about "check_box",
particularly "About contol properties ..."

Essentially, you link each check box to a particular cell, and that
cell will contain True or False, which can be interpreted as 1 or 0. If
you have 4 of these, say A1 to A4, then a formula to convert these into
a number is:

=8*A4 + 4*A3 + 2*A2 +1*A1

which takes A4 as the most significant bit. This number is what I gave
in brackets above. Let's say this formula is in B1, then in C1 you
might have a formula like:

=VLOOKUP(B1,grid,5,0)

where grid is the table of 16 rows by 5 columns showing the
permutations as indicated above - this formula would get the input
value from the fifth column, which I assume is what you refer to as
Input 1. In D1 you would have this formula:

=VLOOKUP(B1,grid,4,0)

which would get the data from column 4 and would represent input value
2. Similar formulae in E1 and F1, with just the 3rd parameter changing,
would return input 3 and input 4.

I haven't a clue what you want to do with these, but I hope this helps
so far.

Pete

Pete, thats perfect! It led me to create exactly what I wanted!

Thanks for your help :)


--
inoexcel
------------------------------------------------------------------------
inoexcel's Profile: http://www.excelforum.com/member.php...o&userid=33055
View this thread: http://www.excelforum.com/showthread...hreadid=528699