IF Problem

[martyloo]

Hi I have two columns A, B
A1 = 4
A2 =
A3 = 3
A4 =
A5 =
A6 = 7
A7 =

B1 =
B2 = 6
B3 =
B4 =
B5 = 4
B6 =
B7 = 9

I want to do a calculation in C1 - C7 that if B7 has a value then minus this value from the next cell in column A which has a value

So in this case, B7 - A6 = 2
B5 - A3 = 1
B2 - A1 =2

Can anyone help with a forumla I can put in C1 and just copy it down to calculate these values?

Thanks in advance

Marty

 

[Joe4]

What is the maximum number of levels (rows) that you might need to check (in your example, it was three, from row 5 to row 3, but realsitically, how far might you need to go)?
Also, are you only doing calculations in column C where there are matching values in column B (so no calculations in C6, C4, C3, etc)?

 

[Fluff]

How about
=IF(B1="","",B1-LOOKUP(2,1/(A$1:A1<>""),A$1:A1))

 

[martyloo]

So there are around 7,000 rows I just need the first value in column B to minus the next value in column A
Then the next value after this in column B to minus the next value in column A so on so forth.