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
Bookmarks