Hi All,

If it is not the right place for my question, please point me to the correct
newsgroup.

I have a perplexing problem building a macro in excel that I hope someone
can help me solve as follows;

I have a stream of data values in column AB2:AB14516.
And what I would like to do, is to find lowest value in the last highest
group of 13 consecutive values, rather than the first group using Excel VBA.
Then put that value into cell W3 and the address of that group's range into
cell W4.
In addition, Identify the address of the five cells ahead of that group that
was found and put that address into cell W2.
And finally, identify the address of the 22 cells after the group of 13
consecutive high values and put its address into cell W5.

Any help at this point would be greatly appreciated!
Thanks in advance,
CTown

Yes it can be done and this is the right place. But I need to see some
data because your instructions can be interpreted in more than 1 way.
Either post some sample data in your posting or go to TheCodeCage.Com
and attach the file to the posting. I'm not sure which website you
generated you request but there are a few websites that simulataneously
posts the same requests. The CodeCage allows files to be attached to
the request.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

Modify to suit

Sub findlowestinblock()
MC = 1' col A
On Error Resume Next
For i = Cells(Rows.Count, MC).End(xlUp).Row To 1 Step -1
mr = Cells(i - 3, MC).Resize(4)
If Application.CountA(mr) = 4 Then
'MsgBox i
'MsgBox Application.Min(mr)
Cells(2, MC + 4) = Application.Min(mr)
Exit For
End If
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cecil" wrote in message
...
Hi All,

If it is not the right place for my question, please point me to the
correct newsgroup.

I have a perplexing problem building a macro in excel that I hope someone
can help me solve as follows;

I have a stream of data values in column AB2:AB14516.
And what I would like to do, is to find lowest value in the last highest
group of 13 consecutive values, rather than the first group using Excel
VBA.
Then put that value into cell W3 and the address of that group's range
into cell W4.
In addition, Identify the address of the five cells ahead of that group
that was found and put that address into cell W2.
And finally, identify the address of the 22 cells after the group of 13
consecutive high values and put its address into cell W5.

Any help at this point would be greatly appreciated!
Thanks in advance,
CTown

joel;578841 Wrote:
Yes it can be done and this is the right place. But I need to see some
data because your instructions can be interpreted in more than 1 way.
Either post some sample data in your posting or go to TheCodeCage.Com
and attach the file to the posting. I'm not sure which website you
generated you request but there are a few websites that simulataneously
posts the same requests. The CodeCage allows files to be attached to
the request.


Hi Joel,



Thanks for responding to this request!

The following is a sample of 40 consecutive cells from the column of
data that I described in which I want to capture the cell addresses of.


Please note how the groupings that I explained previously, in total
would comprise a total of 40 consecutive cells of data, while the first
five cells and the remaining 22 cells are consecutive in relation to the
target grouping of the 13 highest values originally searched on. The
order of steps below should paint a clearer picture/description of the
task.



For example;

Found some where in

Column AB

Row Count.) Row# Values



Step 2 Identify the address of the five cells ahead of that target
group that was found in “step 1” and put that address into cell W2,
which would be “$AB$3270:$AB$3274”.



1.) 3270 484

2.) 3271 912.4

3.) 3272 2884.8

4.) 3273 2793.2

5.) 3274 4745.6



Step 1 The macro would first locate the following 13 values in column
AB, simply because they represent the highest consecutive values in the
column list, while “4018” of Row #3286 in this group is found to be the
lowest value in this group of high values.

So the value “4018” would be copied into cell “W3”, and the address of
that group's range “$AB$3275:$AB$3287”, would be stored into cell W4.
This first step is critical because it represents a worst case scenario
to be acted on when the macro completes its process.



6.) 3275 5773

7.) 3276 5310

8.) 3277 5137

9.) 3278 4982

10.) 3279 4828

11.) 3280 4635

12.) 3281 4500

13.) 3282 4249

14.) 3283 4307

15.) 3284 4211

16.) 3285 4172

17.) 3286 4018

18.) 3287 4963



Step 3 Identify the address of the 22 cells after the target group of
13 consecutive high values from “Step 1” and put its address into cell
W5, which in this case would be “$AB$3288:$AB$3309.



19.) 3288 2688.15

20.) 3289 2497.3

21.) 3290 415.45

22.) 3291 70.6

23.) 3292 -1021

24.) 3293 -790

25.) 3294 -655

26.) 3295 -539

27.) 3296 -230

28.) 3297 -326

29.) 3298 59

30.) 3299 117

31.) 3300 156

