+ Reply to Thread
Results 1 to 7 of 7

Conditional Rolling Data using OFFSET or INDEX (or anything else)

  1. #1
    Registered User
    Join Date
    05-17-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Conditional Rolling Data using OFFSET or INDEX (or anything else)

    Hey,

    Not sure if this is possible, but it'd be greatly helpful if it was.

    I am looking for a function (OFFSET or INDEX seem the best bet) that only counts the last n instances of an event if it meets certain criteria.

    For Example: Say Column A contains "text" ("Y" and "Z") and Column B contains a value. I'd like a formula that sums the last n instances that Column A contains "Y".

    Sample:

    A B
    Y 18
    Z 23
    Z 16
    Y 18
    Y 21
    Y 24
    Z 27
    Z 13
    Y 19
    Z 15

    Say in this example I want the last 3 instances of Y (19+24+21=64) or Z (15+13+27=55), but for it to update when new values of each are added. So, before the final "Y" value was added Y (24+21+18=63).

    Any help would be appreciated.
    Last edited by HavokJak; 10-26-2015 at 01:37 AM.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

    I think this may work... it is an ARRAY formula, meaning you should select a cell, click in the formula bar, and paste the following formula. Instead of hitting ENTER, you have to press CTRL + SHIFT + ENTER to apply it.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Of course, you will need to make the range longer than you would ever need to go, say A1:A500, or something like that, so it looks in those cells for new entries.

    - Moo

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

    Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

    From C2 then drag down:

    =IFERROR(SUMPRODUCT(--(IF($A$1:$A1=$A2,ROW($A$1:$A1),0)>=LARGE(IF($A$1:$A1=$A2,ROW($A$1:$A1),""),3))*$B$1:$B1)," less than 3 of"&$A2)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Quang PT

  4. #4
    Registered User
    Join Date
    10-07-2015
    Location
    Vancouver, BC, Canada
    MS-Off Ver
    Office 365
    Posts
    22

    Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

    here you are
    you can select the Y ore Z from the list

    Feel free to find more tutorials on my Excel Blog.
    Attached Files Attached Files

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

    Assumes there will always be 3 instances.

    Data Range
    A
    B
    C
    D
    E
    1
    ------
    ------
    ------
    ------
    ------
    2
    Y
    18
    Y
    64
    3
    Z
    23
    Z
    55
    4
    Z
    16
    5
    Y
    18
    6
    Y
    21
    7
    Y
    24
    8
    Z
    27
    9
    Z
    13
    10
    Y
    19
    11
    Z
    15
    12


    This array formula** entered in E2 and copied down:

    =SUM(LOOKUP(LARGE((A$2:A$20=D2)*ROW(A$2:A$20),{1,2,3}),ROW(A$2:A$20),B$2:B$20))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    05-17-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

    Thank You bsuperiorsystem,

    This works perfectly. One question, is there any way to make it so that if Column A contains a value ("Y") and Column B is blank it does not count that value? Currently, it returns an N/A error (which makes sense).

    I think I can figure it out or can work around it, but if you know offhand it'd be greatly appreciated.

    Thanks again.
    Last edited by HavokJak; 10-26-2015 at 01:16 PM.

  7. #7
    Registered User
    Join Date
    05-17-2014
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Conditional Rolling Data using OFFSET or INDEX (or anything else)

    Moo,

    Thank you, this works as well, and may be easier for me to work with. One question similar to my previous post. When a there's a value in Column A but not in Column B any way to make it so it does not count (currently it counts as zero which makes sense).

    I can probably play around with some version of IF(NOT(ISBLANK(*))) but if you know off hand it'd be a great help.

+ 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. Index match offset to get subsequent index values in a column
    By Andrew_Step in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2013, 02:55 PM
  2. Using INDEX, OFFSET, etc. to manage data
    By tshine1030 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2013, 09:36 PM
  3. Struggling with using OFFSET formula for rolling averages
    By greykitten in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2013, 10:31 AM
  4. Replies: 2
    Last Post: 01-14-2013, 06:30 AM
  5. data validation using offset or index match
    By arnab0711 in forum Excel General
    Replies: 3
    Last Post: 01-24-2012, 06:46 AM
  6. 90 Day Rolling Period help with INDEX?
    By mikeydaman in forum Excel General
    Replies: 2
    Last Post: 07-25-2011, 11:21 PM
  7. Replies: 2
    Last Post: 12-10-2010, 11:39 AM
  8. Rolling Average:use INDEX and OFFSET
    By SKAh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2007, 09:41 PM

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