averaging the last n numbers with date and year criteria

[Mistoto2]

Hi!
Here's what i want to do explained in steps:
Step 1 if we are at the day of 2011-05-02 (just for example), I want to average the last 15 historical sale-values that occured at a Monday in year 2011 after 2011-05-02.
Step 2 Then I want to average the 15 sale-values for the same days (here I mean day as a number ex. 21:st) as in step 1 but for the previous year (which is 2010).


What I've done this far is that I've come up with 2 AVERAGEIFS-functions, one to average the sales on a specific weekdays (ex.mondays) and in year 2011. The Other AVERAGEIFS-function only averages the sale-values at the specifc day (ex. the 3:rd) in the year of 2010. But the problem here is that the AVERAGEIFS functions are averaging all the days at a specified year, not just the 15 last that I want, so there's my problem. I need to have a function that only averages the 15 last sale-values!

I would really appreciate if someone could help me with this problem.

 

[Aladin Akyurek]

Care to post 5 records along with the manually calculated average 3 numbers?

 

[Mistoto2]

Okay here I have a pic of what my data table looks like if that's what you ment? -> https://www.dropbox.com/s/12aiqkt6798sl50/sales-values_datatable.jpg

 

[Aladin Akyurek]

Okay here I have a pic of what my data table looks like if that's what you ment? -> https://www.dropbox.com/s/12aiqkt6798sl50/sales-values_datatable.jpg

Could you post that here in a way that the sample can be directly read into Excel (without retyping) and the outcome that goes along with it?

 

[Mistoto2]

Okay I will do my best.

See the new picture -> https://www.dropbox.com/s/9vl8nwgp002ul9b/excel_table_2.jpg