32.) 3301 291

33.) 3302 253

34.) 3303 310

35.) 3304 387

36.) 3305 445

37.) 3306 368

38.) 3307 503

39.) 3308 484

40.) 3309 388



Step 4 Finally, copy the values in the three groups of addresses
beginning with the starting address from cell W3, which would be
“$AB$3270” and the ending address from cell W5, which in this case would
be $AB$3309”, so the full range of 40 cell values can be copied into
Range(“$F$4:$F$43”), completing the macro process.



If it would be more helpful to have a sample workbook with an actual
data stream to practice with, let me know and I'll have to post it as an
attachment on “TheCodeCage.Com” as you stated?



And thank you Joel, for your interest in helping me with this. Because
I don't mind saying it has been one major hair puller by the hand full
for me.



Cecil,

----- Original Message -----
From: joel
Newsgroups: microsoft.public.excel.programming
Sent: Sunday, December 06, 2009 6:35 AM
Subject: Is this Multi-Search Macro Possible?



Yes it can be done and this is the right place. But I need to see
some
data because your instructions can be interpreted in more than 1 way.
Either post some sample data in your posting or go to TheCodeCage.Com
and attach the file to the posting. I'm not sure which website you
generated you request but there are a few websites that
simulataneously
posts the same requests. The CodeCage allows files to be attached to
the request.


--
joel
------------------------------------------------------------------------
joel's Profile: 'The Code Cage Forums - View Profile: joel'
(http://www.thecodecage.com/forumz/member.php?userid=229)
View this thread: 'Is this Multi-Search Macro Possible? - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=160004)

'Microsoft Office Help' ("http://www.thecodecage.com")


--
c-town
------------------------------------------------------------------------
c-town's Profile: 695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

Don,

I've made the following changes in the code you suggested in order to
better understand it.


Code:
--------------------
Sub findlowestinblock()
'MC = 1 ' col A
MC = 28 ' col AB
Worksheets("Sheet1").Activate
On Error Resume Next
For i = Cells(Rows.Count, MC).End(xlUp).Row To 1 Step -1
mr = Cells(i - 3, MC).Resize(4)
If Application.CountA(mr) = 4 Then
MsgBox "Row Count = " & i
Range("T4").value = i
MsgBox "Highest Group Minimum Value = " & Application.Min(mr)
Range("T2").value = mr
Cells(2, MC + 4) = Application.Min(mr)
Exit For
End If
Next i
End Sub
--------------------


Please correct me if I am wrong, but is the following line of code
seeking only a group of four values?


Code:
--------------------
mr = Cells(i - 3, MC).Resize(4)
--------------------


I was able to figure out with the use of the message boxes that the
overall function determines the length of the column data stream and I
think it found the lowest value in the groups that it searched, but it
does not reveal the address of the block to which it belongs. In the
data stream that I tried the macro on, there are 92 occurances. A bit of
in-lightenment may help make better use of this macro. I'll prepare a
worksheet that I can post, the actual worksheet is way too congested to
post. In the meantime, please examine the following explanation to
farther clarify my task.

The following is a sample of 40 consecutive cells from the column of
data that I described in which I want to capture the cell addresses of.


Please note how the groupings that I explained previously, in total
would comprise a total of 40 consecutive cells of data, while the first
five cells and the remaining 22 cells are consecutive in relation to the
target grouping of the 13 highest values originally searched on. The
order of steps below should paint a clearer picture/description of the
task.



For example;

Found some where in

Column AB

Row Count.) Row# Values



Step 2 Identify the address of the five cells ahead of that target
group that was found in “step 1” and put that address into cell W2,
which would be “$AB$3270:$AB$3274”.



1.) 3270 484

2.) 3271 912.4

3.) 3272 2884.8

4.) 3273 2793.2

5.) 3274 4745.6



Step 1 The macro would first locate the following 13 values in column
AB, simply because they represent the highest consecutive values in the
column list, while “4018” in this group is found to be the lowest value
in this group of high values.

So the value “4018” would be copied into cell “W3”, and the address of
that group's range “$AB$3275:$AB$3287”, would be stored into cell W4.
This first step is critical because it represents a worst case scenario
to be acted on when the macro completes its process.



6.) 3275 5773

7.) 3276 5310

8.) 3277 5137

9.) 3278 4982

10.) 3279 4828

11.) 3280 4635

12.) 3281 4500

13.) 3282 4249

14.) 3283 4307

15.) 3284 4211

16.) 3285 4172

17.) 3286 4018

18.) 3287 4963



