the CHOOSE function

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp


Just so we're clear on the lingo: a not contiguous rank (with reference to Excel) means a range that cannot be highlighted by mouse alone. In the picture above, to highlight colored cells, I would have to press the button CONTROL key also.

INDEX, SEARCH FOR, VLOOKUP Y HLOOKUP all require contiguous references. They refer to lists, row vectors, column vectors y / or matrices. CHOOSE It is different:

= CHOOSE (index_num, valor1, [value2]…)

As previously explained, this function allows references to different calculations, book references / work sheets, etc. Try to use the function properly. As an example, a popular Excel web portal proposes the next formula to calculate the US Thanksgiving date. UU. Assuming cell A1 has the year:

= DATE (A1,11, CHOOSE (WEEKDAY (DATE (A1,11,1)), 26,25,24,23,22,28,27))

To understand this formula, note that DATE (year, my, day) returns a date and WEEKDAY (date) returns a number from 1 (Sunday) al 7 (Saturday). But doesn't this formula look horrible? It is packed with hard code and contains an unnecessary amount of arguments. The formula could exclude CHOOSE verbigracia.

= DATE (A1,11,28-MOD (WEEKDAY (DATE (A1,11,1)) + 1,7))

Now let me be clear here. I am not saying this is a simple and transparent formula. test it. They both give the same answer. CHOOSE – and a lot of additional physical code – has been used unnecessarily.

That is not to say that there is not a time or a place to CHOOSE. It is useful when you need to reference cells in different worksheets or in other workbooks. Some argue that it is useful when a calculation needs to be calculated using different methods., p.ej

= CHOOSE (index_num, calculation1, calculation2, calculation3, calculation4)

I do not agree. Let me explain. In the following example, I have created a lookup table in cells E10: E13 what have i called Data (I will explain how to create range names later). All calculations are visible in the worksheet, instead of being hidden in the formula bar. The index_num selection, here referred to as Selection_Number, is entered in the cell E2. The result?

Subscribe to our Newsletter

We will not send you SPAM mail. We hate it as much as you.