+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    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
    Last edited by fionafiona; 03-10-2017 at 05:27 AM. Reason: grammatical error

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

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



  3. #3
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

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

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,215

    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

    then large(c:C,3) and large(c:C,4) will get you your answer

    Regards

    Dav

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

    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...
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 03-10-2017 at 09:40 AM.

  6. #6
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    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. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

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

    One way... see column P.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    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. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

    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. #10
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    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. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

    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!!
    Last edited by Glenn Kennedy; 03-14-2017 at 07:13 AM.

  12. #12
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

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

  13. #13
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

    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.

    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.
    Last edited by Glenn Kennedy; 03-15-2017 at 11:43 AM.

  14. #14
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

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

  15. #15
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    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. #16
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,641

    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. #17
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finding peak days of the within a period
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-24-2016, 04:18 PM
  2. Finding Peak and Trough
    By VJR in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-01-2014, 11:01 AM
  3. Finding multiple peak values and reporting associated data
    By jsmith6 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 07:16 PM
  4. LARGE Function and Duplicates
    By andrewc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-28-2013, 09:46 AM
  5. Replies: 5
    Last Post: 05-18-2013, 11:59 AM
  6. using large function with duplicates
    By merlyn45 in forum Excel General
    Replies: 10
    Last Post: 10-18-2012, 02:03 PM
  7. using large function with duplicates
    By merlyn45 in forum Excel General
    Replies: 2
    Last Post: 10-10-2012, 05:30 PM

Tags for this Thread

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