+ Reply to Thread
Results 1 to 6 of 6

CHOOSE Function returns "#VALUE!" when using an array

  1. 03-25-2013, 02:25 PM #1
    wolf83
    wolf83 is offline
    Registered User
    Join Date
    03-25-2013
    Location
    Anywhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    CHOOSE Function returns "#VALUE!" when using an array

    the function: =CHOOSE(3,B31:B34) returns "#VALUE!"

    BUT

    the function =CHOOSE(3,B31,B32,B33,B34) returns what's in cell B33

    this truly has me baffled.

    Please keep in mind my original formula is very complicated, and "CHOOSE" is the easiest and most elegant way to do what i need to do. In other words, i am not simply choosing between 4 values, and the "choose" function is embedded into other functions that work when i type out the array, so please do not be mislead by this.

    at this point, it's not really a matter of solving my problem, because i know i can just type out the array. instead, i want to understand what's going on, and why i can't do what the office help file says i should be able to do.

    i'm running excel 2010.
    any thoughts?
    thanks!
    W.
    Reply With QuoteRegister To Reply
  2. 03-25-2013, 02:55 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: CHOOSE Function returns "#VALUE!" when using an array

    thats the way choose works use lookup instead =LOOKUP(X,ROW(A1:A4),(B31:B34))
    or=LOOKUP(X,{1,2,3,4},(B31:B34))
    you can use ranges in choose
    eg
    SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10)) would give the sum of b1:b10
    SUM(CHOOSE(3,A1:A10,B1:B10,C1:C10)) would give the sum of c1:c10
    so
    =sum(CHOOSE(3,B31:B34) ) doesn't work there is no option 3
    Last edited by martindwilson; 03-25-2013 at 03:01 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 03-25-2013, 02:55 PM #3
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: CHOOSE Function returns "#VALUE!" when using an array

    wolf, welcome to the forum.

    From having a quick look on the internet, it would appear that selecting a range of cells (ie B31:B34) is only appropriate when you are combining CHOOSE with another function, eg SUM. Any examples I've seen where the CHOOSE function is used in isolation, the individual cells are listed/named individually (ie B31,B32,B33,B34)
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
    Reply With QuoteRegister To Reply
  4. 03-25-2013, 03:01 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CHOOSE Function returns "#VALUE!" when using an array

    The reason you get the error is because you don't have enough arguments to match the index number.

    =CHOOSE(3,B31:B34)

    That expects there to be 3 value arguments but you only have 1, B31:B34.

    You seem to think that =CHOOSE(3,B31:B34) should return the value of cell B33 but that is not the case.

    If that's what you want then use the INDEX function:

    =INDEX(B31:B34,3)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 03-25-2013, 04:54 PM #5
    wolf83
    wolf83 is offline
    Registered User
    Join Date
    03-25-2013
    Location
    Anywhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: CHOOSE Function returns "#VALUE!" when using an array

    thanks to all for the corrections and the directions. not only has my question been answered, but i got several really good ideas as well. thanks!
    Reply With QuoteRegister To Reply
  6. 03-25-2013, 09:37 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CHOOSE Function returns "#VALUE!" when using an array

    You're welcome. We appreciate the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1