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?

 

[Joe4]

Try using the formula:

=ROW()-10

and copy to all the rows in column A you want populated.
For your example, if you put that formula in all the cells, it will show the correct values you want.

Really, the format of the formula is:
=ROW() - x
where x = the row number you want zero in

 

[jmpatrick]

Try using the formula:

=ROW()-10

and copy to all the rows in column A you want populated.
For your example, if you put that formula in all the cells, it will show the correct values you want.

Really, the format of the formula is:
=ROW() - x
where x = the row number you want zero in

Doesn't work if I move the zero from one cell to another in column A.

 

[jmpatrick]

This might be a better example. I want to be able to move the zero from A10 to (for instance) A15 and have it still compute the positive and negative numbers.

 

[Joe4]

Doesn't work if I move the zero from one cell to another in column A.

You would simply need to re-enter the formula and copy to all to reflect the new entry.

The issue is that cells in Excel can either have hard-coded values OR formulas, but never both at the same time.
So anytime you enter a new value into a new cell, you are going to have to do something to update all the other cells. Or have VBA do something for all the other cells.

If you want a VBA solution, please let us know exactly what ranges you want to restrict the "0" entry to, and what rows we should populate (i.e. where is the first and last cell located that would need to be populated?).

 

[Fluff]

Would putting the formula in col B work for you?

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

Cell Formulas
Range	Formula
B1:B30	B1	=ROW()-MATCH(0,$A$1:$A$30,0)

 

[jmpatrick]

Would putting the formula in col B work for you?

This works. Thanks. What if I don't want to use zero? Like this:

-5
-4
-3
-2
-1
1
2
3
4
5

 

[Joe4]

What if I don't want to use zero? Like this:

Then what number exactly will you be entering? 1?

 

[Fluff]

A sequence of numbers from -ve to +ve hast to pass through 0, so I don't understand what you want.

 

[jmpatrick]

Then what number exactly will you be entering? 1?

Sorry, yes. I will be using the number 1. It refers to day number, so there wouldn't be a day zero.