Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula(s)- not working, need some help please (problem with an 'AND') | Excel Worksheet Functions | |||
array formula problem | Excel Worksheet Functions | |||
Sumproduct / Max array formula problem | Excel Worksheet Functions | |||
Problem with array and msgbox | Excel Worksheet Functions | |||
Problem with array and msgbox | Excel Worksheet Functions |