The formula for finding the first non-empty cell

Question

I have a table with two data columns, columns A and B and column C, where I am looking for a formula.

row ABC 1 50 2 3 4 5 56 6 6 7 8 46 10 9 10 11 64 18 

As you can see, the string either contains a value or not. In column C, I want to calculate the difference between

a) the value in column B and the value in column A is the first non-empty cell immediately higher (for example, in line 5, I calculate the difference between B5 and A1 56-50 = 6) if the value of B is a number and

b) the value in column A and the value in column B of the first non-empty cell immediately above (row 8, 56-46 = 10)

and don’t put anything if neither columns A and B are empty.

I struggled with the "first non-empty cell" to write the formula R1C1. Notice that I know I can do this in VBA, but I'm looking for the formula R1C1.

Thank you for your help.

Answer 1

Here is the array formula (which you need to check with Ctrl + Shift + Enter ) that you can put in C1 and drag to the end of your data:

 =IF(OR(A1<>"",B1<>""),INDEX($B$1:B1,MAX(IF($B$1:B1="",0,ROW($B$1:B1))))-INDEX($A$1:A1,MAX(IF($A$1:A1="",0,ROW($A$1:A1)))),"") 

Or, in the French version of Excel:

 =SI(OU(A1<>"";B1<>"");INDEX($B$1:B1;MAX(SI($B$1:B1="";0;LIGNE($B$1:B1))))-INDEX($A$1:A1;MAX(SI($A$1:A1="";0;LIGNE($A$1:A1))));"") 

Please note that if you are interested, you can go to Stackoverflow in French

Answer 2

Maybe try this formula in C2 copy down

=IF(B2="",IF(A2="","",LOOKUP(9.99E+307,B$1:B1)-A2),B2-LOOKUP(9.99E+307,A$1:A1))