Finding zero crossings of sine wave and its maximas

Hi,

This should do for the first part

Please Login or Register  to view this content.

And this will do both

Please Login or Register  to view this content.

You should paste this in to a module

Press alt + F11 --> then right click Project Window --> Press insert module in the menu that appears

Afterwards you run the code by clicking anywhere in the code and pressing F5

Ahh, minor error in the values used in the calculation.

Try this

Please Login or Register  to view this content.

Your welcome.

Remember to mark the thread as solved if the answer given solved your question.

In D2, put the CSE formula
=SMALL(IF(SIGN($B$2:$B$6175)<>SIGN($B$3:$B$6176),($A$2:$A$6175+$A$3:$A$6176)/2), ROW(A2))
and then drag down.

In H2, put the CSE formula
=SMALL(IF(7900<$B$2:$B$6175, $A$2:$A$6175), ROW(A1))
in I2, the formula =VLOOKUP(H2, A:B, 2, False)
and drag down.

Your test result missed
3.0297, 7910.16 (row 136)
3.0298, 7939.45 (row 137)
3.0299, 7949.22 (row 138)
3.0300, 7968.75 (row 139)

That's what the formula does.
After I get off work, I post a formula that returns the relative maxima. (your data looks pretty smooth, i.e. noise in the data shouldn't create false maxima)
Do you want the minima as well?

@ Mike

I think you might be double counting, when U (Column B) is zero, (i.e. an exact cross-over zero) See the graph ... 35 cross-overs.
Also there might be an issue with simply splitting the difference of the points +/- 0.
Some cross-overs seem to be biased to either the upper or lower value.

P.S.
I've taken the easy way and used helpers (Columns E:F)

My method (and the more extensive below) might catch the "zero" at the ends of the data.
Spike is reminding me its time to take a walk.

This is an interesting problem. Thank you for bringing it here.

Given times in column A and voltages in column B.
We seek the times at which the voltage is 0.
We seek the relative maxima of the voltages and also the times at which these maxima occur.

There are two different scenarios.
The Data Only scenario is where only actual data point are returned.
The Interpolation scenario allows for intermediate, interpolated values to be returned.

First, define some names
Name: tVals0 RefersTo: =Sheet1!$A$2:$A$6173
Name: tVals1 RefersTo: =Sheet1!$A$3:$A$6174
Name: tVals2 RefersTo: =Sheet1!$A$4:$A$6175

Name: vVals0 RefersTo: =Sheet1!$B$2:$B$6173
Name: vVals1 RefersTo: =Sheet1!$B$3:$B$6174
Name: vVals2 RefersTo: =Sheet1!$B$4:$B$6175

These names are all arrays, but one can think of tVals0 as the time of interest, tVals1 as the value in the cell below, and tVals2 the cell below that

vVals0, vVals1 and vVals2 are the corresponding voltage value arrays.

We seek the times at which the voltage is 0.

If Sign(vVals0) <> Sign(vVals1) then the votage has crossed 0.

Data Only Scenarios:

=IF(Sign(vVals0) <> Sign(vVals1), tVals0, FALSE)
will be the time immediately before the voltage crosses 0.
1 Sec, 2Volts
2 Sec, -1Volt
will return 1 Sec

=IF(Sign(vVals0) <> Sign(vVals1), IF(ABS(vVals0)<ABS(vVals1), tVals0, tVals1), FALSE)
will return the time matching the smaller of the two voltage.
1 Sec, 2Volts
2 Sec, -1Volt
will return 2 Sec

Interpolated Scenarios:

=IF(Sign(vVals0) <> Sign(vVals1), (tVals0 + tVals1)/2, FALSE)returns the median value as in the OP.
1 Sec, 2Volts
2 Sec, -1Volt
will return 1.5 Sec

=IF(SIGN(vVals0)<>SIGN(vVals1), tVals0+((tVals1-tVals0)*(0-vVals0)/(vVals1-vVals0)), FALSE)
returns the interpolated time at which the voltage was 0.
1 Sec, 2Volts
2 Sec, -1Volt
returns 1.6666667 Sec

Using the last interpolation formula, it still assumes that neither end-point has 0 voltage, to account for that
=IF(vVals0=0, tVals0, IF(vVals1=0, FALSE, IF(SIGN(vVals0)<>SIGN(vVals1), tVals0+((tVals1-tVals0)*(0-vVals0)/(vVals1-vVals0)),FALSE)))

We need to remember that tVals0 etc are arrays, so the result of that formula is an array.
Each element of that array will be either a time when V=0 or FALSE.

Lets Name that array, ArrayOfZeroTimes.
=SMALL(ArrayOfZeroTimes, 1) is the first time that the voltage was 0
=SMALL(ArrayOfZeroTimes, 2) is the second time that the voltage was 0

Putting =SMALL(ArrayOfZeroTimes, ROW(A1)) in a cell and dragging down will list all the times at which the voltage was 0.

Please Login or Register  to view this content.

For this problem, we consider tVals1 and vVals1 to be the values of interest.

Extrema occur when the slope of a graph changes sign,
when SIGN(vVals1-vVals0)<>SIGN(vVals2-vVals1)

=IF(SIGN(vVals1-vVals0)<>SIGN(vVals2-vVals1), vVals1, False)
will be an array of the extreme voltages

And the corresponding array of times is
=IF(SIGN(vVals1-vVals0)<>SIGN(vVals2-vVals1), tVals1, False)

The above will find extrema. To return an array of maxima
=IF(SIGN(vVals1-vVals0)>SIGN(vVals2-vVals1), vVals1, False)

Neighboring voltages in the data set that are equal (slope=0) are a problem. One can either:
1) write a VBA routine.

