?

[pierre]

can someone tell me the meanining of this formula :

=CHOOSE((B9="X")+2*(C9="Y")+1; "neither"; 5; 6;"both")

WHAT DOES THIS FUNCTION MEAN ?? : 2*(C9="Y")+1

IS THERE ANY OTHER VARIATIONS LIKE THIS FORMULA ???

 

[Dave Peterson]

(c9="Y")
will return true or false.

But by adding 1 to the True/False, it'll make excel treat True as 1 and false as
0.

So 2*(c9="y")
will return
2 or 0 (2*true or 2*false) or (2*1 or 2*0)

Then 1 is added to the result.
So you end up with: 3 or 1

 

[Rick Rothstein \(MVP - VB\)]

The CHOOSE function evaluates the first argument, which in your example
is...

(B9="X")+2*(C9="Y")+1

in order to get an index number (1, 2, 3, etc.). Whatever the index value
evaluates to, that item number in the list that follows is retrieved. So,
for you example, if the above expression evaluates to 1, the text string
"neither" is returned; if it evaluates to 2, the number 5 is returned; if it
evaluates to 3, 6 is returned; and if it evaluates to 4, the text string
"both" is returned. Now, let's look at how the above expression is
evaluated. Both ((B9="X") and (C9="Y") are what are called "logical
expressions"... they will either evaluate to TRUE or FALSE. When used in a
mathematical expression, Excel treats TRUE as the number 1 and FALSE as the
number 0. So, if B9 is not "X", it will evaluate to 0 and if it does equal
"X", it will evaluate to 1, in the mathematical expression. The same with
the second logical expression... if C9 does not equal "Y", it evaluates to 0
and if it does equal "Y", it evaluates to 1. The 2* part makes sure that the
two logical expressions do not make the mathematical expression evaluate to
the same non-zero index number. That is, (B9="X") will contribute only 0 or
1 in the mathematical expression, 2*(C9="Y") will only contribute 0 or 2 to
the expression. Therefore, the four possible results are 0,1,2 or 3
depending on how the logical expressions evaluate. However, the CHOOSE
function requires its index values start number at 1, so the mathematical
expression add 1 to the result to bump the 0,1,2 or 3 return values to 1,2,3
or 4 and that value is used to retrieve the items from the return list part
of the CHOOSE function.

Rick

 

[Pete_UK]

The first term (B9="X") is equivalent to IF(B9="X",TRUE,FALSE), but
the TRUE and FALSE will get converted to 1 or 0 through the addition,
so effectively this will return 1 or 0 depending on the value of B9.

The second term 2*(C9="Y") is similar - it will return 2 or 0
depending on the value of C9.

So, the first parameter of the CHOOSE function, (B9="X")+2*(C9="Y")+1,
can take on these values:

B9<>"X" and C9<>"Y" returns 1
B9="X" and C9<>"Y" returns 2
B9<>"X" and C9="Y" returns 3
B9="X" and C9="Y" returns 4

and the CHOOSE function itself will return "neither" for 1, 5 for 2, 6
for 3, or "both" for 4.

Hope this helps.

Pete

 

[Dave Peterson]

But by adding 1 to the True/False, it'll make excel treat True as 1 and false as

0.

should have been
But by multiplying 2 times the True/False, it'll make excel treat True as 1 and
false as 0.

 

[daddylonglegs]

This formula would do the same

=IF(B9="X";IF(C9="Y";both;5);IF(C9="Y";6;"neither"))