Not sure how to word this exactly. We have a spreadsheet to keep track of pump runtimes.

We want to subtract the run times in column B and output to Column C so for instance B4-B5 would output .3 to C4. I don't have a problem with basic formula =B4-B5 in C4 and so on down the column. However when I get to B8 I want B7 to subtract B10 and skip the B8 and B9. The reason is that the 25th and 26th was a weekend so values weren't recorded. (these sheets are printed, recorded on location and then brought back to the plant at the end of the month) so what if possible would be a formula I can use?

after using the formula this is what happens: enter image description here

CodePudding user response：

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)

It's checking the cells below for the first non-zero and returning the difference to that value. The key principle here is in the range B5:$B$33. The dollar signs ensure that when you drag the formula down the end of the range you're checking stays at B33, whereas B5 will become B6, B7, etc.

CodePudding user response：

If the column B entries are stricly non-increasing, as per your example, in C4:

=IF(B4,B4-XLOOKUP(9^9,B5:B$33,B5:B$33,B4,-1),0)

and copied down.