I want to subtract the previous non blank cell in the same column

Question

To simplify this, I am computing transmission frequency (in Hertz) for data on a bus. I need to compute frequency of transmission for each data item. For example, for each value in column B, I have a corresponding time in column A. I find a value (my current position in column B), find the previous value (also in B), subtract the times in column A corresponding to both.

If I can just calculate the last non blank column cell in the column B (relative to my current position), then transpose to my column of corresponding times, I will be good to go. The last non-blank cell in B is the kicker for me.

I have scoured the web, used a lot of examples, and been able to return a lot of things, but not the value I want.

Answer 1

Assuming that the colA & colB values starts at A1 n B1. Put :

=IF(B2="",C1+1,0) in C2

and

=IF(B2="","",A2-OFFSET(A2,-C1-1,0)) in D2

and drag downwards. That should do.

hint: count empty cells in colB, reset at each B value. Use the count to 'guide' the subtraction.

pls share if it works/not/understandable.