Subtracting values based on dates from specific cases

Hello all,

I need to subtract baseline score from the most recent score for a specific id. I was filtering and subtracting for each case number but there are too many entries and some have multiple dates. Please help! What is the most efficient way to do this? Formulas? I need it in the "Results" column. (I have beginner skills :/) (Using 2010 Excel on windows.)
Please see attached:

11-29-2018 1-59-15 PM.jpg

Thank You in advance.

it is difficult to tell what you need to have done based on a snapshot. If possible please upload a sample sheet with enough information (nothing confidential) and clarify your question so people can see what is being "subtracted" from what and the expected results. Go advanced (button under this window) then manage attachments and browse to upload a sample sheet (I find it easiest to save a sample to my desktop as it is the quickest to get to and upload).
It doesn't have to be all your data, just a representative sample and your expected results.

Thank you Sambo kid for your reply.

The Results are the difference from the "Average functioning score". So I want to take the baseline "average functioning score" from the earliest date for a "case number" and subtract it from the most recent date of that "case number".

For Case number 265754 the earliest date entry is 2/6/2018, and its score is 1.60. So I want to subtract that from the latest date entry: 10/15/2018 and its score is 1.13. So it should be 1.13-1.60=-0.47. (example formula:=C3-C1)I would like to see if there is an easier way as opposed to doing it manually for all enteries. I hope that makes sense. Thank you

Ok, I can get there but I had to use three helper columns to do it. The first two are array formulas that when entered have to be activated by clicking in the formula in the formula bar and hitting ctrl/shift/enter at the same time so that {} appear around the formula (you cannot hand enter these). You can copy and paste the array formulas after they are entered and you do not have to worry about hitting ctrl/shift/enter again (and they are active in the attached sheet). The third formula is a concatenate formula. The results are in col E.
hopefully someone will come along and be able to give you a single formula to accomplish the task. I'll keep thinking on it in the mean time.

Please try at D2 and copy down

=IF(AGGREGATE(15,6,$B$2:$B$11/($A$2:$A$11=A2),1)=B2,INDEX($C$2:$C$11,MATCH(AGGREGATE(14,6,$B$2:$B$11/($A$2:$A$11=A2),1),INDEX($B$2:$B$11/($A$2:$A$11=A2),),))-C2,"")

Assuming dates are increasing for each Case_num
In D2:

Please Login or Register  to view this content.

Drag down

Cross-posted here: https://www.mrexcel.com/forum/excel-...e-numbers.html

As you are new, I am providing this information for you. In future, if you cross-post, you must disclose this at the start of your thread. Please take a moment to read our forum rules before posting again. Thanks.

Similar. Also array entered.

Formula:

Please Login or Register  to view this content.

or same without array entry.

Formula:

Please Login or Register  to view this content.

You are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

14,15 for large and small 6 for ignore error

(AGGREGATE(15,6,$B$2:$B$11/($A$2:$A$11=A2),1)

($A$2:$A$11=A2) if CASE_NUM are the same as its row, return True and return false for different CASE_NUM

=(AGGREGATE(15,6,$B$2:$B$11/({TRUE;TRUE;TRUE;FALSE;…;FALSE}),1) when do math operation True > 1 and False > 0
=(AGGREGATE(15,6,$B$2:$B$11/({1;1;1;0;…;0}),1) B2:B11 are date divide by 1 give same value but divide by 0 give error #DIV/0!

6 will ignore error and 14, 15 will give large, small rank 1