If, formula's and datastreams

P

platform8

Hi there,
I have a csv file of a 1000 random numbers.
I'd like to import that into excel and have excel analyse each number
(as it arrives, hopefully) to determine wether it's value is higher or
lower than the previous value.
I'd then like, say, 3 consecutive similar results (i.e.
higher,higher,higher) to put an higher or lower text string in a
separate cell along with the last number analyzed. e.g

123
653
100
200
234 "higher 234"
566
433
288
64 "lower 64"
356
654
222
111
544
677
888 "higher 888"

If possible I'd also like to have a configureable percentage value so I
could use an If value increases by 20% then trigger the text string
output and If value decreases by 15% then trigger the text string
output.

Any help appreciated
Thanks
Platform8
 
D

Don Guillett

try something like this
Sub consecutivehigherlower()
Columns(2).ClearContents
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row

If Cells(i + 1, 1) > Cells(i, 1) _
And Cells(i + 2, 1) > Cells(i + 1, 1) _
And Cells(i + 3, 1) > Cells(i + 2, 1) Then
Cells(i + 3, 2).Value = "Higher " & Cells(i + 3, 1).Value
End If

If Cells(i + 1, 1) < Cells(i, 1) _
And Cells(i + 2, 1) < Cells(i + 1, 1) _
And Cells(i + 3, 1) < Cells(i + 2, 1) Then
Cells(i + 3, 2) = "Lower " & Cells(i + 3, 1)
End If
Next i
End Sub
 
Top