INDEX Error? Assigning a value after finding a max number

**aeaton** · 05-15-2010, 06:44 PM

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

**pike** · 05-15-2010, 07:37 PM

hi aeaton
The first part try
=INDIRECT(ADDRESS(1,MATCH(MAX(A2:e2),A2:e2,0)))

**pike** · 05-15-2010, 07:41 PM

or if you dont like to use indirect
=CHOOSE(MATCH(MAX(A2:e2),A2:e2,0), "walk","bus","bike","drive","car")

**aeaton** · 05-15-2010, 10:41 PM

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

**pike** · 05-16-2010, 12:43 AM

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")

**DonkeyOte** · 05-16-2010, 01:29 AM

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 ?

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.

**aeaton** · 05-16-2010, 10:59 AM

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

**DonkeyOte** · 05-17-2010, 08:22 AM

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.