SOLVED

Selecting the max value from a date range using repeated measures

Copper Contributor

I have thousands of IDs, some of which have multiple visits or occurrences. Each occurrence has a value and a date. My objective is to identify the max value out of the repeated occurrences for a specific date range. The code I have written works correctly for the majority of my IDs, however some IDs have issues. For example, in my file, for column P, I want the max value for each ID from between the dates 1/1/16 and 12/31/17. This is what I get for the majority of IDs, except as you see with ID 100003, it is providing me with a date from 2015, when you can see from column C that ID has visits in 2016 and 2017. The correct value that should pop up in cell P5 is 6.2, but the associated date in Q5 should be 3/25/16 because that date falls between 1/1/16 and 12/31/17.

 

Any help would be greatly appreciated.

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@tdwagner 

See the attached version.

This looks great - thanks a lot! Would you by chance know why, when I go to use that same formula in my master file, after entering the formula and using SHIFT+CTRL+ENTER, I receive the #VALUE! error? When I copy and paste it within the short except file that I shared it works (doesn't produce an error), but everywhere else it's leaving me with error responses. Thanks!

@tdwagner 

Could the RESULT_VALUE column contain invalid entries, for example text values? Try this formula in H2, also confirmed with Ctrl+Shift+Enter:

 

=MAX(IF(($A$2:$A$55665=G2)*($C$2:$C$55665>=DATE(2016,1,1))*($C$2:$C$55665<=DATE(2017,12,31)),$E$2:$E$55665))

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@tdwagner 

See the attached version.

View solution in original post