Help defining and setting variables

[menos]

Skin conductance level fluctuates in waves. I would like to count the number of peaks that rose 2 or more units in 30 seconds. In col A I have SCL (skin conductance level). Col B shows every SCL reading that was a rise of 2 or more units in 30seconds (else 0).

However, this flags multiple rises per peak because many peaks rise much more than 2 in 30s. I am only interested in counting no. of peaks meeting the criteria, so I want to create a new col that ignores any rises after the first, until it reaches the peak. I have some code that I think will more or less do it, but I don’t know vba well enough to correctly define and set my variables and to use terms correctly. Could anyone help with this?

Thanks!


Dim SCLrange As Range
Dim HFrange As Range
Dim HFcell As Range
Dim SCLcell As Range

'HFrange is col B
'SCLrange is the cells in col A which rise after the first rise in each peak

For Each HFcell In HFrange
If HFcell > 0 Then

'need to define SCLrange as the cells after the first rise where SCL continues to rise
For Each SCLcell In SCLrange
If SCLcell.Value - SCLcell.Offset(-1, 0).Value < 0 Then
HFcell.Offset(0, 1).Value = HFcell.Value 'if reached the peak of the SCL rise then copy the value in row x, column b to row x, column c, and then look at the next value >0 in col b
'[go to the next HFcell >0]
If SCLcell.Value - SCLcell.Offset(-1, 0).Value >= 2 Then
HFcell.Offset(0, 1).Value = 0
End If 'if SCLcell.Value - SCLcell.Offset(-1, 0).Value is between 0 and 2 then just need to go to next SCLcell so haven't specified this
Next SCLcell

End If

Next HFcell

[mdmackillop]

Can you post a workbook containing sample data?

[menos]

workbook attached

[Bob Phillips]

Can you tell us what you expect in that data?

[menos]

I have created some data which should better demonstrate what I am trying to do. I have since realised that my attempt at code in the initial post won't do the job.

To summarise:
col A shows the skin conductance level. I want to count the number of peaks in skin conductance level (SCL) that meet a criteria, namely where it rises by 2 or more units within the last 3 measurements. Col B (called HF) shows the SCL whenever this criteria is met and 0 where it isn't. Large peaks meet this criteria several times in one peak but I only want to count each peak once. I want to create a third column to do this (call it NewHF). I think I need to get excel to check each cell in col B (call it HFcell) and if the value is >0 (denoting a rise that met the criteria), define an array where upper bound is first cell upwards in col A where cell(0)-cell(-1)<0 (i.e. start of rise in SCL) and lower bound is HFcell, and if there is a value of >0 within this array in col B above HFcell, then NewHFcell = 0, if not NewHFcell = HFcell.

[Bob Phillips]

How a bout a simple formula

=IF(B2=0,0,IF(B2>0,IF(B1=0,B2,0)))

[menos]

I could but I need to check back as far as Ax where Ax-A(x-1)<0 and I don't know how to tell it to do that. Any suggestions?

[Bob Phillips]

I don't know what you mean. You didn't give examples as I suggested.

[menos]

Did you have a look at better example.xls? I guess it is not a better example. I just don't know how else to explain it. Thanks for your persistence though, its much appreciated!

[Bob Phillips]

I did, and I assumed that the values in column C were what you are aiming at. I believe that the formula that I gave achieves that. I did not understand that comment of yours, but presumably it means the actual data is more complex, so I was asking for an example of where my formula goes wrong.