Secrets and mysteries of the CHOOSE function

[bines53]

Hello friends!

Have you ever encountered a function CHOOSE in such a way ?

=SUM(F1:CHOOSE(1,F5,F6,F7,F8))
=SUM(F1:CHOOSE(1,H2,H3,H4,H5))
=COUNT(F1:CHOOSE(1,H2,H3,H4,H5))
=COUNT(A1:CHOOSE(1,F5,F6,F7,F8))

For me at least, there is a discovery !

 

[Hui]

Yes Choose can be used in a number of ways like you have shown
One issue is that typically the first parameter is a variable

Why?
Because CHOOSE(1,F5,F6,F7,F8) has hard coded the function to return the first value ie: F5 and so always will

It should be like
CHOOSE(A1,F5,F6,F7,F8)
Where A1 can have the values 1 to 4

 

[Hui]

One of the great uses of Choose is to allow VLookup to reference columns left of the lookup column !, Yes it can be done.

You can read about how that works here:
http://chandoo.org/wp/2012/09/06/formula-forensics-no-028/

 

[bines53]

Hi Hui,

In the example I sent, change from F1 to A1, you will see the magic !
Also, with integration with other functions !

I have a request to you, if you can keep the post Sticky.
It can also be useful in this wonderful forum.

Thank you!

David

 

[Hui]

David

I understand the function but don't agree with using 1 as the first parameter in Choose
Changing F1 to A1 doesn't change that issue as using 1 simply Locks the last part of the range to F5

If you want to write a Post on the Choose function please feel free to write one and submit to Chandoo or I in Word format

 

[bines53]

Hi Hui,

I mean, change from F1 to A1, on functions sum or count.
Coverage, is not limited of columns and rows.

About writing a post, I'm not sure, my English is good enough .I will try to check this evening, if I can do it.

David

 

[bines53]

Of course, even so,Also possible.
=SUM(CHOOSE(1,A1,A2,A3,A4):F11)
=COUNT(CHOOSE(1,A1,A2,A3,A4):F11)

The arguments have to choose from a table working with her.

These examples, arguments were chosen out of the table, and there's no logic.
=SUM(F1:CHOOSE(1,H2,H3,H4,H5))
=COUNT(F1:CHOOSE(1,H2,H3,H4,H5))

 

[bines53]

A small example with the CHOOSE function, as compared to other

COUNTIF Last 5