+ Reply to Thread
Results 1 to 25 of 25

Multiple Peak Values (MAX/MIN)

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Question 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.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    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.

  3. #3
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    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.

  5. #5
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    I suppose a quick example spreadsheet is in order. Note that this was quickly put together and, for demonstration purposes, relies on manually copying and pasting each iteration into the loop. This can be automated (either by turning iterative calculations on or by using VBA to write a macro) if you really want to go to that effort. Also, as noted above, finding the maxima and minima is the same as finding the roots of the 1st derivative. So, this same algorithm will work for finding those points if you add a column to calculate the slope of the function.

    From here, one would have to decide how automated they wanted this to be (if this is a one time exercise, I don't know how much effort I'd waste on automating it). There could also be some significant work to test and debug the algorithm for different scenarios, for determining when all roots/max/min have been found, and so on.
    Attached Files Attached Files
    Last edited by MrShorty; 10-02-2012 at 02:09 PM.

  7. #7
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

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

  9. #9
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    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"

  11. #11
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

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

  13. #13
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

  14. #14
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

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

  15. #15
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    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?

  17. #17
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    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.

  19. #19
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

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

  20. #20
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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?

  21. #21
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    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.

  22. #22
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

  23. #23
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: Multiple Peak Values (MAX/MIN)

    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.

  24. #24
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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!

  25. #25
    Registered User
    Join Date
    08-02-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Multiple Peak Values (MAX/MIN)

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1