Need Some Help Guys..Want to find max value and some values above and below it

[Haidar123]

Column1
1
2
3
4
5
6
7
6
5
4
3
2
1
For example I have these value...I want to know how can I extract maximum value and certain values above and below that maximum Value

For example

Max=7

above max values=6,5,4

Below max Values=6,5,4

Thanks

[Pete_UK]

How can 6, 5, 4 be above the maximum if the max value is 7 ? Do you mean the three values in the cells BEFORE and AFTER where the maximum occurs?

Pete

[Haidar123]

yes exactly....certain number of values below and after the max occured...

[malcmail]

Rather than a custom function wouldn't you be better using MATCH, INDEX and working from there to get what you are looking for? That will only work if there's only 1 instance of the max value of course. I'd presume that would be the same for a custom function too though.

[AZ-XL]

Hi
For above
=IFERROR(LARGE(OFFSET($A$1,0,0,MATCH(MAX($A:$A),$A:$A,0)-1,1),COLUMN()-5),"")

For below
=IFERROR(LARGE(OFFSET($A$1,MATCH(MAX($A:$A),$A:$A,0),0,COUNT($A:$A)-MATCH(MAX($A:$A),$A:$A,0),1),COLUMN()-5),"")

COLUMN()-5 inside the formula you may change depending on your data

[Haidar123]

thank u so much....Can these values in order exactly the same in column?

[martindwilson]

do you want then sorted or just as they are?
if its not relevent to wether they are sorted
then just =INDEX($A$1:$A$20,MATCH(MAX($A$1:$A$20),$A$1:$A$20,0)-1) then -2 then -3 and +1+2+3

[Haidar123]

Martin thank u so much it works fine Thanks to all

[AZ-XL]

Column() - 5 means
I started formula from F column and value of it is 6. Column() formula gives me 6. In order to get 1st (2nd and etc. as I drag right) larger value I write =Column()-5. For example if your formula will start from D column then you should modify it to Column()-3 to get value of one.
Also if you want to get value from up to down you can use Row() function too.

[Pete_UK]

@AZ-XL:

I got the impression that the data represented some time-variant data, and that the OP was interested in values on either side of the peak (as with Martin's suggestion - he beat me to it!), so I'm not sure if the OP needed the data sorted as your solution does.

Pete

[AZ-XL]

You are right Pete. Seeing 6-5-4 directly I thought that OP wants sort from larger to smaller.
Thank you for your attention.