highs and lows

D

dmheller

Board Regular
Joined
May 26, 2017
Messages
156
Office Version
  1. 365
I have seen several post about this in the past via search but i was hoping there was a better way to find the highs and lows than using 5 helper columns. I am wanting to find the low and high each time along with the date and time it happened.
I was hoping to do this with a Let formula. Please let me know if you can help.
thanks

8/6/2024 16:4827.07074
8/6/2024 16:4827.06866
8/6/2024 16:4827.05708
8/6/2024 16:4927.05482
8/6/2024 16:4927.04707
8/6/2024 16:4927.05209
8/6/2024 16:5027.05242
8/6/2024 16:5027.05677
8/6/2024 16:5027.05141
8/6/2024 16:5127.05256
8/6/2024 16:5127.04018
8/6/2024 16:5127.05569
8/6/2024 16:5227.04778
8/6/2024 16:5227.04755
8/6/2024 16:5227.03412
8/6/2024 16:5327.04916
8/6/2024 16:5327.03556
8/6/2024 16:5327.03226
8/6/2024 16:5427.03216
8/6/2024 16:5427.02731
8/6/2024 16:5427.03384
8/6/2024 16:5527.04128
8/6/2024 16:5527.03334
8/6/2024 16:5527.03842
8/6/2024 16:5627.03031
8/6/2024 16:5627.03842
8/6/2024 16:5627.02583
8/6/2024 16:5727.01765
8/6/2024 16:5727.02364
8/6/2024 16:5727.01586
8/6/2024 16:5827.0123
8/6/2024 16:5827.01624
8/6/2024 16:5827.00294
8/6/2024 16:5927.01186
8/6/2024 16:5927.00903
8/6/2024 16:5927.00826
8/6/2024 17:0027.01701
8/6/2024 17:0027.00893
8/6/2024 17:0027.0063
8/6/2024 17:0127.00951
8/6/2024 17:0127.00513
8/6/2024 17:0127.01879
8/6/2024 17:0226.98425
8/6/2024 17:0226.99146
8/6/2024 17:0226.99664
8/6/2024 17:0327.00307
8/6/2024 17:0326.99914
8/6/2024 17:0326.99822
8/6/2024 17:0426.99099
8/6/2024 17:0426.9922
8/6/2024 17:0426.98823
8/6/2024 17:0526.99075
8/6/2024 17:0526.98715
8/6/2024 17:0526.98462
8/6/2024 17:0626.98237
8/6/2024 17:0626.97924
8/6/2024 17:0626.98025
8/6/2024 17:0726.97927
8/6/2024 17:0726.97243
8/6/2024 17:0726.97301
8/6/2024 17:0826.97977
8/6/2024 17:0826.96856
8/6/2024 17:0826.97301
8/6/2024 17:0926.97462
8/6/2024 17:0926.97453
8/6/2024 17:0926.96173
8/6/2024 17:1026.97075
8/6/2024 17:1026.97365
8/6/2024 17:1026.95621
8/6/2024 17:1126.96102
8/6/2024 17:1126.94968
8/6/2024 17:1126.95513
8/6/2024 17:1226.96452
8/6/2024 17:1226.95695
8/6/2024 17:1226.94933
8/6/2024 17:1326.95736
8/6/2024 17:1326.93927
8/6/2024 17:1326.94211
8/6/2024 17:1426.93217
8/6/2024 17:1426.93042
8/6/2024 17:1426.92783
8/6/2024 17:1526.93681
8/6/2024 17:1526.94119
8/6/2024 17:1526.9391
8/6/2024 17:1626.93797
8/6/2024 17:1626.92322
8/6/2024 17:1626.94132
8/6/2024 17:1726.92379
8/6/2024 17:1726.91824
8/6/2024 17:1726.91672
8/6/2024 17:1826.91746
8/6/2024 17:1826.91018
8/6/2024 17:1826.93422
8/6/2024 17:1926.90039
8/6/2024 17:1926.8885
8/6/2024 17:1926.89312
8/6/2024 17:2026.89166
8/6/2024 17:2026.90897
8/6/2024 17:2026.90186
8/6/2024 17:2126.89359
8/6/2024 17:2126.87204
8/6/2024 17:2126.85447
8/6/2024 17:2226.88244
8/6/2024 17:2226.89258
8/6/2024 17:2226.88705
8/6/2024 17:2326.87978
8/6/2024 17:2326.87197
8/6/2024 17:2326.88089
8/6/2024 17:2426.88477
8/6/2024 17:2426.86322
8/6/2024 17:2426.87339
8/6/2024 17:2526.85063
8/6/2024 17:2526.86777
8/6/2024 17:2526.84525
8/6/2024 17:2626.83463
8/6/2024 17:2626.84494
8/6/2024 17:2626.86107
8/6/2024 17:2726.83878
8/6/2024 17:2726.86877
8/6/2024 17:2726.84154
8/6/2024 17:2826.82733
8/6/2024 17:2826.8185
8/6/2024 17:2826.86318
8/6/2024 17:2926.81187
8/6/2024 17:2926.81454
8/6/2024 17:2926.80228
8/6/2024 17:3026.79619
8/6/2024 17:3026.79774
8/6/2024 17:3026.7904
8/6/2024 17:3126.80222
8/6/2024 17:3126.81026
8/6/2024 17:3126.80151
8/6/2024 17:3226.82696
8/6/2024 17:3226.7869
8/6/2024 17:3226.79548
8/6/2024 17:3326.79192
8/6/2024 17:3326.79572
8/6/2024 17:3326.75124
8/6/2024 17:3426.76902
8/6/2024 17:3426.81282
8/6/2024 17:3426.77636
8/6/2024 17:3526.77659
8/6/2024 17:3526.77602
8/6/2024 17:3526.76505
8/6/2024 17:3626.75979
8/6/2024 17:3626.75273
8/6/2024 17:3626.76525
8/6/2024 17:3726.7672
8/6/2024 17:3726.75643
8/6/2024 17:3726.76798
8/6/2024 17:3826.75942
8/6/2024 17:3826.76562
8/6/2024 17:3826.76694
8/6/2024 17:3926.75791
8/6/2024 17:3926.75781
8/6/2024 17:3926.75377
8/6/2024 17:4026.76161
8/6/2024 17:4026.76094
8/6/2024 17:4026.75343
8/6/2024 17:4126.75084
8/6/2024 17:4126.75508
8/6/2024 17:4126.74306
8/6/2024 17:4226.75758
8/6/2024 17:4226.75741
8/6/2024 17:4226.75273
8/6/2024 17:4326.7568
8/6/2024 17:4326.75239
8/6/2024 17:4326.74989
8/6/2024 17:4426.75448
8/6/2024 17:4426.74061
8/6/2024 17:4426.74905
8/6/2024 17:4526.75475
8/6/2024 17:4526.74969
8/6/2024 17:4526.7476
8/6/2024 17:4626.7507
8/6/2024 17:4626.75222
8/6/2024 17:4626.77323
8/6/2024 17:4726.7402
8/6/2024 17:4726.74505
8/6/2024 17:4726.74535
8/6/2024 17:4826.74538
8/6/2024 17:4826.74027
8/6/2024 17:4826.74084
8/6/2024 17:4926.74653
8/6/2024 17:4926.74657
8/6/2024 17:4926.74229
8/6/2024 17:5026.73653
8/6/2024 17:5026.73727
8/6/2024 17:5026.74394
8/6/2024 17:5126.72997
8/6/2024 17:5126.73566
8/6/2024 17:5126.73061
8/6/2024 17:5226.74279
8/6/2024 17:5226.73441
8/6/2024 17:5226.73791
8/6/2024 17:5326.72744
8/6/2024 17:5326.73317
8/6/2024 17:5326.73707
8/6/2024 17:5426.7298
8/6/2024 17:5426.73037
8/6/2024 17:5426.73118
8/6/2024 17:5526.73044
8/6/2024 17:5526.72936
8/6/2024 17:5526.7374
8/6/2024 17:5626.72646
8/6/2024 17:5626.7302
8/6/2024 17:5626.72993
8/6/2024 17:5726.73535
8/6/2024 17:5726.73731
8/6/2024 17:5726.72765
8/6/2024 17:5826.72542
8/6/2024 17:5826.72832
8/6/2024 17:5826.72727
8/6/2024 17:5926.72835
8/6/2024 17:5926.7274
8/6/2024 17:5926.7295
8/6/2024 18:0026.73566
8/6/2024 18:0026.72289
8/6/2024 18:0026.7234
8/6/2024 18:0126.71913
8/6/2024 18:0126.71973
8/6/2024 18:0126.72212
8/6/2024 18:0226.71333
8/6/2024 18:0226.72515
8/6/2024 18:0226.71559
8/6/2024 18:0326.71633
8/6/2024 18:0326.7196
8/6/2024 18:0326.72195
8/6/2024 18:0426.71202
8/6/2024 18:0426.71502
8/6/2024 18:0426.70943
8/6/2024 18:0526.71674
8/6/2024 18:0526.6962
8/6/2024 18:0526.70438
8/6/2024 18:0626.71192
8/6/2024 18:0626.70831
8/6/2024 18:0626.71054
8/6/2024 18:0726.70698
8/6/2024 18:0726.70172
8/6/2024 18:0726.71011
8/6/2024 18:0826.69762
8/6/2024 18:0826.69923
8/6/2024 18:0826.69943
8/6/2024 18:0926.69798
8/6/2024 18:0926.70158
8/6/2024 18:0926.69822
8/6/2024 18:1026.70795
8/6/2024 18:1026.70007
8/6/2024 18:1026.70455
8/6/2024 18:1126.69495
8/6/2024 18:1126.69438
8/6/2024 18:1126.69088
8/6/2024 18:1226.68643
8/6/2024 18:1226.69559
8/6/2024 18:1226.69018
8/6/2024 18:1326.69624
8/6/2024 18:1326.69415
8/6/2024 18:1326.68869
8/6/2024 18:1426.68771
8/6/2024 18:1426.67846
8/6/2024 18:1426.66209
8/6/2024 18:1526.67209
8/6/2024 18:1526.66714
8/6/2024 18:1526.67519
8/6/2024 18:1626.67687
8/6/2024 18:1626.67634
8/6/2024 18:1626.67643
8/6/2024 18:1726.65993
8/6/2024 18:1726.6691
8/6/2024 18:1726.65765
8/6/2024 18:1826.65604
8/6/2024 18:1826.64072
8/6/2024 18:1826.64775
8/6/2024 18:1926.64594
8/6/2024 18:1926.69745
8/6/2024 18:1926.64957
8/6/2024 18:2026.71468
8/6/2024 18:2026.82372
8/6/2024 18:2027.02229
8/6/2024 18:2127.16403
8/6/2024 18:2127.42926
8/6/2024 18:2127.6731
8/6/2024 18:2227.92036
8/6/2024 18:2228.19734
8/6/2024 18:2228.40927
8/6/2024 18:2328.6879
8/6/2024 18:2328.92441
8/6/2024 18:2329.20091
8/6/2024 18:2429.43621
8/6/2024 18:2429.67148
8/6/2024 18:2429.93745
8/6/2024 18:2530.16749
8/6/2024 18:2530.47006
8/6/2024 18:2530.71509
8/6/2024 18:2630.95045
8/6/2024 18:2631.19939
8/6/2024 18:2631.41664
8/6/2024 18:2731.68543
8/6/2024 18:2731.81676
8/6/2024 18:2732.0792
8/6/2024 18:2832.33392
8/6/2024 18:2832.66716
8/6/2024 18:2832.83135
8/6/2024 18:2933.06894
8/6/2024 18:2933.31689
8/6/2024 18:2933.57963
8/6/2024 18:3033.81015
8/6/2024 18:3034.06727
8/6/2024 18:3034.28139
8/6/2024 18:3134.51756
8/6/2024 18:3134.80292
8/6/2024 18:3134.9929
8/6/2024 18:3235.32755
8/6/2024 18:3235.52895
8/6/2024 18:3235.73441
8/6/2024 18:3335.94854
8/6/2024 18:3336.14371
8/6/2024 18:3336.41227
8/6/2024 18:3436.64312
8/6/2024 18:3436.91424
8/6/2024 18:3437.15075
8/6/2024 18:3537.4385
8/6/2024 18:3537.66336
8/6/2024 18:3537.77308
8/6/2024 18:3638.01548
8/6/2024 18:3638.32512
8/6/2024 18:3638.53843
8/6/2024 18:3738.7571
8/6/2024 18:3738.98257
8/6/2024 18:3739.22752
8/6/2024 18:3839.41626
8/6/2024 18:3839.74253
8/6/2024 18:3839.89881
8/6/2024 18:3940.14606
8/6/2024 18:3940.42146
8/6/2024 18:3940.59309
8/6/2024 18:4040.83576
8/6/2024 18:4041.05278
8/6/2024 18:4041.29491
8/6/2024 18:4141.43564
8/6/2024 18:4141.75914
8/6/2024 18:4141.90863
8/6/2024 18:4242.222
8/6/2024 18:4242.39444
8/6/2024 18:4242.67801
8/6/2024 18:4342.87924
8/6/2024 18:4343.08495
8/6/2024 18:4343.34388
8/6/2024 18:4443.66813
8/6/2024 18:4443.82562
8/6/2024 18:4444.063
8/6/2024 18:4544.40237
8/6/2024 18:4544.50542
8/6/2024 18:4544.73594
8/6/2024 18:4644.90734
8/6/2024 18:4645.23108
8/6/2024 18:4645.38046
8/6/2024 18:4745.67831
8/6/2024 18:4745.86297
8/6/2024 18:4746.05777
8/6/2024 18:4846.37185
8/6/2024 18:4846.4878
8/6/2024 18:4846.78945
8/6/2024 18:4946.99691
8/6/2024 18:4947.20433
8/6/2024 18:4947.40673
8/6/2024 18:5047.72802
8/6/2024 18:5047.86892
8/6/2024 18:5048.1743
8/6/2024 18:5148.3956
8/6/2024 18:5148.61578
8/6/2024 18:5148.82296
8/6/2024 18:5249.10432
8/6/2024 18:5249.30713
8/6/2024 18:5249.49462
8/6/2024 18:5349.76668
8/6/2024 18:5350.03215
8/6/2024 18:5350.19361
8/6/2024 18:5450.39905
8/6/2024 18:5450.63707
8/6/2024 18:5450.88271
8/6/2024 18:5551.16009
8/6/2024 18:5551.37909
8/6/2024 18:5551.50403
8/6/2024 18:5651.38902
8/6/2024 18:5651.42255
8/6/2024 18:5651.41145
8/6/2024 18:5751.39239
8/6/2024 18:5751.46023
8/6/2024 18:5751.43363
8/6/2024 18:5851.38896
8/6/2024 18:5851.40582
8/6/2024 18:5851.39619
8/6/2024 18:5951.43363
8/6/2024 18:5951.38606
8/6/2024 18:5951.44804
8/6/2024 19:0051.44134
8/6/2024 19:0051.39306
8/6/2024 19:0051.42117
8/6/2024 19:0151.41363
8/6/2024 19:0151.41209
8/6/2024 19:0151.3966
8/6/2024 19:0251.3693
8/6/2024 19:0251.39865
8/6/2024 19:0251.37159
8/6/2024 19:0351.43161
8/6/2024 19:0351.36792
8/6/2024 19:0351.397
8/6/2024 19:0451.43138
8/6/2024 19:0451.39502
8/6/2024 19:0451.37673
8/6/2024 19:0551.39256
8/6/2024 19:0551.36034
8/6/2024 19:0551.37
8/6/2024 19:0651.35751
8/6/2024 19:0651.37481
8/6/2024 19:0651.36552
8/6/2024 19:0751.38525
8/6/2024 19:0751.34411
8/6/2024 19:0751.34361
8/6/2024 19:0851.34734
8/6/2024 19:0851.35152
8/6/2024 19:0851.36024
8/6/2024 19:0951.35478
8/6/2024 19:0951.35498
8/6/2024 19:0951.37128
8/6/2024 19:1051.34967
8/6/2024 19:1051.34492
8/6/2024 19:1051.356
8/6/2024 19:1151.35845
8/6/2024 19:1151.35253
8/6/2024 19:1151.3359
8/6/2024 19:1251.35711
8/6/2024 19:1251.33825
8/6/2024 19:1251.3395
8/6/2024 19:1351.33532
8/6/2024 19:1351.33613
8/6/2024 19:1351.33485
8/6/2024 19:1451.33243
8/6/2024 19:1451.33068
8/6/2024 19:1451.32199
8/6/2024 19:1551.33266
8/6/2024 19:1551.31159
8/6/2024 19:1551.34041
8/6/2024 19:1651.32189
8/6/2024 19:1651.3126
8/6/2024 19:1651.31206
8/6/2024 19:1751.31516
8/6/2024 19:1751.30031
8/6/2024 19:1751.32947
8/6/2024 19:1851.30684
8/6/2024 19:1851.30486
8/6/2024 19:1851.30822
8/6/2024 19:1951.29987

