+ Reply to Thread
Results 1 to 5 of 5

Averageifs and rolling data range

  1. #1
    Registered User
    Join Date
    10-29-2013
    Location
    Saskatchewan, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Averageifs and rolling data range

    Hello,

    I have a averageifs formula:

    = AVERAGEIFS(C4:C5000,B4:B5000,Data!B4)

    Is there away I can just average the last 10 rolling data entries of column C that meet the criteria in Column B.

    Thanks
    Rob
    Last edited by Wolfgang17; 03-24-2014 at 07:40 AM.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Averageifs and rolling range

    Hi,

    Would be better if you could attach an actual workbook so that this can be verified, but perhaps this array formula**:

    =AVERAGE(IF(B4:B5000=Data!B4,IF(ROW(B4:B5000)>=LARGE(IF(B4:B5000=Data!B4,ROW(B4:B5000)),10),C4:C5000)))


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    10-29-2013
    Location
    Saskatchewan, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Averageifs and rolling data range

    Sorry for the delay in my reply.

    I've uploaded the spreadsheet. I'm trying to average last 10 Gallons run of softener #1 in cell G4. As more data (gallons run For Softener #1) are added to column C you get just the last 10 data entries.
    Does this make sense.

    Thanks for you help.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Averageifs and rolling data range

    Quote Originally Posted by Wolfgang17 View Post
    Sorry for the delay in my reply.

    I've uploaded the spreadsheet. I'm trying to average last 10 Gallons run of softener #1 in cell G4. As more data (gallons run For Softener #1) are added to column C you get just the last 10 data entries.
    Does this make sense.

    Thanks for you help.
    try use formula
    =AVERAGE(IF(B4:B5000=E13,IF(ROW(B4:B5000)>=LARGE(IF(B4:B5000=E13,ROW(B4:B5000)),5),C4:C5000)))

    Plz press Ctrl+Shift+Enter to finish input formula

    try replace number 5 in above formula (fill red color) to 10 as there are enough or more than 10 items....
    Best regard, -)iger-/iger
    If you are pleased with a solution mark your post SOLVED.

  5. #5
    Registered User
    Join Date
    10-29-2013
    Location
    Saskatchewan, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Averageifs and rolling data range

    That worked nicely.

    Thanks for your 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. AVERAGEIFS with multiple criteria on one range
    By ctsmith84 in forum Excel General
    Replies: 7
    Last Post: 04-23-2020, 11:31 PM
  2. [SOLVED] Averageifs using a named range?
    By fgruhlke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-18-2014, 02:16 PM
  3. Using HLOOKUP to determine the range to AVERAGEIFS
    By wishkey in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2013, 10:01 PM
  4. [SOLVED] Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)
    By thaphthia in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 04-11-2013, 05:22 PM
  5. Replies: 3
    Last Post: 04-17-2012, 09:54 AM

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