Formula Problems-Zero Results Need to be Blank & Calculation Needs to Pickup from Previous Amount Greater Than Zero

[KarenG]

Hello,

I'm a beginner when it comes to formulas and this particular spreadsheet is giving me 2 issues.

The zero amounts in the 2nd column need to be blank. I got the first one to work but that's it. Then I have a problem where the 859910 is the second column shows up. It needs to be the 859910 in the first column minus the 858650 in the first column so that 1260 is the result in the 2nd column (in place of 859910)

855000	//////////
856120	1120
857330	1210
858650	1320
0
0	0
0	0
0	0
0	0
859910	859910	1260
861150	1240
862300	1150
863550	1250
864860	1310

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Below is the table showing what I've done with the formula.

855000	//////////////////////////////////
856120	=IF(A2-A1>=0,A2-A1,"")
857330	=IF(A3-A2>=0,A3-A2,"")
858650	=IF(A4-A3>=0,A4-A3,"")
0	=IF(A5-A4>=0,A5-A4,"")
0	=IF(A6-A5>=0,A6-A5,"")
0	=IF(A7-A6>=0,A7-A6,"")
0	=IF(A8-A7>=0,A8-A7,"")
0	=IF(A9-A8>=0,A9-A8,"")
859910	=IF(A10-A9>=0,A10-A9,"")	1260
861150	=IF(A11-A10>=0,A11-A10,"")
862300	=IF(A12-A11>=0,A12-A11,"")
863550	=IF(A13-A12>=0,A13-A12,"")
864860	=IF(A14-A13>=0,A14-A13,"")

<tbody>
</tbody><colgroup><col><col><col></colgroup>

I hope this makes sense. I'm using Excel 2013. Thanks in advance for any help you can provide.

Karen

 

[jtakw]

Hi,

If I understand correctly, this would achieve what you want:

Book1
	A	B
1	855000	//////////
2	856120	1120
3	857330	1210
4	858650	1320
5	0
6	0
7	0
8	0
9	0
10	859910	1260
11	861150	1240
12	862300	1150
13	863550	1250
14	864860	1310
Sheet2

Cell Formulas
Range	Formula
B2	=IF(AND(A1=0,A2<>0),A2-LOOKUP(9.99999999999999E+307,B$1:B1,A$1:A1),IF(A2-A1>0,A2-A1,""))

B2 formula copied down.

 

[KarenG]

You got it but when I copy and pasted the formula it's good until cell B10 where it displays 859910. Also, any help in actually understanding the formula would be wonderful.

 

[jtakw]

Hi,

It shouldn't show 859910 in B10, works for me as shown:

Book1
	A	B
1	855000	//////////
2	856120	1120
3	857330	1210
4	858650	1320
5	0
6	0
7	0
8	0
9	0
10	859910	1260
11	861150	1240
12	862300	1150
13	863550	1250
14	864860	1310
Sheet2

Cell Formulas
Range	Formula
B2	=IF(AND(A1=0,A2<>0),A2-LOOKUP(9.99999999999999E+307,B$1:B1,A$1:A1),IF(A2-A1>0,A2-A1,""))
B10	=IF(AND(A9=0,A10<>0),A10-LOOKUP(9.99999999999999E+307,B$1:B9,A$1:A9),IF(A10-A9>0,A10-A9,""))

Let's get it working for you first, I'll explain the formula later.

 

[jtakw]

I have a feeling your 0 values in Column A is TEXT, how do the 0s get there?
Is it manually entered, or from a formula?
If it's from a formula, please post the formula.

 

[KarenG]

Finally, it worked. I was one cell off when I posted the formula. Just for an FYI the zeros are entered manually and it is formatted as GENERAL. Now, for the explanation please.

 

[jtakw]

Glad you got it working.

For the explanation, I'll use the formula as it sits in B10 after being copied down from B2:

Book1
	B
10	1260
Sheet2

Cell Formulas
Range	Formula
B10	=IF(AND(A9=0,A10<>0),A10-LOOKUP(9.99999999999999E+307,B$1:B9,A$1:A9),IF(A10-A9>0,A10-A9,""))

If both A9=0 And A10 does Not =0, then A10 minus (LOOKUP, using BIGNUM, 9.9999999999999999E+307, which finds the last value in range of B$1:B9, in this case 1320, and return the corresponding value in A$1:A9, in this case, 858650); otherwise, test if A10-A9 is Greater than 0, if it is, than give the result of A10-A9, if it's Not, leave cell Blank, this part keeps the cells Blank with 0s in Column A.

Hope this explains it.