Step 3 Identify the address of the 22 cells after the target group of
13 consecutive high values from “Step 1” and put its address into cell
W5, which in this case would be “$AB$3288:$AB$3309.



19.) 3288 2688.15

20.) 3289 2497.3

21.) 3290 415.45

22.) 3291 70.6

23.) 3292 -1021

24.) 3293 -790

25.) 3294 -655

26.) 3295 -539

27.) 3296 -230

28.) 3297 -326

29.) 3298 59

30.) 3299 117

31.) 3300 156

32.) 3301 291

33.) 3302 253

34.) 3303 310

35.) 3304 387

36.) 3305 445

37.) 3306 368

38.) 3307 503

39.) 3308 484

40.) 3309 388



Step 4 Finally, copy the values in the three groups of addresses
beginning with the starting address from cell W3, which would be
“$AB$3270” and the ending address from cell W5, which in this case would
be $AB$3309”, so the full range of 40 cell values can be copied into
Range(“$F$4:$F$43”), completing the macro process.



And thank you for your interest in helping me with this.

CTown,


Don Guillett;579100 Wrote:
Modify to suit


Code:
--------------------

Sub findlowestinblock()
MC = 1' col A
On Error Resume Next
For i = Cells(Rows.Count, MC).End(xlUp).Row To 1 Step -1
mr = Cells(i - 3, MC).Resize(4)
If Application.CountA(mr) = 4 Then
'MsgBox i
'MsgBox Application.Min(mr)
Cells(2, MC + 4) = Application.Min(mr)
Exit For
End If
Next i
End Sub


--------------------

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cecil" wrote in message
...
Hi All,

If it is not the right place for my question, please point me to
the
correct newsgroup.

I have a perplexing problem building a macro in excel that I hope
someone
can help me solve as follows;

I have a stream of data values in column AB2:AB14516.
And what I would like to do, is to find lowest value in the last
highest
group of 13 consecutive values, rather than the first group using
Excel
VBA.
Then put that value into cell W3 and the address of that group's
range
into cell W4.
In addition, Identify the address of the five cells ahead of that
group
that was found and put that address into cell W2.
And finally, identify the address of the 22 cells after the group
of 13
consecutive high values and put its address into cell W5.

Any help at this point would be greatly appreciated!
Thanks in advance,
CTown







--
c-town
------------------------------------------------------------------------
c-town's Profile: 695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

This is pretty simple


Sub GetHigh()

Const GroupSize = 13

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSize
- 1) & ")")
If Total MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If
Next RowCount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))

Range("W2") = FivePreviousRows.Address

Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address

StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow
End If

Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address

'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

Hey Joel,

You are the Man!!! That macro accomplished exactly what I've been
trying to do.
I guess you probably get this response many time throughout the day,
but I have to say it anyway, “Thank you so much”!!!
When I ran the macro over many different data streams, it performed
exactly as intended.
But it also revealed something that I had overlooked before now that is
missing.

With the current capabilities that this macro provides, I can now
target that worst case scenario, but what I would like to know is would
you mind modifying the macro to capture the last possible consecutive
values that meet the same criteria, except the 13 high values are an
average of all the high values?

This way I'll have a norm to compare with the worst case scenario in my
chart graph. I noticed how direct your code addressed each step as
requested, so I'll bet changing the macro to capture the average is
something simple that is still beyond my VBA abilities. This is my final
request on this task if you don't mind helping me with it. I would guess
that both processes could be combined, but I would prefer to run them
separately as needed!

If you are willing to help me with this, the cells to store the
addresses of the second macro for the average high consecutive values
would be as follows;

Preceding 5 cell Group Range = T7
Lowest 1 of Highest 13 Value = T8
Highest 13 group Range = T9
Trailing 22 Cell Group Range = T10

And the range to copy the full consecutive 40 cell values into is
"AK2:AK41".

As a long shot guess, would changing the following line of your code be
moving in the right direction?

Changing this,

Total = Evaluate("Sum(AB" & RowCount &
":AB" & (RowCount + GroupSize - 1) & ")")

To this?

Total = Evaluate("Avg(AB" & RowCount &
":AB" & (RowCount + GroupSize - 1) & ")")


And thank you again Joel for the help you have already given me.
You guys are really the greatest!

Cecil,


joel;579495 Wrote:
This is pretty simple


Sub GetHigh()

Const GroupSize = 13

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSize
- 1) & ")")
If Total MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If
Next RowCount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))

Range("W2") = FivePreviousRows.Address

Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address

StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow
End If

Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address

'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")


End Sub


--
c-town
------------------------------------------------------------------------
c-town's Profile: 695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

I made some changes to the code below. I don't know where you want to
put the average of all the totals. I also don't know what the last set
of values should be.

From your last instructions you said you ran the macro on different
data streams. I think you want to keep a worse case of the different
data streams but not sure. You could also means that the min and max
are refering to a single data stream.


Sub GetHigh()

Const GroupSize = 13

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
LowestAverage = 0
HighestAverage = 0
TotalSum = 0
NumberofSums = 1
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSize
- 1) & ")")
If Total MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If

TotalSum = TotalSum + Total
NumberofSums = NumberofSums + 1

Average = Evaluate("Avg(AB" & RowCount & ":AB" & (RowCount +
GroupSize - 1) & ")")
If LowestAverage = 0 Then
LowestAverage = Average
Else
If Average < LowestAverage Then
LowestAverage = Average
End If
If Average HighestAverage Then
HighestAverage = Average
End If

End If

Next RowCount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))

Range("W2") = FivePreviousRows.Address

Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address

StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow
End If

Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address

'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")

Range("T8") = LowestAverage
Range("T9") = HighestAverage

'where does this go?
TotalAverage = TotalSum / NumberofSums

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

joel;580656 Wrote:
I made some changes to the code below. I don't know where you want to
put the average of all the totals. I also don't know what the last set
of values should be.

From your last instructions you said you ran the macro on different
data streams. I think you want to keep a worse case of the different
data streams but not sure. You could also means that the min and max
are refering to a single data stream.

The evaluate method refers to the worksheet function names the AVG
should be Average.


Sub GetHigh()

Const GroupSize = 13

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
LowestAverage = 0
HighestAverage = 0
TotalSum = 0
NumberofSums = 1
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSize
- 1) & ")")
If Total MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If

TotalSum = TotalSum + Total
NumberofSums = NumberofSums + 1

Average = Evaluate("Average(AB" & RowCount & ":AB" & (RowCount +
GroupSize - 1) & ")")
If LowestAverage = 0 Then
LowestAverage = Average
Else
If Average < LowestAverage Then
LowestAverage = Average
End If
If Average HighestAverage Then
HighestAverage = Average
End If

End If

Next RowCount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))

Range("W2") = FivePreviousRows.Address

Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address

StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow
End If

Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address

'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")

Range("T8") = LowestAverage
Range("T9") = HighestAverage

'where does this go?
TotalAverage = TotalSum / NumberofSums

End Sub

Joel,

You stated;

I made some changes to the code below. I don't know where you want to
put the average of all the totals. I also don't know what the last
set
of values should be.

For a bit more clarity I will briefly say this, that the total 40 cells
of values that I used in my previous example represent a pressure curve
of 40 data points per second and when displayed in a line chart, reveal
a pressure rise (the first 5 data points, a peak (the 13 highest data
points), a decay (of 3 data points, and finally a low between each
pressure curve (of 19 data points), hence the decay and low values
comprised the 22 consecutive values that followed the targeted 13 high
values. I hope I haven’t muddy the water, but I’ll bet that you get the
picture a little clearer now.

So, in answer to your question of “where to put the average of all the totals?” the search results value would be save into the cell location as
follows;

The cells to store the addresses of the second macro for the average
high consecutive values;

Preceding 5 cell Group Range Address in T7
Lowest 1 of the Average Highest 13 Value in T8
Average Highest 13 group Range Address in T9
Trailing 22 Cell Group Range Address in T10

And the range to copy the full consecutive 40 cell values into is
"AK2:AK41".

In answer to your next question of “I also don't know what the last set
of values should be.”.
That is understandable, because I worded that part of my message to you
pretty poorly; as I was think aloud to myself when I wrote it. Please
excuse!
Just understand that when the data stream is generated, the targeted 13
high values appear to always be generated earlier in the column data and
then begins to plateau down to a lower set of high values. This is why I
need an average set of high values to compare. So please disregard my
remark about seeking the last set, seeking the average of high values is
a far better approach to achieving my goal during the corrective action
calculations that are ready and waiting for these search results.

You Asked;

From your last instructions you said you ran the macro on different
data streams. I think you want to keep a worse case of the different
data streams but not sure. You could also means that the min and max
are refering to a single data stream.

Yes, I loaded previously saved data streams one at a time into the same
source data column beginning at ”AB2” to examine the performance of the
macro search results. When the worst case is determined, another macro
attached to a button on the worksheet is run to save that data to a
different location to calculate corrective actions. So yes, in that
respect the reference is always to a single data stream intended for the
search task of both macros. Note, that the search result from each macro
will be saved to separate locations as described. In addition, both
macros must remain as separate utilities.

The main purpose of having a second macro to locate the average 13 high
values is to better establish a low end boundary in comparison to the
previous maximum 13 high values during the corrective action
calculation, but the first macro may not be needed in every examination.
My thinking is that the second macro we're working on can target the
average 13 high values group, relative to all of the groups of high/peak
values and capture that set of 40 values. The completion of this second
macro will finally resolve this problem I’ve been working on for months.
:o:

I am attaching a text file to this message of an actual data stream you
can use to help in testing your macro.

So thank you again Joel for sticking with me on this!

Cecil,


+-------------------------------------------------------------------+
|Filename: DataStream.txt |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=382|
+-------------------------------------------------------------------+

--
c-town
------------------------------------------------------------------------
c-town's Profile: 695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

1) I have a BSEE and a MSCS with over 30 year off on-job experience.
2) My son is a Chemical Engineer
3) My roomate at school was also a chemical Engineer and his favorite
expression was "Thermal dynamics Sucks!".


You have Sinusoidal data and I'm not sure what is the important
parameters are that you are looking for.

a) You can take the absolute avarage of all the points or the RMS
value of the points
If you save a true sine wave thenb the RMS is .707 of the peak and the
average is approximately .63 of peak. You can get harmonic distortion
which is how close the signal (pressure measurement) is to a true sine
wave.
b) You can get the Peak-to-Peak vaue of the data
c) You can get the minimum and maximum



I looked at the data closely and see the the data eventually ramps up
and becomes a true sinusoidal data. I think you want to get the
peak-to-peak value by calculating the average. I would look at the data
backwards instead of foward to find the point where the average value
drops to something like 90% to find the transition point. If you
average the last 100 to 200 points and use this average as a reference
then move backwards until the average drops to 90% of the reference you
can find where the data transitions.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

joel;581857 Wrote:
1) I have a BSEE and a MSCS with over 30 year off on-job experience.
2) My son is a Chemical Engineer
3) My roomate at school was also a chemical Engineer and his favorite
expression was "Thermal dynamics Sucks!".


You have Sinusoidal data and I'm not sure what is the important
parameters are that you are looking for.

a) You can take the absolute avarage of all the points or the RMS
value of the points
If you save a true sine wave thenb the RMS is .707 of the peak and the
average is approximately .63 of peak. You can get harmonic distortion
which is how close the signal (pressure measurement) is to a true sine
wave.
b) You can get the Peak-to-Peak vaue of the data
c) You can get the minimum and maximum



I looked at the data closely and see the the data eventually ramps up
and becomes a true sinusoidal data. I think you want to get the
peak-to-peak value by calculating the average. I would look at the data
backwards instead of foward to find the point where the average value
drops to something like 90% to find the transition point. If you
average the last 100 to 200 points and use this average as a reference
then move backwards until the average drops to 90% of the reference you
can find where the data transitions.
--- AUTOMERGED CONSECUTIVE POST BEFORE RESPONSE ---
I was think some more and think you really want the average peak value.
You should get the average of all the points once the data ramps up.
Then compute the the absolute peak by using the formula

X is all the points after the data ramp up
x(i) is each individual data point

Offset = Average(X) after data ramps up

Average Peak = Average(abs(x(i)-Offset)) after data ramps up

Joel,

Thanks for sharing a bit of your background. This is great news and I
like your roommate already.
Heaven must be smiling upon us both, because your suggestion “b) You can get the Peak-to-Peak vaue of the data” as you stated;

“I would look at the data backwards instead of foward to find the point
where the average value drops to something like 90% to find the
transition point. If you average the last 100 to 200 points and use
this average as a reference then move backwards until the average drops
to 90% of the reference you can find where the data transitions.”

Yes, this is precisely the direction I’d like the macro to take. And
because one complete pressure curve is 40 points of data, I would like
to expand the average of the last data points to maybe 400.

In your "Automerged Post" you also said;

You should get the average of all the points once the data ramps up.
Then compute the the absolute peak by using the formula

X is all the points after the data ramp up
x(i) is each individual data point

Offset = Average(X) after data ramps up

Average Peak = Average(abs(x(i)-Offset)) after data ramps up

If I understand your suggestion correctly, this would allow the macro
to focus on only the average peaks after the data has ramped up! That is
ultimately exactly what is needed. Many of the other data streams I've
examined have very low values prior to the ramp up, some times negative
values and often for 400 or more data points before the ramp even
begins.

So I wanted to figure away in VBA to disregard as much of that as
possible.
Unfortunately, my experience in VBA is too limited, so as you can
imagine I learn a tremendous amount about the power of VBA and how it
can automate Excel, every time I seek help with problems such as this
one. So Joel, please know that your time, experience and generosity are
greatly appreciated, I hope to be in your position someday soon, helping
others this way!

Thank you so much for your help and insight, and I look forward to
trying out the macro you suggest so that others can get the meaningful
assistance you’ve shared with me!

Kind Regards,
Cecil


--
c-town
------------------------------------------------------------------------
c-town's Profile: 695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

I have 3 macros below. I removed some of the newer code from the 1st
macro. The I create two macros to find the threshhold. Modify as
necessary. The first uses just the average to get the trip point. The
2nd uses the average peak to determine the trip point.

My son got laid off from Honeywell yesterday. Do you know of any
contacts for an eager your chemical engineer?


Sub GetHigh()

Const GroupSize = 13

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
LowestAverage = 0
HighestAverage = 0
TotalSum = 0
NumberofSums = 1
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSize -
1) & ")")
If Total MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If

TotalSum = TotalSum + Total
NumberofSums = NumberofSums + 1

Average = Evaluate("Average(AB" & RowCount & ":AB" & (RowCount +
GroupSize - 1) & ")")
If LowestAverage = 0 Then
LowestAverage = Average
Else
If Average < LowestAverage Then
LowestAverage = Average
End If
If Average HighestAverage Then
HighestAverage = Average
End If

End If

Next RowCount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))

Range("W2") = FivePreviousRows.Address

Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address

StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow
End If

Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address

'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")

Range("T8") = LowestAverage
Range("T9") = HighestAverage



End Sub



Sub GetStartFromAverage()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

TripPoint = Reference * ThreshHold
'get ramp point
For RowCount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalReference = Evaluate("Average(AB" & RowCount & ":AB" &
(RowCount + ComparePoints - 1) & ")")
If LocalReference <= TripPoint Then
TripRow = RowCount
Exit For
End If

Next RowCount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalReference

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub



Sub GetStartFromAveragePeak()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

ReferenceAveragePeak = Evaluate("Average(abs(AB" & (LastRow -
ReferencePoints + 1) & _
":AB" & LastRow & "-" & Reference & "))")


TripPoint = ReferenceAveragePeak * ThreshHold
'get ramp point
For RowCount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalAverage = Evaluate("Average(AB" & RowCount & ":AB" & (RowCount
+ ComparePoints - 1) & ")")

LocalAveragePeak = Evaluate("Average(abs(AB" & RowCount & _
":AB" & (RowCount + ComparePoints - 1) & "-" & LocalAverage &
"))")

If LocalAveragePeak <= TripPoint Then
TripRow = RowCount
Exit For
End If

Next RowCount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalAveragePeak

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

Joel,

Although “Option Explicit” is not in the module you sent,
the third macro “GetStartFromAveragePeak “ is raising errors such as
“Type mismatch (13)” at the following line of code;

“TripPoint = ReferenceAveragePeak * ThreshHold” = <type mismatch

and

“ReferenceAveragePeak” from the previous line;

ReferenceAveragePeak = Evaluate("Average(abs(AB" & (LastRow -
ReferencePoints + 1) & ":AB" & LastRow & "-" & Reference & "))")

is getting “ReferenceAveragePeak = Error 2015”, can you help me head
this off and other possible errors with the “Option Explicit” statement
in place for the whole moldule? This is a big weakness in my VBA
abilities is knowing what variable or object is the correct intent.

You also stated;
My son got laid off from Honeywell yesterday. Do you know of any
contacts for an eager your chemical engineer?

I will have to make some inquiries when I return to work next week.
I appologize for the lengthy delays in communication with you. I work
evening shift on very demanding projects and I tend to bring my work
home with me. Not a good habit, I know!

If I believe I have a suggestion that may be fruitful for him, it would
not be advisable to post it in a public forum such as this because
competition for such positions these days are very high. Therefore, I
will request a more private means of communication from you at that
point.
HTH

Thank you for help any suggestions you may have.

Cecil,

joel;582865 Wrote:
I have 3 macros below. I removed some of the newer code from the 1st
macro. The I create two macros to find the threshhold. Modify as
necessary. The first uses just the average to get the trip point. The
2nd uses the average peak to determine the trip point.

