Array problem - TIA

[S Davis]

Hi everyone,

I've become efficient with using arrays, but can they be used to return
text instead of numbers?

For instance, I have the following set of data:
.......A...........B........................C..................D..................E
1|| Prod. # - Mileage ------------- Open ----------- Close -----------
ID2
2||
7151......1000...............2006-10-20......2006-10-21...........CV
3||
7151......1001...............2006-10-22......2006-10-25...........FB
4|| 7151......1002...............2006-10-22......2006-10-25...........M

I've managed to create a formula that will return the highest mileage
from all Prod.#'s equivalent to 7151, for example. What I would like to
do now is use this information to return the ID2 field for the product
with the highest mileage. In this instance, I would like to return "M".

I'm sure offset could be used somehow, but I'm having difficulties
integrating it with my formula to return the highest mileage.

My formula for that is as follows (I cleaned it up a bit just to make
this easy to understand, it is a bit more dynamic in reality):

=SUM((A2:A10=7151)*(MAX(B2:B10)))/SUM((A2:A10=7151)*1)

Given this formula, can I return the "M" associated with this highest
mileage?

Thanks a lot
-Sean

 

[S Davis]

Hmm. Well, just discovered my 'max' formula doesnt work, so any
suggestions there are welcome too (I was testing it on a small range of
data)

 

[Guest]

If I understand correctly, this array formula ought to pull the label from
column E

=INDEX(E2:E6,MATCH(MAX(IF(A2:A6=7151,B2:B6)),B2:B6))

Since it's an array formula, commit it with Shift-Ctrl-Enter

NOTE: You'll get an error if there is no match for Prod#

 

[Biff]

Try this:

Array entered

=INDEX(E2:E10,MATCH(MAX((A2:A10=7151)*B2:B10),(A2:A10=7151)*B2:B10,0))

=SUM((A2:A10=7151)*(MAX(B2:B10)))/SUM((A2:A10=7151)*1)

You can simplify that to:

=MAX(IF(A2:A10=7151,B2:B10))

Biff

 

[S Davis]

Absolutely perfect. Actually, it solved both my problems.

E-beer for you
-Sean

 

[Biff]

Try that on this data:

7151.....1000
7151...........0
7150.....1000

You need to use a match_type of 0.

Biff

 

[S Davis]

Gotcha, just ran into it. Thanks
-Sean

Try that on this data:

7151.....1000
7151...........0
7150.....1000

You need to use a match_type of 0.

Biff

 

[Guest]

Yup. I got lazy when building it. Good catch

Try that on this data:

7151.....1000
7151...........0
7150.....1000

You need to use a match_type of 0.

Biff

 

[S Davis]

This works really well as well, thanks

Try this:

Array entered

=INDEX(E2:E10,MATCH(MAX((A2:A10=7151)*B2:B10),(A2:A10=7151)*B2:B10,0))


You can simplify that to:

=MAX(IF(A2:A10=7151,B2:B10))

Biff

 

[Biff]

You're welcome!

Biff

This works really well as well, thanks