0
votes

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)

1
What cell will this formula be typed in?Kyle
K2 - and will fill down from thereLoren S
What are you trying to do with the ">="&B2 and ">="&B2+9?Kyle
This was supposed to return the average total orders for nine day periods per product. I used a similar formula to find total orders per 9 day period per product but I see the problem is that some days may have one order others may have multiple while others have none. Since I am working with rolling totals and rolling averages I need to find a way to return average orders for 9 day periods per product without including repeat totals for the same day in my average.Loren S
I don't understand why you would care if the date is less than the date in the cell beneath it if you are simply going for a 9 day rolling average. Seeing a sample of your data would be incredibly helpful. Can't you just sort your data so the dates are always in asc/desc order?Kyle

1 Answers

0
votes

You can use a helper column. In cell E2 use the formula =A2=A1 and drag it down. Then in Columns F and G you can put your averageifs() formulas with an additional criteria. See below:

Column F:

=AVERAGEIFS(D:D,B:B,B2,E:E,"FALSE")

Column G:

=AVERAGEIFS(D:D,A:A,">="&A2,A:A,"<="&A2+9,B:B,B2,E:E,"FALSE")