detect a change in sign in a do loop

[abberyfarm]

Hi there,

I have acceleration for a car in a column, the values are positive and negative for acceleration and deceleration. I want to put a marker "1" in another column each time the sign changes, i.e the point when the car accelerates or decelerates.

I have a do loop set up below, but im not sure how to write the condition for "when the value of the sign changes"?

Would somebody be able to help me out?

Thanks

    Do

        If (ActiveCell.Offset(0, -11) = "sign changes") Then

        ActiveCell.Value = 1
       


        End If

    ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, -1))

this is what the data looks like

0.00
0.44
0.44
0.64
0.75
0.86
0.87
0.87
0.43
0.43
0.06
-0.03
-0.17
-0.42
-0.42
-0.44
-1.36
-1.42
0.06
0.06
0.06
0.92
0.94
0.99
0.99
0.99
-0.08
-0.72
-0.47

 

[VoG]

Maybe like this

If Sgn(ActiveCell.Offset(0, -11).Value) <> Sgn(ActiveCell.Offset(-1, -11).Value) Then

 

[abberyfarm]

Hi,

No, that only returns a 1 for the very first value and the doesn't detect a change in sign in the rest of the column?

 

[VoG]

My results

Excel Workbook
	A	B
1	0
2	0.44	1
3	0.44
4	0.64
5	0.75
6	0.86
7	0.87
8	0.87
9	0.43
10	0.43
11	0.06
12	-0.03	1
13	-0.17
14	-0.42
15	-0.42
16	-0.44
17	-1.36
18	-1.42
19	0.06	1
20	0.06
21	0.06
22	0.92
23	0.94
24	0.99
25	0.99
26	0.99
27	-0.08	1
28	-0.72
29	-0.47
Sheet5

Sub test()
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("A" & i)
        If Sgn(.Value) <> Sgn(.Offset(-1).Value) Then .Offset(, 1).Value = 1
    End With
Next i
End Sub

 

[abberyfarm]

Got it working.

Thanks for the help

 

[abberyfarm]

Hi,

Actually how would I make this count up 1,2,3,4 etc instead of 1,1,1,1?

Thanks

 

[Peter_SSs]

If you are wanting to note when the sign changes from positive to negative, that suggested code could give erroneous results if the acceleration reaches 0 which I assume is possible. For example, it produces these results:

Excel Workbook
	A	B
1
2	0
3	0.44	1
4	0.44
5	0.21
6	0.06
7	0	1
8	0.06	1
9	0	1
10	-0.42	1
11	-0.42
12	-0.44
13	-1.36
14	-1.42
15	0	1
16	0.06	1
17	0.06
18	0.99
19	0.99
20	-0.08	1
21	-0.72
22
Accel 1

This is my suggestion, which also incorporates the 1, 2, 3 values you want. Does the data always start with a 0 and do you expect a '1' the first time the value changes from 0 as shown below (cell B3)?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Accel()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> lr, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1:A" & lr)<br>        a = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> lr<br>            <SPAN style="color:#00007F">If</SPAN> s = 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> Sgn(a(i, 1)) <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                    s = Sgn(a(i, 1))<br>                    k = k + 1<br>                    b(i, 1) = k<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">ElseIf</SPAN> Sgn(a(i, 1)) = -s <SPAN style="color:#00007F">Then</SPAN><br>                s = -s<br>                k = k + 1<br>                b(i, 1) = k<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Offset(, 1).Value = b<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Excel Workbook
	A	B
1
2	0
3	0.44	1
4	0.44
5	0.21
6	0.06
7	0
8	0.06
9	0
10	-0.42	2
11	-0.42
12	-0.44
13	-1.36
14	-1.42
15	0
16	0.06	3
17	0.06
18	0.99
19	0.99
20	-0.08	4
21	-0.72
22
Accel 2

 

[abberyfarm]

Hello,

Thank you that worked fine. I'm actually going to use both methods for different purposes.

 

[Peter_SSs]

Hello,

Thank you that worked fine. I'm actually going to use both methods for different purposes.

Cheers.
FWIW, this could be done by formula, either directly in the sheet (formula in B2 copied down) like this...

Excel Workbook
	A	B
1	Data	Change
2	0
3	0.44	1
4	0.44
5	0.21
6	0.06
7	0
8	0.06
9	0
10	-0.42	2
11	-0.42
12	-0.44
13	-1.36
14	-1.42
15	0
16	0.06	3
17	0.06
18	0.99
19	0.99
20	-0.08	4
21	-0.72
22
Accel 3

.. or if it needs to be part of a macro then the macro could utilise the formula as follows.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Accel_3()<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)<br>        .Formula = "=IF(A2=0,"""",IF(COUNTIF(A$2:A2,""<>0"")=1,1," _<br>            & "IF(SIGN(A2)=-SIGN(LOOKUP(9.99E+307,B$1:B1,A$1:A1))," _<br>            & "LOOKUP(9.99E+307,B$1:B1)+1,"""")))"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Depends a bit how many rows of data you have.

 

[Peter_SSs]

If you are using Excel 2007 or later the formula and code from my last post could be simplified a little to

Formula:
=IF(A2=0,"",IFERROR(IF(SIGN(A2)=-SIGN(LOOKUP(9.99E+307,B$1:B1,A$1:A1)),LOOKUP(9.99E+307,B$1:B1)+1,""),1))

Code:
<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Accel_3()<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)<br>        .Formula = "=IF(A2=0,"""",IFERROR(IF(SIGN(A2)=-SIGN(LOOKUP(" _<br>            & "9.99E+307,B$1:B1,A$1:A1)),LOOKUP(9.99E+307,B$1:B1)+1,""""),1))"<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>