+ Reply to Thread
Results 1 to 6 of 6

Identifying First & Last Values in Each Year & Return Adjacent Cell

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    13

    Identifying First & Last Values in Each Year & Return Adjacent Cell

    Hi all

    I have a basic Google Sheet that I am using to track investment performance. I'd love to be able to automatically identify the first entry in each year (whether it be the 1st, 8th, 12th Jan etc) and return the investment value on that date, and then the same with the final entry. I would then be able to look at performance growth year on year.

    I have included a link to the data set here:

    http://docs.google.com/spreadsheets/...it?usp=sharing

    As you can see (in yellow) on the ROI tab, I have managed to do this using 'MIN' and 'MAX' values, but of course this is no good if the investment value dips after the first entry, or his higher before the final entry. I therefore want to replace the formulas in C5:D13.
    Last edited by markrc139; 04-21-2021 at 11:56 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

    I've not downloaded your document (you can attach it to one of your posts here), but you can apply the MAX and MIN functions conditionally to the date column to find the earliest and latest dates in a particular year (I'm not sure if you have the MAXIF and MINIF functions in Google sheets, but you can simulate these using an array formula where you are looking for the same year). Then you could use the result in a VLOOKUP or INDEX/MATCH formula to extract the appropriate values on those dates.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

    Thanks Pete. As it's a Google Sheet it's not so easy to attach (as when I downloaded to Excel format, the Array Formula the current MAX and MIN functions stopped working. You should be able to copy and paste the link into your browser and have edit access.

    I'll have another look at it in light of your suggestion and see if I get somewhere!
    Appreciate it!

  4. #4
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

    It seems I've hit the 'magic' 10 posts on here now as well so it has let me post the link. I have updated the original post with it.

  5. #5
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

    Hi Markrc139,

    Not sure if this was what you were looking for or not.
    I added a year column to the ISA Value sheet. On the ROI sheet, I used unique, filter, sort and index functions to achieve the results.
    Is this something you can work with?

  6. #6
    Registered User
    Join Date
    03-20-2014
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    13

    Re: Identifying First & Last Values in Each Year & Return Adjacent Cell

    Thanks Flyboy65 yes that looks spot on. Not quite on the same lines that I'd been thinking before, but a solution that works is a solution that works!

    Very grateful for your input.
    I'll mark this thread as solved.

+ 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. Identifying rising values and finding the difference between adjacent cells.
    By peeweelee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-30-2019, 07:59 PM
  2. Identifying and selecting adjacent duplicate values
    By micszekely in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2017, 03:35 AM
  3. Identifying and selecting adjacent duplicate values
    By micszekely in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2017, 05:52 PM
  4. Return multiple values from a cell by identifying , or :
    By monkattack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2013, 06:53 AM
  5. [SOLVED] Lookup Function to Return Values in Adjacent Cell?
    By TCstr8 in forum Excel General
    Replies: 2
    Last Post: 03-30-2012, 11:54 AM
  6. Function to return different values based on adjacent cell
    By KMB_85 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-15-2012, 12:44 AM
  7. Replies: 2
    Last Post: 08-17-2011, 01:35 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