??? pulling from another sheet IF..... ???

[~Bigred]

I have a sheet that 22 columns and contains 30 rows (records).

One of the columns is Yield Rank and has a formula =RANK(U4,$U$4:$U$33),
this ultimately ranks the Yield% from 1 to 30.

Another column is Price Rank and has a formula =RANK(H4,$H$4:$H$33),
this again ranks the Price from 1 to 30.


This prompts two questions:

(1) How can I make the rank function ascend instead of descend? (this
pertains to the price rank, I want
the cheapest stock to be ranked #1 and the highest price ranked #30

(2) How do I pull the top 4 records (or rows) into another sheet instead of
having the copy/paste. I would like to do it with some from of If statement
(so it is flexible to changing how many records to pull - by changing the
code).

TIA,
_Bigred

 

[Pete_UK]

RANK has an optional 3rd parameter - if it is omitted or set to 0 then
it gives results as if the data was sorted in descending order. If it
is any non-zero value, then you get results as if sorted in ascending
order. So, change your formula to:

=RANK(U4,$U$4:$U$33,1)

For the second part of your post you need to check out the functions
LARGE and SMALL, and a formula like:

=SMALL(Sheet1!U$4:U$33,1)

will give you the smallest value in the array U4:U33. You want to copy
this down and change the 1 to 2, 3 or 4. Alternatively, you can amend
the formula to:

=SMALL(Sheet1!U$4:U$33,ROW(A1))

then as you copy this down you will get the increment automatically.
You would then make use of a VLOOKUP formula (or probably INDEX/MATCH
in your case) in order to bring other related data over to the sorted
sheet.

Hope this helps.

Pete