Nearest cell below a certain value.

**cramie10** · 08-27-2015, 08:13 AM

Hi All

I'm new to this forum, so hopefully my question is posted appropriate. Apologies if not.

I have a column of values ranging from approximately -20 to 20. Within these values I have certain collections forming "peaks" (if the data were to be graphed), like this:

-7.431377404
-0.123504621
8.267563041
11.90999127
10.14175866
1.613639202
-7.170097474
-6.826058515

I define anything which is part of a "peak" to be above a value of 3. How do I select all the values within one "peak" and choose the maximum? In the example above, I would want the value of 11.91 to be chosen. There are various examples of these "peaks" across my data. For example, here's another:

0.571340419
0.543676911
3.162139566
4.132734389
1.649930702
-0.201880797

And here I would want 4.13 to be chosen. I have too much data to do every single one of these by hand.

Essentially, if I imagine my data on a graph, I want something which chooses the tip of every single peak. Can this be done?

Many thanks.

**Special-K** · 08-27-2015, 08:24 AM

=MAX(IF(A1:A8>3,A1:A8))
Array formula, use Ctrl-Shift-Enter

**berlan** · 08-27-2015, 08:41 AM

Or,

=IFERROR(AGGREGATE(14,6,(A1:A8)/(A1:A8>3),1),"no peak above 3")

**MrShorty** · 08-27-2015, 08:42 AM

Special-K's formula should work, once you know which range to enter for the desired peak. Technically, I'm not sure that it is really different from a simple =MAX(A1:A8) since anything less than 3 cannot be the Max in a list of numbers that goes above and below 3. Of course, expanding this formula to include the entire data set will only find the max value of the largest peak, not the top of each peak.

I'm not familiar with the real algorithms that are used for this kind of signal processing. If this is something you need to be proficient in, I would suggest some research into signal processing algorithms. The simple algorithm I use for this sort of thing, on the rare occasion that I need it, takes its cue from basic calculus. The "max" of the peak (assuming no noise) will be the point where the slope of the signal crosses 0 (goes from positive to negative). I will add a column that calculates slope (usually a simple (B2-B1)/(A2-A1), though I can get more complicated depending on needs), then a second helper column to "tally" up or otherwise identify where the slope crosses 0. Then I can search these columns for the maxima and minima, test for max or min, and return the maxima.