I need to find the running average of a list of numbers for a specific product within a date range and only if the date in one row is less than the date of the cell beneath it. Currently my formula looks like this:
=AVERAGEIFS(D:D,A:A,">="&A2,A:A,"<="&A2+9,B:B,B2,????)
where D:D is my list of numbers, B:B is my product, and A:A contains dates (sometimes the same date repeats successively in a column). Everything works until I try add the last criteria (????) that only shows averages if one cell is less than the cell that follows it. Any help would be appreciated. Thanks!
A B C D E F
1/2/2015 FFW04 1 215 137.33 182.82
1/5/2015 FFW04 50 219 137.33 165.00
1/5/2015 FFW04 2 219 137.33 165.00
1/6/2015 FFW04 2 179 137.33 127.73
1/7/2015 FFW04 0 177 137.33 122.60
1/7/2015 FFW04 1 177 137.33 122.60
1/7/2015 FFW04 2 177 137.33 122.60
1/7/2015 FFW04 50 177 137.33 122.60
1/7/2015 FFW04 5 177 137.33 122.60
1/7/2015 FFW04 2 177 137.33 122.60
1/9/2015 FFW04 100 117 137.33 41.00
1/14/2015 FFW04 5 19 137.33 12.25
1/15/2015 FFW04 10 14 137.33 10.00
1/15/2015 FFW04 2 14 137.33 10.00
1/20/2015 FFW04 2 2 137.33 2.00
1/5/2015 FFEW015 5 44 36.78 42.14
1/5/2015 FFEW015 5 44 36.78 42.14
1/6/2015 FFEW015 6 52 36.78 34.71
1/7/2015 FFEW015 1 46 36.78 31.83
1/7/2015 FFEW015 9 46 36.78 31.83
1/9/2015 FFEW015 9 36 36.78 24.75
1/14/2015 FFEW015 9 27 36.78 21.00
1/15/2015 FFEW015 9 18 36.78 18.00
1/15/2015 FFEW015 9 18 36.78 18.00
A = date, B = Product, C = Orders
D = total orders per 9 day period=SUMIFS(C:C,A:A,">="&A2,A:A,"<="&A2+9,B:B,B2)
E = Average of total orders =AVERAGEIFS(D:D,B:B,B2)
F = Average of total orders =AVERAGEIFS(D:D,A:A,">="&A2,A:A,"<="&A2+9,B:B,B2)
Neither E nor F is quite what I am looking for because of the multiple returns per day in D. (I have removed columns from the original to simplify this sample)
">="&B2
and">="&B2+9
? – Kyle