1725465507557.png
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Why Let? Why not use XLOOKUP?
=XLOOKUP(MIN(B:B),B:B,A:A)
=XLOOKUP(MIN(B:B),B:B,A:A)

Format as the same date/time format as the first column?
 
Upvote 0
What do you consider the high and low values?
These:

1725470231333.png



Or if you zoom in:

these?

1725470338785.png


is there a number of minimum samples that we have to consider before and after the max min value?
 
Upvote 0
The data you shared look like this:

1725471236051.png


not so much like the image you shared in post #4.

So how do you want do detect a low and high point?
 
Upvote 0
Yea, I cant post that much data trough xb22 it has a max.
Usually the up and down is greater than 10.
 
Upvote 0
I think this does what you want:

Excel Formula: 
=LET(d,A2:B455,top,TAKE(d,1),btm,TAKE(d,-1),mid,DROP(DROP(d,-1),1),crit1,DROP(d,2),crit2,DROP(d,-2),ctot,MAP(INDEX(mid,,2),INDEX(crit1,,2),INDEX(crit2,,2),LAMBDA(a,b,c,OR(AND(b<a,c<a),AND(b>a,c>a)))),out,VSTACK(top,FILTER(mid,ctot),btm),out)
 
Upvote 0
That gave a bunch of weird data unless i did something wrong.
i was hoping for 1 data point for the low then a high then a low then high and so on. It happens every 3 or 4 days i believe.
MIXING_TANK_20240903_161725(6122).csv
ABCD
1TANK LEVEL TimeTANK LEVEL ValueY
28/6/2024 3:3631.079678/6/202431.07967
38/6/2024 3:3731.070178/6/202431.06351
48/6/2024 3:3731.063518/6/202431.06357
58/6/2024 3:3731.063578/6/202431.04513
68/6/2024 3:3831.058598/6/202431.04593
78/6/2024 3:3831.047658/6/202431.04263
88/6/2024 3:3831.045138/6/202431.04913
98/6/2024 3:3931.045938/6/202431.03115
108/6/2024 3:3931.042638/6/202431.04324
118/6/2024 3:3931.049138/6/202431.05589
128/6/2024 3:4031.040688/6/202431.03492
138/6/2024 3:4031.031158/6/202431.04041
148/6/2024 3:4031.043248/6/202431.03543
158/6/2024 3:4131.038598/6/202431.03909
168/6/2024 3:4131.038598/6/202431.02741
178/6/2024 3:4131.055898/6/202431.03388
188/6/2024 3:4231.034928/6/202431.01957
198/6/2024 3:4231.040418/6/202431.02724
208/6/2024 3:4231.035438/6/202431.01078
218/6/2024 3:4331.039098/6/202431.01987
228/6/2024 3:4331.030478/6/202431.01926
238/6/2024 3:4331.027418/6/202431.0225
248/6/2024 3:4431.033888/6/202431.00755
258/6/2024 3:4431.019578/6/202431.02013
268/6/2024 3:4431.027248/6/202431.01206
278/6/2024 3:4531.014528/6/202431.01438
288/6/2024 3:4531.010788/6/202431.01001
298/6/2024 3:4531.019878/6/202431.01065
308/6/2024 3:4631.019268/6/202431.00156
318/6/2024 3:4631.02258/6/202431.00997
328/6/2024 3:4631.010828/6/202430.99506
338/6/2024 3:4731.007558/6/202431.00593
348/6/2024 3:4731.020138/6/202430.99819
358/6/2024 3:4731.012068/6/202430.99876
368/6/2024 3:4831.014388/6/202430.98216
378/6/2024 3:4831.010018/6/202430.99371
388/6/2024 3:4831.010658/6/202430.97564
398/6/2024 3:4931.001568/6/202430.98277
408/6/2024 3:4931.009978/6/202430.97159
418/6/2024 3:4930.995068/6/202430.98675
428/6/2024 3:5030.996238/6/202430.96674
438/6/2024 3:5031.005938/6/202430.99004
448/6/2024 3:5030.998198/6/202430.96159
458/6/2024 3:5130.998768/6/202430.96688
468/6/2024 3:5130.987928/6/202430.94682
478/6/2024 3:5130.982168/6/202430.97799
488/6/2024 3:5230.993718/6/202430.94965
498/6/2024 3:5230.993148/6/202430.95186
508/6/2024 3:5230.979518/6/202430.95022
518/6/2024 3:5330.975648/6/202430.95964
528/6/2024 3:5330.982778/6/202430.93978
538/6/2024 3:5330.971598/6/202430.95772
548/6/2024 3:5430.986758/6/202430.93355
558/6/2024 3:5430.966748/6/202430.93931
568/6/2024 3:5430.967628/6/202430.92123
578/6/2024 3:5530.980158/6/202430.96075
588/6/2024 3:5530.98218/6/202430.91746
598/6/2024 3:5530.990048/6/202430.93015
608/6/2024 3:5630.961598/6/202430.91166
618/6/2024 3:5630.964328/6/202430.93028
628/6/2024 3:5630.966888/6/202430.91446
638/6/2024 3:5730.960348/6/202430.92745
648/6/2024 3:5730.960118/6/202430.90426
658/6/2024 3:5730.95948/6/202430.91766
668/6/2024 3:5830.946828/6/202430.90527
678/6/2024 3:5830.977998/6/202430.92301
688/6/2024 3:5830.952778/6/202430.88867
698/6/2024 3:5930.949658/6/202430.89015
708/6/2024 3:5930.951868/6/202430.8811
718/6/2024 3:5930.950228/6/202430.88467
728/6/2024 4:0030.959648/6/202430.87811
738/6/2024 4:0030.951328/6/202430.89527
748/6/2024 4:0030.943958/6/202430.88319
758/6/2024 4:0130.939788/6/202430.88412
768/6/2024 4:0130.957728/6/202430.85747
778/6/2024 4:0130.942168/6/202430.86164
788/6/2024 4:0230.933558/6/202430.85271
798/6/2024 4:0230.939318/6/202430.87275
808/6/2024 4:0230.926288/6/202430.85036
818/6/2024 4:0330.921238/6/202430.85406
828/6/2024 4:0330.960758/6/202430.83537
838/6/2024 4:0330.925588/6/202430.84558
848/6/2024 4:0430.917468/6/202430.82474
858/6/2024 4:0430.91838/6/202430.84042
868/6/2024 4:0430.929728/6/202430.81861
878/6/2024 4:0530.930158/6/202430.82443
888/6/2024 4:0530.924638/6/202430.81642
898/6/2024 4:0530.911668/6/202430.81844
908/6/2024 4:0630.926488/6/202430.81353
918/6/2024 4:0630.930288/6/202430.81615
928/6/2024 4:0630.920328/6/202430.81296
938/6/2024 4:0730.914468/6/202430.81998
948/6/2024 4:0730.927458/6/202430.80047
958/6/2024 4:0730.904268/6/202430.81046
968/6/2024 4:0830.917668/6/202430.79834
978/6/2024 4:0830.905278/6/202430.80417
988/6/2024 4:0830.923018/6/202430.79393
998/6/2024 4:0930.904978/6/202430.80646
1008/6/2024 4:0930.904268/6/202430.78588
1018/6/2024 4:0930.888678/6/202430.79915
1028/6/2024 4:1030.890158/6/202430.78781
1038/6/2024 4:1030.882038/6/202430.78983
1048/6/2024 4:1030.88118/6/202430.7741
1058/6/2024 4:1130.882258/6/202430.79221
1068/6/2024 4:1130.884678/6/202430.78006
1078/6/2024 4:1130.878118/6/202430.78828
1088/6/2024 4:1230.887978/6/202430.77188
1098/6/2024 4:1230.895278/6/202430.78175
1108/6/2024 4:1230.883528/6/202430.77427
1118/6/2024 4:1330.883198/6/202430.77605
1128/6/2024 4:1330.884128/6/202430.76811
1138/6/2024 4:1330.873258/6/202430.78259
1148/6/2024 4:1430.871578/6/202430.76408
1158/6/2024 4:1430.868478/6/202430.76754
1168/6/2024 4:1430.862928/6/202430.75787
1178/6/2024 4:1530.857478/6/202430.76946
1188/6/2024 4:1530.861648/6/202430.75623
1198/6/2024 4:1530.852718/6/202430.76347
1208/6/2024 4:1630.862178/6/202430.74711
1218/6/2024 4:1630.872758/6/202430.76599
1228/6/2024 4:1630.868038/6/202430.75555
1238/6/2024 4:1730.850368/6/202430.76397
1248/6/2024 4:1730.854068/6/202430.74612
1258/6/2024 4:1730.852588/6/202430.7543
1268/6/2024 4:1830.84738/6/202430.73549
1278/6/2024 4:1830.845148/6/202430.75667
1288/6/2024 4:1830.841548/6/202430.72603
1298/6/2024 4:1930.835378/6/202430.73747
1308/6/2024 4:1930.837368/6/202430.72485
1318/6/2024 4:1930.845588/6/202430.73828
1328/6/2024 4:2030.836828/6/202430.72128
1338/6/2024 4:2030.829488/6/202430.73623
1348/6/2024 4:2030.828278/6/202430.70586
1358/6/2024 4:2130.824748/6/202430.73131
1368/6/2024 4:2130.840428/6/202430.71815
1378/6/2024 4:2130.830898/6/202430.71986
1388/6/2024 4:2230.818618/6/202430.69923
1398/6/2024 4:2230.824438/6/202430.70101
1408/6/2024 4:2230.823768/6/202430.69519
1418/6/2024 4:2330.816428/6/202430.69896
1428/6/2024 4:2330.818448/6/202430.6829
1438/6/2024 4:2330.813538/6/202430.68946
1448/6/2024 4:2430.816158/6/202430.68586
1458/6/2024 4:2430.812968/6/202430.70125
1468/6/2024 4:2430.819988/6/202430.69287
1478/6/2024 4:2530.81388/6/202430.70374
MIXING_TANK_20240903_161725(612
Cell Formulas
RangeFormula
C2:D75003C2=LET(d,A2:B455000,top,TAKE(d,1),btm,TAKE(d,-1),mid,DROP(DROP(d,-1),1),crit1,DROP(d,2),crit2,DROP(d,-2),ctot,MAP(INDEX(mid,,2),INDEX(crit1,,2),INDEX(crit2,,2),LAMBDA(a,b,c,OR(AND(b<a,c<a),AND(b>a,c>a)))),out,VSTACK(top,FILTER(mid,ctot),btm),out)
Dynamic array formulas.
 
Upvote 0
How are those wrong? The value in row 4 is lower than that in row 3 and in row 5 so it's a low. Row 5 is higher than row 4 and row 6 so that's a high. Are there other criteria you haven't mentioned?
 
Upvote 0
So there are roughly 125000 datapoints in my sheet. I am looking for the bottom and top of the big hills, not every little move.
The data i shared is only a little of the graph as again XL22 wont let me share more.
so out of all the noise on 8-6, i only want one data point for the low
then i know there is a high so i want that point as well.
this trend will then continue forever really where it will come down slow then go up and down slow then up.
1725486439079.png

1725486396246.png

1725486232795.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
197
sanrama
S
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
A
  • Solved
Number of Weeks Between First and Last of a List of Dates
Replies
7
Views
195
hagia_sofia
hagia_sofia
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E
L
  • Solved
Calculate start time from end time with multiple times per day for each ID
2
Replies
10
Views
274
Fluff
Fluff

Forum statistics

Threads
1,223,884
Messages
6,175,171
Members
452,615
Latest member
bogeys2birdies

Share this page

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