Getting max/min values in wave data

[TOKYOJ1]

Hi,

I have a set of data that is in wave format going from positive and negative numbers and positive numbers again.... (which I have marked "X")
How do I get excel to identify these points between highest positives and lowest negatives?

https://www.dropbox.com/sh/2bms6secmr3b8oc/AABPQl-EIuP8l0ZzvrnrYLT9a?dl=0


Thanks in advance
TJ


[TABLE="width: 269"]
<tbody>[TR]
[TD]3/4/1925 12:00[/TD]
[TD="align: right"]3.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/4/1925 12:00[/TD]
[TD="align: right"]3.09[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/4/1925 12:00[/TD]
[TD="align: right"]3.14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/4/1925 12:00[/TD]
[TD="align: right"]3.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/4/1925 12:00[/TD]
[TD="align: right"]3.19[/TD]
[TD] X[/TD]
[/TR]
[TR]
[TD]8/4/1925 12:00[/TD]
[TD="align: right"]3.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/4/1925 12:00[/TD]
[TD="align: right"]3.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/4/1925 12:00[/TD]
[TD="align: right"]3.08[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/4/1925 12:00[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/4/1925 12:00[/TD]
[TD="align: right"]2.89[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/4/1925 12:00[/TD]
[TD="align: right"]2.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/4/1925 12:00[/TD]
[TD="align: right"]2.59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/4/1925 12:00[/TD]
[TD="align: right"]2.41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/4/1925 12:00[/TD]
[TD="align: right"]2.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/4/1925 12:00[/TD]
[TD="align: right"]1.98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/4/1925 12:00[/TD]
[TD="align: right"]1.74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/4/1925 12:00[/TD]
[TD="align: right"]1.48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/4/1925 12:00[/TD]
[TD="align: right"]1.21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21/4/1925 12:00[/TD]
[TD="align: right"]0.94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22/4/1925 12:00[/TD]
[TD="align: right"]0.66[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/4/1925 12:00[/TD]
[TD="align: right"]0.37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24/4/1925 12:00[/TD]
[TD="align: right"]0.09[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25/4/1925 12:00[/TD]
[TD="align: right"]-0.19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/4/1925 12:00[/TD]
[TD="align: right"]-0.47[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27/4/1925 12:00[/TD]
[TD="align: right"]-0.73[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/4/1925 12:00[/TD]
[TD="align: right"]-0.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29/4/1925 12:00[/TD]
[TD="align: right"]-1.24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/4/1925 12:00[/TD]
[TD="align: right"]-1.48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/5/1925 12:00[/TD]
[TD="align: right"]-1.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/5/1925 12:00[/TD]
[TD="align: right"]-1.91[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/5/1925 12:00[/TD]
[TD="align: right"]-2.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/5/1925 12:00[/TD]
[TD="align: right"]-2.28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/5/1925 12:00[/TD]
[TD="align: right"]-2.45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/5/1925 12:00[/TD]
[TD="align: right"]-2.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/5/1925 12:00[/TD]
[TD="align: right"]-2.74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/5/1925 12:00[/TD]
[TD="align: right"]-2.86[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/5/1925 12:00[/TD]
[TD="align: right"]-2.96[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/5/1925 12:00[/TD]
[TD="align: right"]-3.06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/5/1925 12:00[/TD]
[TD="align: right"]-3.13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/5/1925 12:00[/TD]
[TD="align: right"]-3.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/5/1925 12:00[/TD]
[TD="align: right"]-3.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/5/1925 12:00[/TD]
[TD="align: right"]-3.29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/5/1925 12:00[/TD]
[TD="align: right"]-3.31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/5/1925 12:00[/TD]
[TD="align: right"]-3.32[/TD]
[TD] X[/TD]
[/TR]
[TR]
[TD]17/5/1925 12:00[/TD]
[TD="align: right"]-3.32[/TD]
[TD] X[/TD]
[/TR]
[TR]
[TD]18/5/1925 12:00[/TD]
[TD="align: right"]-3.31[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[TOKYOJ1]

I wish to add it is sufficient to mark "X" in third column

 

[sandy666]

will be ok this way?


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]DT[/td][td=bgcolor:#70AD47]Value[/td][td=bgcolor:#70AD47]Max/Min[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

03/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

3.01​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

04/04/1925 12:00​

[/td][td]

3.09​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

05/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

3.14​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

06/04/1925 12:00​

[/td][td]

3.18​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

07/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

3.19​

[/td][td=bgcolor:#E2EFDA]

3.19​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

08/04/1925 12:00​

[/td][td]

3.18​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

09/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

3.15​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/04/1925 12:00​

[/td][td]

3.08​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

11/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

12/04/1925 12:00​

[/td][td]

2.89​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

13/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

2.75​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

14/04/1925 12:00​

[/td][td]

2.59​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

15/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

2.41​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

16/04/1925 12:00​

[/td][td]

2.2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

17/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

1.98​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

18/04/1925 12:00​

[/td][td]

1.74​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

19/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

1.48​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20/04/1925 12:00​

[/td][td]

1.21​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

21/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

0.94​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

22/04/1925 12:00​

[/td][td]

0.66​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

23/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

0.37​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

24/04/1925 12:00​

[/td][td]

0.09​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

25/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-0.19​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

26/04/1925 12:00​

[/td][td]

-0.47​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

27/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-0.73​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28/04/1925 12:00​

[/td][td]

-0.99​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

29/04/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-1.24​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30/04/1925 12:00​

[/td][td]

-1.48​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

01/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-1.7​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/05/1925 12:00​

[/td][td]

-1.91​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

03/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-2.1​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

04/05/1925 12:00​

[/td][td]

-2.28​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

05/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-2.45​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

06/05/1925 12:00​

[/td][td]

-2.6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

07/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-2.74​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

08/05/1925 12:00​

[/td][td]

-2.86​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

09/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-2.96​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10/05/1925 12:00​

[/td][td]

-3.06​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

11/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-3.13​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

12/05/1925 12:00​

[/td][td]

-3.2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

13/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-3.25​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

14/05/1925 12:00​

[/td][td]

-3.29​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

15/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-3.31​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

16/05/1925 12:00​

[/td][td]

-3.32​

[/td][td]

-3.32​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

17/05/1925 12:00​

[/td][td=bgcolor:#E2EFDA]

-3.32​

[/td][td=bgcolor:#E2EFDA]

-3.32​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

18/05/1925 12:00​

[/td][td]

-3.31​

[/td][td][/td][/tr]
[/table]

 

[TOKYOJ1]

Yes, this is ok

 

[sandy666]

One more question: are you able to use PowerQuery? (PC Excel 2010/2013 add-in, 2016 and higher - built-in)

 

[TOKYOJ1]

Sorry I had to google it as I am not familiar with Power Query.

However I managed to load it into my worksheet and table comes out in shades of white/light green as what you have pasted above.
I am guessing I am able to use Power Query afterall

 

[sandy666]

there always comes the first time

I think the best way will be if I give you excel file: s666-PQ-Getting max-min values in wave data.xlsm

Let me know if you've any questions

 

[TOKYOJ1]

It feels like learning to swim in 12ft water.....and drowning

Is it possible to load a new data source and use back your query rules?

 

[sandy666]

try to load new data in place of old data, then use Ctrl+Alt+F5

edit:
don't delete anything, just paste new data

 

[sandy666]

here are steps:

Load your source table into PowerQuery Editor
Duplicate it twice
Change name of first duplicate to Max
Select Column2 and from the ribbon Transform - Statistics - select Maximum
From the ribbon select convert to table
Change second duplicate to Min
Select Column2 and from the ribbon Transform - Statistics - select Minimum
From the ribbon select convert to table
Select Table1 and from the ribbon Home - Merge queries
select Table1 and select Column2
then
select Max table and select Max column
OK
you will get Merged table
Do that again
Merge Merged table with Min table (appropriate columns must be selected)
OK
Select two last columns Max and Min then from the ribbon Transform select Merge Columns

then use close and load ... as connection
choose the place eg. D1
from the Queries right click on the last merged table choose Load to

I hope I have not forgotten anything

or I can prepare movie