+ Reply to Thread
Results 1 to 3 of 3

Returning peak numbers from a range of raw data, in the order they appear in the range.

  1. #1
    Registered User
    Join Date
    05-19-2017
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2

    Returning peak numbers from a range of raw data, in the order they appear in the range.

    Please excuse me if this I am missing something completely obvious. I am a self taught Excel user and may not be utilizing formulas as efficiently as I could be. Any and all help is greatly appreciated.

    Heres the scenario. Im using a digital measurement probe to measure an inside diameter of a part, that has peaks and valleys. The only numbers I care about are the peaks (which may be a positive or negative number). Raw data is dumped into Excel through the probe data acquisition software. So I'm left with a long column of numbers that would form some sort of irregular sine wave. I am trying to isolate and return the peaks of each wave. I've made some progress thus far, but can use some help from people who really know this stuff inside and out.

    I have been able to isolate the peaks using a helper column, so that when a peak value is reached the word "HIT" is displayed in the cell next to it in the adjacent column. (I created the formula to ignore if adjacent cells above or below have the same value, the reason being that in each raw data range, I know exactly how many "HITS" I should have, so any duplicate peaks in any give wave are to be considered as one "HIT").

    Here's where I'm at right now...This is just an example of what I'm trying to do as an actual data set may be around 1,000 cells containing 120 "HITS". I included the file as an attachment if needed for clarification.

    Cell B3 (copied down, used to identify the peaks of the data in column A =IF(AND(A2<A3,A4<A3),"HIT",IF(A2=A3,"*",IF(A4=A3,"HIT","*"))

    Cell E1 (used to count the number of "HITS" as a way to identify any errors as I know how many hits there are supposed to be at the end)
    =COUNTIF(B:B,"HIT")

    Basically I want to extract the values from the cells in Column A that have the word HIT next to them, and return them in a list starting at H3, going down , and heres the important part as I'll be graphing this later on, they need to be IN ORDER that they appear in the raw data range.

    My mind has been twisting and turning trying to figure this out. Im sure theres a simple solution that im just not grasping due to my inexperience. Ive been trying to research LOOKUP, VLOOKUP, MATCH, COUNTIF, formulas, but I'm having trouble understanding them exactly or how/if they apply to my problem.

    For whoever reads this, thank you for taking the time. Any, ANY pointers would be so much appreciated. I look forward to some guidance.

    Thanks!

    xfs0lx.jpg
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    8,563

    Re: Returning peak numbers from a range of raw data, in the order they appear in the range

    Would you be willing to use another helper column? I would put in column C some kind of "counting" function to count the number of "HIT"s in column B. Something like =COUNTIF(B$3:B3,"HIT") [note the mix of relative and absolute references] in C3 and copied down would give a column that increases in value as it goes down. Then a simple lookup function [=INDEX($A$3:$A$22,MATCH(G3,$C$3:$C$22,0))] in H3 and copied down would return the values from column A where the countif() first becomes 1,2,3...
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-19-2017
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2

    Re: Returning peak numbers from a range of raw data, in the order they appear in the range

    Thanks so much for such a quick response. By all appearances it seems that your solution is exactly the answer to my question! I'm looking forward to trying it with actual data, but the sample file I was practicing with is not far off from what the actual raw data looks like. I can't tell you how much frustration you've cured. Thank you a million times over. You made it seem so effortless, I envy your expertise. I'll follow up when I have a chance to put it to use. Thank you so much, again.

    EDIT
    Thank you as well for the explanation of the formulas you suggested and not just posting the "FIX", I appreciate the opportunity to learn when I can.
    Last edited by ptelep; 05-19-2017 at 11:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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