Array problem - TIA

S

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

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

Duke Carey

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#
 
B

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)
Click to expand...

You can simplify that to:

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

Biff
 
B

Biff

Try that on this data:

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

You need to use a match_type of 0.

Biff
 
D

Duke Carey

Yup. I got lazy when building it. Good catch

Biff said:
Try that on this data:

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

You need to use a match_type of 0.

Biff
Click to expand...
 
S

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
Click to expand...
 
Top