+ Reply to Thread
Results 1 to 7 of 7

Find last 6 (or3) dates that match a given criteria then find the average

  1. #1
    Registered User
    Join Date
    02-20-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    74

    Find last 6 (or3) dates that match a given criteria then find the average

    Hi Guys, I am hoping one of you can also help me do something similiar. I need to fine the last 3 dates (so starting from the bottom of a range) from a range of cells, sum them and then divide by 3 to provide an average. I had the following formula and excel attachment out in the forum but noone had a suggestion.

    =SUMPRODUCT(--('Weekly Vol Pivot'!$B$12:$B$300>=(MAX(IF('Weekly Vol Pivot'!$B$12:$B$300=CONCATENATE(VLOOKUP($D$5,Lookup!$C$17:$D$28,2,FALSE),$D$6),'Weekly Vol Pivot'!$B$12:$B$300))-42)),--(('Weekly Vol Pivot'!$B$12:$B$300= CONCATENATE(VLOOKUP($D$5,Lookup!$C$17:$D$28,2,FALSE),$D$6))),--('Weekly Vol Pivot'!$N$12:$N$300))/6

    Here i was trying to find the last 6 but the concept is the same. I am also attaching my excel workbook. I had to remove all the other formula's and just paste values except for the one cell where the formula is located on the summary sheet, cell d21.
    Attached Files Attached Files

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find last 6 (or3) dates that match a given criteria then find the average

    krunk,

    Attached is a modified version of your posted workbook. I created a new sheet (which you can hide) in order to have a helper column. I didn't use a helper column on the 'Daily Summary' sheet because of the way it was formatted (small rows, hidden rows, etc).

    On the new sheet (named 'Sheet1') in cell A1 is this formula to get the column B criteria:
    Please Login or Register  to view this content.

    That formula results in "06Week 3" which is what we need to match against in column B of the 'Weekly Vol Pivot' sheet.


    Then in 'Sheet1' cell A2 and copied down is this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter:
    Please Login or Register  to view this content.
    That formula will pull the bottom 6 values that match the criteria (if they exist). Because there are only 3 entries in column B that are "06Week 3" there are only 3 results returned. They also happen to all be the same value, but it does return them in reverse order (bottom listed first). Once it runs out of matches, the formula returns a blank to prevent errors with the Average function.


    Then, to get the average, in the 'Daily Summary' sheet cell D21 is this formula:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-20-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Find last 6 (or3) dates that match a given criteria then find the average

    [QUOTE=tigeravatar;2841522]krunk, is there a way to have the formula sum the values then just do the division within the same cell? i was hoping to not have to always do a manual process of updating the sheet and rather have it update automatically? The reason i am asking is that i have to provide this summary page to executives and its already a very manually intensive process. I want to limit the possibilities of errors by making as much of it as automative as possible.

    The LY will be the same type of formula and has a similar pivot table that it is linked to. The formula that provides the range is simple a vlookup formula that finds the referenced aggregator on the summary tab.

    Thanks Again.

    Kali

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find last 6 (or3) dates that match a given criteria then find the average

    It should update automatically as is. You will see that if you change the values for the Month (D5) or Week (D6).
    As for getting it into a single cell, there's no way to do that that I'm aware of, but I'll ping the formula masters and see if they know of a way.

  5. #5
    Registered User
    Join Date
    02-20-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Find last 6 (or3) dates that match a given criteria then find the average

    Quote Originally Posted by tigeravatar View Post
    It should update automatically as is. You will see that if you change the values for the Month (D5) or Week (D6).
    As for getting it into a single cell, there's no way to do that that I'm aware of, but I'll ping the formula masters and see if they know of a way.
    Not sure what i am doing wrong now. I typed the formula into my main workbook but it returns a blank cell meaning it is not finding a matching criteria but there is a matching criter. can you look at it again. Or tell me if i need to do something special to return a value?

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Find last 6 (or3) dates that match a given criteria then find the average

    Did you add the helper column on a new sheet like in the workbook I posted? That sheet can be hidden, but the helper column needs to exist for the formula to reference.

  7. #7
    Registered User
    Join Date
    02-20-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Find last 6 (or3) dates that match a given criteria then find the average

    Nevermind. I got it. Thank you so much.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1