2) use a formula from above. Which will generate false extrema, e.g. given 1 2 3 3 4 5, it will find two extreema at 3, even though its an inflection point.

3) use a formula, like
=IF(OR(vVals1=vVals0,vVals2=vVals1),FALSE,IF(SIGN(vVals1-vVals0)>SIGN(vVals2-vVals1), vVals1, FALSE))
which ignores that data, which misses maxima like 1 2 3 3 2 1

Using the formulas that give false positives,
Name: ArrayOfExtremeVoltages
RefersTo: =IF(SIGN(vVals1-vVals0)<>SIGN(vVals2-vVals1), vVals1, False)

Name: TimesOfExtremeVoltages
RefersTo: =IF(SIGN(vVals1-vVals0)<>SIGN(vVals2-vVals1), tVals1, False)

As with finding the Zeros, SMALL is used to extract the values from those arrays

Note that =SMALL(ArrayOfExtremeVoltages, ROW(A1)) will return the list of maxima out of order.

Putting =SMALL(TimesOfExtremeVoltages, ROW(A1)) will.

That approach is returning too many extrema, its too sensitive.
I must ponder.

Try this ...
Take a sampling of the max/ min over say 11 cells, I've left a couple of graphs to illustrate this in Sheet2
With Sheet2
In C7

Please Login or Register  to view this content.

In D7

Please Login or Register  to view this content.

Drag/Fill both down

This will find all the peaks/troughs in your data, ironing out smaller spikes.
In several cases there are multiple max/min matches in one wave so the curve isn't that clean.

I have used a crude C/F to highlight these cases.

Columns C:D can be hidden, and the row numbers in the result tables can be integrated in the INDEX() & MATCH() formulae if you don't need them.

Crossing zero would be best with a formula y=sin(x)+k, but the curve appers to be decaying making this tricky, see if the method I used earlier is accurate enough for your purposes. (Sheet1)

I'm working on an approach that
1)finds the zeros
2)divides column of voltages at those zero values
3)finds the max/min of each of the sub-ranges.

Right now, its pretty messy with several helper columns. Working ....

Let's assume that between each Max and the next Min, the voltage crosses 0.

In the attached, Sheet3 has test data, t in column A and Sin(t) in column B.

In D2 (and dragged down), is the masking formula
=IF($B2=0, 1, IF($B3=0,FALSE,IF(SIGN($B2)<>SIGN($B3),1,FALSE)))
which returns 1 if column B crosses 0 and FALSE otherwise

In F2 (and dragged down), =MATCH(2,$D$1:$D2) will return the last row above where the mask = 1
G2's formula MATCH(1, $D3:$D$65535, 0)+ROW($D2) returns the next row where mask = 1

The Formula INDEX($B:$B,$F2,1):INDEX($B:$B,$G2,1) is the range between those two rows.
and columns I and H have the MAX and MIN of that range.

