Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
Hi All,
I would like a flexible Formula to find the Maximum value for a specific Month (criteria will vary) and retrieve its corresponding Numeric Label from Column "A". Row1 Col "C" - "N" = Full Date formatted with Custom Month Format "mmm": Jan - Dec. Row2:Row31 Col "C" - "N" = Numeric Values 0 - 200 Row2:Row31 Col "A" Numeric Labels = 1 - 30 Sample Data Layout: Row1 Labels Misc Jan Feb Mar Apr May Jun Row2 1 0 101 102 123 143 136 128 Row3 2 0 120 130 103 87 143 130 Row4 3 0 120 90 60 200 102 88 Row5 4 0 170 88 79 67 141 110 NB. The zero (0) values should be under the column heading Misc. Expected Results: Looking at January, the maximum value is 170 and I would expect Numeric Label 4 to be returned as the correct answer. Looking at February, the maximum value is 130 and I would expect Numeric Label 2 to be returned as the correct answer. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
For Jan use:
=INDEX(A:A,MATCH(MAX(C:C),C:C,FALSE)) for Feb use: =INDEX(A:A,MATCH(MAX(D:D),D:D,FALSE)) for Mar use: =INDEX(A:A,MATCH(MAX(E:E),E:E,FALSE)) etc. -- Gary's Student gsnu200706 "Sam via OfficeKB.com" wrote: Hi All, I would like a flexible Formula to find the Maximum value for a specific Month (criteria will vary) and retrieve its corresponding Numeric Label from Column "A". Row1 Col "C" - "N" = Full Date formatted with Custom Month Format "mmm": Jan - Dec. Row2:Row31 Col "C" - "N" = Numeric Values 0 - 200 Row2:Row31 Col "A" Numeric Labels = 1 - 30 Sample Data Layout: Row1 Labels Misc Jan Feb Mar Apr May Jun Row2 1 0 101 102 123 143 136 128 Row3 2 0 120 130 103 87 143 130 Row4 3 0 120 90 60 200 102 88 Row5 4 0 170 88 79 67 141 110 NB. The zero (0) values should be under the column heading Misc. Expected Results: Looking at January, the maximum value is 170 and I would expect Numeric Label 4 to be returned as the correct answer. Looking at February, the maximum value is 130 and I would expect Numeric Label 2 to be returned as the correct answer. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
If your table is in A1:H5 with Labels in column A and Jan in column C, you
could try: =INDEX(A1:A5,MATCH(MAX(C1:C5),C1:C5,0)) If you don't want to hardcode the range for the month (ie C1:C5), you could try: =INDEX(A1:A5,MATCH(MAX(INDEX(A1:H5,0,MATCH(B11,A1: H1,0))),INDEX(A1:H5,0,MATCH(B11,A1:H1,0)),0)) where B11 contains the month (Jan) . "Sam via OfficeKB.com" wrote: Hi All, I would like a flexible Formula to find the Maximum value for a specific Month (criteria will vary) and retrieve its corresponding Numeric Label from Column "A". Row1 Col "C" - "N" = Full Date formatted with Custom Month Format "mmm": Jan - Dec. Row2:Row31 Col "C" - "N" = Numeric Values 0 - 200 Row2:Row31 Col "A" Numeric Labels = 1 - 30 Sample Data Layout: Row1 Labels Misc Jan Feb Mar Apr May Jun Row2 1 0 101 102 123 143 136 128 Row3 2 0 120 130 103 87 143 130 Row4 3 0 120 90 60 200 102 88 Row5 4 0 170 88 79 67 141 110 NB. The zero (0) values should be under the column heading Misc. Expected Results: Looking at January, the maximum value is 170 and I would expect Numeric Label 4 to be returned as the correct answer. Looking at February, the maximum value is 130 and I would expect Numeric Label 2 to be returned as the correct answer. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
Hi Gary''s Student,
Thank you very much for your time and assistance. Your Formulae does return the correct values. However, I would like only the specific month's result returned to a control sheet and have the option of changing the required month via an input cell. I think the solution provided by JMB works a little better for my requirements and layout. Much appreciated. Cheers, Sam Gary''s Student wrote: For Jan use: =INDEX(A:A,MATCH(MAX(C:C),C:C,FALSE)) for Feb use: =INDEX(A:A,MATCH(MAX(D:D),D:D,FALSE)) for Mar use: =INDEX(A:A,MATCH(MAX(E:E),E:E,FALSE)) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
Hi JMB,
Thank you very much for your time and assistance. I've tweaked your Formula slightly and array entered it (Ctrl+ Shift + Enter). =INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B 11),MONTH(A1:H1),0))),INDEX (A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0)) Very much appreciated. Cheers, Sam JMB wrote: If your table is in A1:H5 with Labels in column A and Jan in column C, you could try: =INDEX(A1:A5,MATCH(MAX(C1:C5),C1:C5,0)) If you don't want to hardcode the range for the month (ie C1:C5), you could try: =INDEX(A1:A5,MATCH(MAX(INDEX(A1:H5,0,MATCH(B11,A1 :H1,0))),INDEX(A1:H5,0,MATCH(B11,A1:H1,0)),0)) I got #N/A using Formula as it stands. However, this slight tweak & array entered works for me. =INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B 11),MONTH(A1:H1),0))),INDEX (A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0)) where B11 contains the month (Jan) . -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
You're welcome. I overlooked that part about the date being a full date.
"Sam via OfficeKB.com" wrote: Hi JMB, Thank you very much for your time and assistance. I've tweaked your Formula slightly and array entered it (Ctrl+ Shift + Enter). =INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B 11),MONTH(A1:H1),0))),INDEX (A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0)) Very much appreciated. Cheers, Sam JMB wrote: If your table is in A1:H5 with Labels in column A and Jan in column C, you could try: =INDEX(A1:A5,MATCH(MAX(C1:C5),C1:C5,0)) If you don't want to hardcode the range for the month (ie C1:C5), you could try: =INDEX(A1:A5,MATCH(MAX(INDEX(A1:H5,0,MATCH(B11,A1 :H1,0))),INDEX(A1:H5,0,MATCH(B11,A1:H1,0)),0)) I got #N/A using Formula as it stands. However, this slight tweak & array entered works for me. =INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B 11),MONTH(A1:H1),0))),INDEX (A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0)) where B11 contains the month (Jan) . -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
Since your date headers are in ascending order you can simplify that to
(normally entered, not an array): =INDEX(A2:A5,MATCH(MAX(INDEX(C2:H5,,MONTH(B11))),I NDEX(C2:H5,,MONTH(B11)),0)) Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6e03af7eb1e48@uwe... Hi JMB, Thank you very much for your time and assistance. I've tweaked your Formula slightly and array entered it (Ctrl+ Shift + Enter). =INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B 11),MONTH(A1:H1),0))),INDEX (A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0)) Very much appreciated. Cheers, Sam JMB wrote: If your table is in A1:H5 with Labels in column A and Jan in column C, you could try: =INDEX(A1:A5,MATCH(MAX(C1:C5),C1:C5,0)) If you don't want to hardcode the range for the month (ie C1:C5), you could try: =INDEX(A1:A5,MATCH(MAX(INDEX(A1:H5,0,MATCH(B11,A 1:H1,0))),INDEX(A1:H5,0,MATCH(B11,A1:H1,0)),0)) I got #N/A using Formula as it stands. However, this slight tweak & array entered works for me. =INDEX(A2:A5,MATCH(MAX(INDEX(A2:H5,0,MATCH(MONTH(B 11),MONTH(A1:H1),0))),INDEX (A2:H5,0,MATCH(MONTH(B11),MONTH(A1:H1),0)),0)) where B11 contains the month (Jan) . -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
Hi Biff,
Thanks for that. Very much appreciated. Cheers, Sam T. Valko wrote: Since your date headers are in ascending order you can simplify that to (normally entered, not an array): =INDEX(A2:A5,MATCH(MAX(INDEX(C2:H5,,MONTH(B11))), INDEX(C2:H5,,MONTH(B11)),0)) Biff -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Retrieve Numeric Label for Max Value by Specific Month
You're welcome!
Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6e0fc3261dcfb@uwe... Hi Biff, Thanks for that. Very much appreciated. Cheers, Sam T. Valko wrote: Since your date headers are in ascending order you can simplify that to (normally entered, not an array): =INDEX(A2:A5,MATCH(MAX(INDEX(C2:H5,,MONTH(B11))) ,INDEX(C2:H5,,MONTH(B11)),0)) Biff -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Return Numeric Label based on (Numeric Value) Criterion | Excel Worksheet Functions | |||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
AVERAGE Row of Numbers and Return Corresponding Numeric Label | Excel Worksheet Functions | |||
How to convert a label to numeric | Excel Discussion (Misc queries) |