Guidance needed with a formula

Very new to working with formulas so here goes: If I have the average of 10 numbers, cells A2-A11, is there a formula that will give me the 3 numbers closest to the average?

Hi!

The "simple" way:

In B2 enter this formula:

=ABS(A2-AVERAGE(A$2:A$11))+ROW(A2)/10^10

Copy down to B11

In C2 enter this formula:

=INDEX(A$2:A$11,MATCH(SMALL(B$2:B$11,ROW(A1)),B$2:B$11,0))

Copy down to C4.

The "complex" way:

Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A$2:A$11,MATCH(SMALL(ABS(A$2:A$11-AVERAGE(A$2:A$11))+ROW(A$2:A$11)/10^10,ROW(A1)),ABS(A$2:A$11-AVERAGE(A$2:A$11))+ROW(A$2:A$11)/10^10,0))

Copy down to a total of 3 cells.

Biff

"sroehl" <sroehl.1waxea_1128301506.9557@excelforum-nospam.com> wrote in
message news:sroehl.1waxea_1128301506.9557@excelforum-nospam.com...
>
> Very new to working with formulas so here goes: If I have the average
> of 10 numbers, cells A2-A11, is there a formula that will give me the 3
> numbers closest to the average?
>
>
> --
> sroehl
> ------------------------------------------------------------------------
> sroehl's Profile:
> http://www.excelforum.com/member.php...o&userid=21773
> View this thread: http://www.excelforum.com/showthread...hreadid=472481
>

P.S. -

If you already have a cell that calculates the average then you can replace
the calls to AVERAGE with a reference to that cell:

Assume B1 holds the average:

=ABS(A2-B$1)+ROW(A2)/10^10

And:

=INDEX(A$2:A$11,MATCH(SMALL(ABS(A$2:A$11-B$1)+ROW(A$2:A$11)/10^10,ROW(A1)),ABS(A$2:A$11-B$1)+ROW(A$2:A$11)/10^10,0))

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:uMogG29xFHA.788@tk2msftngp13.phx.gbl...
> Hi!
>
> The "simple" way:
>
> In B2 enter this formula:
>
> =ABS(A2-AVERAGE(A$2:A$11))+ROW(A2)/10^10
>
> Copy down to B11
>
> In C2 enter this formula:
>
> =INDEX(A$2:A$11,MATCH(SMALL(B$2:B$11,ROW(A1)),B$2:B$11,0))
>
> Copy down to C4.
>
> The "complex" way:
>
> Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =INDEX(A$2:A$11,MATCH(SMALL(ABS(A$2:A$11-AVERAGE(A$2:A$11))+ROW(A$2:A$11)/10^10,ROW(A1)),ABS(A$2:A$11-AVERAGE(A$2:A$11))+ROW(A$2:A$11)/10^10,0))
>
> Copy down to a total of 3 cells.
>
> Biff
>
> "sroehl" <sroehl.1waxea_1128301506.9557@excelforum-nospam.com> wrote in
> message news:sroehl.1waxea_1128301506.9557@excelforum-nospam.com...
>>
>> Very new to working with formulas so here goes: If I have the average
>> of 10 numbers, cells A2-A11, is there a formula that will give me the 3
>> numbers closest to the average?
>>
>>
>> --
>> sroehl
>> ------------------------------------------------------------------------
>> sroehl's Profile:
>> http://www.excelforum.com/member.php...o&userid=21773
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=472481
>>
>
>

The first solution works using B2 and C2 cell formulas.
I want to copy this down through 60 sets of values. Copy/paste doesn't change the cell references. Is there an easier way to do this than changing each formula and recopying for each set of values?

TIA

>Is there an easier way to do this than
>changing each formula and recopying for each set of values?

Well, if you have 60 sets that seems to be about the only thing you can do.

Biff

"sroehl" <sroehl.1wbjmd_1128330314.6847@excelforum-nospam.com> wrote in
message news:sroehl.1wbjmd_1128330314.6847@excelforum-nospam.com...
>
> The first solution works using B2 and C2 cell formulas.
> I want to copy this down through 60 sets of values. Copy/paste doesn't
> change the cell references. Is there an easier way to do this than
> changing each formula and recopying for each set of values?
>
> TIA
>
>
> --
> sroehl
> ------------------------------------------------------------------------
> sroehl's Profile:
> http://www.excelforum.com/member.php...o&userid=21773
> View this thread: http://www.excelforum.com/showthread...hreadid=472481
>