+ Reply to Thread
Results 1 to 8 of 8

Lagged value based on groups and date

  1. #1
    Registered User
    Join Date
    11-15-2021
    Location
    London
    MS-Off Ver
    2016
    Posts
    12

    Lagged value based on groups and date

    Hello good people,

    I would like to calculate a lagged value of cells D and E for each HomeTeam and AwayTeam (cells B and C).

    In the example that I uploaded, I would like the 'APG lagged1' (cell G) for Brentford at line 14 (date 21/08/2021) to be its latest value occuring at a preceeding date (regardless if home or away) - in the case at hand - line 1 where the HPG is 3.

    I hope this is clear enough. I tried a couple of things with offset but did not work as intended.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Lagged value based on groups and date

    I'm sure this could be more efficient but I'm having a brain meltdown right now. This in F2:

    Please Login or Register  to view this content.
    Copy across/down as required. Not super-efficient if you have a high number of rows I'd say.

    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    11-15-2021
    Location
    London
    MS-Off Ver
    2016
    Posts
    12

    Re: Lagged value based on groups and date

    Hi WBD,

    Thank you for the quick reply!

    Unfortunately my Excel does not recognize the function 'Let'. I have #NAME? when placing the formula in F2.

    Is there a way around this (not including Let)?

    Thank you!

  4. #4
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Lagged value based on groups and date

    Your MS Office version says "365 ProPlus". LET() is available in O365. Is that not the version you have?

    WBD

  5. #5
    Registered User
    Join Date
    11-15-2021
    Location
    London
    MS-Off Ver
    2016
    Posts
    12

    Re: Lagged value based on groups and date

    Hello,

    I think it is but this (#NAME?) is the message that I have. I will check again when I get home tonight.

    In any case, as I would like to share this file with many other people, I would like to have a function in place that does not require a particular version. I am not sure if this is possible. But that was my idea

    Oxlade

  6. #6
    Registered User
    Join Date
    11-15-2021
    Location
    London
    MS-Off Ver
    2016
    Posts
    12

    Re: Lagged value based on groups and date

    Hello again

    Would it be possible to have a solution compatible with Excel 2016?

    Apologies about that.

    Kind regards,
    Oxlade

  7. #7
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Lagged value based on groups and date

    The only way I could figure out how to do this was using helper columns. I've hidden them in the attached.

    WBD
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-15-2021
    Location
    London
    MS-Off Ver
    2016
    Posts
    12

    Re: Lagged value based on groups and date

    Top! Amazing! Thank you!

+ 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. Adding lagged factor
    By DanVG in forum Excel General
    Replies: 4
    Last Post: 03-31-2021, 10:27 AM
  2. [SOLVED] Placing the a date value to a 3 different groups
    By hellforce in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-11-2014, 11:08 AM
  3. [SOLVED] Finding date groups
    By OAM in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-24-2013, 11:47 PM
  4. Generate lagged dummy variables
    By tianyi86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2009, 01:28 PM
  5. [SOLVED] Matching Date From Two Groups
    By Biff in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  6. Matching Date From Two Groups
    By wsteel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. Matching Date From Two Groups
    By wsteel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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