Tuesday, May 20, 2014

yogi_Compute In Column C Difference In Value Of Column A From The Value In Column A That Corresponds To Previous Non-Blank Row In Column B

                                         Google Spreadsheet   Post  #1642
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-20-2014
post by mcglk: (https://productforums.google.com/forum/#!mydiscussions/docs/zp_yDFZ3LY8)
Next non-blank cell in a range.
What I'd like to do:

In the current row, subtract A from B where the location of B is determined by the next non-blank value in column C.

    A  B      C
1   2  bluh
2   8
3  10
4  15  narf  13
5  21  blee   6
6  25
7  33
8  42  keeg  21

So far, I haven't been able to seek forward or backward in a range for a non-blank cell. I was hoping for something like:
=IF(ISBLANK(B4)," ",A4-nextnonblank(B4:B$1))

or some permutation of the various lookup functions, but so far, no dice.

Any advice?

following is my convoluted solution based on my understanding of mcglk is intending to accomplish -- presented in the following is a solution without use of helper columns --

with the user of helper columns a much simpler formulation can be used