+ Reply to Thread
Results 1 to 13 of 13

Assigning scores based on how long ago something happened, stuck on formula

  1. #1
    Registered User
    Join Date
    07-12-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    33

    Assigning scores based on how long ago something happened, stuck on formula

    I’m trying to use a bunch of if / and statements to work on the following problem but it isn’t really getting me anywhere.
    The premise of what I’m working on is that certain things have taken place in the past, and depending on how long ago it happened, it gets a certain score.

    - If it happened in 2018 it gets a score of 10.
    - If it happened 1-3 years ago it gets a score of 20.
    - If it happened 4-7 years ago it gets a score of 30.
    - If it happened 8 or more years ago it gets a score of 40.

    So I have in column “A” a list of the different types of events that have taken place. In column B the date they took place, and in column C, I’m trying to put in the scores.

    If just can’t seem to make a formula that does what I’m after and the inclusion of working out how long ago something happened is really confusing me.


    Any help would be hugely appreciated !

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Assigning scores based on how long ago something happened, stuck on formula

    try: =if(year(b1)=2018,10,if(and(year(b1)>=2015,year(b1)<=2017),20,if(and(year(b1)>=2011,year(b1)<=2014),30,if(year(b1)<=2010,40))))
    Click the * to say thanks.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Assigning scores based on how long ago something happened, stuck on formula

    How about
    Excel 2013 32 bit
    B
    C
    2
    01/01/2019
    0
    3
    01/01/2018
    10
    4
    01/01/2017
    20
    5
    01/01/2016
    20
    6
    01/01/2015
    20
    7
    01/01/2014
    30
    8
    01/01/2013
    30
    9
    01/01/2012
    30
    10
    01/01/2011
    30
    11
    01/01/2010
    40
    12
    01/01/2009
    40
    Sheet: Lapr
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-12-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    33
    Quote Originally Posted by PaulM100 View Post
    try: =if(year(b1)=2018,10,if(and(year(b1)>=2015,year(b1)<=2017),20,if(and(year(b1)>=2011,year(b1)<=2014),30,if(year(b1)<=2010,40))))
    Thank you for this. This would be perfect, but unfortunatly I need it to relate to "todays" date or rather the current year, so that the formula is continually updated, rather than being dependant on manually entering years that are x number of years ago....if that makes sense?

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

    Re: Assigning scores based on how long ago something happened, stuck on formula

    Try:

    Please Login or Register  to view this content.
    Quang PT

  6. #6
    Registered User
    Join Date
    07-12-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    33

    Re: Assigning scores based on how long ago something happened, stuck on formula

    =IFERROR(CHOOSE(YEAR(TODAY())-YEAR(B2),10,20,20,20,30,30,30,30,40),0)


    I'm a little confused as to how this formula determines if the year is say 2 years from now, then assign a specific score, vs if it was say 10 years ago, it assigns a specific score?
    Last edited by blackrosepetals9169; 02-20-2019 at 07:13 AM.

  7. #7
    Registered User
    Join Date
    07-12-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    33

    Re: Assigning scores based on how long ago something happened, stuck on formula

    =LOOKUP(YEAR(B2),{0,2011,2015,2018,2019},{40,30,20,10,0})



    I see what you mean with this, my only concern is that this scoring needs to be done each year, in reference to the current year. So whilst for this year 2018 is 1 year ago, this time next year, it will be 2 years ago, so this table would need to be amended each year.

    This isn't as such an issue as I know it won't take long, but I'm constantly being pushed in work to make things more automatic, would there be a way to either make the table populate automatically or the formula itself?

  8. #8
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Assigning scores based on how long ago something happened, stuck on formula

    Imagine this part: 10,20,20,20,30,30,30,30,40 as 2019,2018,2017,2016,2015,2014,2013,2012 and previous years. A value is assigned based on the result of YEAR(TODAY())-YEAR(B2)

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Assigning scores based on how long ago something happened, stuck on formula

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-12-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    33

    Re: Assigning scores based on how long ago something happened, stuck on formula

    Quote Originally Posted by Fluff13 View Post
    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    hmm I gave this a go just by typing in a blank sheet in column A, a list of different years and in column C and D the table you posted above (and therefore updating the formula by changing B2 to C2), but the scores I get don't match up/ aren't correct. I'm not sure what I'm doing wrong

  11. #11
    Registered User
    Join Date
    07-12-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    33

    Re: Assigning scores based on how long ago something happened, stuck on formula

    Quote Originally Posted by PaulM100 View Post
    Imagine this part: 10,20,20,20,30,30,30,30,40 as 2019,2018,2017,2016,2015,2014,2013,2012 and previous years. A value is assigned based on the result of YEAR(TODAY())-YEAR(B2)
    ahh ok I see what you mean.... I think. So how would it know that if a year is 3 years ago from the current year, which value to pull back? Or is this to do with the order in which you put in the scores in the formula?

    Thanks for your help !

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Assigning scores based on how long ago something happened, stuck on formula

    Can you post a sample workbook, including your expected results.
    It would make things a lot easier

  13. #13
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Assigning scores based on how long ago something happened, stuck on formula

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

    No further help to be offered, please, until the OP has complied with this request.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. formula for Assigning pts based on scores
    By RandyG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-15-2017, 01:29 PM
  2. [SOLVED] I need a macro to select range of scores, choose the best scores and apply a formula
    By hadleedog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2015, 03:15 PM
  3. [SOLVED] Formula to calculate supplier evaluation scores based on their profit margin %
    By The_Snook in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2015, 06:03 PM
  4. Formula to sum up scores based on letters
    By scottj15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-21-2013, 09:12 AM
  5. Assigning positions for students scores from different sheets
    By aboyetolu in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2012, 11:34 PM
  6. Hi Kiwiscotsman here. I am ok on excel but get stuck on long formulas and code.
    By kiwiscotsman in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-17-2012, 04:48 AM
  7. [SOLVED] Assigning scores to a date range
    By Koen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-20-2005, 08:05 AM

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