Trigger based on maxima and minima

    Hi,


    I found some VBA code for finding local maximas and minimas. Compared to some cell formulas it works best, but I really don't care if these calulations are cell formulas or VBA.


    Several cell formulas that I tested for tested for finding local maximas and minimas, missed some points. The code included in this post seems to work best. That's why I am posting to the VBA Forum.




    Also included are data collected in an experimental environment. This is what I tested the included code with. Column A is time data, column B is the experimental data.


    Column C is local maxima- column D is the local minima.


    Here's what is supposed to happen: following a local maxima, as the data value is falling, column E would indicate True when column B reaches 0.8x (80%) of the last local maxima. It will stay True until the next local minima occurs. At this point, column E would indicate False when column B reaches 1.2x (120%) of the last local minima.


    Since the data is cyclic, this will repeat through the dataset.


    Could anyone in the Forum help with code or cell formulas to get the output that I need?


    Thanks,


    -Art

    Hi,


    Thanks for response. Attached is a workbook- probably it's easier to get a sense of what's going on looking at that.


    First local minima occurs at 0.5 sec, i.e., 1.02961. This local minima is multiplied by the value in J2; the value in F12. What I would like to see is when the data in column B is equal to or greater than F12, the cross is shown by a marker on the chart.


    Similarly, for a local maxima like at 2.75sec, at 1.19622, this local maxima is multiplied by the value in I2; the value in E57. When the data in column B is equal to or less than than E57, the cross is shown by a marker on the chart.


    Columns F and G would contain the charting data for the crossovers.


    Please let me know if you have any further questions.


    Thanks!


    -Art

    Some clarification required I think. You've posted two files but they have different data and column headings. Please could you post an illustrative file with before and after sheets so I'm clear what exactly you need to be done as you seem to have done some of it already? Also, I don't think I understood what you meant by trigger signals.

    Hi,


    To give you a better example of what I am trying to do, I located the "trigger" points by hand on the attached chart. Please ignore the Triggerv1 file that I sent earlier, it was only to illustrate a point. I may not have gotten all the points right since I scanned through column B manually searching for the values that were best matches for max below (<=) and min above (>=). Those are the trigger transition points.


    Please let me know if this help to clear this up.


    Thanks,


    -Art

    OK, think I see that but one more question. When you say you want to find the values in B which are >= the value in F, presumably this is only local values in column B, i.e. until the next maxima? So in your v2 file you would start looking at B2:B56? Do I have that right?

    Hi,


    Yes, that's right. As the local maximas and minimas are found, the calculation for the trigger points proceeds through the range until the end of the data record. Though I did not mention this, the actual data range is dynamic containing from hundreds of rows to tens of thousands of rows. the calculation will need to adjust accordingly.


    Hope this helps.


    Thanks,


    -Art

    Cross-posting Without Telling Us


    Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.


    Post a link to any other forums where you have asked the same question.


    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.


    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!


    Read https://www.excelguru.ca/content.php?184 to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).


    No further help to be offered, please, until the OP has complied with this request.

    All,


    Sorry - did not mean to cause a problem for those helping me. I posted to Ozgrid because they allow file uploading they do not at Mrexcel. It seemed I was having a hard time explaining what I needed and thought having a file to look at would help the person providing assistance. I only thought of the file uploading service idea at the last minute.


    My posting link for Mrexcel was:


    https://www.mrexcel.com/forum/…a-minima.html#post5272627


    Guess I messed up here, I should have read the Forum rules, also.. Sorry to all. I would very thankful if anyone would still be willing to help.


    Thanks,


    -Art

    Thanks, Stephen much appreciated. No, I have no responses from MrExcel. I hacked at the problem last night; made a little progress (I think) but was still stuck. I will post my latest file when I get home tonight. -Art

    Hi Stephen,


    The attached shows the trigger point from the minimum values to a point a percentage above these local minima for several minima in the data set, The cell formulas gets the point across very crudely. There would be a formula for an equivalent trigger based on the maxima where the trigger would be a percentage below the local maxima.


    A couple of things: data record is variable: 10s rows to thousands so calculation needs dynamic ranges. I created the maxima/minima or the trigger points with VBA. VBA calculations are my preference, but any way to do these calculations works for me.


    Thanks,


    -Art

    OK, I've had a stab, though can't quite replicate the results. This code is adding a few more values than you indicated. The results are placed in column I, coloured green.


    If can iron out the method, can then address speed if it's an issue, probably by using arrays rather than operating on the sheet.


    Hi Stephen,


    Good first cut, thanks. Maybe if you setup the other case where the trigger is a percentage of the maxima (based on the column E data) , something may jump out at you. What do you think?


    Thanks,


    -Art

    Hi Stephen,


    The point that your code identified at I174 is not related to the minima that occurs at B184 so it should not be flagged. The value should be the minima that occurs at B184* O2 which equals 1.109301. The trigger point that occurs at B192 is >= 1.109301.


    Make sense?


    -Art

    There may be some confusion about the goal; maybe I was not clear about this. The plan was to have two separate columns. One column tracks the values that are a calculated a % above the local minima. Column two holds the values calculated % below the local maxima. Did you combine these together in one column?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login