Assign number to combination of checkboxes

[Al Delgado]

I have 3 conditions set with checkboxes, I would like to generate a number between 1 and 7 which would show what combination of checkboxes have been checked. Is there a simpler way, rather than use of If statements to do this?

Combinations selected
1 - 1
2 - 2
3 - 3
4 - 1 & 2
5 - 1 & 3
6 - 2 & 3
7 -1 & 2 & 3

 

[NARAYANK991]

Hi Al ,

I think the best way to represent this is using the binary system :

If we say that the weightage of Checkbox1 is 4 , Checkbox2 is 2 and Checkbox3 is 1 , then the following would hold :

Checkbox1 ............. Checkbox2 .............. Checkbox 3

Unchecked ............. Unchecked .............. Checked ------------ 1
Unchecked ............. Checked ................ Unchecked ---------- 2
Unchecked ............. Checked ................ Checked ------------ 3
Checked ............... Unchecked .............. Unchecked ---------- 4
Checked ............... Unchecked .............. Checked ------------ 5
Checked ............... Checked ................ Unchecked ---------- 6
Checked ............... Checked ................ Checked ------------ 7

A checkbox returns TRUE if checked , and FALSE if unchecked ; multiplying these values by 1 returns either 1 or 0.

Using the BIN2DEC function on the combination will return the decimal value of the combination.

For instance , if the cell links for the 3 checkboxes are A1 , B1 and C1 , and if these cells contain the values TRUE , FALSE and TRUE , then the following formula will return 5 :

=BIN2DEC(A1*1 & B1*1 & C1*1)

Narayan

 

[Al Delgado]

Hi Narayan
Very nice, so simple. Thanks for the help.