How to pick up the last value in a column to use in a formula

[chariot101]

I've got a formula that needs to do a calculation that when column B has a certain value, then the value in column F needs to minus the previous value in column F. The problem is the previous value in column F isn't always on the previous row as if column B has a certain value then it's blank. I'm not sure on the best way to do this. At the moment I have:

=IF(NOT(B5="Get on"),F5-F4,"")

I think the easiest thing to do would be to add another IF statement to say IF B5 = Get on then F5 should equal F4 so it puts the previous value in F4 so the above calculation should be correct, but I can't get my head around how to do this. Or maybe there's a better solution?

 

[Special-K99]

It's a little vague without example data
Try
IF(B5<>"Get on", F5-LOOKUP(2,1/(F$1:F4<>""),F$1:F4),"")

This
LOOKUP(2,1/(F$1:F4<>""),F$1:F4)
will find the last non blank value in column F up to F4

 

[chariot101]

Thank you!