Min of 5 cells before different from 0

[rama1234]

Hello everyone, thanks for your time in advance.
My first column are numbers, for example:
A1: Column 1
A2: 10
A3: 2
A4: 10
A5: 10
A6: 5
A7: 0
A8: 0
A9: 0
A10: 5
A11: 5
A12: 5
...
Then in my second column calculates the minimum of 5 cells before that are different from 0.
For example the cell B6 is the minimum of A6-A5-A4-A3-A2 different from 0, so is 2... But the cell B9 is also the minimum of A6-A5-A4-A3-A2 as I want the minimum of 5 numbers that are different from 0 and I don't know how to do it.
My formula in B9 is:
{=IF(ROW(A9)<=5;0;MIN(IF((INDEX(A:A;ROW(A10)-5):A9)<>0;(INDEX(A:A;ROW(A10)-5):A9))))}

My desired results in column 2 are:
B1: Column 2
B2: 0
B3: 0
B4: 0
B5: 0
B6: 2
B7: 2
B9: 2
B10: 2
B11: 5
B12: 5

Thanks a lot

 

[FormR]

Hi, here is one option you can try.

Excel 2013/2016
	A	B
2	10	0
3	2	0
4	10	0
5	10	0
6	5	2
7	0	2
8	0	2
9	0	2
10	5	2
11	5	5
12	5	5
Sheet1

Cell Formulas
Range	Formula
B2	{=IF(COUNTIFS(A$2:A2,"<>0")<5,0,MIN(IF(ISNUMBER(MATCH(ROW(A$2:A2),LARGE(IF(A$2:A2<>0,ROW(A$2:A2)),{1,2,3,4,5}),0)),A$2:A2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pgc01]

Hi

Another option, also array formula:

=IFERROR(MIN(IF(ROW($A$2:A2)>=LARGE(IF(A$2:A2<>0,ROW(A$2:A2)),5),IF($A$2:A2>0,$A$2:A2))),0)

 

[rama1234]

Thanks, it outputs the desired results but I want to use the minimum of 5 days as variable so I can change it to say 10, 20, 50... Maybe you know how to do it,
Thanks

 

[rama1234]

Wow! Thanks, this is what I was lloking for. Thanks both for your replies