SOLVED

Selecting the max value from a date range using repeated measures

%3CLINGO-SUB%20id%3D%22lingo-sub-2658045%22%20slang%3D%22en-US%22%3ESelecting%20the%20max%20value%20from%20a%20date%20range%20using%20repeated%20measures%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2658045%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20thousands%20of%20IDs%2C%20some%20of%20which%20have%20multiple%20visits%20or%20occurrences.%20Each%20occurrence%20has%20a%20value%20and%20a%20date.%20My%20objective%20is%20to%20identify%20the%20max%20value%20out%20of%20the%20repeated%20occurrences%20for%20a%20specific%20date%20range.%20The%20code%20I%20have%20written%20works%20correctly%20for%20the%20majority%20of%20my%20IDs%2C%20however%20some%20IDs%20have%20issues.%20For%20example%2C%20in%20my%20file%2C%20for%20column%20P%2C%20I%20want%20the%20max%20value%20for%20each%20ID%20from%20between%20the%20dates%201%2F1%2F16%20and%2012%2F31%2F17.%20This%20is%20what%20I%20get%20for%20the%20majority%20of%20IDs%2C%20except%20as%20you%20see%20with%20ID%20100003%2C%20it%20is%20providing%20me%20with%20a%20date%20from%202015%2C%20when%20you%20can%20see%20from%20column%20C%20that%20ID%20has%20visits%20in%202016%20and%202017.%20The%20correct%20value%20that%20should%20pop%20up%20in%20cell%20P5%20is%206.2%2C%20but%20the%20associated%20date%20in%20Q5%20should%20be%203%2F25%2F16%20because%20that%20date%20falls%20between%201%2F1%2F16%20and%2012%2F31%2F17.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2658045%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2658079%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20the%20max%20value%20from%20a%20date%20range%20using%20repeated%20measures%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2658079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883942%22%20target%3D%22_blank%22%3E%40tdwagner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2658648%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20the%20max%20value%20from%20a%20date%20range%20using%20repeated%20measures%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2658648%22%20slang%3D%22en-US%22%3EThis%20looks%20great%20-%20thanks%20a%20lot!%20Would%20you%20by%20chance%20know%20why%2C%20when%20I%20go%20to%20use%20that%20same%20formula%20in%20my%20master%20file%2C%20after%20entering%20the%20formula%20and%20using%20SHIFT%2BCTRL%2BENTER%2C%20I%20receive%20the%20%23VALUE!%20error%3F%20When%20I%20copy%20and%20paste%20it%20within%20the%20short%20except%20file%20that%20I%20shared%20it%20works%20(doesn't%20produce%20an%20error)%2C%20but%20everywhere%20else%20it's%20leaving%20me%20with%20error%20responses.%20Thanks!%3C%2FLINGO-BODY%3E
New 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))