The green line is our starting point (imagine that that's today just for simplicity). And I want to average 3 sales-values from year 2010(blue rows) and 3 sale-values from year 2011 (yellow rows), so I have 6 sale-values in total that I want to average.
The reason I want the average of the yellow sale-values is because the occur at a wednesday in year 2011 and the reason why I want to average the blue sale-values is because they occur at the same day and in the same month (not weekdays, its a difference) as the ones that are on the yellow rows, but in year 2010.

And I might say that the number of 3 sale-values per year needs to be fixed, For example: If there was 90 sales that occured in 2011-01-05 I wouldn't want to average that in this case, because I already have 3 sale-values (1:2011-06-15, 2:2011-04-06 and 3: 2011-01-19) that occur at a wednesday that's more recent that that one.

So the outcome of all this is an average value of the yellow and blue highlighted sale-values (6 sale-values) from year 2010 and 2011.

 

[Aladin Akyurek]

Sorry for insisting. Would you try rather one of the following methods: Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste?

 

[Mistoto2]

I just linke you a excel file so you can download it and edit it yourself is that okay?

-> https://www.dropbox.com/s/bxjhq2mz152q85u/Sales.xlsx

 

[Aladin Akyurek]

I just linke you a excel file so you can download it and edit it yourself is that okay?

-> https://www.dropbox.com/s/bxjhq2mz152q85u/Sales.xlsx

What are the criteria we must use in order to obtain the average of last 3 sales?

 

[Mistoto2]

The criteria for obtain the average for the yellow sales are:
- The sales have to occur at the same weekday (ex. wednesday) and the same year but before the starting day(green row).
- And it should only be 3 sales that are selected

The criteria for obtaining the blue colored sales are:
- The sales has to occur at the previous year (in this case in 2010)
- The sales has to occur in the same month & day (ex. 21:st of june) as the sales selected from the year of 2011 (the yellow ones).
- And also here only 3 sale-values can be selected, not any more.

*And I critera which is applied to both selection of 2011 and 2010 sale-values are that they should be the most recent ones when counting backwards from the starting date(which is 2011-07-20 here in our case).

 

[Aladin Akyurek]

The criteria for obtain the average for the yellow sales are:
- The sales have to occur at the same weekday (ex. wednesday) and the same year but before the starting day(green row).
- And it should only be 3 sales that are selected

The criteria for obtaining the blue colored sales are:
- The sales has to occur at the previous year (in this case in 2010)
- The sales has to occur in the same month & day (ex. 21:st of june) as the sales selected from the year of 2011 (the yellow ones).
- And also here only 3 sale-values can be selected, not any more.

*And I critera which is applied to both selection of 2011 and 2010 sale-values are that they should be the most recent ones when counting backwards from the starting date(which is 2011-07-20 here in our case).

[TABLE="width: 474"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Year
[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent"]Month
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Day
[/TD]
[TD="class: xl66, width: 152, bgcolor: transparent"]complete date
[/TD]
[TD="class: xl65, width: 78, bgcolor: transparent"]Weekday
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Sales
[/TD]
[TD="class: xl67, width: 36, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 101, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]2010
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]1
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]19
[/TD]
[TD="class: xl69, bgcolor: #00b0f0, align: right"]1/19/2010
[/TD]
[TD="class: xl68, bgcolor: #00b0f0"]Tue
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]80
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Wed
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2010
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]2/3/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"]Wed
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2010
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]3/21/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"]Sun
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Average(s)
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]2010
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]4
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]6
[/TD]
[TD="class: xl69, bgcolor: #00b0f0, align: right"]4/6/2010
[/TD]
[TD="class: xl68, bgcolor: #00b0f0"]Tue
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]90
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]41.33333333
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2010
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]13
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]5/13/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"]Thur
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]65
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]73.66666667
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]2010
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]6
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]15
[/TD]
[TD="class: xl69, bgcolor: #00b0f0, align: right"]6/15/2010
[/TD]
[TD="class: xl68, bgcolor: #00b0f0"]Tue
[/TD]
[TD="class: xl68, bgcolor: #00b0f0, align: right"]51
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2010
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]7/19/2010
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]43
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: yellow, align: right"]2011
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]1
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]19
[/TD]
[TD="class: xl72, bgcolor: yellow, align: right"]1/19/2011
[/TD]
[TD="class: xl71, bgcolor: yellow"]Wed
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]20
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2011
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]28
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]2/28/2011
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2011
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]3/1/2011
[/TD]
[TD="class: xl67, bgcolor: transparent"]Tue
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]217
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: yellow, align: right"]2011
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]4
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]6
[/TD]
[TD="class: xl72, bgcolor: yellow, align: right"]4/6/2011
[/TD]
[TD="class: xl71, bgcolor: yellow"]Wed
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]92
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]2011
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]5/2/2011
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mon
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: yellow, align: right"]2011
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]6
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]15
[/TD]
[TD="class: xl72, bgcolor: yellow, align: right"]6/15/2011
[/TD]
[TD="class: xl71, bgcolor: yellow"]Wed
[/TD]
[TD="class: xl71, bgcolor: yellow, align: right"]12
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #92d050, align: right"]2011
[/TD]
[TD="class: xl73, bgcolor: #92d050, align: right"]7
[/TD]
[TD="class: xl73, bgcolor: #92d050, align: right"]20
[/TD]
[TD="class: xl74, bgcolor: #92d050, align: right"]7/20/2011
[/TD]
[TD="class: xl73, bgcolor: #92d050"]Wed
[/TD]
[TD="class: xl73, bgcolor: #92d050, align: right"]8
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

H3, just enter:

=COUNTIFS($A$2:$A$15,H1,E2:E15,H2)-1

H5, control+shift+enter (CSE), not just enter:

=AVERAGE(IF(ROW(Sales)>=LARGE(IF(Year=H1,IF(Weekday=H2,
  IF(ISNUMBER(Sales),ROW(Sales)))),MIN(3,H3)),IF(Year=H1,
  IF(Weekday=H2,IF(ISNUMBER(Sales),Sales)))))

H6, CSE:

=AVERAGE(IF(ISNUMBER(MATCH(Date,EDATE(IF(ROW(Sales)>=LARGE(IF(Year=H1,
  IF(Weekday=H2,IF(ISNUMBER(Sales),ROW(Sales)))),MIN(3,H3)),
  IF(Year=H1,IF(Weekday=H2,Date))),-12),0)),Sales))

You need the following definitions which appear in the formulas... This is to be done using Formulas | Name Manager.

Define Date as referring to:

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,
  MATCH(9.99999999999999E+307,Sheet1!$A:$A)-1)

Define Sales as referring to:

=Sheet1!$F$2:INDEX(Sheet1!$F:$F,
  MATCH(9.99999999999999E+307,Sheet1!$A:$A)-1)

Define Weekday as referring to:

=Sheet1!$E$2:INDEX(Sheet1!$E:$E,
  MATCH(9.99999999999999E+307,Sheet1!$A:$A)-1)

Define Year as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,
  MATCH(9.99999999999999E+307,Sheet1!$A:$A)-1)