Subtract first non-zero row while non-zero in column

[GeeWhiz7]

Hi folks,
Kicking myself that I haven't figured out this yet on my own.
See the mini-sheet below and here is what I'm trying to do:

• The Aton_Delta (C) column is data that comes in, from that I have a helper column that has a "1" in it if the C column data is non-zero
• The DeltaFromStart (D) column is the result I am trying to get.
• The value in column D should use the first occurrence of a non-zero in column C (12 for example) and subtract it from each subsequent non-zero value in the C column until a zero is encountered again.
• It then starts over when another non-zero delta value is encountered in column C.

I've gotten close using OFFSET or counting with the helper row, but for some reason I can't quite get it right. I can't filter, sequence or anything like that without causing bigger problems elsewhere.
Thank you for your help.

Book2
	A	B	C	D	E	F	G	H	I	J
1	Helper	Run Date	Aton_∆	∆FromStart		Desired result in column D
2		6/13/2022	0	0		0
3		6/12/2022	0	0		0
4		6/11/2022	0	0		0
5	1	6/10/2022	12	12		First non-zero delta is 12
6	1	6/9/2022	16	4		=16-12
7	1	6/8/2022	25	13		=25-12
8	1	6/7/2022	32	20		=32-12
9	1	6/6/2022	4	-8		=4-12
10		6/5/2022	0	0		Delta is zero again so delta from start should = 0
11		6/4/2022	0	0		Delta is zero again so delta from start should = 0
12		6/3/2022	0	0		Delta is zero again so delta from start should = 0
13		6/2/2022	0	0		Delta is zero again so delta from start should = 0
14		6/1/2022	0	0		Delta is zero again so delta from start should = 0
15		5/31/2022	0	0		Delta is zero again so delta from start should = 0
16		5/30/2022	0	0		Delta is zero again so delta from start should = 0
17	1	5/29/2022	6	6		First non-zero delta is 6
18	1	5/28/2022	17	11		=17-6
19	1	5/27/2022	35	29		=35-6
20	1	5/26/2022	38	32		=38-6
21	1	5/25/2022	41	35		=41-6
22	1	5/24/2022	12	6		=12-6
23	1	5/23/2022	18	12		=18-6
24		5/22/2022	0	0		Delta is zero again so delta from start should = 0
25		5/21/2022	0	0		Delta is zero again so delta from start should = 0
26		5/20/2022	0	0		Delta is zero again so delta from start should = 0
27		5/19/2022	0	0		Delta is zero again so delta from start should = 0
28		5/18/2022	0	0		Delta is zero again so delta from start should = 0
29		5/17/2022	0	0		Delta is zero again so delta from start should = 0
30		5/16/2022	0	0		Delta is zero again so delta from start should = 0
31		5/15/2022	0	0		Delta is zero again so delta from start should = 0
32		5/14/2022	0	0		Delta is zero again so delta from start should = 0
33		5/13/2022	0	0		Delta is zero again so delta from start should = 0
Sheet1

Cell Formulas
Range	Formula
D6,D18	D6	=C6-C5
D7,D19	D7	=C7-C5
D8,D20	D8	=C8-C5
D9,D21	D9	=C9-C5
D22	D22	=C22-C17
D23	D23	=C23-C17
B2	B2	=TODAY()
B3:B33	B3	=B2-1

 

[Marcelo Branco]

Maybe something like this

Pasta1
	A	B	C	D
1	Helper	Run Date	Aton_∆	∆FromStart
2		6/13/2022	0	0
3		6/12/2022	0	0
4		6/11/2022	0	0
5	1	6/10/2022	12	12
6	1	6/9/2022	16	4
7	1	6/8/2022	25	13
8	1	6/7/2022	32	20
9	1	6/6/2022	4	-8
10		6/5/2022	0	0
11		6/4/2022	0	0
12		6/3/2022	0	0
13		6/2/2022	0	0
14		6/1/2022	0	0
15		5/31/2022	0	0
16		5/30/2022	0	0
17	1	5/29/2022	6	6
18	1	5/28/2022	17	11
19	1	5/27/2022	35	29
20	1	5/26/2022	38	32
21	1	5/25/2022	41	35
22	1	5/24/2022	12	6
23	1	5/23/2022	18	12
24		5/22/2022	0	0
25		5/21/2022	0	0
26		5/20/2022	0	0
27		5/19/2022	0	0
28		5/18/2022	0	0
29		5/17/2022	0	0
30		5/16/2022	0	0
31		5/15/2022	0	0
32		5/14/2022	0	0
33		5/13/2022	0	0
Plan1

Cell Formulas
Range	Formula
B2	B2	=TODAY()
B3:B33	B3	=B2-1
D2:D33	D2	=IF(C2=0,0,IF(N(C1)=0,C2,C2-LOOKUP(2,1/(C$1:C1=0),C$2:C2)))

