0

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.

MWeise
  • 1
  • 1

1 Answers1

0

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.

p._phidot_
  • 1,913
  • 1
  • 9
  • 17
  • It does not seem to work -- not getting it to find or compute correct numbers – MWeise Mar 09 '23 at 15:14
  • First of all - thank you to everyone. I have this working. I think large table the only value that will stay consistent and not be cut or pasted elsewhere is Column F. – MWeise Mar 09 '23 at 19:49
  • "Assuming that the colA & colB values starts at A1 n B1" <<-- I assumed this.. is ur colA & colB values starts here too? (if it doesn't u'll need to adjust the formula accordingly.) – p._phidot_ Mar 09 '23 at 19:55
  • First of all - Thank you to everyone. Ignore the Col F comment above. -- I just simplified it -- sorry for the early comment - hit by accident and could not edit. I have this working. For this example, we have a large table -- the only value that will stay consistent is Column A (timestamp). I am looking at a transmission in column B. I am using column C and D for my computations with my answer in column D. This returns my answer in seconds or milliseconds and then I simply convert that to the units I want. Column C =IF(B2<>"",$A2,C1) Column D =IF(C2<>C1,C2-C1,"") – MWeise Mar 09 '23 at 20:01
  • Now if that can be simplified and made not so ugly would love to hear it but am satisfied. – MWeise Mar 09 '23 at 20:02