| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month
Hi All,
I have numerous subsets of data listed by Day: 1st - 31st & Month: Jan - Dec. Is it possible to provide a single Formula to reference a large group of data containing some miscellaneous rows that should not be reflected in the data returned. I would like a flexible Formula to explicitly reference specific Day and Month to find the Maximum value for that specific Day (1st, 2nd, 10th etc) & Month (criteria will vary) and retrieve its corresponding Numeric Label from Row "4" Column "C" : "AO" I may want to find the Numeric Label that has the maximum value for 1st Mar, 2nd Mar, 5 Mar; or 31 Feb, 31 Mar, 31 Dec. Col "A" = Day, Col "B" = Month, Col "C" - "AO" = Data - Numeric Values Col "A" Col "B" Col "C" - "AO" 01 Jan 01 Feb 01 Mar etc 01 Dec Misc Misc 02 Jan 02 Feb 02 Mar etc 02 Dec Misc Misc 31 Jan 31 Feb 31 Mar etc etc 31 Dec Numeric Labels = Row4 Col "C" - "AO" Data = Row5:Row438 Col "C" - "AO" - Numeric Values Day = Row5:Row438 Col "A" - Full Date formatted with Custom Day Format "dd": 1 - 31 Month = Row5:Row438 Col "B" - Full Date formatted with Custom Month Format "mmm": Jan - Dec. Sample Data Layout: Row4 Day Month 1 2 3 4 5 6 Row5 01 Jan 101 102 123 143 136 128 Row6 01 Feb 120 130 103 87 143 130 Row7 01 Mar 120 90 60 200 102 88 Row8 01 Apr 170 88 79 67 141 110 etc etc Row16 01 Dec 185 188 179 167 141 110 Row17 Misc Row18 Misc Row19 02 Jan 101 102 123 143 136 128 Row20 02 Feb 120 130 103 87 143 130 Row21 02 Mar 120 90 60 200 102 88 Row22 02 Apr 170 88 79 67 141 110 etc etc Row31 02 Dec 100 102 182 107 111 110 Row32 Misc Row33 Misc Row425 31 Jan 103 142 112 114 137 118 Row426 31 Feb 120 130 103 87 143 130 Row427 31 Mar 120 90 60 200 102 88 Row428 31 Apr 170 88 79 67 141 110 etc etc Row436 31 Dec 128 98 145 198 102 156 Cheers, Sam -- Message posted via http://www.officekb.com |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month
Assuming that A2 contains the day of interest (true date value formatted
as "dd"), and B2 contains the month of interest (true date value formatted as "mmm"), try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF( DAY(A5:A436)=DAY(A2),IF (MONTH(B5:B436)=MONTH(B2),1)),0),0)),INDEX(C5:AO43 6,MATCH(1,IF(DAY(A5:A43 6)=DAY(A2),IF(MONTH(B5:B436)=MONTH(B2),1)),0),0),0 )) If, however, A2 contains the day of interest, such as 1, 2, 3, etc., and B2 contains the month of interest, such as Jan, Feb, Mar, etc., try the following instead... =INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF( DAY(A5:A436)=A2,IF(TEXT (B5:B436,"mmm")=B2,1)),0),0)),INDEX(C5:AO436,MATCH (1,IF(DAY(A5:A436)=A2,I F(TEXT(B5:B436,"mmm")=B2,1)),0),0),0)) Hope this helps! In article <6e1c2a8bf5cdf@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, I have numerous subsets of data listed by Day: 1st - 31st & Month: Jan - Dec. Is it possible to provide a single Formula to reference a large group of data containing some miscellaneous rows that should not be reflected in the data returned. I would like a flexible Formula to explicitly reference specific Day and Month to find the Maximum value for that specific Day (1st, 2nd, 10th etc) & Month (criteria will vary) and retrieve its corresponding Numeric Label from Row "4" Column "C" : "AO" I may want to find the Numeric Label that has the maximum value for 1st Mar, 2nd Mar, 5 Mar; or 31 Feb, 31 Mar, 31 Dec. Col "A" = Day, Col "B" = Month, Col "C" - "AO" = Data - Numeric Values Col "A" Col "B" Col "C" - "AO" 01 Jan 01 Feb 01 Mar etc 01 Dec Misc Misc 02 Jan 02 Feb 02 Mar etc 02 Dec Misc Misc 31 Jan 31 Feb 31 Mar etc etc 31 Dec Numeric Labels = Row4 Col "C" - "AO" Data = Row5:Row438 Col "C" - "AO" - Numeric Values Day = Row5:Row438 Col "A" - Full Date formatted with Custom Day Format "dd": 1 - 31 Month = Row5:Row438 Col "B" - Full Date formatted with Custom Month Format "mmm": Jan - Dec. Sample Data Layout: Row4 Day Month 1 2 3 4 5 6 Row5 01 Jan 101 102 123 143 136 128 Row6 01 Feb 120 130 103 87 143 130 Row7 01 Mar 120 90 60 200 102 88 Row8 01 Apr 170 88 79 67 141 110 etc etc Row16 01 Dec 185 188 179 167 141 110 Row17 Misc Row18 Misc Row19 02 Jan 101 102 123 143 136 128 Row20 02 Feb 120 130 103 87 143 130 Row21 02 Mar 120 90 60 200 102 88 Row22 02 Apr 170 88 79 67 141 110 etc etc Row31 02 Dec 100 102 182 107 111 110 Row32 Misc Row33 Misc Row425 31 Jan 103 142 112 114 137 118 Row426 31 Feb 120 130 103 87 143 130 Row427 31 Mar 120 90 60 200 102 88 Row428 31 Apr 170 88 79 67 141 110 etc etc Row436 31 Dec 128 98 145 198 102 156 Cheers, Sam |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month
Hi Domenic,
Thank you for reply and assistance. Formula provides expected results. Great! How do I return the Maximum value from the same set of data rather than the Numeric Label? Cheers, Sam Domenic wrote: Assuming that A2 contains the day of interest (true date value formatted as "dd"), and B2 contains the month of interest (true date value formatted as "mmm"), try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF (DAY(A5:A436)=DAY(A2),IF (MONTH(B5:B436)=MONTH(B2),1)),0),0)),INDEX(C5:AO4 36,MATCH(1,IF(DAY(A5:A43 6)=DAY(A2),IF(MONTH(B5:B436)=MONTH(B2),1)),0),0), 0)) This works Great! If, however, A2 contains the day of interest, such as 1, 2, 3, etc., and B2 contains the month of interest, such as Jan, Feb, Mar, etc., try the following instead... =INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF (DAY(A5:A436)=A2,IF(TEXT (B5:B436,"mmm")=B2,1)),0),0)),INDEX(C5:AO436,MATC H(1,IF(DAY(A5:A436)=A2,I F(TEXT(B5:B436,"mmm")=B2,1)),0),0),0)) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month
In article <6e1d2b933a4f7@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote: Hi Domenic, Hi Sam! Thank you for reply and assistance. You're very welcome! How do I return the Maximum value from the same set of data rather than the Numeric Label? Try... =MAX(INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A436)=DAY(A2 ),IF(MONTH(B5:B436)=MON TH(B2),1)),0),0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month
Hi Domenic,
That's Brilliant! Thank you very much. Cheers, Sam How do I return the Maximum value from the same set of data rather than the Numeric Label? Domenic wrote: Try... =MAX(INDEX(C5:AO436,MATCH(1,IF(DAY(A5:A436)=DAY(A 2),IF(MONTH(B5:B436)=MON TH(B2),1)),0),0)) ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! -- Message posted via http://www.officekb.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Retrieve Numeric Label for Max Value by Specific Day & Month | Excel Worksheet Functions | |||
| Retrieve Numeric Label for Max Value by Specific Month | Excel Worksheet Functions | |||
| Find and Return Numeric Label based on (Numeric Value) Criterion | 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) | |||
Linear Mode
