counting sign changes

[hrundle]

Hi,

I have a set of 5 cells (a row vector), each cell containing a number from -1 to +1. Reading through the values from first to last, I want to count how many times it changes sign (i.e. from positive to negative or vice versa). Zeros are present but need to be ignored (skipped). For example:

-1 0 1 1 -1 would yield two sign changes (initial value is negative and it switches to positive and then back to negative)

0 0 1 1 0 would yield zero sign changes (first non-zero value is positive and it remains so)

-1 0 0 1 0 would yield one sign change

Obviously I could do this by hand for a small number of vectors, but I need to repeat this calculation across thousands of unique 5 number sets. Although my example above only uses values of -1, 0, +1, I may also need to generalize this such that the numbers may include several different negative and positive values (again, only sign changes are of interest however).

Any suggestions would be much appreciated. I am working in xl2010.

Thanks,
Howard

 

[Ruddles]

I think this custom function will do what you want:-

Option Explicit
 
Public Function SignChanges(argRange As Range) As Integer
 
  Dim vArray() As Integer
  Dim oCell As Range
  Dim iPtr As Integer
  
  ReDim vArray(1 To 5) As Integer

  iPtr = 0

  For Each oCell In argRange
    If oCell.Value <> 0 Then
      iPtr = iPtr + 1
      vArray(iPtr) = oCell.Value
    End If
  Next oCell
  
  SignChanges = 0
  
  For iPtr = 1 To 4
    If vArray(iPtr + 1) = 0 Then Exit Function
    If Sgn(vArray(iPtr)) <> Sgn(vArray(iPtr + 1)) Then
      SignChanges = SignChanges + 1
    End If
  Next iPtr
  
End Function

Place this code in a new general code module.

If your data is in A1:E1 (for example), then use this formula to count the sign changes:-

=SignChanges(A1:E1)

Shout if it doesn't work!

 

[hrundle]

It seems to work beautifully.

Many thanks!

Howard

 

[hrundle]

Upon a bit more testing, it fails with some values <=0.5. For example,
-0.5 0 -1 1 -1 returns an incorrect value of 3, whereas it should be 2. Replacing the original value of -0.5 with -1 fixes it. Is there a way to generalize it for any positive/negative values?

Thanks,
Howard

 

[West Man]

A formula solution (a bit clunky) that seems to work for the 3 examples you posted is:
=SUM(IF(A1=0,1,SIGN(A1))<>IF(B1=0,1,SIGN(B1)),IF(B1=0,1,SIGN(B1))<>IF(C1=0,1,SIGN(C1)),IF(C1=0,1,SIGN(C1))<>IF(D1=0,1,SIGN(D1)),IF(D1=0,1,SIGN(D1))<>IF(E1=0,1,SIGN(E1)))

 

[hrundle]

A formula solution (a bit clunky) that seems to work for the 3 examples you posted is:
=SUM(IF(A1=0,1,SIGN(A1))<>IF(B1=0,1,SIGN(B1)),IF(B1=0,1,SIGN(B1))<>IF(C1=0,1,SIGN(C1)),IF(C1=0,1,SIGN(C1))<>IF(D1=0,1,SIGN(D1)),IF(D1=0,1,SIGN(D1))<>IF(E1=0,1,SIGN(E1)))

Thanks, I'll have a go with this. The following (suggested by someone else) does seem to work for all cases:


if you have a row of values in A1:E1, then say in G1 enter formula:

=SIGN(IF(A1=0,IFERROR(INDEX(A1:$E1,MATCH(TRUE,INDEX(A1:$E1<>0,0),0)),F1),A1))

copied across 5 columns and then down as far as you need to convert all entries.

Then you can use formula

=SUMPRODUCT(--(H1:K1<>G1:J1))

on that new table and copy down.

 

[Ruddles]

Upon a bit more testing, it fails with some values <=0.5.

Sorry! Change:-

  Dim vArray() As [B][COLOR=red]Integer[/COLOR][/B]
  ReDim vArray(1 To 5) As [COLOR=red][B]Integer[/B][/COLOR]

to:-

  Dim vArray() As [COLOR=blue][B]Single[/B][/COLOR]
  ReDim vArray(1 To 5) As [COLOR=blue][B]Single[/B][/COLOR]