wdprice3
BinaryBuffonary
45912 Posts
user info
edit post |
I have dataset sorted by date/time, with 4 columns of data. (Table A)
I have another table with dates that I'm interested in. (Table B)
I need to use Table B to look in Table A and return the maximum value of a range associated with the date from Table A which is specified by Table B.
Example:
Table A
Col A Col B Col C Col D Col E
01/01/10 0:00 1 1 0 1
01/01/10 12:00 2 2 3 2
01/02/10 0:00 0 0 0 0
01/02/10 12:00 3 1 2 1
01/03/10 0:00 0 0 0 0
01/03/10 12:00 3 5 2 1
Table B
Col A Col B
01/01/10 Max of 01/01/10 data from Cols B - E (thus, 3)
01/03/10 Max of 01/03/10 data from Cols B - E (thus, 5)
Notes:
I can pull the dates from the date/times in Table A easy enough
Both tables are in chronological order (date/time)
Not all dates from Table A are used; just the ones in Table B
Data points are every 5, 8, or 10 minutes for each day, so pattern based solutions are probably out
Need the maximum value from Columns B - E for the date specified in Table B.
Looking for a single cell solution
I thought I could use an if statement as an array function with the logic expression comparing a date in Table B to the range of dates in Table A... I could have sworn that I did something similar before... and then used a nested if to return the maximum value from the set of values with a date matching the specified in Table B.
not sure on vlookup since it can't look at a range to report on.
Excel 2010 11/14/2010 10:07:10 PM
|
wdprice3
BinaryBuffonary
45912 Posts
user info
edit post |
just ended up doing a pivot table for now; though a cell solution would be nice to have. 11/14/2010 11:30:57 PM |
nacstate
All American
3785 Posts
user info
edit post |
This would be super easy to do in access. Not that that helps you any. 11/14/2010 11:49:31 PM |
rbrthwrd
Suspended
3125 Posts
user info
edit post |
So the number of rows per date are different? If they are the same the MAX function would work easy enough with cell references. 11/15/2010 12:11:47 AM |
wdprice3
BinaryBuffonary
45912 Posts
user info
edit post |
^eh, yeh since there are different intervals. and the max function is the obvious part. the more difficult part is getting the max function to look at a range for a specified day, among 400 days. there are 90,000+ rows of data here, so some convoluted formula isn't going to work. so I don't really see what you are suggesting... to just manually do a max for each day? that is not at all what I am asking for.
[Edited on November 15, 2010 at 8:28 AM. Reason : .] 11/15/2010 8:24:27 AM |
rbrthwrd
Suspended
3125 Posts
user info
edit post |
oh table b doesn't have a row for every day, i missed that.
i'd use pivot tables too 11/15/2010 9:49:25 AM |
wdprice3
BinaryBuffonary
45912 Posts
user info
edit post |
haha, yeh. I don't think a single cell solution is that straight forward 
and the pivot table worked well; just time consuming to filter out the dates I wasn't interested in.
and sorry if I sounded dickish in ^^... I was still half asleep. 11/15/2010 10:59:00 AM |
