Array problem - TIA
 

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

Array problem - TIA
 

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)

Array problem - TIA
 

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#



"S Davis" wrote:

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

Array problem - TIA
 

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" wrote in message
ups.com...
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

Array problem - TIA
 

Absolutely perfect. Actually, it solved both my problems.

E-beer for you:)
-Sean
Duke Carey wrote:
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#



"S Davis" wrote:

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

Array problem - TIA
 

Try that on this data:

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

You need to use a match_type of 0.

Biff

"Duke Carey" wrote in message
...
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#



"S Davis" wrote:

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

Array problem - TIA
 

Gotcha, just ran into it. Thanks
-Sean

Biff wrote:
Try that on this data:

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

You need to use a match_type of 0.

Biff

"Duke Carey" wrote in message
...
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#



"S Davis" wrote:

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

Array problem - TIA
 

Yup. I got lazy when building it. Good catch

"Biff" wrote:

Try that on this data:

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

You need to use a match_type of 0.

Biff

"Duke Carey" wrote in message
...
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#



"S Davis" wrote:

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

Array problem - TIA
 

This works really well as well, thanks

Biff wrote:
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" wrote in message
ups.com...
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

Array problem - TIA
 

You're welcome!

Biff

"S Davis" wrote in message
oups.com...
This works really well as well, thanks

Biff wrote:
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" wrote in message
ups.com...
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