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

A

AmeliaBedelia

New Member
Joined
Apr 8, 2018
Messages
19
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD](Want Average to posted here
as data is added to this column)[/TD]
[TD](Want the date where there
is a minimum of 5 and an 80%
average to be posted here)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 4 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 6 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 7 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 7 2018[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[/TR]
</tbody>[/TABLE]

Second Result:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 4 2018[/TD]
[/TR]
</tbody>[/TABLE]

Third Result:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]33%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 4 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[/TR]
</tbody>[/TABLE]


Fourth Result:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]50%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 4 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[/TR]
</tbody>[/TABLE]

Fifth result:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]60%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 4 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 6 2018[/TD]
[/TR]
</tbody>[/TABLE]

Sixth Result:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]60%[/TD]
[TD](Want Date hit 80% here)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 4 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 6 2018[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 7 2018[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]80%[/TD]
[TD]June 7 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 3 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 4 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 5 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 6 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]June 7 2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June 7 2018[/TD]
[/TR]
</tbody>[/TABLE]


Thanks :confused:
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Maybe something like this.
See below - this didn't paste right.


<tbody>
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]

</tbody>
 
Last edited:
Upvote 0
Maybe like this:
Excel Workbook
AB
180%June 7 2018
2ResultsDate
30June 3 2018
40June 4 2018
51June 5 2018
61June 5 2018
71June 6 2018
81June 7 2018
Sheet
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
One more option:


AB
Avg of last 5Date > 80%
ResultsDate

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]6-Jan[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1-Jan[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2-Jan[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3-Jan[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4-Jan[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5-Jan[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6-Jan[/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(A2>=0.8,LOOKUP(2,1/(B4:B100<>""),B4:B100),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?
 
Upvote 0
Same with my formula in A1, just change the 4 to a 9.

Code: 
[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]
 
Upvote 0
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 :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Pulling Correct Number of Dates Based on Column Value
Replies
1
Views
41
dreid1011
dreid1011
S
  • Question Question
Yearly Interest Calculation
Replies
1
Views
334
signup
S
D
  • Solved
Add content to empty cells in column - macro
Replies
6
Views
77
AFZAL SOHAIL
A
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf
S
  • Question Question
Employee Training Tracker
Replies
0
Views
233
Schuyla
S

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top