# macro to determine points of inflection

#### bisetti

##### Board Regular
is there a way to use vb to write a macro that will determine the points of inflection in a given set of data? for example to find all the peaks and vallies in a sine wave?

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Damon Ostrander

##### MrExcel MVP
Hi bisetti,

The answer is yes, but how you would write this code depends on the functionality you want. For example

- do you want to interpolate between data points to estimate the true inflection points (which might be between data values), or do you just want the data values that are the local min and max values?

- what do you want the macro or function to do when it finds these points? Write them to a block of cells, or perhaps highlight them with colors in the original data set?

Damon

#### bisetti

##### Board Regular
Hi Damon,

I have lots of data and many files that I want to run this macro on. The data shows voltage and the overall shape is a sin wave, but its not completly smooth. I think by taking the absolute min and max, it might result in something that is not the true peak or valley i want. I can look at the data and very easily tell where the points are, but since they are not so smooth, i don't know if this will make it difficult for excel to determine. What I would like it to do after finding these points is to take the corresponding y values (which in this case y is time) and have the macro tell me the time between the two points by writing it in a cell.

#### Damon Ostrander

##### MrExcel MVP
Hi again bisetti,

Yes, if the data contain either noise or high-frequency components that you want to ignore (as you describe), then it is not just a straightforward matter of finding local max and min values. The algorithm would either have to do some local smoothing, filtering, or even curve fitting. If the signal is totally continuous with no transient part, I would recommend Fourier analysis which would give you the frequencies of the various harmonics, and from what you describe I believe you want the period (1/frequency) of the lowest harmonic (the fundamental frequency). Excel has a Fourier analysis tool (Excel mistakenly groups it in its Statistical tool set) that you could use for this.

But if the function is transient or has a significant transient component you should use smoothing, such as a sliding fit on a number of points that is small compared to the period of the fundamental frequency, looking for inflections in the fit function that are within the range of the sliding fit.

I hope this helps.

Damon

#### bisetti

##### Board Regular

I will check out the analysis tool. I think from what you describe it might be what I need.

Thanks again very much.

#### bisetti

##### Board Regular
One last question - how do I get to the tool set?

Thanks

#### Damon Ostrander

##### MrExcel MVP
Hi again bisetti,

You first have to have the Analysis Toolpack Add-ins loaded (Tools > Addins). Then you can access the Fourier Analysis Tool from Tools > Data Analysis > Fourier Analysis.

#### bisetti

##### Board Regular
Awesome! Thanks so much!!!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,018
Messages
5,834,976
Members
430,331
Latest member
Syed Yasir Hannan

### 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.

### Which adblocker are you using?

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

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