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 40so 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?

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:

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

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.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History