Hi,
I found some VBA code for finding local maximas and minimas. Compared to some cell formulas it works best, but I really don't care if these calulations are cell formulas or VBA.
Several cell formulas that I tested for tested for finding local maximas and minimas, missed some points. The code included in this post seems to work best. That's why I am posting to the VBA Forum.
Sub IdentifyPeaksandTroughs()
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) = Range("B" & i).Value
Change = False
End If
Else
If Range("B" & i) < Range("B" & i + 1) Then
Range("D" & i) = Range("B" & i).Value
Change = True
End If
End If
Next i
Application.ScreenUpdating = True
End Sub
Display More
Time V Max Min
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 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 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 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 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 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 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 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 1.23758
14.9 1.23643
14.95 1.23414
15 1.23085
Display More
Also included are data collected in an experimental environment. This is what I tested the included code with. Column A is time data, column B is the experimental data.
Column C is local maxima- column D is the local minima.
Here's what is supposed to happen: following a local maxima, as the data value is falling, column E would indicate True when column B reaches 0.8x (80%) of the last local maxima. It will stay True until the next local minima occurs. At this point, column E would indicate False when column B reaches 1.2x (120%) of the last local minima.
Since the data is cyclic, this will repeat through the dataset.
Could anyone in the Forum help with code or cell formulas to get the output that I need?
Thanks,
-Art