Multiple Peak Values (MAX/MIN)

Hello All,

I would like to know if you all could help me determine the max or min values (peaks) on a graph (data set). Attached is the worksheet with the graph as well as a picture of the values needed. The picture shows the data points I need with arrows. Also the graph is voltage vs. time. I tried to get results using VBA however it seemed to be overcomplicated as I am a novice. Anyhow any feedback is greatly appreciated.

Your description of the problem suggests that this is basically a root finding problem (those who have studied calculus will recognize that the max/min's will occur at the roots of the 1st derivative). My first suggestion might be to go to a website like Wikipedia and study up on root finding algorithms.

The first option I think of for a problem like this is to find a relatively simple function that can represent the entire data set adequately. At first glance, the curve looks similar to a damped oscillation type function, so maybe a function of that form will work. The main advantage is, if you can use a regression algorithm to reduce the tabulated data to a single function, then applying the root finding algorithms to that function is usually straightforward.

The easiest algorithm to program might be a simple "brute force" type algorithm. Start at one end of the data set, then loop through, watching for the places where f and/or df change sign. If you can look at the function and feed it a couple of "initial guesses" that bracket the result, then a bisection algorithm might take fewer iterations to locate the roots.

At some point, because of the noise in the data, you will probably want to apply some kind of smoothing function. So, I might use the bisection method to get close to each root/max/min. Once I'm close, then use a polynomial regression to several points around the suspected root to smooth the data and locate the final value I would report for the root/min/max.

Thank you for your reply MrShorty, could you take a quick look at Column C on the new attachment and see it I am headed in the right direction? Thanks again.

I don't think it is the approach I would use for the problem, but your formula for C seems fairly effective at locating the Y values for the local maxima. At this point you would need a formula to return the x values as well. Then, you'll need a way to eliminate the "extra" returns so you get only one value for each peak.

MrShorty,

My knowledge of physics and calculus is inadequate and that is why I used the approach mentioned above. However, I have been investigating the functions and algorithms from your previous post. I understand the logic behind what you mentioned, I just do not understand the execution and details. Either way, thank you for your contribution.

Thank you MrShorty and I am tinkering with your demo as we speak. This will be used regularly because the points that I need will be later used in an equation to figure out inductance and resistance, so automation is required.

I did try to write a macro (with help) to figure the max of the entire range, then the min of the entire range(which worked but, I think by mistake). I have attached that here so you may take a look.

I would like the macro to continue in similar fashion to obtain the next two points. The problem that has me roadblocked is getting the macro to start the max function at the point of the min function that previously executed (T3, V3). I'm not sure if I am being clear enough as to what I am trying to do and if not I can elaborate later.Graph Data.xlsm

Unfortunately, due to version incompatibilities, I can't view your xlsm file.

MrShorty, here is the code for the macro in my xlsm file.


In the first Module

Please Login or Register  to view this content.

In the second Module

Please Login or Register  to view this content.

Right now, it looks like you are using the string literal "E1:E10000" as the argument for the range method. This argument can essentially be any string expression that evaluates to a workable reference. I would probably put that string into a variable, then use that variable to control the lookup range for each iteration. The first time through, this variable will contain "E1:E10000" (refstr="E1:E10000"). Subsequent loops will contain a string like refstr=previousmincell.address & ":E10000"

MrShorty, Thank you very much for your insight, it seems that you are helping me more than you know. I think I have it down for the first time through, could you take a look and tell me if this is what you had in mind in regards to the string variable? It does work after a test run so I am confident it is correct. It is the subsequent loops that are giving me trouble.

Please Login or Register  to view this content.

That's the idea for the first loop. Now you need to figure out the code to adjust refstr for the subsequent loops.

This part is quite difficult for me. Although the code works for the second pass through, I can't seem to be able to obtain the cell address for the "dMinVolt" variable. I think I may be taking this pass through to a difficult turn. I'm not sure where to go from here, any advice?

Please Login or Register  to view this content.

Hold on, I think I figured something out. I will post when finished. thanks.

Ok, so I managed to get through the 2nd and 3rd loops and obtaining the correct points I am looking for, however I am not sure how to aquire the cell address for the 3rd point. I tried the same approach as the other test functions, but the cell that is returned is not the correct one.

Please Login or Register  to view this content.

TestFunction3() is returning an incorrect cell address.

What address is it returning? What value is it looking for? Are there multiple instances of that value, and it is returning the "wrong" instance? What address do you expect it to return?

It's returning E3611. It's looking for the MaxCell function of the range variable refStrB. I think so. I bet it is. I expect it to return cell E4249.

And do E3611 and E4249 contain the same value? As I understand it, the Find method returns the first instance of whatever it is looking for. It seems that the most common reason for the find method to return the wrong reference is because it is finding the wrong instance of a value that occurs more than once in the lookup list.

Yes, except for the sign. E3611 contains -5.48 and E4249 contains 5.48, I am working on how to find the correct instance.

The odd thing is that when I run the code to evaluate the max of the range as:

Please Login or Register  to view this content.

the correct value is returned. Is there a better way to obtain the cell address other than the find method?

Yes, except for the sign. E3611 contains -5.48 and E4249 contains 5.48, I am working on how to find the correct instance.

I've never really used the find method, so I'm not familiar with the nuances. I find it interesting that it is finding the negative result when searching for the positive result. I would suggest that you go through the help file for the find method and see if there is a toggle or other setting that will force it to search for numbers (and thus appropriately distinguish between -5.48 and +5.48). You could also use the MATCH or VLOOKUP functions that can search on numbers. Something has to change, because you can't have your lookup routine returning -5.48 (or 5.487 or 15.48 or similar) when you give it 5.48.

Another thought on the lookup: If I'm following you correctly, you are searching on Y/V. If you look at the bisection spreadsheet I posted, you will note that I did my VLOOKUP using X/T. Because of the cyclical nature of Y/V, there is the possibility that a given value will occur multiple times in the table, and at very different locations in the table. This could create complications in your lookup routines, especially if there are going to be several different (and yet to be generated) data sets. X/T, on the other hand, appears to be a continously increasing variable, and should only have one entry per value (within any roundoff errors). This suggests to me that X/T might be a better choice for the lookup value.

I find it interesting that it is finding the negative result when searching for the positive result. I would suggest that you go through the help file for the find method and see if there is a toggle or other setting that will force it to search for numbers (and thus appropriately distinguish between -5.48 and +5.48). You could also use the MATCH or VLOOKUP functions that can search on numbers. Something has to change, because you can't have your lookup routine returning -5.48 (or 5.487 or 15.48 or similar) when you give it 5.48.

I was thinking the same thing, and so far I haven't found anything promising.

Another thought on the lookup: If I'm following you correctly, you are searching on Y/V. If you look at the bisection spreadsheet I posted, you will note that I did my VLOOKUP using X/T. Because of the cyclical nature of Y/V, there is the possibility that a given value will occur multiple times in the table, and at very different locations in the table. This could create complications in your lookup routines, especially if there are going to be several different (and yet to be generated) data sets. X/T, on the other hand, appears to be a continously increasing variable, and should only have one entry per value (within any roundoff errors). This suggests to me that X/T might be a better choice for the lookup value.

I'm not sure this is an option for me because the Y/V (peaks in voltage) is the main values that I am looking for. A lookup of X/T (times at which these voltages occur) is basically secondary data. Being that the Y/V values "bounce" around due to noise is considered and that is why the first occurance of both the max or min value is acceptable for my purposes. I need the values of the first and second "peaks" along with their corresponding minima (as close to the x-axis > 0) so that I can plug them into formulas to find inductance and resistance. I just find it odd that the function that I used to find the cell addresses of the first and second values works, however the third is giving me an incorrect return.

I just find it odd that the function that I used to find the cell addresses of the first and second values works, however the third is giving me an incorrect return.

I'm not really surprised by this. Within a given data set, as the peaks become smaller, it would seem likely that there would be a greater chance of finding "duplicate" values in Y. I expect if you ran this algorithm over 20 data sets and 100 peaks, you would find it returns the wrong cell a certain percentage of the time.

I'm not sure this is an option for me because the Y/V (peaks in voltage) is the main values that I am looking for. A lookup of X/T (times at which these voltages occur) is basically secondary data.

This may be true. As a programmer, though, we also have to ask ourselves which approach is going to allow you to consistently, accurately, and easily locate the roots/mins/maxes in V. Sometimes it is easiest to look at V directly. Other times (and I believe this is true for your problem) it is easier to locate the T's at which the roots/mins/maxes occur. In this case, once T is found, V is a simple (almost trivial) lookup. Whichever direction you decide to go, I just want you to consider that the most important variable to find might not be the easiest variable to find.

I was thinking the same thing, and so far I haven't found anything promising.

If you can't find anything promising using the Find method, then I would suggest using the MATCH function. The Match function with the final parameter set to False/0 will find an exact numerical match (within the limits of double precision). The odds of find the wrong duplicate will be much lower this way.

I'm not really surprised by this. Within a given data set, as the peaks become smaller, it would seem likely that there would be a greater chance of finding "duplicate" values in Y. I expect if you ran this algorithm over 20 data sets and 100 peaks, you would find it returns the wrong cell a certain percentage of the time.

Thank you for that observation, I failed to consider this.

This may be true. As a programmer, though, we also have to ask ourselves which approach is going to allow you to consistently, accurately, and easily locate the roots/mins/maxes in V. Sometimes it is easiest to look at V directly. Other times (and I believe this is true for your problem) it is easier to locate the T's at which the roots/mins/maxes occur. In this case, once T is found, V is a simple (almost trivial) lookup. Whichever direction you decide to go, I just want you to consider that the most important variable to find might not be the easiest variable to find.

You are right, and I am a beginner and this is a difficult habit to break. I am currently looking into finding T and running a few tests to see if I can get it to work.

If you can't find anything promising using the Find method, then I would suggest using the MATCH function. The Match function with the final parameter set to False/0 will find an exact numerical match (within the limits of double precision). The odds of find the wrong duplicate will be much lower this way.

Indeed, I will be testing this function as well. You are a great asset to these forums and I appreciate you pointing me in the right direction, rather than giving me code and sending me on my way. Thanks again!

Mr. Shorty,

I'm not sure if you are still interested, but I managed to figure out how to get the find function to obtain exact matches while going through the information. Apparently there is an optional variant within the find method named "LookAt", where you can specify an exact match or a partial match. Default value is xlPart.

In my example the find method would be finding an exact match.

Please Login or Register  to view this content.