Count back or forward based on (moving) zero cell?

[jmpatrick]

I'm struggling to create a formula that will count positive or negative numbers based off of 0.

Example: I have a column with 30 rows. If I put a zero in A10 I want A9 to show -1, A8 -2, etc. and A11 to show 1, A12 2, etc.. If I later put the zero in A15 then A14 should be -1, A16 1, and so on.

Make sense?

 

[Fluff]

So should the row with 0 be 1?

 

[jmpatrick]

So should the row with 0 be 1?

Yes.

 

[Fluff]

In that case how about

=ROW()-MATCH(0,$A$1:$A$30,0)+(ROW()>=MATCH(0,$A$1:$A$30,0))

 

[jmpatrick]

In that case how about

=ROW()-MATCH(0,$A$1:$A$30,0)+(ROW()>=MATCH(0,$A$1:$A$30,0))

I changed the 0 to 1 and it works perfect. Thanks!

=ROW()-MATCH(1,$A$1:$A$30,1)+(ROW()>=MATCH(1,$A$1:$A$30,1))

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

I changed the 0 to 1 and it works perfect.

I don't know if it is likely to happen, but if any new rows are subsequently added at the top of the sheet, the formula will then return incorrect results.
An alternative formula that is robust against row insertion (& is a bit shorter) is ..

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

Cell Formulas
Range	Formula
B1:B30	B1	=ROWS(A$1:A1)-MATCH(1,A$1:A$30)+COUNT(A$1:A1)