Guidance needed with a formula

S

sroehl

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? :confused:
 
B

Biff

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
 
B

Biff

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
 
S

sroehl

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

TI
 
B

Biff

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

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

Biff
 
Top