Number wheel chart

[TOKYOJ1]

Hello,

Need some help with the data below with dates and numbers changing from positive to negative and positive again.

At the point of change from positive to negative number, I would like to mark "X" and also from negative to positive number also mark "X" on third column.

Can anyone suggest a formula I can use for my spreadsheet?

Thanks in advance,
TJ


Date

13/1/1925 0:00	2.07
14/1/1925 0:00	1.91
15/1/1925 0:00	1.75
16/1/1925 0:00	1.59
17/1/1925 0:00	1.43
18/1/1925 0:00	1.27
19/1/1925 0:00	1.11
20/1/1925 0:00	0.95
21/1/1925 0:00	0.79
22/1/1925 0:00	0.64
23/1/1925 0:00	0.19
24/1/1925 0:00	0.05
25/1/1925	-0.09	X
26/1/1925 0:00	-0.23
27/1/1925 0:00	-0.36
28/1/1925 0:00	-0.49
29/1/1925 0:00	-0.7
30/1/1925 0:00	-0.54
31/1/1925 0:00	-0.36
1/2/1925 0:00	-0.18
2/2/1925	0	X
3/2/1925 0:00	0.2
4/2/1925 0:00	0.4
5/2/1925 0:00	0.6
6/2/1925 0:00	0.81
7/2/1925 0:00	1.01
8/2/1925 0:00	1.22
9/2/1925 0:00	1.43
10/2/1925 0:00	1.63
11/2/1925 0:00	1.82
12/2/1925 0:00	2.01
13/2/1925 0:00	2.19
14/2/1925 0:00	2.36

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[shg]

	b​	c​	d​
2​	2.07​		c2: =if(sign(b1)*sign(b2)=-1, "x", "")
3​	1.91​
4​	1.75​
5​	1.59​
6​	1.43​
7​	1.27​
8​	1.11​
9​	0.95​
10​	0.79​
11​	0.64​
12​	0.19​
13​	0.05​
14​	-0.09​	x
15​	-0.23​
16​	-0.36​
17​	-0.49​
18​	-0.7​
19​	-0.54​
20​	-0.36​
21​	-0.18​
22​	0​
23​	0.2​
24​	0.4​
25​	0.6​
26​	0.81​
27​	1.01​
28​	1.22​
29​	1.43​
30​	1.63​
31​	1.82​
32​	2.01​
33​	2.19​
34​	2.36​

 

[TOKYOJ1]

Thanks, I got a 0 in row 22 - how do I mark "X" there?

Thanks!

 

[shg]

... and numbers changing from positive to negative and positive again.

0 is not a positive number or negative number ....

	B​	C​	D​
1​	​	​	​
2​	2.07​
3​	1.91​		C3: =IF(OR(SIGN(B2) * SIGN(B3) = -1, B3 = 0), "X", "")
4​	1.75​
5​	1.59​
6​	1.43​
7​	1.27​
8​	1.11​
9​	0.95​
10​	0.79​
11​	0.64​
12​	0.19​
13​	0.05​
14​	-0.09​	X
15​	-0.23​
16​	-0.36​
17​	-0.49​
18​	-0.7​
19​	-0.54​
20​	-0.36​
21​	-0.18​
22​	0​	X
23​	0.2​
24​	0.4​
25​	0.6​
26​	0.81​
27​	1.01​
28​	1.22​
29​	1.43​
30​	1.63​
31​	1.82​
32​	2.01​
33​	2.19​
34​	2.36​