M.

 

[KRice]

try this for another option:

MrExcel_20220610.xlsx
	A	B	C
1	Run Date	Aton_∆	∆FromStart
2	6/13/2022	0	0
3	6/12/2022	0	0
4	6/11/2022	0	0
5	6/10/2022	12	0
6	6/9/2022	16	4
7	6/8/2022	25	13
8	6/7/2022	32	20
9	6/6/2022	4	-8
10	6/5/2022	0	0
11	6/4/2022	0	0
12	6/3/2022	0	0
13	6/2/2022	0	0
14	6/1/2022	0	0
15	5/31/2022	0	0
16	5/30/2022	0	0
17	5/29/2022	6	0
18	5/28/2022	17	11
19	5/27/2022	35	29
20	5/26/2022	38	32
21	5/25/2022	41	35
22	5/24/2022	12	6
23	5/23/2022	18	12
24	5/22/2022	0	0
25	5/21/2022	0	0
26	5/20/2022	0	0
27	5/19/2022	0	0
28	5/18/2022	0	0
29	5/17/2022	0	0
30	5/16/2022	0	0
31	5/15/2022	0	0
32	5/14/2022	0	0
33	5/13/2022	0	0
Sheet8

Cell Formulas
Range	Formula
A2	A2	=TODAY()
A3:A33	A3	=A2-1
C2:C33	C2	=IF(B2=0,0,B2-INDEX(B$2:B2,XMATCH(0,B$2:B2,0,-1)+1))

 

[Eric W]

One more:

Book3
	A	B	C	D
1	Helper	Run Date	Aton_∆	∆FromStart
2		6/13/2022	0	0
3		6/12/2022	0	0
4		6/11/2022	0	0
5	1	6/10/2022	12	12
6	1	6/9/2022	16	4
7	1	6/8/2022	25	13
8	1	6/7/2022	32	20
9	1	6/6/2022	4	-8
10		6/5/2022	0	0
11		6/4/2022	0	0
12		6/3/2022	0	0
13		6/2/2022	0	0
14		6/1/2022	0	0
15		5/31/2022	0	0
16		5/30/2022	0	0
17	1	5/29/2022	6	6
18	1	5/28/2022	17	11
19	1	5/27/2022	35	29
20	1	5/26/2022	38	32
21	1	5/25/2022	41	35
22	1	5/24/2022	12	6
23	1	5/23/2022	18	12
24		5/22/2022	0	0
25		5/21/2022	0	0
26		5/20/2022	0	0
27		5/19/2022	0	0
28		5/18/2022	0	0
29		5/17/2022	0	0
30		5/16/2022	0	0
31		5/15/2022	0	0
32		5/14/2022	0	0
33		5/13/2022	0	0
Sheet4

Cell Formulas
Range	Formula
D2:D33	D2	=IF(C2=0,0,IF(N(C1)=0,C2,C2-IF(C1=D1,C1,C1-D1)))

 

[GeeWhiz7]

Maybe something like this

Pasta1
	A	B	C	D
1	Helper	Run Date	Aton_∆	∆FromStart
2		6/13/2022	0	0
3		6/12/2022	0	0
4		6/11/2022	0	0
5	1	6/10/2022	12	12
6	1	6/9/2022	16	4
7	1	6/8/2022	25	13
8	1	6/7/2022	32	20
9	1	6/6/2022	4	-8
10		6/5/2022	0	0
11		6/4/2022	0	0
12		6/3/2022	0	0
13		6/2/2022	0	0
14		6/1/2022	0	0
15		5/31/2022	0	0
16		5/30/2022	0	0
17	1	5/29/2022	6	6
18	1	5/28/2022	17	11
19	1	5/27/2022	35	29
20	1	5/26/2022	38	32
21	1	5/25/2022	41	35
22	1	5/24/2022	12	6
23	1	5/23/2022	18	12
24		5/22/2022	0	0
25		5/21/2022	0	0
26		5/20/2022	0	0
27		5/19/2022	0	0
28		5/18/2022	0	0
29		5/17/2022	0	0
30		5/16/2022	0	0
31		5/15/2022	0	0
32		5/14/2022	0	0
33		5/13/2022	0	0
Plan1

Cell Formulas
Range	Formula
B2	B2	=TODAY()
B3:B33	B3	=B2-1
D2:D33	D2	=IF(C2=0,0,IF(N(C1)=0,C2,C2-LOOKUP(2,1/(C$1:C1=0),C$2:C2)))

M.

