+ Reply to Thread
Results 1 to 8 of 8

INDEX Error? Assigning a value after finding a max number

  1. #1
    Registered User
    Join Date
    05-15-2010
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    INDEX Error? Assigning a value after finding a max number

    Hello everyone,

    My first time on this forum so please forgive any protocol mistakes I'm about to make. Thanks in advance for any help!

    I have a worksheet that holds all of the answers to a commute survey I conducted a few months ago.

    In Row 1, each question is listed in its entirety and has its own column.
    Respondents' answers begin on Row 2.

    I am trying to assign each respondent a primary mode of transportation for their "to work" and "home" trips. Imagine that A - E represents their "to work" answers in minutes.

    So, if for instance, someone walked to a bus stop it might look like this on Row 2. What I would like to do is assign "BUS" to F2

    A | B | C | D | E | F
    Row 1 WALK | BUS | BIKE | DRIVE | CARPOOL |
    Row 2 10 | 25 | 0 | 0 | 0 | BUS
    Row 3 0 | 10 | 15 | 0 | 0 | BIKE


    Apologies for the formatting.

    I was having success with the following formula but now I'm getting an Err508:
    =INDEX($A$1:$E$1,1,MATCH(MAX(A2:E2),E2:E2,0))

    Any suggestions?

    Also, in Row 1 the entire question is restated from the survey - not like it's shown here. This is what Cell B1 looks like in my sheet:

    "6. How Did You Get to Work Today (If today was not a normal commute day, please describe a typical day) [BUS]"

    Is it easier to pluck out BUS from the statement in Row 1 or is there a way for me to assign "BUS" or any other transport type after the statement is evaluated? For those who are willing I appreciate your thoughts on how to do this as well.

    After I assign everyone a primary mode I will sum them all up.

    Looking forward to your replies!

    Thanks again,

    Angela

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: INDEX Error? Assigning a value after finding a max number

    hi aeaton
    The first part try
    =INDIRECT(ADDRESS(1,MATCH(MAX(A2:e2),A2:e2,0)))
    Last edited by pike; 05-15-2010 at 08:10 PM. Reason: add 0

  3. #3
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: INDEX Error? Assigning a value after finding a max number

    or if you dont like to use indirect
    =CHOOSE(MATCH(MAX(A2:e2),A2:e2,0), "walk","bus","bike","drive","car")
    Last edited by pike; 05-15-2010 at 08:11 PM. Reason: add 0

  4. #4
    Registered User
    Join Date
    05-15-2010
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: INDEX Error? Assigning a value after finding a max number

    Thanks pike!

    I found that the CHOOSE/MATCH/MAX combo worked but the INDIRECT formula is throwing me the same Err508 - a bracketing error - as the formula I came up with.

    I'm trying to understand why I'm having problems with these other two formulas. The best I can come up with is that the MATCH function is temperamental about nesting formulas?

    Cheers!

    Angela

  5. #5
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,324

    Re: INDEX Error? Assigning a value after finding a max number

    Angela
    did you see the "0" i added to the functions
    =INDIRECT(ADDRESS(1,MATCH(MAX(A2:e2),A2:e2,0)))
    =CHOOSE(MATCH(MAX(A2:e2),A2:e2,0), "walk","bus","bike","drive","car")

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: INDEX Error? Assigning a value after finding a max number

    Quote Originally Posted by aeton
    I was having success with the following formula but now I'm getting an Err508:
    =INDEX($A$1:$E$1,1,MATCH(MAX(A2:E2),E2:E2,0))
    The only Excel issue I see with the above is your MATCH range which should read A2:E2 not E2:E2, however, Err508 implies you're using OpenOffice not Excel - can you confirm ?

    Quote Originally Posted by aeaton
    Also, in Row 1 the entire question is restated from the survey - not like it's shown here. This is what Cell B1 looks like in my sheet:

    "6. How Did You Get to Work Today (If today was not a normal commute day, please describe a typical day) [BUS]"

    Is it easier to pluck out BUS from the statement in Row 1 or is there a way for me to assign "BUS" or any other transport type after the statement is evaluated?
    Is the transport mode always encased within [ ] ?
    Does it always appear at the end of the string ?

    I would make the point that this is an Excel forum rather than OpenOffice (the two are not the same) - I don't use the latter but others will so you should still get a resolution.

  7. #7
    Registered User
    Join Date
    05-15-2010
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: INDEX Error? Assigning a value after finding a max number

    DonkeyOte,

    You caught me. I've been working on this problem at work and at home. At home I am using Open Office and at work Excel. Since I plan to do most of my work in Excel I joined this forum.

    Perhaps it's an Open Office problem. As you suspected the positive results I got were both on Excel and I'm happy to keep it simple.

    As for the extraction question, yes, the transport mode is always encased within [ ] and always at the end of the string as shown in the example.

    I will check both the debugging suggestions you and pike make on Excel - Many Thanks to you both!

    -Angela

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: INDEX Error? Assigning a value after finding a max number

    Quote Originally Posted by aeaton
    As for the extraction question, yes, the transport mode is always encased within [ ] and always at the end of the string as shown in the example.
    You should find you can extract the transport mode (in Excel) using something along the lines of:

    =SUBSTITUTE(REPLACE(B$1,1,FIND("[",B$1&"["),""),"]","")

    where B1 contains the string of interest.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1