+ Reply to Thread
Results 1 to 7 of 7

Formula to get data from 7th field (dynamic entry)

  1. 08-23-2011, 04:06 AM #1
    ebachour is offline
    Registered User
    Join Date
    08-23-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Formula to get data from 7th field (dynamic entry)

    Hi guys,

    I'm trying to do a simple calculation where I minus two cells with one another to get the sum of the difference.
    The catch is though, the cells are in a column that is ever-increasing with new entries. I am trying to do a %Change calculation on a 7 day basis. So in other words, I'm trying to calculate the %change of the 7th-last entry and the last entered entry in the column (this moves every time as new data is added to the bottom of column A, and needs to update with the most current values, but it is always the 7th-last entry minus the last entry of column A.

    I have attached an example of my sheet. The area of interest is marked in red.
    Yellow represents the constant stream of new data that is added.

    Any help would be much appreciated
    Last edited by ebachour; 08-23-2011 at 05:59 AM. Reason: removed attachment, put in subsequent post.
    Reply With QuoteRegister To Reply
  2. 08-23-2011, 04:53 AM #2
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Formula to get data from 7th field (dynamic entry)

    What is wrong with what you have?

    You could limit it a tad more

    =MEDIAN(INDEX(A:A,MATCH(99^99,A1:A64999)-7+1):INDEX(A:A,MATCH(99^99,A1:A64999)))
    Reply With QuoteRegister To Reply
  3. 08-23-2011, 04:56 AM #3
    ebachour is offline
    Registered User
    Join Date
    08-23-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula to get data from 7th field (dynamic entry)

    Quote Originally Posted by Bob Phillips View Post
    What is wrong with what you have?

    You could limit it a tad more

    =MEDIAN(INDEX(A:A,MATCH(99^99,A1:A64999)-7+1):INDEX(A:A,MATCH(99^99,A1:A64999)))
    Sorry I explained what I needed wrong. The red cell (with the explanation on it) is the cell that I need to show the 7 day change % (not the median cell to the left of it).
    Reply With QuoteRegister To Reply
  4. 08-23-2011, 05:01 AM #4
    ebachour is offline
    Registered User
    Join Date
    08-23-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula to get data from 7th field (dynamic entry)

    Please use this new attachment.

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 08-23-2011, 05:35 AM #5
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Formula to get data from 7th field (dynamic entry)

    try
    =(INDEX(A1:A64999,MATCH(9.99999999999999E+307,A1:A64999))-INDEX(A1:A64999,(MATCH(9.99999999999999E+307,A1:A64999)-7+1)))/INDEX(A1:A64999,(MATCH(9.99999999999999E+307,A1:A64999)-7+1))
    Last edited by Azam Ali; 08-23-2011 at 05:38 AM.
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.
    Reply With QuoteRegister To Reply
  6. 08-23-2011, 05:35 AM #6
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Formula to get data from 7th field (dynamic entry)

    This array formula

    =(INDEX(A:A,MATCH(99^99,A:A))-INDEX(A:A,MATCH(99^99,A:A)-6))/INDEX(A:A,MATCH(99^99,A:A)-6)
    Reply With QuoteRegister To Reply
  7. 08-23-2011, 05:58 AM #7
    ebachour is offline
    Registered User
    Join Date
    08-23-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula to get data from 7th field (dynamic entry)

    Thanks both of you, both formulas worked!

    Very much appreciated
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1