New Contributor

# Selecting the max value from a date range using repeated measures

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

# Re: Selecting the max value from a date range using repeated measures

See the attached version.

# Re: Selecting the max value from a date range using repeated measures

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!

# Re: Selecting the max value from a date range using repeated measures

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))