| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up 3 cells based on defined value in a row/column location
I have this data. The 17894 in the last row( C17) is the max of C3:F16. That
value is located in D16. Is there a way that I can have a cell produce these results - the # in row 2 ( 101,102, 103 or 104), and the day and date in the A & B column to indicate the max value in this case (17894) is from --- #102 on Wed 6/13 ? A B C D E F 101 102 103 104 Thu 5/31/2007 17485 12375 14821 9613 Fri 6/1/2007 17533 13059 14090 11798 Sat 6/2/2007 11549 14634 Sun 6/3/2007 17106 16079 13112 Mon 6/4/2007 16529 15759 16333 11113 Tue 6/5/2007 15756 15968 12519 14690 Wed 6/6/2007 17214 13197 14304 7644 Thu 6/7/2007 16957 14613 15106 12105 Fri 6/8/2007 14951 10369 14718 5468 Sat 6/9/2007 15027 14302 13777 Sun 6/10/2007 17861 14707 15678 9403 Mon 6/11/2007 17155 14791 10892 10225 Tue 6/12/2007 15942 15021 15156 14240 Wed 6/13/2007 17489 17894 13917 15599 17894 Thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up 3 cells based on defined value in a row/column location
Try these:
B3:B16 = date C3:F16 = rng C17 = a formula (as per your description): =MAX(rng) Assume you want the date in cell B20. For the date use this array formula**: =INDEX(Date,MATCH(TRUE,MMULT(--(rng=C17),TRANSPOSE(COLUMN(rng)^0))0,0)) For the column header (dependent upon the date formula cell B20): =INDEX(C2:F2,MATCH(C17,INDEX(rng,MATCH(B20,Date,0) ,),0)) For the weekday (dependent upon the date formula cell B20): =TEXT(B20,"ddd") Biff "Steve" wrote in message ... I have this data. The 17894 in the last row( C17) is the max of C3:F16. That value is located in D16. Is there a way that I can have a cell produce these results - the # in row 2 ( 101,102, 103 or 104), and the day and date in the A & B column to indicate the max value in this case (17894) is from --- #102 on Wed 6/13 ? A B C D E F 101 102 103 104 Thu 5/31/2007 17485 12375 14821 9613 Fri 6/1/2007 17533 13059 14090 11798 Sat 6/2/2007 11549 14634 Sun 6/3/2007 17106 16079 13112 Mon 6/4/2007 16529 15759 16333 11113 Tue 6/5/2007 15756 15968 12519 14690 Wed 6/6/2007 17214 13197 14304 7644 Thu 6/7/2007 16957 14613 15106 12105 Fri 6/8/2007 14951 10369 14718 5468 Sat 6/9/2007 15027 14302 13777 Sun 6/10/2007 17861 14707 15678 9403 Mon 6/11/2007 17155 14791 10892 10225 Tue 6/12/2007 15942 15021 15156 14240 Wed 6/13/2007 17489 17894 13917 15599 17894 Thanks, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up 3 cells based on defined value in a row/column location
I forgot something:
For the date use this array formula**: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "T. Valko" wrote in message ... Try these: B3:B16 = date C3:F16 = rng C17 = a formula (as per your description): =MAX(rng) Assume you want the date in cell B20. For the date use this array formula**: =INDEX(Date,MATCH(TRUE,MMULT(--(rng=C17),TRANSPOSE(COLUMN(rng)^0))0,0)) For the column header (dependent upon the date formula cell B20): =INDEX(C2:F2,MATCH(C17,INDEX(rng,MATCH(B20,Date,0) ,),0)) For the weekday (dependent upon the date formula cell B20): =TEXT(B20,"ddd") Biff "Steve" wrote in message ... I have this data. The 17894 in the last row( C17) is the max of C3:F16. That value is located in D16. Is there a way that I can have a cell produce these results - the # in row 2 ( 101,102, 103 or 104), and the day and date in the A & B column to indicate the max value in this case (17894) is from --- #102 on Wed 6/13 ? A B C D E F 101 102 103 104 Thu 5/31/2007 17485 12375 14821 9613 Fri 6/1/2007 17533 13059 14090 11798 Sat 6/2/2007 11549 14634 Sun 6/3/2007 17106 16079 13112 Mon 6/4/2007 16529 15759 16333 11113 Tue 6/5/2007 15756 15968 12519 14690 Wed 6/6/2007 17214 13197 14304 7644 Thu 6/7/2007 16957 14613 15106 12105 Fri 6/8/2007 14951 10369 14718 5468 Sat 6/9/2007 15027 14302 13777 Sun 6/10/2007 17861 14707 15678 9403 Mon 6/11/2007 17155 14791 10892 10225 Tue 6/12/2007 15942 15021 15156 14240 Wed 6/13/2007 17489 17894 13917 15599 17894 Thanks, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up 3 cells based on defined value in a row/column loca
Perfect solution.
Thanks so much. Steve "T. Valko" wrote: I forgot something: For the date use this array formula**: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "T. Valko" wrote in message ... Try these: B3:B16 = date C3:F16 = rng C17 = a formula (as per your description): =MAX(rng) Assume you want the date in cell B20. For the date use this array formula**: =INDEX(Date,MATCH(TRUE,MMULT(--(rng=C17),TRANSPOSE(COLUMN(rng)^0))0,0)) For the column header (dependent upon the date formula cell B20): =INDEX(C2:F2,MATCH(C17,INDEX(rng,MATCH(B20,Date,0) ,),0)) For the weekday (dependent upon the date formula cell B20): =TEXT(B20,"ddd") Biff "Steve" wrote in message ... I have this data. The 17894 in the last row( C17) is the max of C3:F16. That value is located in D16. Is there a way that I can have a cell produce these results - the # in row 2 ( 101,102, 103 or 104), and the day and date in the A & B column to indicate the max value in this case (17894) is from --- #102 on Wed 6/13 ? A B C D E F 101 102 103 104 Thu 5/31/2007 17485 12375 14821 9613 Fri 6/1/2007 17533 13059 14090 11798 Sat 6/2/2007 11549 14634 Sun 6/3/2007 17106 16079 13112 Mon 6/4/2007 16529 15759 16333 11113 Tue 6/5/2007 15756 15968 12519 14690 Wed 6/6/2007 17214 13197 14304 7644 Thu 6/7/2007 16957 14613 15106 12105 Fri 6/8/2007 14951 10369 14718 5468 Sat 6/9/2007 15027 14302 13777 Sun 6/10/2007 17861 14707 15678 9403 Mon 6/11/2007 17155 14791 10892 10225 Tue 6/12/2007 15942 15021 15156 14240 Wed 6/13/2007 17489 17894 13917 15599 17894 Thanks, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Looking up 3 cells based on defined value in a row/column loca
You're welcome. Thanks for the feedback!
Biff "Steve" wrote in message ... Perfect solution. Thanks so much. Steve "T. Valko" wrote: I forgot something: For the date use this array formula**: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "T. Valko" wrote in message ... Try these: B3:B16 = date C3:F16 = rng C17 = a formula (as per your description): =MAX(rng) Assume you want the date in cell B20. For the date use this array formula**: =INDEX(Date,MATCH(TRUE,MMULT(--(rng=C17),TRANSPOSE(COLUMN(rng)^0))0,0)) For the column header (dependent upon the date formula cell B20): =INDEX(C2:F2,MATCH(C17,INDEX(rng,MATCH(B20,Date,0) ,),0)) For the weekday (dependent upon the date formula cell B20): =TEXT(B20,"ddd") Biff "Steve" wrote in message ... I have this data. The 17894 in the last row( C17) is the max of C3:F16. That value is located in D16. Is there a way that I can have a cell produce these results - the # in row 2 ( 101,102, 103 or 104), and the day and date in the A & B column to indicate the max value in this case (17894) is om --- #102 on Wed 6/13 ? A B C D E F 101 102 103 104 Thu 5/31/2007 17485 12375 14821 9613 Fri 6/1/2007 17533 13059 14090 11798 Sat 6/2/2007 11549 14634 Sun 6/3/2007 17106 16079 13112 Mon 6/4/2007 16529 15759 16333 11113 Tue 6/5/2007 15756 15968 12519 14690 Wed 6/6/2007 17214 13197 14304 7644 Thu 6/7/2007 16957 14613 15106 12105 Fri 6/8/2007 14951 10369 14718 5468 Sat 6/9/2007 15027 14302 13777 Sun 6/10/2007 17861 14707 15678 9403 Mon 6/11/2007 17155 14791 10892 10225 Tue 6/12/2007 15942 15021 15156 14240 Wed 6/13/2007 17489 17894 13917 15599 17894 Thanks, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting based on location & dates | Excel Discussion (Misc queries) | |||
| Counting based on location & dates | Excel Discussion (Misc queries) | |||
| Counting based on location & dates | Excel Discussion (Misc queries) | |||
| Counting based on location & dates | Excel Discussion (Misc queries) | |||
| user-defined chart template location | Charts and Charting in Excel | |||
Linear Mode
