Next lowest/next highest value

[pkh987]

Consider the following example of a dataset: 2,3,3,4,5,7,7,8,9. The average is 5.333.

I am looking for a formula that gives me the "next lowest value" and the "next highest value" from the average within the dataset. In this example, the next lowest value is "5" and the next highest value is "7". I have tried various combinations of functions but nothing has worked so far.

Thanks.

 

[Eric W]

Welcome to the board.

Try:

Excel 2012

	A	B	C	D

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5.333333
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]7
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10"]C1
[/TH]
[TD="align: left"]=AVERAGE(A1:A9)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10"]C2
[/TH]
[TD="align: left"]{=MAX(IF(A2:A9< C1,A2:A9<C1,A2:a9< font="">)</C1,A2:a9<>)}
[/TD]
[/TR]
[TR]
[TH="width: 10"]D2
[/TH]
[TD="align: left"]{=MIN(IF(A2:A9>C1,A2:A9))}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



C2 and D2 are array formulas, make sure to enter them using Control+Shift+Enter.

 

[pkh987]

Thanks Eric - that is excellent, works a treat. I would never have got there by trial and error - and its the first time I have used array formulas. Will hang around the forum as may learn a few more tricks!

 

[Eric W]

Glad to help! I'm still learning new Excel tricks!