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


--
hidden_stairway
------------------------------------------------------------------------
hidden_stairway's Profile: http://www.excelforum.com/member.php...o&userid=24459
View this thread: http://www.excelforum.com/showthread...hreadid=380550

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

Can you give an example with the expected answer.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=38055

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(Shee t1!$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





Mangesh,

The solution I got from ninja stevie does not solve my problem. Here is
an example... please bear with me...

in columns A1:A5 i have "Purchase Orders" "568", "559", "564", and
"570" respectively. in columns B1:B5 i have "Vendors", "abc
contracting", "dunrite electrical", "moes painting", and "xyz
plumbing". thus creating a numbered list of
purchase orders with their respective recipients. please note that they
are not in numerical order... on another sheet I would like to have the
purchase orders displayed in ascending numerical order with their
respective recipients also displayed. is this possible?

Thank you for your time
Brad


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=380550