+ Reply to Thread
Results 1 to 13 of 13

Formula for finding last year's value for a particular variable and name

  1. #1
    Registered User
    Join Date
    03-09-2015
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013
    Posts
    13

    Formula for finding last year's value for a particular variable and name

    Hi there.

    I have 3 columns called Date_Assess, First_Last, and SDQ_Total that represent the date of the assessment, the first and last name combined, and the total score on the SDQ scale. These are filled in manually.

    I have created 3 more columns called Last_SDQ, SDQ_Difference, and SDQ_Improval. What I'm aiming for is to create a formula in the Last_SDQ column that will use the Date_Assess and First_Last variables to find the previous year's SDQ_Total value in each row. Then it would be easy to make a formula in the SDQ_Difference column that subtracts the current value from the previous value, and understand whether the child has improved in the SDQ_Improval column from last year's measurement.

    Any ideas on the formula I described for Last_SDQ?? I've been around and around and I feel like I'm on the verge but just can't get it right. I've attached a picture of the spreadsheet. I would appreciate your insight!!

    Thank you,

    Alison
    Last edited by alisonhs; 10-19-2015 at 01:03 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Formula for finding last year's value for a particular variable and name

    We can do nothing with an image, can you post a worksheet instead?
    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Quang PT

  3. #3
    Registered User
    Join Date
    03-09-2015
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for finding last year's value for a particular variable and name

    Hi bebo021999,

    Thanks for the reply. Attached is the example workbook of what I'm attempting.

    Thanks again,

    Alison
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula for finding last year's value for a particular variable and name

    First the dates you have in your file are text. Dates are numbers. I converted those to dates in the format indicated in the upload (d/m/yyyy).
    These formulas will not work without converting those text values to dates. The format will not affect anything.

    Try this in D2:D7 Last_SDQ
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then this in E2:E7 SDQ_Diff
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and this in F2:F7 SDQ_Improval
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    Date_Assess First_Last SDQ_Total Last_SDQ SDQ_Diff SDQ_Improval
    2
    19/10/2015
    JessaSmith
    19
    20
    -1
    Improved In D2:D7 =IF(AND(B2=B3,A3<A2),C3,"")
    3
    19/10/2014
    JessaSmith
    20
    22
    -2
    Improved In E2:E7 =IFERROR(C2-D2,"1st year")
    4
    18/10/2013
    JessaSmith
    22
    1st year 1st year In F2:F7 =IF(ISNUMBER(E2),IF(E2<0,"Improved","Declined"),E2)
    5
    19/10/2015
    JamesSmith
    22
    18
    4
    Declined
    6
    19/10/2014
    JamesSmith
    18
    18
    0
    Declined
    7
    17/10/2013
    JamesSmith
    18
    1st year 1st year
    Last edited by FlameRetired; 10-19-2015 at 03:34 PM.
    Dave

  5. #5
    Registered User
    Join Date
    03-09-2015
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for finding last year's value for a particular variable and name

    Hi Dave,

    Thanks so much for your time! Those formulas worked when I put them in, but that way will only work when everything is in order by date and name, right? I'm thinking it will have to be some kind of vlookup or index/match formula that somehow matches the name, then goes through the dates within that name to pick the previous date. I've been playing with different index/match combinations and will post if I come across one that works. Knowing that I can use "<" for dates though will be very helpful as I keep plugging along.

    Thanks again and I appreciate this more than you know!

    Alison

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula for finding last year's value for a particular variable and name

    Correct. They will only work if in order.

  7. #7
    Registered User
    Join Date
    03-09-2015
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for finding last year's value for a particular variable and name

    Anyone else have an idea that would work if the rows are not in order? Still working on it and can't seem to figure out how to tie the Date_Assess and First_Last columns together in the formula.

    Alison

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for finding last year's value for a particular variable and name

    See if this ARRAY formula will work for you...
    =IFERROR(INDEX($C$2:$C$7,MATCH(LARGE(IF($B$2:$B$7=B2,$A$2:$A$7),COUNTIF($B$2:B2,B2)+1)&B2,$A$2:$A$7&$B$2:$B$7,0)),"N/A")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Agaian, please note that your dates are not really dates and need to be xided before this will work
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    03-09-2015
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for finding last year's value for a particular variable and name

    Thanks, Ford. I applied the correct format to my dates and tried your formula using ctrl+shift+enter and am getting "N/A" in every row. Any thoughts?

    Alison

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula for finding last year's value for a particular variable and name

    hmm maybe something with how you changed the dates?

    See the attached
    Attached Files Attached Files

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula for finding last year's value for a particular variable and name

    In the meantime I mixed the order up and did this. Seems to work. Array enter this in D2 and fill down. Ford already covered the Ctrl + Shift + Enter for array-entered formulas.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in E2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and in F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The re-worked file is attached.

    Edit I've since tried adding some more data and mixing it up. This doesn't work. Sorry.


    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    Date_Assess First_Last SDQ_Total Last_SDQ SDQ_Diff SDQ_Improval
    2
    19/10/2014
    JessaSmith
    20
    22
    -2
    Declined In D2:D7 {=IFERROR(1/(1/MIN(IF(A2>IF(B2=$B$2:$B$7,$A$2:$A$7),$C$2:$C$7))),"1st year")}
    3
    17/10/2013
    JamesSmith
    18
    1st year 1st year 1st year In E2:E7 =IF(ISNUMBER(D2),C2-D2,"1st year")
    4
    18/10/2013
    JessaSmith
    22
    1st year 1st year 1st year In F2:F7 =IF(ISNUMBER(E2),INDEX({"Declined","NC","Improved"},SIGN(C2-D2)+2),"1st year")
    5
    19/10/2014
    JamesSmith
    18
    18
    0
    NC
    6
    19/10/2015
    JamesSmith
    22
    18
    4
    Improved
    7
    19/10/2015
    JessaSmith
    19
    20
    -1
    Declined
    Last edited by FlameRetired; 10-19-2015 at 11:05 PM.

  12. #12
    Registered User
    Join Date
    03-09-2015
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013
    Posts
    13

    Re: Formula for finding last year's value for a particular variable and name

    Hi all,

    Thank you so much! Definitely getting close...

    I updated the sheet with the longer formula Ford gave, and the update is attached. Then I found the error depicted in the sheets attached - the "BEFORE" tab is the original, the "ERROR" tab shows that in the highlighted rows the Last_SDQ is not correct, and then the "CORECT" tab shows what it should be (worked when I put the dates in order).

    I'm playing with the second formula now but running into some similar quirks. Will keep trying.

    To put this in context: I'm an MPH student and this is all part of a large database I'm working on for an orphans and vulnerable children organization in East Africa. The goal is to have Excel do as much work as possible so their limited staff doesn't have to. So yet again, I very much appreciate the help (and so will the people who will be using this!)

    Alison
    Attached Files Attached Files

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula for finding last year's value for a particular variable and name

    Here's another try.

    I added a sheet called Another formula. The data was copied / pasted from ERROR.

    The formula in Last_SDQ (array-entered) is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please let us know how it does with your live data.

+ 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] Need Year to be Variable in Formula
    By JadaDot in forum Excel General
    Replies: 8
    Last Post: 05-17-2014, 01:43 AM
  2. Replies: 3
    Last Post: 06-14-2013, 02:45 PM
  3. Formula for finding number of consecutive blank cells over a year - See info!
    By oldtauntonian in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-16-2013, 08:31 AM
  4. [SOLVED] Finding the Date of the 2nd and 4th Wed.'s in a year.
    By Dwand in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-11-2012, 07:52 PM
  5. Replies: 3
    Last Post: 07-05-2012, 01:49 AM
  6. Finding the last date of the year
    By thewrathful in forum Excel General
    Replies: 1
    Last Post: 05-02-2010, 02:53 PM
  7. Finding age from year
    By [email protected] in forum Excel General
    Replies: 4
    Last Post: 03-04-2006, 11:55 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