Thank you Marcelo, this works perfectly and thanks to KRice and Eric as those are alternatives I will bank away for other potential uses.
KRice's suggestion is the one I understand the easiest since I don't yet know what "N" is as in IF(N(C1)=0, but I will go look it up. Thanks guys!

 

[Marcelo Branco]

Thank you Marcelo,

You're welcome.
But... reviewing the problem I noticed that my formula doesn't work in certain scenarios.
I could adapt it for such situations, but I would use the formula suggested by Eric in post #4 - simpler and more robust.

M.

 

[KRice]

I just noticed a problem with mine too. I was missing the correct result for the first non-zero that occurs after a 0 in the Aton column. I've needed to added a nested IF as well to handle that:

MrExcel_20220610.xlsx
	A	B	C
1	Run Date	Aton_∆	∆FromStart
2	6/13/2022	0	0
3	6/12/2022	0	0
4	6/11/2022	0	0
5	6/10/2022	12	12
6	6/9/2022	16	4
7	6/8/2022	25	13
8	6/7/2022	32	20
9	6/6/2022	4	-8
10	6/5/2022	0	0
11	6/4/2022	0	0
12	6/3/2022	0	0
13	6/2/2022	0	0
14	6/1/2022	0	0
15	5/31/2022	0	0
16	5/30/2022	0	0
17	5/29/2022	6	6
18	5/28/2022	17	11
19	5/27/2022	35	29
20	5/26/2022	38	32
21	5/25/2022	41	35
22	5/24/2022	12	6
23	5/23/2022	18	12
24	5/22/2022	0	0
25	5/21/2022	0	0
26	5/20/2022	0	0
27	5/19/2022	0	0
28	5/18/2022	0	0
29	5/17/2022	0	0
30	5/16/2022	0	0
31	5/15/2022	0	0
32	5/14/2022	0	0
33	5/13/2022	0	0
Sheet8

Cell Formulas
Range	Formula
A2	A2	=TODAY()
A3:A33	A3	=A2-1
C2:C33	C2	=IF(B2=0,0,IF(B1=0,B2,B2-INDEX(B$2:B2,XMATCH(0,B$2:B2,0,-1)+1)))

 

[GeeWhiz7]

I just noticed a problem with mine too. I was missing the correct result for the first non-zero that occurs after a 0 in the Aton column. I've needed to added a nested IF as well to handle that:

MrExcel_20220610.xlsx
	A	B	C
1	Run Date	Aton_∆	∆FromStart
2	6/13/2022	0	0
3	6/12/2022	0	0
4	6/11/2022	0	0
5	6/10/2022	12	12
6	6/9/2022	16	4
7	6/8/2022	25	13
8	6/7/2022	32	20
9	6/6/2022	4	-8
10	6/5/2022	0	0
11	6/4/2022	0	0
12	6/3/2022	0	0
13	6/2/2022	0	0
14	6/1/2022	0	0
15	5/31/2022	0	0
16	5/30/2022	0	0
17	5/29/2022	6	6
18	5/28/2022	17	11
19	5/27/2022	35	29
20	5/26/2022	38	32
21	5/25/2022	41	35
22	5/24/2022	12	6
23	5/23/2022	18	12
24	5/22/2022	0	0
25	5/21/2022	0	0
26	5/20/2022	0	0
27	5/19/2022	0	0
28	5/18/2022	0	0
29	5/17/2022	0	0
30	5/16/2022	0	0
31	5/15/2022	0	0
32	5/14/2022	0	0
33	5/13/2022	0	0
Sheet8

Cell Formulas
Range	Formula
A2	A2	=TODAY()
A3:A33	A3	=A2-1
C2:C33	C2	=IF(B2=0,0,IF(B1=0,B2,B2-INDEX(B$2:B2,XMATCH(0,B$2:B2,0,-1)+1)))

Thanks all, I have a working solution from your ideas and some alternatives.

 

[Eric W]

I don't yet know what "N" is as in IF(N(C1)=0, but I will go look it up

In case you haven't found it yet, N() simply makes sure that the value is numeric. It's an old function left over from the early days of Excel that was used to ensure compatibility with other spreadsheet programs. My formula checks the previous row to see if column C is 0 or not. To avoid have a special check to see if the previous row is the header row, I use N(C1). If C1 is a number, nothing changes, if it's the header N(C1) turns it to 0 and I don't have to do anything else special.

Anyway, glad we could help!

 

[GeeWhiz7]

In case you haven't found it yet, N() simply makes sure that the value is numeric. It's an old function left over from the early days of Excel that was used to ensure compatibility with other spreadsheet programs. My formula checks the previous row to see if column C is 0 or not. To avoid have a special check to see if the previous row is the header row, I use N(C1). If C1 is a number, nothing changes, if it's the header N(C1) turns it to 0 and I don't have to do anything else special.

Anyway, glad we could help!

Ah, now N makes sense in context of header. thanks!