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.

 

[Mistoto2]

Thanks very much, but I have a couple of issues despite I have followed your instructions very carefully. The first COUNTIF-formula works fine for me I get the correct value, but it's the other formulas I'm struggeling with.
I don't know if I have another version of Excel than you, because I have to write the formula like this to make it work =COUNTIFS($A$2:$A$15;H1;E2:E15;H2)-1 (with ; instead of , ) and I have tried to replace all "," with semicolons and it's still not working? Do you know how I can fix this?

The definings I have put in the name manager is like this:

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



Sales
=Sheet1!$F$2;INDEX(Sheet1!$F:$F;MATCH(9,99999999999999E+307;Sheet1!$A:$A)-1)


Weekday
=Sheet1!$E$2;INDEX(Sheet1!$E:$E;MATCH(9,99999999999999E+307;Sheet1!$A:$A)-1)


Year
=Sheet1!$A$2;INDEX(Sheet1!$A:$A;MATCH(9,99999999999999E+307;Sheet1!$A:$A)-1)

I don't know if this is the correct way but Excel doesn't seem to detect any problem with the formula when entered like the above.
One noteable thing is that Excel was also okay with JUST replacing the first semicolon (the one before the "index"-formula)in all the name definings. I don't know which is the correct way or whats the differnce it is when writing ; or : in excel, but I just wanted you to know.

 

[Aladin Akyurek]

Thanks very much, but I have a couple of issues despite I have followed your instructions very carefully.

...

See the workbook that implements the set up:
https://dl.dropboxusercontent.com/u...st n numbers with date and year criteria.xlsx

 

[Mistoto2]

Thank you very much for all your help! It works exactly like I wanted (and tried to describe :/) thank you again :P I just have a little question left to ask you: Which changes in the Excel document you posted earlier should I make if I wanted (for example) 20 sale-values to be selected and averaged from the year of 2011 and 2010 instead of just 3 per year? Could you try to describe what and how to make the changes and then link a excel document?

 

[Aladin Akyurek]

Thank you very much for all your help! It works exactly like I wanted (and tried to describe :/) thank you again :P

You are welcome.

I just have a little question left to ask you: Which changes in the Excel document you posted earlier should I make if I wanted (for example) 20 sale-values to be selected and averaged from the year of 2011 and 2010 instead of just 3 per year? Could you try to describe what and how to make the changes and then link a excel document?

The value of 3 in the MIN(3,H3) bit which appears in the formulas of H5 and H6. If you want to, enter 20 in G3 and replace 3 in MIN(3,H3) with G3...

 

[Mistoto2]

so just basically just switch from MIN(3,H3) to MIN(20;H3) or to MIN(G3;H3) if I put the value 20 in the cell G3 ?

 

[Aladin Akyurek]

so just basically just switch from MIN(3,H3) to MIN(20;H3) or to MIN(G3;H3) if I put the value 20 in the cell G3 ?

To MIN(G3;H3)

 

[Mistoto2]

Hi! again..
I was just curious how it would be if you wanted to add a time-criteria to the current excel averaging formulas? I have written an example in the new file below and also stated the requested output and made a criteria explaination as well in the Excel file. Would you mind look at it?

The new excel file with time criterias -> https://www.dropbox.com/s/cczs1ix7r...umbers with time,date and year criterias.xlsx

 

[Aladin Akyurek]

The data is located in Sheet1, A:H...

