Average Of Local Maximums

[sci'01]

I have a series of data that make up number of waves (it is time series data). What to do it take the maximum and minimum values of each wave (ie local max/min) and calculate and average max and min for the data set.

Any help would be much appreciated

[eliW]

On 2002-05-21 09:09, sci'01 wrote:
I have a series of data that make up number of waves (it is time series data). What to do it take the maximum and minimum values of each wave (ie local max/min) and calculate and average max and min for the data set.

Any help would be much appreciated

Hi,
Try this sample by entering the formulas shown in D3 and E3 into B3 and C3 and copy down. Hope that it make sense.
Eli

Microsoft Excel - LocalMaxMin.xls_______________Running: xl97 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
E1		=	MAX
	A	B	C	D	E
1	DATA	MIN	MAX	MIN	MAX
2	2
3	1	:alert("=IF(AND(SIGN(A4-A3)%3E=0,SIGN(A3-A2)%3C0),A3,"")")>	:alert("=IF(AND(SIGN(A4-A3)%3C0,SIGN(A3-A2)%3E=0),A3,"")")>	IF(AND(SIGN(A4-A3)>=0,SIGN(A3-A2)<0),A3,"")	IF(AND(SIGN(A4-A3)<0,SIGN(A3-A2)>=0),A3,"")
4	0	:alert("=IF(AND(SIGN(A5-A4)%3E=0,SIGN(A4-A3)%3C0),A4,"")")>	:alert("=IF(AND(SIGN(A5-A4)%3C0,SIGN(A4-A3)%3E=0),A4,"")")>	IF(AND(SIGN(A5-A4)>=0,SIGN(A4-A3)<0),A4,"")	IF(AND(SIGN(A5-A4)<0,SIGN(A4-A3)>=0),A4,"")
5	-1	:alert("=IF(AND(SIGN(A6-A5)%3E=0,SIGN(A5-A4)%3C0),A5,"")")>-1	:alert("=IF(AND(SIGN(A6-A5)%3C0,SIGN(A5-A4)%3E=0),A5,"")")>	etc	etc
6	4	:alert("=IF(AND(SIGN(A7-A6)%3E=0,SIGN(A6-A5)%3C0),A6,"")")>	:alert("=IF(AND(SIGN(A7-A6)%3C0,SIGN(A6-A5)%3E=0),A6,"")")>	etc	etc
7	6	:alert("=IF(AND(SIGN(A8-A7)%3E=0,SIGN(A7-A6)%3C0),A7,"")")>	:alert("=IF(AND(SIGN(A8-A7)%3C0,SIGN(A7-A6)%3E=0),A7,"")")>6	etc	etc
8	4	:alert("=IF(AND(SIGN(A9-A8)%3E=0,SIGN(A8-A7)%3C0),A8,"")")>4	:alert("=IF(AND(SIGN(A9-A8)%3C0,SIGN(A8-A7)%3E=0),A8,"")")>	etc	etc
9	9	:alert("=IF(AND(SIGN(A10-A9)%3E=0,SIGN(A9-A8)%3C0),A9,"")")>	:alert("=IF(AND(SIGN(A10-A9)%3C0,SIGN(A9-A8)%3E=0),A9,"")")>	etc	etc
10	16	:alert("=IF(AND(SIGN(A11-A10)%3E=0,SIGN(A10-A9)%3C0),A10,"")")>	:alert("=IF(AND(SIGN(A11-A10)%3C0,SIGN(A10-A9)%3E=0),A10,"")")>16	etc	etc
11	13	:alert("=IF(AND(SIGN(A12-A11)%3E=0,SIGN(A11-A10)%3C0),A11,"")")>	:alert("=IF(AND(SIGN(A12-A11)%3C0,SIGN(A11-A10)%3E=0),A11,"")")>	etc	etc
12	7	:alert("=IF(AND(SIGN(A13-A12)%3E=0,SIGN(A12-A11)%3C0),A12,"")")>	:alert("=IF(AND(SIGN(A13-A12)%3C0,SIGN(A12-A11)%3E=0),A12,"")")>	etc	etc
13	4	:alert("=IF(AND(SIGN(A14-A13)%3E=0,SIGN(A13-A12)%3C0),A13,"")")>	:alert("=IF(AND(SIGN(A14-A13)%3C0,SIGN(A13-A12)%3E=0),A13,"")")>	etc	etc
14	3	:alert("=IF(AND(SIGN(A15-A14)%3E=0,SIGN(A14-A13)%3C0),A14,"")")>3	:alert("=IF(AND(SIGN(A15-A14)%3C0,SIGN(A14-A13)%3E=0),A14,"")")>	etc	etc
15	5	:alert("=IF(AND(SIGN(A16-A15)%3E=0,SIGN(A15-A14)%3C0),A15,"")")>	:alert("=IF(AND(SIGN(A16-A15)%3C0,SIGN(A15-A14)%3E=0),A15,"")")>	etc	etc
16	5	:alert("=IF(AND(SIGN(A17-A16)%3E=0,SIGN(A16-A15)%3C0),A16,"")")>	:alert("=IF(AND(SIGN(A17-A16)%3C0,SIGN(A16-A15)%3E=0),A16,"")")>	etc	etc
17	8	:alert("=IF(AND(SIGN(A18-A17)%3E=0,SIGN(A17-A16)%3C0),A17,"")")>	:alert("=IF(AND(SIGN(A18-A17)%3C0,SIGN(A17-A16)%3E=0),A17,"")")>8	etc	etc
18
19	AVG	:alert("=AVERAGE(B2:B17)")>2	:alert("=AVERAGE(C2:C17)")>10
20		MIN	MAX
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

Eli


[ This Message was edited by: eliW on 2002-05-21 14:58 ]

[eliW]

Hi all Colo's AddIns users!
Why my formulas are not appearing in the cells in my sample?
How can I fix it?
Eli

[Mark O'Brien]

What? You mean like your "IF(AND(SIGN..." type formula?

If you hover the mouse pointer over the value that is the result of the formula, you should see the full formula in the status bar at the bottom of your browser. (I'm usinf IE though, so I don't know about any other browser)

[sci'01]

Eli,

Thanks for the solution!

I works nicely however it doesn’t work for what I need it for. I realized this is because my explanation was perhaps not specific enough.

The problem arises when a single wave has a double peak. The data I am using has a few hundred data points per wave and is noisy. I was thinking of doing some sort threshold value to distinguish waves. For example if the wave oscillates +/- 10, the limit values could be +5 for max and –5 for min. The wave would have to pass through the threshold values to count as a max or min. If the wave started at 0 peaked at 9 reduced to 7 and then peak again at 9.5 the max value would be 9.5 If the wave then reduced to a value of –3 but when back up above +5 no min value would be recorded. Hope this explains my problem better

If you any thoughts on how to do they would be much appreciated.

Cheers