average last 5 values

[Scarybuh]

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!

 

[sandy666]

with Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Example:[/td][td][/td][td=bgcolor:#70AD47]Avg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10​

[/td][td][/td][td=bgcolor:#E2EFDA]

4.8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

7​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

8​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

0​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

0​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

0​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

0​

[/td][td][/td][td][/td][/tr]
[/table]

[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]

 

[sandy666]

find me the last 5 values so then I can do Average, Min, Max...

again with Power Query

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Example:[/td][td][/td][td=bgcolor:#70AD47]Min[/td][td=bgcolor:#70AD47]Max[/td][td=bgcolor:#70AD47]Avg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10​

[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

8​

[/td][td=bgcolor:#E2EFDA]

4.8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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]

 

[AhoyNC]

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
	C	D	E
1
2	Data
3	10
4	20		Average
5	7		4.8
6	1
7	4
8	8
9	4
10	0
11	0
12	0
13	0
Sheet

 

[Scarybuh]

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).

 

[sandy666]

I have excel 2013 in the work pc so I would have to install Microsoft Power Query for Excel

sure, have a nice day