Going from Bottom to top how to check if a cell contains a number, and if it does, then subtract that cell from the next number in the column

[rsd007]

I am looking for formula to find the value in C if value in B column and subtract from the next number in upper B cell and my sheet is keep growing in top as new data is added in top line (27..........).

Thanks for all the help

A	B	C
27​
26​	41​	?
25​
24​
23​
22​
21​
20​
19​	33​	?
18​
17​
16​	9​	?
15​
14​
13​
12​
11​	6​	?
10​	5​	5-29=?
9​
8​
7​	29​	29-10=?
6​
5​
4​
3​	10​	10-40=?
2​
1​	40​

 

[rsd007]

Anyone?

 

[iggydarsa]

The data you posted has some invisible characters that are breaking the calculations since they are not technically pure numbers.

But assuming they are correctly formatted numbers, following code works for me:

Sub Calc()
    lr = Range("B" & Rows.Count).End(xlUp).Row
    a = 0
    b = Trim(Range("B" & lr).Value)
    lr = lr - 1
    For i = lr To 1 Step -1
        If Not IsEmpty(Trim(Range("B" & i).Value)) And Trim(Range("B" & i).Value) <> "" And IsNumeric(Trim(Range("B" & i).Value)) Then
            a = Range("B" & i).Value
            Range("C" & i).Value = a - b
            b = a
        End If
    Next i
End Sub

 

[rsd007]

The data you posted has some invisible characters that are breaking the calculations since they are not technically pure numbers.

But assuming they are correctly formatted numbers, following code works for me:

Sub Calc()
    lr = Range("B" & Rows.Count).End(xlUp).Row
    a = 0
    b = Trim(Range("B" & lr).Value)
    lr = lr - 1
    For i = lr To 1 Step -1
        If Not IsEmpty(Trim(Range("B" & i).Value)) And Trim(Range("B" & i).Value) <> "" And IsNumeric(Trim(Range("B" & i).Value)) Then
            a = Range("B" & i).Value
            Range("C" & i).Value = a - b
            b = a
        End If
    Next i
End Sub

Thankyou iggydarsa.
I am very new to excel and have no idea about vba.
is it possible for come up with some formula please.

 

[iggydarsa]

I personally dont know how to lookup a number at a random position by formula. Maybe some other user can have an idea how to solve this issue but only way I am capable of doing is via VBA.

 

[Peter_SSs]

Is this what you are after?
If not, please post again with sample data** but also include the expected results and explain again in relation to that sample data.

** Best way to provide sample data etc is XL2BB

22 10 25.xlsm
	A	B	C
1	27
2	26	41	8
3	25
4	24
5	23
6	22
7	21
8	20
9	19	33	24
10	18
11	17
12	16	9	3
13	15
14	14
15	13
16	12
17	11	6	1
18	10	5	-24
19	9
20	8
21	7	29	19
22	6
23	5
24	4
25	3	10	-30
26	2
27	1	40
Subtract

Cell Formulas
Range	Formula
C1:C27	C1	=IF(B1="","",IFNA(B1-INDEX(B2:B$30,MATCH(TRUE,INDEX(B2:B$30<>"",0),0)),""))

 

[rsd007]

Thanks For the help,
I was trying to use XL2BB but not working. as shown in photo, will like to subtract automatically 41-33=8, 33-9=24, 9-6=3, 6-5=1 and will show in column C and if result is negative show negative.
I was told that in EXCEL data should be entered from top to bottom or left to right

Thanks

 

[Peter_SSs]

I was trying to use XL2BB but not working.

In what way is it not working?
At what point in the instructions in the link I provided does the problem occur?

You did not specify what you want to happen in row 21 where there is a number, but no more numbers below. See if this is headed in the right direction.

rsd007.xlsm
	B	C
1
2	41	8
3
4
5	A
6
7
8
9	33	24
10
11
12	9	3
13
14
15
16
17	6	1
18	5	-24
19
20	X
21	29	#N/A
Subtract (2)

Cell Formulas
Range	Formula
C2:C21	C2	=IF(ISNUMBER(B2),B2-INDEX(B3:B$22,MATCH(TRUE,ISNUMBER(B3:B$22),0)),"")

 

[rsd007]

In what way is it not working?
At what point in the instructions in the link I provided does the problem occur?

You did not specify what you want to happen in row 21 where there is a number, but no more numbers below. See if this is headed in the right direction.

rsd007.xlsm
	B	C
1
2	41	8
3
4
5	A
6
7
8
9	33	24
10
11
12	9	3
13
14
15
16
17	6	1
18	5	-24
19
20	X
21	29	#N/A
Subtract (2)

Cell Formulas
Range	Formula
C2:C21	C2	=IF(ISNUMBER(B2),B2-INDEX(B3:B$22,MATCH(TRUE,ISNUMBER(B3:B$22),0)),"")

Hello,

It was my mistake I was subtracting from next cell, which is not blank, needed other way subtracting 33-41= (-8); 9-33= (-24); 6-9= (-3); 5-6= (-1); 29-5= 24
Also ignore if blank or anything other then number.
Row 2 should be empty as it is the first entry and row 21 should be 29-5=24

 

[Peter_SSs]

While I am considering that new information, what about the first two questions in my previous post?