+ Reply to Thread
Results 1 to 12 of 12

Subtracting values based on dates from specific cases

  1. #1
    Registered User
    Join Date
    11-29-2018
    Location
    U.S
    MS-Off Ver
    2010
    Posts
    4

    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.
    Last edited by Shellystar1; 11-30-2018 at 03:41 PM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    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.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    11-29-2018
    Location
    U.S
    MS-Off Ver
    2010
    Posts
    4

    Re: Subtracting scores based on dates from case numbers

    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
    Attached Files Attached Files
    Last edited by jeffreybrown; 11-29-2018 at 08:12 PM. Reason: Removed full quote!

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    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.
    Attached Files Attached Files

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,054

    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. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,462

    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,183

    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.
    Last edited by AliGW; 11-30-2018 at 02:04 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Subtracting scores based on dates from case numbers

    Similar. Also array entered.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or same without array entry.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  9. #9
    Registered User
    Join Date
    11-29-2018
    Location
    U.S
    MS-Off Ver
    2010
    Posts
    4

    Re: Subtracting scores based on dates from case numbers

    Quote Originally Posted by Bo_Ry View Post
    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. #10
    Registered User
    Join Date
    11-29-2018
    Location
    U.S
    MS-Off Ver
    2010
    Posts
    4

    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. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Subtracting values based on dates from specific cases

    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.

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,054

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Auto filling dates based on previously entered dates and averaging numbers if dates equal
    By grambograham in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 03:21 PM
  2. Subtracting Dates
    By arebryn in forum Excel General
    Replies: 2
    Last Post: 06-23-2010, 01:03 PM
  3. Subtracting dates
    By loner2003 in forum Excel General
    Replies: 2
    Last Post: 04-01-2010, 01:58 PM
  4. subtracting dates
    By galarza in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 10-30-2009, 06:23 PM
  5. [SOLVED] Subtracting dates?
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 07:05 AM
  6. Subtracting dates?
    By Bill R in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Subtracting dates: 8/31/05-8/1/05?
    By dstock in forum Excel General
    Replies: 1
    Last Post: 07-26-2005, 11:04 AM

Tags for this Thread

Bookmarks

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