In excel 365 how to subtract values sequentially but skip empty cells

Question

We have a spreadsheet to keep track of pump runtimes.

We want the run times in column B to be subtracted, with the result being sent to column C as, for example, B4-B5. 3 to C4. Simple formulas like =B4-B5 in C4 and subsequent columns don't bother me. However, I want B7 to omit B8 and B9 and instead deduct B10 when I arrive to B8. Because it was a weekend on the 25th and 26th, no values were recorded. What, if any, would be a formula I could apply (since these sheets are produced, recorded on the spot, and then sent back to the facility at the end of the month)?

Answer 1

Try the following formula in C4 and drag down

=IF(B4<>0,B4-INDEX(B5:$B$33,MATCH(TRUE,B5:$B$33<>0,0)),0)