I would write a function for something like this personally and do it with code (would be cleaner)
For your regular if statements you would need to have each row be different (longer) than the proceeding one.
for B4
=if(A4="","",if(A3 <> "",A4/A3-1, if( A2 <> "", A4/A2-1, if( A1 <> "", A4/A1-1, ""))))
The function I would use is as follows. Then you can just type =FindReturn('cell you want') in the workbook.
Function FindReturn(ByRef rng As Range) As Double
Dim r, i As Integer
Dim prev, curr As Double
r = rng.Row
curr = rng.Value
If curr = 0 Then
FindReturn = 0
Else
For i = 1 To r - 1
If rng.Offset(-i, 0).Value <> "" Then
prev = rng.Offset(-i, 0).Value
FindReturn = (curr / prev) - 1
Exit For
End If
Next
End If
End Function
Bookmarks