• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Choose formula doesn't return a value

G

GN0001

Member
I have values from E2 to E5: 4,5,6,9

I want to use choose function:

=Choose(1,$E$2:$E$5) returns 2

=Choose(2,$E$2:$E$5) returns # value

=Choose(3,$E$2:$E$5) returns # value

=Choose(4,$E$2:$E$5) returns # value

why it returns #value when the index is 2, 3 or 4? Have I entered the formula correct?

Thank you,
 
Guity


Try using the format of

=CHOOSE(2,$E$2,$E$3,$E$4,$E$5)


By using a range you are telling Excel to treat the range as an array of values for the first Choose position and then subsequent positions 2-4 are empty


To show that it is taking E2:E5 as an array, paste =CHOOSE(1,$E$2:$E$5) into F2 or G2 and then copy down 4 cells
 
Hui,

I watched two videos on Choose formulas and with what you said, I completely understand the formula. But what I don't understand, I guess is arrays.


I did paste =Choose(1,$E$2:$E$5) into F2 or G2 and I copied it down 4 cells. I got an answer like this:

2 2 2 2

3 3 3 3

4 4 4 4

9 9 9 9

I can't visualize what happens on first, second, third and fourth line: We say: go and select the first range: which is: {2,3,4,9}. Why does it return 2 for value 2, 3 for value 3, and so on. I guess my problem goes back to array not "choose function". We say to select the first range of values, How does the function know as to which value should be returned from the range of values? It returns 2, not 3 for the first line?

Thank you for all your help.

Guity
 
Guity

By putting an array into the first value of the choose function the array will only be used when the Choose lookup value is 1


By copying the choose down with the array you are enabling the array to return subsequent values of the array based on the Relativity to the first formula, Dont ask me how it works it just does.


For your purposes you should use =CHOOSE(lookup value,$E$2,$E$3,$E$4,$E$5)
 
Hui,

I understood the formula very well; moreover, I learned how an array acts in choose function.

Thank you for you help.

Regards,

Guity
 
You must log in or register to reply here.
Back
Top