Calculating an Average with only the last X number of values in a column

[AmeliaBedelia]

I have hit a wall with how to create an average of only the last X number of data in a column. Most of the answers I have found online for this have formulas that either do not work or I am not able to understand what needs to be amended to fit my situation.

My spreadsheet is as follows:
Column A Column B

(Want Average to posted here as data is added to this column)	(Want the date where there is a minimum of 5 and an 80% average to be posted here)
Results	Date
0	June 3 2018
0	June 4 2018
1	June 5 2018
1	June 5 2018
1	June 6 2018
0	June 7 2018
1	June 7 2018

<tbody>
</tbody>

First I need to calculate the average for the Results column, but only for the last 5 results. The results and date columns will be dynamic as data will constantly be added to these columns, so I need it to constantly look for the last row and only use the last 5. Results will only either be a 1 (100%) or a 0 (0%). There will be no empty rows. I was hoping that the average could be placed in a cell above the heading "Results", but due to some formulas I have tried, this sometimes creates a circular reference and may not be possible. So I do have flexibility that I can post the average in another column.

I want the average to post even if the minimum number is not reached, which is 5 in this case.

For example,
First Result:

0%
Results	Date
0	June 3 2018

<tbody>
</tbody>

Second Result:

0%
Results	Date
0	June 3 2018
0	June 4 2018

<tbody>
</tbody>

Third Result:

33%
Results	Date
0	June 3 2018
0	June 4 2018
1	June 5 2018

<tbody>
</tbody>

Fourth Result:

50%
Results	Date
0	June 3 2018
0	June 4 2018
1	June 5 2018
1	June 5 2018

<tbody>
</tbody>

Fifth result:

60%
Results	Date
0	June 3 2018
0	June 4 2018
1	June 5 2018
1	June 5 2018
1	June 6 2018

<tbody>
</tbody>

Sixth Result:

60%	(Want Date hit 80% here)
Results	Date
0	June 3 2018
0	June 4 2018
1	June 5 2018
1	June 5 2018
1	June 6 2018
0	June 7 2018

<tbody>
</tbody>

Once there are a minimum of 5 results and it reaches an average of 80% I want the date next to the result that created the 80% to be posted into the cell above the Date heading.

So in this example, since the minimum # we want is 5, it is only after the 7th result where there is a minimum of 5 results and the average has reached an 80%, so I want the date to be posted above the Date heading:

80%	June 7 2018
Results	Date
0	June 3 2018
0	June 4 2018
1	June 5 2018
1	June 5 2018
1	June 6 2018
0	June 7 2018
1	June 7 2018

<tbody>
</tbody>

Thanks

 

[AhoyNC]

Maybe something like this.
See below - this didn't paste right.

<tbody>
</tbody>

 

[AhoyNC]

Maybe like this:

Excel Workbook
	A	B
1	80%	June 7 2018
2	Results	Date
3	0	June 3 2018
4	0	June 4 2018
5	1	June 5 2018
6	1	June 5 2018
7	1	June 6 2018
8	1	June 7 2018
Sheet

 

[Saba Sabaratnam]

Another way to do is given below.

Enter the first table into column A to C - (you can keep the table any range you want. It is just to help you understand the formula)

Enter the cut off date (say 4 June 2018) into F3

Enter the following array formula into E1 by pressing Shift + Control + Enter

= AVERAGE(IF((LARGE(IF(B5:B11<=F3,A5:A11,-1),ROW(H1:H5)))>=0,(LARGE(IF(B5:B11<=F3,A5:A11,-1),ROW(H1:H5))),""))

Let us know how you go.

Kind regards

Saba

 

[Saba Sabaratnam]

Please note that Large will pick same dates more than one if they are the same date and fall withing top five dates. Example is 7 June 2018.

 

[Eric W]

One more option:

	A	B
1	Avg of last 5	Date > 80%
2	0.8	6-Jan
3	Results	Date
4	0	1-Jan
5	1	2-Jan
6	0	3-Jan
7	1	4-Jan
8	1	5-Jan
9	1	6-Jan

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula A2 =AVERAGE(INDEX(A:A,MAX(ROW(A4),LOOKUP(2,1/(A4:A100<>""),ROW(A4:A100)-4))):INDEX(A:A,LOOKUP(2,1/(A4:A100<>""),ROW(A4:A100)))) B2 =IF(A2>=0.8,LOOKUP(2,1/(B4:B100<>""),B4:B100),"") <tbody> </tbody>

<tbody>
</tbody>

 

[AmeliaBedelia]

Thank you AhoyNC and Eric - both your options worked for exactly what I need.

One more question though, how do I adjust the formulas if I want to look at the last 10 instead of 5?

 

[Eric W]

On my A2 formula, just change the -4 to -9.

Glad we could help!

 

[AhoyNC]

Same with my formula in A1, just change the 4 to a 9.

[TABLE="width: 1296"]
<colgroup><col width="1296"></colgroup><tbody>[TR]
   [TD="width: 1296"]IF(COUNT(A3:A500000)<=[COLOR=#ff0000]9[/COLOR],AVERAGE(A3:A500000),AVERAGE(INDEX(A3:A500000,MATCH(9.99999999999999E+307,A3:A500000)-[COLOR=#ff0000]9[/COLOR]):INDEX(A3:A500000,MATCH(9.99999999999999E+307,A3:A500000))))
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[AmeliaBedelia]

Thanks again to both Eric and AhoyNC!
These both work marvellously and are much easier than what I was trying to do prior to posting this