+ Reply to Thread
Results 1 to 6 of 6

Finding peak and valley values where there are some small waves within the large waves

  1. #1
    Registered User
    Join Date
    02-20-2018
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    38

    Finding peak and valley values where there are some small waves within the large waves

    Hello,
    I'm trying to find the peak and valley values of a sinusoidal function. I have a column with ID numbers (column A) where each ID has its own list of y-values. Currently I'm using IF(MAX) and IF(MIN) functions where I find the max of column D (y axis) within 40 rows and returns the value if true and a blank cell if false. With these functions I have to examine the values for each ID and change the range on random cells because there are small waves within the large waves and I only want the peaks and valleys of the large waves.

    Is there a way to find the maximum within each group of positive numbers and the minimum within each group of negative numbers for each ID number? Thanks in advance.

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,298

    Re: Finding peak and valley values where there are some small waves within the large waves

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

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

    Re: Finding peak and valley values where there are some small waves within the large waves

    Short but useless answer -- yes there is a way to perform this kind of "signal processing". You might try variations of FlameRetired's solution or build something off of the outline I describe here: https://www.excelforum.com/excel-gen...al-points.html

    Questions or difficulties in implementing something like that?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    02-20-2018
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Finding peak and valley values where there are some small waves within the large waves

    In the attachment I added data from two ID's. I made columns peak 1 and valley 1 where I used the IF(MAX) and IF(MIN) functions. The columns peak 2 and valley 2 I manually entered the words peak and valley in the rows where the correct values should be. I highlighted the correct values in green and the incorrect in red. With the first ID the function works fine but with the second there are some incorrect values where I would have to manually adjust the min or max range for those cells. I have 50,000 rows of data so if I could find a better way to find these values where I won't have to examine every wave I would appreciate any help. If there's a way to find the max within each group of positive numbers, for example row 3 to row 43, then find the minimum for the following group of negative numbers that would be perfect if its possible.
    Attached Files Attached Files

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Finding peak and valley values where there are some small waves within the large waves

    How about something simple?

    B
    C
    D
    E
    1
    x-axis
    y-axis
    2
    1600
    -0.22
    3
    1734
    0.10
    4
    1776
    0.33
    5
    1801
    0.54
    6
    1822
    0.66
    #N/A
    D6: =IF(OR(C6 = MAX(C2:C10), C6 = MIN(C2:C10)), C6, NA())
    7
    1840
    0.74
    #N/A
    8
    1857
    0.79
    #N/A
    9
    1874
    0.83
    #N/A
    10
    1905
    0.88
    #N/A
    11
    1920
    0.90
    #N/A
    12
    1934
    0.91
    0.91
    13
    1949
    0.91
    0.91
    14
    1964
    0.91
    0.91
    15
    1979
    0.91
    0.91
    16
    1994
    0.90
    #N/A
    17
    2009
    0.89
    #N/A
    18
    2024
    0.88
    #N/A
    19
    2055
    0.84
    #N/A
    20
    2072
    0.82
    #N/A
    21
    2089
    0.80
    #N/A
    22
    2106
    0.77
    #N/A
    23
    2124
    0.75
    #N/A
    24
    2143
    0.72
    #N/A
    25
    2163
    0.69
    #N/A
    26
    2183
    0.67
    #N/A
    27
    2204
    0.64
    #N/A
    28
    2226
    0.61
    #N/A
    29
    2250
    0.58
    #N/A
    30
    2276
    0.54
    #N/A
    31
    2303
    0.50
    #N/A
    32
    2334
    0.46
    #N/A
    33
    2369
    0.40
    #N/A
    34
    2405
    0.37
    #N/A
    35
    2479
    0.36
    #N/A
    36
    2523
    0.32
    #N/A
    37
    2577
    0.27
    #N/A
    38
    2640
    0.22
    #N/A
    39
    2709
    0.20
    #N/A
    40
    2790
    0.17
    #N/A
    41
    2885
    0.15
    #N/A
    42
    3004
    0.12
    #N/A
    43
    3124
    0.12
    #N/A
    44
    3325
    -0.08
    #N/A
    45
    3390
    -0.56
    #N/A
    46
    3413
    -0.66
    #N/A
    47
    3434
    -0.73
    #N/A
    48
    3453
    -0.79
    #N/A
    49
    3471
    -0.85
    #N/A
    50
    3487
    -0.90
    #N/A
    51
    3503
    -0.94
    #N/A
    52
    3518
    -0.97
    #N/A
    53
    3533
    -0.99
    #N/A
    54
    3548
    -1.01
    #N/A
    55
    3562
    -1.04
    #N/A
    56
    3576
    -1.05
    -1.05
    57
    3591
    -1.04
    #N/A
    58
    3605
    -1.02
    #N/A
    59
    3620
    -0.98
    #N/A
    60
    3636
    -0.96
    #N/A
    61
    3652
    -0.94
    #N/A


    EDIT: I see you did about the same thing. I just sized the window to exclude the local extrema.
    Last edited by shg; 02-23-2019 at 09:26 PM.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    02-20-2018
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Finding peak and valley values where there are some small waves within the large waves

    I think I have it figured out the only problem is I only want the max and min displayed once within the group of positive or negative numbers and I can't figure out how to do that. For example in the attachment I added earlier the first peak has 4 points and I only want it displayed once. Here is the formula I'm using =IF(MAX(IF($E2=$E$2:$E$60499,IF($E2>0,$D$2:$D$60499)))=$D2,$D2,"") where column E is a helper column and column D is the y-axis. Thanks

+ 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. Replies: 12
    Last Post: 06-14-2017, 10:35 AM
  2. Large function: finding the values of the 3rd and 4th peak with duplicates
    By fionafiona in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 03-16-2017, 08:16 AM
  3. Viewing sound waves in excel?
    By Micbrook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2016, 07:34 AM
  4. [SOLVED] Read a Row and catch the maximum and minimum points
    By ropbasuel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-27-2014, 09:42 PM
  5. Greetings fellow waves
    By Surfboard in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-14-2013, 09:23 AM
  6. [SOLVED] Help with a formula to help count # of Waves in Progress, # Completed
    By karryan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-27-2013, 04:56 PM
  7. *Waves Hello*
    By spoonedmango in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 11-15-2012, 09:05 AM

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