I am working with a very large data set (200+ pages) and I am trying to isolate the max and min values for various parameters. Is there any way to write a macro or equation that will pull up the data and time associated with that max or min value? I have been searching the column for the max value and finding that date that way. Date is column A, Time column B and the data go from C to L. Thanks
Last edited by ScottL; 02-11-2011 at 10:05 AM.
Check out pivot tables and/or the various LOOKUP functions. They're perfect for what you're trying to do!
Ok, I have the formula =VLOOKUP(C31300,C8:C31298,1,FALSE) where C31300 is the max of that column (wind direction) and C8:C31298 is the whole wind direction data set. I am struggling with the reference column which is column A (A8:A31298) and contains the dates. The equation gives me a date that is not on the table oddly enough. It is generating 12/25/04 when the dates are for Oct-Dec 2010. Do I have the wrong reference column number?
Ok no it appears to be the table array. How do I set the table array to be only column A and column C? The max may appear in other data Columns and I don't want it to pull the date from another data point.
I'm not entirely sure I follow but if you're saying you have points in C:L and you want the Date at which the Max(C:L) first occurred then:
Note the Array entry requirement=INDEX(A8:A31298,MIN(IF(C8:L31298=MAX(C8:L31298),ROW(C8:L31298)-ROW(C8)+1))) confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
If on the other hand you want the date associated with Max of just column C:
edit: revised Array at 1439 UK time - typo !=INDEX($A$8:$A$31298,MATCH(MAX(C$8:C$31298),C$8:C$31298,0)) confirmed with Enter copied across to column L for other max/date combinations
Last edited by DonkeyOte; 02-10-2011 at 09:39 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
@DonkeyOte Thank you very much. The index worked perfectly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks