Trigger event based on local maxima and minima

artz

Well-known Member
Joined
Aug 11, 2002
Messages
830
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I want to trigger an event based on two conditions: at a fixed percentage of a data value of a local maxima, after a local maxima occurs in the data, to turn the event on. Alternatively, at fixed a percentage of the data value where a local minima occurs, to turn the event off. Data values after local maxima will be decreasing and vice versa following a local minima. The data values are pretty much sinusoidal, varying with time, and have some minor peak to peak amplitude variations.

Said more simply, turn on when signal decreases to some percentage of last peak value; turn off when signal value increases some percentage above last minimum value.

The output of the trigger can be binary, 1 when turned it on, 0 when turned off.

I started with the following code to find the maxima and minima, it seems to work:

Code:
Sub FindMaximaMinima()
Application.ScreenUpdating = False
Dim i As Long
Dim Change As Boolean
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
If Change Then
  If Range("B" & i) > Range("B" & i + 1) Then
    Range("C" & i) = "Max"
    Change = False
  End If
Else
  If Range("B" & i) < Range("B" & i + 1) Then
    Range("C" & i) = "Min"
    Change = True
  End If
End If
Next i
Application.ScreenUpdating = True
End Sub

It's pretty simple but does not account for potential noise in the data.

I am stuck though and need some help getting the trigger points added to the code. Is there anyone in the Forum who could help out with some code to add the on/off trigger?

Thanks for any help,

-Art
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Can you post some sample data with the outcomes that you're expecting?
 
Upvote 0
Hi lrobbo314,

Thanks for your response.

Sure, that's a reasonable request. I don't have access to the data tonight- will have to the in the morning, tomorrow. Am also in CA. Let me get a data set together and we can see what you can do with it.


I had problems last time posting data because ELJeanie wasn't supported for Win7. What's the best way to get a data file to you?

If you can just work with my simplified data, you could create a worksheet that creates the sine wave:

Code:
=1.5*SIN(2*PI()*A2*$G$2)
G2 sets the frequency. For the delta time, A2 is the time increment (.01, .02 seconds, $G$2 is the frequency in Hz)

Please let me know what works best.

Thanks,

-Art
 
Upvote 0
Hi Irobbo314,

Here are 15 seconds of representative data. Hopefully you can work with this.

-Art

Code:
0	1.05012
0.05	1.04717
0.1	1.04454
0.15	1.04208
0.2	1.03962
0.25	1.03765
0.3	1.03552
0.35	1.03355
0.4	1.03174
0.45	1.03026
0.5	1.02961
0.55	1.02977
0.6	1.03026
0.65	1.03125
0.7	1.0324
0.75	1.03371
0.8	1.03584
0.85	1.03847
0.9	1.04208
0.95	1.04553
1	1.04881
1.05	1.05259
1.1	1.05669
1.15	1.06129
1.2	1.06539
1.25	1.06966
1.3	1.07393
1.35	1.07852
1.4	1.08295
1.45	1.08771
1.5	1.09264
1.55	1.09707
1.6	1.10167
1.65	1.10577
1.7	1.11053
1.75	1.11513
1.8	1.11989
1.85	1.12481
1.9	1.12908
1.95	1.13368
2	1.13827
2.05	1.14287
2.1	1.14697
2.15	1.15173
2.2	1.15616
2.25	1.16093
2.3	1.16552
2.35	1.16946
2.4	1.17373
2.45	1.17816
2.5	1.18226
2.55	1.18653
2.6	1.19047
2.65	1.19343
2.7	1.19507
2.75	1.19622
2.8	1.19556
2.85	1.19425
2.9	1.19146
2.95	1.18867
3	1.18522
3.05	1.18128
3.1	1.17652
3.15	1.17143
3.2	1.16569
3.25	1.16043
3.3	1.15469
3.35	1.14911
3.4	1.14369
3.45	1.13811
3.5	1.13302
3.55	1.12777
3.6	1.12235
3.65	1.11693
3.7	1.11168
3.75	1.1061
3.8	1.10183
3.85	1.09707
3.9	1.09247
3.95	1.08771
4	1.08312
4.05	1.07869
4.1	1.07442
4.15	1.07031
4.2	1.06654
4.25	1.06309
4.3	1.05915
4.35	1.05571
4.4	1.05242
4.45	1.04898
4.5	1.04586
4.55	1.04323
4.6	1.0406
4.65	1.03831
4.7	1.03568
4.75	1.03338
4.8	1.03158
4.85	1.02928
4.9	1.02698
4.95	1.0255
5	1.0237
5.05	1.02189
5.1	1.02041
5.15	1.01877
5.2	1.01729
5.25	1.01647
5.3	1.01615
5.35	1.01647
5.4	1.01746
5.45	1.01877
5.5	1.02041
5.55	1.02271
5.6	1.02599
5.65	1.02961
5.7	1.03355
5.75	1.03798
5.8	1.04225
5.85	1.0475
5.9	1.05291
5.95	1.058
6	1.06326
6.05	1.06917
6.1	1.07491
6.15	1.08049
6.2	1.08706
6.25	1.0933
6.3	1.09953
6.35	1.10577
6.4	1.11217
6.45	1.11825
6.5	1.12481
6.55	1.13171
6.6	1.13893
6.65	1.14582
6.7	1.15321
6.75	1.16027
6.8	1.16716
6.85	1.17389
6.9	1.18062
6.95	1.18719
7	1.19244
7.05	1.19687
7.1	1.20032
7.15	1.20245
7.2	1.20344
7.25	1.20377
7.3	1.2036
7.35	1.20245
7.4	1.20114
7.45	1.19868
7.5	1.19622
7.55	1.19293
7.6	1.18949
7.65	1.18588
7.7	1.18177
7.75	1.17816
7.8	1.17406
7.85	1.16979
7.9	1.16569
7.95	1.16125
8	1.15666
8.05	1.1519
8.1	1.14763
8.15	1.14353
8.2	1.13909
8.25	1.13483
8.3	1.13072
8.35	1.12678
8.4	1.12284
8.45	1.11874
8.5	1.11529
8.55	1.11086
8.6	1.10708
8.65	1.1038
8.7	1.10052
8.75	1.09756
8.8	1.09477
8.85	1.09182
8.9	1.09001
8.95	1.08886
9	1.08771
9.05	1.08755
9.1	1.08804
9.15	1.08919
9.2	1.09067
9.25	1.09264
9.3	1.09576
9.35	1.09904
9.4	1.10282
9.45	1.10659
9.5	1.11086
9.55	1.11496
9.6	1.11923
9.65	1.12383
9.7	1.12826
9.75	1.13335
9.8	1.13844
9.85	1.14353
9.9	1.14845
9.95	1.1537
10	1.15896
10.05	1.16421
10.1	1.1693
10.15	1.17488
10.2	1.17997
10.25	1.18538
10.3	1.19129
10.35	1.19687
10.4	1.20245
10.45	1.20853
10.5	1.21411
10.55	1.21871
10.6	1.2233
10.65	1.22708
10.7	1.23003
10.75	1.23184
10.8	1.23249
10.85	1.23217
10.9	1.23118
10.95	1.23003
11	1.22757
11.05	1.22445
11.1	1.2215
11.15	1.21805
11.2	1.21427
11.25	1.21017
11.3	1.20574
11.35	1.20229
11.4	1.19786
11.45	1.19359
11.5	1.18867
11.55	1.18423
11.6	1.17964
11.65	1.17537
11.7	1.17127
11.75	1.16667
11.8	1.16257
11.85	1.15797
11.9	1.15387
11.95	1.14993
12	1.14582
12.05	1.14254
12.1	1.1386
12.15	1.13499
12.2	1.13138
12.25	1.1281
12.3	1.12465
12.35	1.12137
12.4	1.11841
12.45	1.11513
12.5	1.11283
12.55	1.10971
12.6	1.10741
12.65	1.10495
12.7	1.10249
12.75	1.10035
12.8	1.09822
12.85	1.09674
12.9	1.09559
12.95	1.09527
13	1.09576
13.05	1.09674
13.1	1.09838
13.15	1.10052
13.2	1.10314
13.25	1.1061
13.3	1.10971
13.35	1.11414
13.4	1.11808
13.45	1.12202
13.5	1.12629
13.55	1.13089
13.6	1.13483
13.65	1.13975
13.7	1.14467
13.75	1.14943
13.8	1.15436
13.85	1.15928
13.9	1.16437
13.95	1.16897
14	1.17356
14.05	1.17849
14.1	1.18341
14.15	1.18834
14.2	1.1931
14.25	1.19786
14.3	1.20213
14.35	1.20672
14.4	1.21132
14.45	1.21509
14.5	1.21953
14.55	1.2233
14.6	1.22741
14.65	1.23118
14.7	1.2343
14.75	1.23676
14.8	1.23758
14.85	1.23758
14.9	1.23643
14.95	1.23414
15	1.23085
 
Upvote 0
Great, but I am still unclear as to what your actual goal is here. You say
Said more simply, turn on when signal decreases to some percentage of last peak value; turn off when signal value increases some percentage above last minimum value.

But, decrease or increase by what percentage. Are you looking to get a value in column C in the end? Just need to know what the end result should look like.
 
Upvote 0
Hi,

Thanks for response. Attached is a link to the workbook I started- probably it's easier to get a sense of what's going on looking at that.

First local minima occurs at 0.5 sec, i.e., 1.02961. This local minima is multplied by the value in J2; the value in F12. What I would like to see is when the data in column B is equal to or greater than F12,a marker is placed on the chart and the trigger chart is updated.

Similarly, for a local maxima like at 2.75sec, at 1.19622, this local maxima is multplied by the value in I2; the value in E57. When the data in column B is equal to or less than than E57, a marker is placed on the chart and the trigger chart is updated.

I've also added trigger signals manually that switch between 0 and 1 based on the max below (<=) and min above (>=). To give you a better example of what I am trying to do, I located the "trigger" points by hand on the attached chart. I may not have gotten all the points right since I scanned through column B manually searching for the values that were best matches for max below (<=) and min above (>=). Those are the trigger transition points.

I'd still like symbols plotted on the signal waveform at the transition points. The link to the file is: http://s000.tinyupload.com/?file_id=91338417668327762069

Please let me know if you have any further questions.

Thanks!

-Art
 
Upvote 0
Cross posted https://www.ozgrid.com/forum/forum/...os/1218100-trigger-based-on-maxima-and-minima

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Hi,

Sorry - did not mean to cause a problem for those helping me. I posted to ozgrid because they allow file uploading. It seemed I was having a hard time explaining what I needed and thought having a file to look at would help the person providing assistance. I only thought of the file uploading service idea at the last minute.

Guess I messed up here. Sorry to all. I would very thankful if anyone would still be willing to help. :-)

Thanks,

-Art
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top