offset function

[hidden_stairway]

I am trying to offset the value of the 'n'th smallest number in an array
that is NOT in order... is there any way I can do this? I have tried
=offset(small(a1:a5,1),0,1,1,1), but it returns an error. If you can
shed any light on this problem I would be much abliged.

Brad
CANADA

 

[Ninja Stevie]

you are using the formula incorrectly here.

The offset function requires that the first item you insert is the
reference to the range of cells.
You have put the small() function here - but this returns only the nth
smalles value, and not the range.

Im not sure which direction you are wanting to offset the range, but I
will presume in a downwards direction, here is how you would want to
type the formula:

=offset( [ target range in here ],small(a1:a5,[ nth number here
])-1,1,1,1)

this translates to:

the value within the [ target range in here ] which is x number of rows
from the top, where x is the nth smallest figure within the range a1:a5

 

[mangesh_yadav]

Can you give an example with the expected answer.

Manges

 

[mangesh_yadav]

Hi Brad,

do the following, supposing your data is in sheet1 in range A2:B5, with
the headers in row 1, then in another sheet, in cell A1 enter:
=SMALL(Sheet1!$A$2:$A$5,ROW(A1))
and copy down upto 4 rows.

In B1, enter:
=INDEX(Sheet1!$B$2:$B$5,SUMPRODUCT(--(Sheet1!$A$2:$A$5=A1),ROW(INDIRECT("1:"&COUNT(Sheet1!$A$2:$A$5)))))
and copy down 4 cells.


Note: Also try to post your queries on the newsgroup instead of
personal message, as there is a much larger scope that your query will
be answered by someone else.


Mangesh