Excel getting value from cells above a cell containing 0 -without vba-

Question

I have a column with running hours of a machine I have (these are filled in by hand).

After the machine breaks down the machine will be repaired and the hour counter will be reset to 0 hours.

The first cell (in the screenshot) with the number 2538 in it is calculated by using:

=IF(COUNTIF(K2:K31,"0"),INDEX(K2:K31,MATCH(0,K2:K31,0)-1,1),"false") 

K2:K31 is the hours column.

This code now only check if there is a 0, then find a match in the column, selects the first 0 it finds, then selects the cell above and show the value of this cell.

The problem is that I can't find a way to skip the first 0 and show the value above the second zero, is this case the result should be 476. After i know how do this it would probably be easy to change the code and also check for the value above the third zero, which in this case should be 105.

I can't use VBA so it has to be a formula and I'm using Excel 2016 Pro and no MS365.

Running Hours
2122
2230
2345
2447
2538
0
100
233
421
444
448
460
475
476
0
10
13
18
25
54
70
100
101
105
0
4
8
156
250
300

Header	Value Above 0
Number Above First Zero	2538
Number Above Second Zero	476
Number Above Third Zero	105

Answer 1

Try using the following formula:

---

• Formula used in cell N2

=LET(
     a,SCAN(1,K2:K31,LAMBDA(x,y,x+(y=0))),
     b,UNIQUE(a),
     DROP(LOOKUP(b,a,K2:K31),-1))

---

Or, instead of using DROP( ) use TAKE( ) function.

---

=LET(
     a,SCAN(1,K2:K31,LAMBDA(x,y,x+(y=0))),
     b,UNIQUE(a),
     TAKE(LOOKUP(b,a,K2:K31),3))

---

Alternative approach without using LAMBDA( ) helper functions.

---

=INDEX(K2:K31,TOCOL(IFS(K2:K31=0,ROW(K2:K31)),3)-2)

---

Another way, if you are not using MS365 then using INDEX() & AGGREGATE()

---

=INDEX($K$2:$K$31,AGGREGATE(15,6,(ROW($K$2:$K$31)-1)/($K$2:$K$31=0),ROW(A1))-1)

---

Answer 2

You could also use: =LET(range,K2:K31,FILTER(DROP(range,-1),DROP(range,1))

Answer 3

This is based on Excel 2016, no MS365 or newer versions of Excel.

I found a solution to my problem: It requires a bit more columns, but this is fine for me. I can hide these columns if needed. This is probably not the best way to fix this, but it works for me and finding my own solution after getting some tips from Stackoverflow is the best feeling. This is also a good way to understand the code in Excel.

Column D is raw data, just numbers Column C is checking for 0's and if a 0 is found it adds a 1 to it, or a 2 if its the second one that can be found.

Column E, in this case E9 if looking for the value in F9 in column C, if found it selects the number above.

C2:C4 is caluating the max, avg and min of Column E.