Forum Discussion
Solved
Daily average,skipping cells
I’m attempting to make a formula that can use in our pump stations. The formula as currently written functions properly when I am at the station DAILY (The meters are gallons, and “/42” is added to convert it to barrels). However, some stations don’t need checked daily. After skipping a day or two (or even 3, 4 or 5 days), I would like it to average how much water was daily moved. With the formula I currently have, it only works when I am there two days in a row
Hi,
that could be like
=IF(B3<>"", (B3-LOOKUP(2,1/($B$2:B2<>""),$B$2:B2))/42 /(A3-LOOKUP(2,1/($B$2:B2<>""),$A$2:A2)), "")
if put the formula in B3 and drag it down. LOOKUP finds the value of the previous non-empty cell in the column B (first LOOKUP) and value of the related cell in A (second LOOKUP)
Please see attached
4 Replies
Hi,
that could be like
=IF(B3<>"", (B3-LOOKUP(2,1/($B$2:B2<>""),$B$2:B2))/42 /(A3-LOOKUP(2,1/($B$2:B2<>""),$A$2:A2)), "")
if put the formula in B3 and drag it down. LOOKUP finds the value of the previous non-empty cell in the column B (first LOOKUP) and value of the related cell in A (second LOOKUP)
Please see attached
- Wow you really know your stuff; that works perfect. Thanks. When I use “fill” down, it works just right, but when I try and copy/paste it to column D, it’s not working. Should I not use copy paste? (Im not sure how to fill to the next columns. I’m using an iPad btw). Please see attached. Thank you!
I adjusted absolute and relative references
=IF(B3<>"", (B3-LOOKUP(2,1/(B$2:B2<>""),B$2:B2))/42 /($A3-LOOKUP(2,1/(B$2:B2<>""),$A$2:$A2)), "")
now you may copy/paste to next column.
Please see attached.
