Large function: finding the values of the 3rd and 4th peak with duplicates

Hello! I have relatively small experience with more complex Excel functions and would be grateful for some help. I have a set of data which is similar to sine graph in appearance. The graph contains 6 peaks. I would like to know to get the numerical values of the 3rd and 4th peak.

However, there is a problem because my data contains duplicate values at those peaks (each peak in many data sets vary in the number of duplicates they have), where the graph is flat for a few data points. So if I use the large function, I get the same outcome in both cases.
=large(array, 3) and =large(array, 4) I am aware there is an 'if' function to change what the outcome satisfies but I am struggling to apply it to this situation.

After obtaining these two values at the 3rd and 4th peak I wish to use them as limits so the data within these limits can be use in my analysis.

I would be extremely grateful for any help!

Kind regards,

Fiona

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I wasn't entirely sure what you wanted. So, I've given you 3 possibilities. I think (looking back) that the 3rd is what you need. I identified local maxima (you're lucky that your data are "noise free" and don't generate many very localised maxima.
With your data in A1 to B584, in C2, copied down:

=IF(B3>B2, IF(B3<B4,"",B3),"")

then refer to the sheet for the various options I tried. This may result in multiple local maxima in noisier data, n which case, another think will be required!!


The formulae in G and M are array formulae. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Just seen the earlier post...

One way... see column P.

I assume that you meant that sometimes the max does NOT exceed the threshold. It is possible to get Excel to adjust it for you. Providing that none of the maxima in a singel data set are lower than any of the minima. can you post an example of your most erratic data, or are they all beautifully symmetrical??!!

Whats the minimum value of the maximum? if that doesn't sound too Irish...

I'm cutting back apple trees today and will try to get back, later on, and take another look at a more automated way of assigning the "70". Probably calculate the highest minimum and add 10% or something like that... Do you always have that wobbly lead in before the "sine wave" establishes itself?

Re symmetrical data... i spent years as a research scientist in the biological/veterinary area. None of my data were ever beautifully symmetrical. You should count yourself lucky!!

Hi there. I fiddled with this for a while. One option is to set the formula to ignore the first 40 rows of data, as the first real maximum never occurs in that "timeframe". The attached sheet does that. It seems to work well for almost all of your data. However... you do have one dataset that is a bit flakey. Sheet 2, Dataset 1 (the first 600 rows). the signal amplitude between successive peaks and troughs is relatively small, leading to "false positive" peaks. however, if that sort of dataset is the exception, rather than the rule, you might be OK. If you have any thoughts on how to tackle data like that, I'd be happy yo try to put it into Excel.

Edit for some reason I can't upload the file. I'll try again shortly. Alternatively, PM me your email address and I'll email it to you.

Sorted!! I accidentally induced file bloat, increasing the file size to 6Mb. fixed and attached.

That's a good idea... Do you know how to get Excel to add a vertical line at a defined value? Create another data series with the same X value (40) and variable y values. If stuck, shout and I'll modify one of your graphs...