View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf[_3_] MyVeryOwnSelf[_3_] is offline
external usenet poster
 
Posts: 56
Default How to choose a qualifier from different set of criteria or combinations

Is this assigning of 1,2,4,8, in anyway related to the
maximum no. of combinations of 4 conditions?


Yes. (--A1--2*B1--4*C1--8*D1) is using binary arithmetic to get a 4-bit number with each bit representing one condition, TRUE or FALSE. The values are 0, 1, 2, 3, 4, ..., 15. If there were 5 conditions, we'd use 1,2,4,8,16 and the values would be 0, 1, 2, 3, 4, ..., 33.

In this situation, we could actually use regular old decimal arithmetic (--A1--10*B1--100*C1--1000*D1) to get values (0, 1, 10, 11, 100, ..., 1111) and the list would be {1111, 1101, 0110, 1011, 0101, 1100}.


I was trying to use the "Choose" function ...


MATCH works well (compared with CHOOSE) only because the decision numbers are small integers.