# Subtracting values based on dates from specific cases

1. ## 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.)

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

2. ## Re: Subtracting scores based on dates from case numbers

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.

3. ## Re: Subtracting scores based on dates from case numbers

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

4. ## Re: Subtracting scores based on dates from case numbers

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.

5. ## Re: Subtracting scores based on dates from case numbers

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

6. ## Re: Subtracting scores based on dates from case numbers

Assuming dates are increasing for each Case_num
In D2:
``Please Login or Register  to view this content.``
Drag down

7. ## Re: Subtracting scores based on dates from case numbers

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.

8. ## Re: Subtracting scores based on dates from case numbers

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.`

9. ## Re: Subtracting scores based on dates from case numbers

Originally Posted by Bo_Ry
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,"")
Hello Bo_Roy,

Thank you for this formula it worked for me. However, I am still learning the different function formulas so can you please explain how you arrived at this? I noticed you used index-match. What is the 15,6 and 14,6 referring to?

Thank you so much

10. ## Re: Subtracting scores based on dates from case numbers

Hello Everyone,

I would just like to thank everyone who took the time out to help me, I really do appreciate it.

The formulas that worked for me immediately were:

=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,"")
by Bo_Roy
& Sambo kid 3 helper column one.

I did try the other ones but they were giving me a hard time. Nonetheless thanks again for the help! You all are awesome!

& ALIGW my apologies for the double post- I wasn't aware that it can be an issue.

11. ## Re: Subtracting values based on dates from specific cases

You are welcome. Thank you for the feedback.

12. ## Re: Subtracting values based on dates from specific cases

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1