Formula for - [prior cell in column with data]

[Spare]

I'd like to write a formula in column B to find the difference between
a value in the current row, say, A500, and the prior cell in column A
that has a value.. could be A499, or it might be A200, or A1.

Example: First column is literals, the second column s/b a formula,
=A3 - A[prior row with data]


2

5 3
6 1

4 -2


9 5

 

[T. Valko]

This may not be very efficient if you 1000's of rows of data.

Assume your data is in the range A1:An

Enter this formula in B2:

=IF(OR(COUNT(A$1:A2)<2,A2=""),"",A2-LOOKUP(1E100,A$1:A1))

Copy down as needed.

 

[Spare]

Actually, that worked perfectly, only a couple thousand rows, and it
calculated very quickly.

Thanks!!!!!

This may not be very efficient if you 1000's of rows of data.

Assume your data is in the range A1:An

Enter this formula in B2:

=IF(OR(COUNT(A$1:A2)<2,A2=""),"",A2-LOOKUP(1E100,A$1:A1))

Copy down as needed.

--
Biff
Microsoft Excel MVP

I'd like to write a formula in column B to find the difference between
a value in the current row, say, A500, and the prior cell in column A
that has a value.. could be A499, or it might be A200, or A1.

Example:  First column is literals, the second column s/b a formula,
=A3 - A[prior row with data]

2

5          3
6          1

4         -2

9          5- Hide quoted text -

- Show quoted text -

 

[T. Valko]

You're welcome. Thanks for the feedback!

Ok, just for the heck of it, this should be more efficient:

=IF(A2="","",IF(COUNT(A$1:A2)>1,A2-LOOKUP(1E100,A$1:A1),""))

Instead of executing a count on every row, we only count on those rows that
have a number.


--
Biff
Microsoft Excel MVP


Actually, that worked perfectly, only a couple thousand rows, and it
calculated very quickly.

Thanks!!!!!

This may not be very efficient if you 1000's of rows of data.

Assume your data is in the range A1:An

Enter this formula in B2:

=IF(OR(COUNT(A$1:A2)<2,A2=""),"",A2-LOOKUP(1E100,A$1:A1))

Copy down as needed.

--
Biff
Microsoft Excel MVP

I'd like to write a formula in column B to find the difference between
a value in the current row, say, A500, and the prior cell in column A
that has a value.. could be A499, or it might be A200, or A1.

Example: First column is literals, the second column s/b a formula,
=A3 - A[prior row with data]

2

5 3
6 1

4 -2

9 5- Hide quoted text -

- Show quoted text -