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

1. ## 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

2. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

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.

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

Hello, thanks so much for a reply.
The data I have attached is real (not confidential). The values which I am hoping that the function will obtain for the data set is 90.85 for the 3rd peak and 90.3 for the 4th. I have attached a text file which can be copied into Excel.

Thank you!

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

if your data is in column b

in c2 put =IF(AND(B2>=B1,B2>B3),B1,"") and copy down

Regards

Dav

5. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

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

6. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

Thank you both so much!

I appreciate it immensely! My aim is to use only the data between peaks 3 and 4. Following on from this, after obtaining 90.85 and 90.3, is there anyway to set these values as limits, and maybe use conditional formatting to highlight or even move the data which is between (and equal to) 90.85 and 90.3 (the two peaks and no other data throughout which satisfies this)?

7. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

One way... see column P.

8. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

This is great, thank you so much.

I have an issue with the threshold value - in some cases the maximum peak values exceed this threshold value so this wouldn't be applied to all data. Would I just have to check manually and change the threshold in each case?

Regards,

Fiona

9. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

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??!!

10. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

Hello, you're right I did mean does not exceed the threshold. In general no minimum value has ever fallen below 30, and no maximum value generally exceeds 120. All data is beautifully symmetrical!

11. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

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!!

12. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

(Not too Irish!) In general I would say 50 but it varies for each data set. I've attached more information of the type of data. Each data set has 10 corresponding graphs which are unique to that specific data set (usually).

Graph 3 is particularly interesting which crops up a lot, with each graph out of phase - I have a feeling this will cause some issues.

There is always the wobbly lead before the sine wave establishes itself.

I'm wondering if there would be a manual aspect to dealing with this data since each data set varies.

Thanks again, Glenn!

(PS. I'm sorry that the data is a bit messy in the file, I didn't realise there was a file size limit so I had to switch some data around.)

13. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

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.

14. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

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

15. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

This is great! Thank you so much.

Will there need to be some manual checking involved? For example a marking line at 40 on the graphs as a quick check whether the first real maximum does in fact occur after 40.

Thanks again, your help is hugely appreciated

16. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

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

17. ## Re: Large function: finding the values of the 3rd and 4th peak with duplicates

Hello, I have created a model excel file which I was trying to include your formulae in. However, I was having trouble including cells from separate sheets - I am dealing with quite a large amount of data and thought I could be able to separate it into separate sheets at each stage of a new formula. When trying to use the formula which obtains the values in the range from peak 3 to peak 4 I could not get it to work, I'm not sure if this is because they must occur in the same sheet.

I'd be grateful if you could have a look.

Thanks so much for your help Glenn, I really do appreciate it.

(It says upload of file failed when I try to upload, I will PM you)

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