Solved how to find the peak above a certain value of a given range?

Microsoft Excel 2010
October 8, 2012 at 07:57:18
Specs: Windows 7
Hello again!

The description given in this forum did really help a lot!

But now i have a new problem which i couldn't solve until now:

I need to build a function out of the points before and after the maximum.
and to do so i need to find them first.

as example i have the following data:

1 60
2 70
3 90
4 80
5 60
6 50
7 40

so i know how to get my maximum [{=MAX(B1:B7)} equals 90].
But i don't know with which function i can get the top underneath the Max [represented by 70] and the Top beyond the Max [represented by 80]

I tried to do it with =LARGE(IF((A1:A7)>3,(B1:B7)),1) but i didn't succeed yet.

Does anyone know a dynamic function, which displays 80, respectively 70 instead of #value at the end?

See More: how to find the peak above a certain value of a given range?

October 8, 2012 at 08:19:03
✔ Best Answer
Use INDIRECT and MATCH to set the range for the LARGE function which will return the 2nd largest value in the new range.

You didn't include Column letters, so I am going to assume that your values are in B1:B7.

To return 70:


To return 80:

=LARGE(INDIRECT("B" &MATCH(MAX(B1:B7),B1:B7,0)&":B7"),2)

In both cases MATCH(MAX(B1:B7),B1:B7,0) will return 3. The INDIRECT function will then use that 3 as the Row number to set the range for the LARGE function.

B1:B3 for 70 and B3:B7 for 80.

Since MATCH returns the Row number of the array searched, not the Row number of the sheet, you'll have to add an offset to the value returned by MATCH if your table starts in any Row other than 1.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question