+ Reply to Thread
Results 1 to 7 of 7

Dynamic formula which compares a cell to a prior range of cells

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    6

    Question Dynamic formula which compares a cell to a prior range of cells

    Excel gurus out there, I need your help! Normally after a bit of googling, I seem to find a solution to formula issues. This one I am struggling...

    I am working on sales pipeline data.

    I have a vertical data set which continually grows each week as data is input on there from each extract from the system. (ie so at week 10 I have 10 rows of data of the same sales item (opportunity))

    I am trying to create a number of formulas which are all dynamic in the sense that they look at the current line of data and returns a comparison to the previous data extract of data (row).

    I have attached an example spreadsheet which hopefully demonstrates what I would like the formula to return.
    However, there is a number of issues with the formulas as they are:

    1) They rely on the data in each extract to be in the same order (which they are not)
    2) They are not fixed as I would normally do, with an INDEX MATCH checking that row of data to the previous (usually use opportunity number)

    I have attached an example datasheet which hopefully shows this...

    So far I believe it may be something to do with OFFSET...

    Any help/ suggestions would be greatly appreciated
    Attached Files Attached Files
    Last edited by nathanwlee; 03-12-2019 at 01:22 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,735

    Re: Dynamic formula which compares a cell to a prior range of cells

    Named Ranges:
    Close_Date =DATA!$D$2:INDEX(DATA!$D:$D,COUNTA(DATA!$D:$D))
    Data_Extract_Number =DATA!$A$2:INDEX(DATA!$A:$A,COUNTA(DATA!$A:$A))
    Opp_Names =DATA!$C$2:INDEX(DATA!$C:$C,COUNTA(DATA!$C:$C))
    Opportunity_Name_List =DATA!$C$2:$C$11
    Pipeline_Stage =DATA!$F$2:INDEX(DATA!$F:$F,COUNTA(DATA!$F:$F))
    Values_data =DATA!$E$2:INDEX(DATA!$E:$E,COUNTA(DATA!$E:$E))


    Formula for column G:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 03-09-2019 at 11:28 PM.
    Ben Van Johnson

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Dynamic formula which compares a cell to a prior range of cells

    This seems to work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Adjust for the other columns. For example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Dynamic formula which compares a cell to a prior range of cells

    This is the updated workbook

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Dynamic formula which compares a cell to a prior range of cells

    Is this resolved?

  6. #6
    Registered User
    Join Date
    02-12-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    6

    Re: Dynamic formula which compares a cell to a prior range of cells

    Thank you all for your help. It is fixed now.

  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
    59,030

    Re: Dynamic formula which compares a cell to a prior range of cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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.

+ 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] Formula that identifies highest Score, compares days, and display's result in a cell
    By Doofus1 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-07-2018, 05:27 AM
  2. [SOLVED] Formula for Multiple Range Compares in Excel
    By RaMubara in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-27-2017, 03:01 AM
  3. [SOLVED] Excel formula that compares thousands of cells between two sheets
    By jaa522 in forum Excel General
    Replies: 6
    Last Post: 01-10-2016, 12:56 PM
  4. [SOLVED] If formula that compares Quantity before selecting a range to use
    By KARENKERBA in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2014, 12:07 AM
  5. Replies: 4
    Last Post: 08-15-2012, 12:27 PM
  6. [SOLVED] IF statement (non nested) that compares cell value against range
    By r2d3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2006, 08:11 AM
  7. IF statement (non nested) that compares cell value against range
    By r2d3 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2006, 06:10 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