My son got laid off from Honeywell yesterday. Do you know of any
contacts for an eager your chemical engineer?


Sub GetHigh()

Const GroupSize = 13

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
LowestAverage = 0
HighestAverage = 0
TotalSum = 0
NumberofSums = 1
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For RowCount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & RowCount & ":AB" & (RowCount + GroupSize -
1) & ")")
If Total MaxCount Then
FirstRow = RowCount
MaxCount = Total
End If

TotalSum = TotalSum + Total
NumberofSums = NumberofSums + 1

Average = Evaluate("Average(AB" & RowCount & ":AB" & (RowCount +
GroupSize - 1) & ")")
If LowestAverage = 0 Then
LowestAverage = Average
Else
If Average < LowestAverage Then
LowestAverage = Average
End If
If Average HighestAverage Then
HighestAverage = Average
End If

End If

Next RowCount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))

Range("W2") = FivePreviousRows.Address

Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address

StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow
End If

Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address

'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")

Range("T8") = LowestAverage
Range("T9") = HighestAverage



End Sub



Sub GetStartFromAverage()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

TripPoint = Reference * ThreshHold
'get ramp point
For RowCount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalReference = Evaluate("Average(AB" & RowCount & ":AB" &
(RowCount + ComparePoints - 1) & ")")
If LocalReference <= TripPoint Then
TripRow = RowCount
Exit For
End If

Next RowCount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalReference

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub



Sub GetStartFromAveragePeak()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

ReferenceAveragePeak = Evaluate("Average(abs(AB" & (LastRow -
ReferencePoints + 1) & _
":AB" & LastRow & "-" & Reference & "))")


TripPoint = ReferenceAveragePeak * ThreshHold
'get ramp point
For RowCount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalAverage = Evaluate("Average(AB" & RowCount & ":AB" & (RowCount
+ ComparePoints - 1) & ")")

LocalAveragePeak = Evaluate("Average(abs(AB" & RowCount & _
":AB" & (RowCount + ComparePoints - 1) & "-" & LocalAverage &
"))")

If LocalAveragePeak <= TripPoint Then
TripRow = RowCount
Exit For
End If

Next RowCount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalAveragePeak

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub


--
c-town
------------------------------------------------------------------------
c-town's Profile: 695
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help

They say networking is the best method of getting a job. I told my son
to call twop good friend from college that we still keep in touch with.
I also went to my company website which has job all over the country.
There are some jobs that get listed internally before they go on the
public site. I on;y found one chemical enginerring job that was already
filled.

Option explicit only adds a requirement to declare all variables. I
added the variable declaration to the code below.

The 2015 error is caused when the evaluate function returns a bad
value. It is equivalent to the worksheet giving an error like N/A. I'm
not getting the error.

the coide I posted has an unline (line continuation character) which
you didn't post. It could be missing.

LocalAveragePeak = Evaluate("Average(abs(AB" & Rowcount & _
":AB" & (Rowcount + ComparePoints - 1) & "-" & LocalAverage & "))")


Since the code is almost exactly like the previous macro. This means
either The "ABS" isn't working or you are using different data that the
data I tested with. First try the data you posted to see if it gives
the same error. Next put on the worksheet the formula =abs(-5) to see
if the ABS is working. I would also change the VBA menu option Tools -
Option - General - Error Trapping - Break on All Errors. Another
possibility is the add-in on the worksheet in the menu Tools - Addins
check the following two items

Analysis Toolpak
Analysis Toolpak VBA

I'm working with 2003 and wouildn't expect any of this code to fail in
2007 especially since the only different between the 2nd and 3rd macro
is the ABS function.




Option Explicit
Sub GetHigh()

Const GroupSize = 13
Dim Average As Single
Dim DataRange As Range
Dim EndRow As Long
Dim FirstRow As Long
Dim FivePreviousRows As Range
Dim HighestAverage As Single
Dim LastRow As Long
Dim LowestAverage As Single
Dim MaxCount As Single
Dim Min As Single
Dim NumberofSums As Single
Dim Rowcount As Long
Dim StartRow As Long
Dim Total As Long
Dim TotalSum As Single
Dim TwentyTwoNextRows As Range


LastRow = Range("AB" & Rows.Count).End(xlUp).Row

MaxCount = 0
FirstRow = 0
LowestAverage = 0
HighestAverage = 0
TotalSum = 0
NumberofSums = 1
'find the hisghest consecuitive 13 numbers by getting the sum of the
values
For Rowcount = 2 To (LastRow - GroupSize + 1)
Total = Evaluate("Sum(AB" & Rowcount & ":AB" & (Rowcount + GroupSize -
1) & ")")
If Total MaxCount Then
FirstRow = Rowcount
MaxCount = Total
End If

