average last 5 values

Scarybuh

New Member
Joined
Nov 5, 2019
Messages
6
Hello,

I want excel to find me the last 5 values so then I can do Average, Min, Max...
I found this formula

=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
from site
https://exceljet.net/formula/average-last-5-values
</pre>
but the problem is my column is full of numbers and I want it to ignore the Zeros. So what I really want is the last 5 non zero values
Example:
10
20
7
1
4
8
4
0
0
0
0

Here I want the formula to pick up the 4,8,4,1,7. (the 5 last numbers that are not 0)
Should I copy the column to another place removing the zeros first (how to automate it?) and then use the offset on that new column?

much appreciated if anyone could help!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
with Power Query

Example:Avg
10​
4.8​
20​
7​
1​
4​
8​
4​
0​
0​
0​
0​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each ([#"Example:"] <> 0)),
    Last5 = Table.LastN(Filter, 5),
    Avg = List.Average(Last5[#"Example:"]),
    C2T = #table(1, {{Avg}}),
    Ren = Table.RenameColumns(C2T,{{"Column1", "Avg"}})
in
    Ren[/SIZE]
 
Upvote 0
find me the last 5 values so then I can do Average, Min, Max...
again with Power Query

Example:MinMaxAvg
10​
1​
8​
4.8​
20​
7​
1​
4​
8​
4​
0​
0​
0​
0​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each ([#"Example:"] <> 0)),
    Last5 = Table.LastN(Filter, 5),
    List = Table.AddColumn(Last5, "Custom", each 1),
    Group = Table.Group(List, {"Custom"}, {{"Min", each List.Min([#"Example:"]), type number}, {"Max", each List.Max([#"Example:"]), type number}, {"Avg", each List.Average([#"Example:"]), type number}}),
    RC = Table.RemoveColumns(Group,{"Custom"})
in
    RC[/SIZE]
 
Upvote 0
Here is a formula way.
For efficiency I would not reference whole columns if you can avoid it. If your data will never go below say row 10000 then maybe something like C3:C10000 (as in the below example).

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
CDE
1
2Data
310
420Average
574.8
61
74
88
94
100
110
120
130
Sheet
 
Upvote 0
Thanks everyone!
I have excel 2013 in the work pc so I would have to install Microsoft Power Query for Excel.
I will try the formula way first as my values would not exceed 31 (it comes from days in a month, maximum would be 31).
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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