not sure how to proceed

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

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

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