TotalSum = TotalSum + Total
NumberofSums = NumberofSums + 1

Average = Evaluate("Average(AB" & Rowcount & ":AB" & (Rowcount +
GroupSize - 1) & ")")
If LowestAverage = 0 Then
LowestAverage = Average
Else
If Average < LowestAverage Then
LowestAverage = Average
End If
If Average HighestAverage Then
HighestAverage = Average
End If

End If

Next Rowcount

Set FivePreviousRows = Range("AB" & (FirstRow - 5) & ":AB" & (FirstRow
- 1))

Range("W2") = FivePreviousRows.Address

Set DataRange = Range("AB" & FirstRow & ":AB" & (FirstRow + GroupSize -
1))
Min = WorksheetFunction.Min(DataRange)
Range("W3") = Min
Range("W4") = DataRange.Address

StartRow = FirstRow + GroupSize
EndRow = StartRow + 21
'Don't exceed the length of data
If EndRow LastRow Then
EndRow = LastRow

End If

Set TwentyTwoNextRows = Range("AB" & StartRow & ":AB" & (EndRow))
Range("W5") = TwentyTwoNextRows.Address

'copy data
Range("AB" & (FirstRow - 5) & ":AB" & EndRow).Copy _
Destination:=Range("F4")

Range("T8") = LowestAverage
Range("T9") = HighestAverage



End Sub



Sub GetStartFromAverage()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

Dim FivePreviousRows As Range
Dim LastRow As Long
Dim LocalReference As Single
Dim Reference As Single
Dim Rowcount As Long
Dim TripPoint As Single
Dim TripRange As Range
Dim TripRow As Long
Dim TwentyTwoNextRows As Range

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

TripPoint = Reference * ThreshHold
'get ramp point
For Rowcount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalReference = Evaluate("Average(AB" & Rowcount & ":AB" & (Rowcount +
ComparePoints - 1) & ")")
If LocalReference <= TripPoint Then
TripRow = Rowcount
Exit For
End If

Next Rowcount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalReference

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub



Sub GetStartFromAveragePeak()
Const ReferencePoints = 400
Const ComparePoints = 400
Const ThreshHold = 0.9 ' 90% of reference

Dim FivePreviousRows As Range
Dim LastRow As Long
Dim LocalAverage As Single
Dim LocalAveragePeak As Single
Dim LocalReference As Single
Dim Reference As Single
Dim ReferenceAveragePeak As Single
Dim Rowcount As Long
Dim TripPoint As Single
Dim TripRange As Range
Dim TripRow As Long
Dim TwentyTwoNextRows As Range

LastRow = Range("AB" & Rows.Count).End(xlUp).Row

Reference = Evaluate("Average(AB" & (LastRow - ReferencePoints + 1) &
":AB" & LastRow & ")")

ReferenceAveragePeak = Evaluate("Average(abs(AB" & (LastRow -
ReferencePoints + 1) & _
":AB" & LastRow & "-" & Reference & "))")


TripPoint = ReferenceAveragePeak * ThreshHold
'get ramp point
For Rowcount = (LastRow - ReferencePoints + 1) To 2 Step -1
LocalAverage = Evaluate("Average(AB" & Rowcount & ":AB" & (Rowcount +
ComparePoints - 1) & ")")

LocalAveragePeak = Evaluate("Average(abs(AB" & Rowcount & _
":AB" & (Rowcount + ComparePoints - 1) & "-" & LocalAverage & "))")

If LocalAveragePeak <= TripPoint Then
TripRow = Rowcount
Exit For
End If

Next Rowcount

If TripRow = 0 Then
MsgBox ("Did not find Trip Point")
Exit Sub
End If

Set FivePreviousRows = Range("AB" & (TripRow - 5) & ":AB" & (TripRow -
1))

Range("T7") = FivePreviousRows.Address
Range("T8") = LocalAveragePeak

Set TripRange = Range("AB" & TripRow & ":AB" & (TripRow + 12))
Range("T9") = TripRange.Address


Set TwentyTwoNextRows = Range("AB" & (TripRow + 13) & ":AB" & (TripRow
+ 13 + 21))
Range("T10") = TwentyTwoNextRows.Address


'copy data
Range("AB" & (TripRow - 5) & ":AB" & (TripRow + 13 + 21)).Copy _
Destination:=Range("AK2")


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160004

Microsoft Office Help