[TABLE="width: 441"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2560" width=72><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5404" width=152><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2787" span=2 width=78><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Year[/TD]
[TD="class: xl63, width: 72, bgcolor: transparent"]Month[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Day[/TD]
[TD="class: xl64, width: 152, bgcolor: transparent"]complete date[/TD]
[TD="class: xl63, width: 78, bgcolor: transparent"]Weekday[/TD]
[TD="class: xl63, width: 78, bgcolor: transparent"]Time[/TD]
[TD="class: xl63, width: 79, bgcolor: transparent"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2010[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1/21/2010[/TD]
[TD="class: xl65, bgcolor: transparent"]Thur[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]80[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2010[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2/12/2010[/TD]
[TD="class: xl65, bgcolor: transparent"]Fri[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]12:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: #00b0f0, align: right"]2010[/TD]
[TD="class: xl77, bgcolor: #00b0f0, align: right"]2[/TD]
[TD="class: xl77, bgcolor: #00b0f0, align: right"]14[/TD]
[TD="class: xl78, bgcolor: #00b0f0, align: right"]2/14/2010[/TD]
[TD="class: xl77, bgcolor: #00b0f0"]Sun[/TD]
[TD="class: xl79, bgcolor: #00b0f0, align: right"]12:00[/TD]
[TD="class: xl77, bgcolor: #00b0f0, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2010[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]3/3/2010[/TD]
[TD="class: xl65, bgcolor: transparent"]Wed[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]0:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]75[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]2010[/TD]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]4[/TD]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]4[/TD]
[TD="class: xl67, bgcolor: #00b0f0, align: right"]4/4/2010[/TD]
[TD="class: xl66, bgcolor: #00b0f0"]Sun[/TD]
[TD="class: xl76, bgcolor: #00b0f0, align: right"]12:00[/TD]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]90[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]2010[/TD]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]5[/TD]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]2[/TD]
[TD="class: xl67, bgcolor: #00b0f0, align: right"]5/2/2010[/TD]
[TD="class: xl66, bgcolor: #00b0f0"]Sun[/TD]
[TD="class: xl76, bgcolor: #00b0f0, align: right"]12:00[/TD]
[TD="class: xl66, bgcolor: #00b0f0, align: right"]65[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2010[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]6/15/2010[/TD]
[TD="class: xl65, bgcolor: transparent"]Tue[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]12:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]51[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2010[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]7/19/2010[/TD]
[TD="class: xl65, bgcolor: transparent"]Mon[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]7:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]43[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2011[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1/19/2011[/TD]
[TD="class: xl65, bgcolor: transparent"]Wed[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]10:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: yellow, align: right"]2011[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]2[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]14[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]2/14/2011[/TD]
[TD="class: xl69, bgcolor: yellow"]Mon[/TD]
[TD="class: xl74, bgcolor: yellow, align: right"]12:00[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2011[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]3/2/2011[/TD]
[TD="class: xl65, bgcolor: transparent"]Wed[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]0:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]90[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: yellow, align: right"]2011[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]4[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]4[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]4/4/2011[/TD]
[TD="class: xl69, bgcolor: yellow"]Mon[/TD]
[TD="class: xl74, bgcolor: yellow, align: right"]12:00[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]92[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: yellow, align: right"]2011[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]5[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]2[/TD]
[TD="class: xl70, bgcolor: yellow, align: right"]5/2/2011[/TD]
[TD="class: xl69, bgcolor: yellow"]Mon[/TD]
[TD="class: xl74, bgcolor: yellow, align: right"]12:00[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]16[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2011[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]6/18/2011[/TD]
[TD="class: xl65, bgcolor: transparent"]Sat[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]12:00[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #92d050, align: right"]2011[/TD]
[TD="class: xl71, bgcolor: #92d050, align: right"]7[/TD]
[TD="class: xl71, bgcolor: #92d050, align: right"]18[/TD]
[TD="class: xl72, bgcolor: #92d050, align: right"]7/18/2011[/TD]
[TD="class: xl71, bgcolor: #92d050"]Mon[/TD]
[TD="class: xl73, bgcolor: #92d050, align: right"]12:00[/TD]
[TD="class: xl71, bgcolor: #92d050, align: right"]8[/TD]
[/TR]
</TBODY>[/TABLE]


The processing is located in Sheet1, I:J...

[TABLE="width: 259"]
<COLGROUP><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6371" width=179><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 5944" width=167><TBODY>[TR]
[TD="class: xl66, width: 179, bgcolor: transparent, align: right"]2011[/TD]
[TD="class: xl65, width: 167, bgcolor: transparent"]Year[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Mon[/TD]
[TD="class: xl65, bgcolor: transparent"]Day[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]12:00:00 PM[/TD]
[TD="class: xl65, bgcolor: transparent"]Time[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]Requested N[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]Available N[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Requested average output[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]36[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #00b0f0, align: right"]65[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

List of names to be defined:

1. Date
2. Sales
3. Time
4. Weekday
5. Year
6. Endrow

Definitions are respectively:

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

I1, just enter:

=LOOKUP(9.99999999999999E+307,A:A)

I2, just enter:

=LOOKUP(REPT("z",255),E:E)

I3, just enter:

=LOOKUP(9.99999999999999E+307,F:F)

I4: 3

I5, just enter:

=COUNTIFS($A$2:$A$16,I1,E2:E16,I2,F2:F16,I3)-1

I6: Requested average output

I7, CSE:

=AVERAGE(IF(ROW(Sales)>=LARGE(IF(Year=I1,
  IF(Weekday=I2,IF(Time=I3,IF(ISNUMBER(Sales),
  ROW(Sales))))),MIN(I4,I5)),IF(Year=I1,IF(Weekday=I2,
  IF(Time=I3,IF(ISNUMBER(Sales),Sales))))))

I8, CSE:

=AVERAGE(IF(ISNUMBER(MATCH(Date,EDATE(IF(ROW(Sales)>=
  LARGE(IF(Year=I1,IF(Weekday=I2,IF(Time=I3,IF(ISNUMBER(Sales),
  ROW(Sales))))),MIN(3,I5)),IF(Year=I1,IF(Weekday=I2,
  IF(Time=I3,Date)))),-12),0)),Sales))

See the extended workbook:
https://dl.dropboxusercontent.com/u...mbers with time, date and year criterias.xlsx