Tuesday, May 30, 2006

Sounds Easy

this morning i figured out, sorta, how to get excel to pick out multiple maxima on a graph. it sounds pretty easy and i am sure there is an easier way to do it, but it was interesting to learn a few more things in excel.
first: i took the first derivative of the data. this is the change in y-value over the change in x-value. technically with a continuous function the value of 1st derivative at the peak is zero (it is also zero at the minima, ie troughs between peaks). my data is discrete so i don't usually hit the zero mark, but there is a change in sign of the first derivative.
second: i wrapped up a way to spit out if i was at a regular data point (false) or a minima or maxima (true). check for the sign of the current value of derivative to be different than the sign of the last value using SIGN using NOT. so if they are the same sign it spits out FALSE and if they are differnt signs then TRUE. added in an AND to make sure i only got the maxima by requiring the value of the current to be less than the previous (on the way down). and added a couple more and statements to adjust for noise in the signal and a threshold for peaks.
third: i used this web page for the basics of picking out values where there was a true and then just manually incremented for my max 5 peaks per graph that i needed.

1 comment:

k2h said...

if you got vlookup to work, you deserve the PHD. thats a very powerful function and i NEED to use it but every time I give it a whirl it doesn't work for me. you'll have to send me your spreadsheet so I have a working example of vlookup