Column D has the mask, 1 when the graph of B crosses 0, False other-wise
Let's replace each of the 1's in that array with the extreme value that is in the range (bounded by 0crossings) that begins on that row.

Which extreme, Max or Min can be determined by looking at Bn, if Bn<0 then B is crossing from negative to poititive and is heading towards a maxima.

Thus the formula in L2, =IF($D2, IF($B2<0, $I2, $J2)) will return FALSE if D is False and the extreme value otherwise.

So column L lists all of the extreems from column B, interspersed with FALSE's.
To remove the FALSE, we could use the formula =SMALL(L:L, ROWS($1:1)) dragged down, which would return the extrema sorted ascending, but that would not be the order in which they occur in column B.

To keep it in the same order as the data, we the mask to create an array of row numbers
IF($D$2:$D$31,ROW($D$2:$D$31))
sort that array with
SMALL(IF($D$2:$D$31,ROW($D$2:$D$31)), ROWS($1:1))

and use Lookup (in column N), to return the extrema

=LOOKUP(SMALL(IF($D$2:$D$31,ROW($D$2:$D$31)), ROWS($1:1)), IF($D$2:$D$31,ROW($D$2:$D$31)), $L$2:$L$31)

Note that N alternates between a maximum and a minimum. By controling the second argument of SMALL, we can return only the Maxima or only the Minima.
=LOOKUP( SMALL( IF($D$2:$D$31,ROW($D$2:$D$31)), 2*ROWS($1:1)-($A$2<0)), IF($D$2:$D$31,ROW($D$2:$D$31)), $L$2:$L$31 )
The ($A$2<0) finds out if the first extreme value is a maximum or a minimum.

----
Sheet1 of the attached workbook has the data from the OP and named formulas have replaced some of the helper columns.
(Note the relative referencing, a cell in row 2 should be active when defining these names)

Name: zeroAbove RefersTo: =MATCH(2,INDEX(maskColumn,1,1):Sheet1!$D2)
Name: zeroBelow RefersTo: =MATCH(1, Sheet1!$D3:$D$65536, 0)+ROW(Sheet1!$D2)

Name: CurrentRange
RefersTo: =INDEX(vColumn,zeroBelow,1):INDEX(vColumn,zeroAbove,1)

The formula for the big array of extremes (Sheet1 column F) is
=IF($D2,IF($B2<0,MAX(CurrentRange),MIN(CurrentRange)))

There is also a name created for the masked row numbers
Name: zeroRowNumbers RefersTo: =IF(maskRange,ROW(maskRange))

and dynamic named ranges that adjust as data rows are added or removed.
Name: vColumn RefersTo: =Sheet1!$B:$B
Name: vRange RefersTo: =Sheet1!$B$2:INDEX(vColumn,MATCH(9.9E+305,vColumn),1)

Name: extremeColumn RefersTo: =Sheet1!$F:$F
Name: extremeRange RefersTo: =Sheet1!$F$2:INDEX(extremeColumn,ROWS(vRange)+ROW(Sheet1!$F$2)-1,1)

Name: maskColumn RefersTo: =Sheet1!$D:$D
Name: maskRange RefersTo: =Sheet1!$D$2:INDEX(maskColumn,ROWS(vRange)+ROW(Sheet1!$D$2)-1,1)

The native excel version above piles array formulas on array formulas and slows things to a crawl.

This is a UDF based solution.
=ArrayOfZeros(A:A, B:B, 1, FALSE) returns an array of the times at which the voltage crosses 0
=ArrayOfZeros(A:A, B:B, 1, TRUE) returns an array of the voltages at those time.

The returnType argument (the third argument) determines which time is counted.

returnType = -1 (default) returns the time before the voltage crosses 0
returnType = 0 returns an interpolated time when the voltage is 0
returnType = 1 returns the time closest to when the voltage is 0
returnType = any other positive number returns the time immediatly after the voltage crosses 0.

=ArrayOfExtrems(A:A, B:B, 0, TRUE) returns the times when the voltage is at an extreme
=ArrayOfExtrems(A:A, B:B, 0, FALSE) returns the (semi-local) extremes of the voltages

If the returnType=-1 then only minima are returned, if returnType = 1, maxima are returned.

These are array functions that should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)