I am trying to rank 7 restaurants based on their food cost %.  Lowest
comes first.   Result will be pasted into another area (in the same
worksheet).  Basically, there are 2 cols. - one for the name of the
restaurants 1to7 and one for the food cost %.
Result will also be 2 cols.  I will need a excel macro or whatever
method in excel to make this work.  Thanks

Hello Dreamangel247

This is easily set up without a macro but it is quite difficult to
explain using only the text we have available here (unfortunately we
cannot add attachments for you to download) so if you have any
questions please ask for clarification.

I will first of all describe how the spreadsheet is set up in order
for this solution to work.  In your spreadsheet you may need to alter
some of the letters in the formulae used.

Set up of the Excel spreadsheet:
1) Column A contains the list of names of the restaurants (starting at
cell A1 until cell A7)
2) Column B contains the food cost % (starting at cell B1 until cell B7)

With this set up you now need to click on cell C1 and type this into the cell:

=RANK(B1,$B$1:$B$7)

Now select cell C1 and press Ctrl-C (to copy the cell and its
formula).  Highlight cells C1 to C7 inclusive and press Ctrl-V to
paste the formulae into the cells.  You should now have a number from
1 to 7 in each cell.  These are the results ranked in order from
largest to smallest.

To now put that in the correct order you should click on cell E1 and type this:

=OFFSET(A$1,MATCH(SMALL(C$1:C$7,ROW()-ROW(C$1)+1),C$1:C$7,0)-1,0)

In cell F1 you should type this:

=OFFSET(B$1,MATCH(SMALL(C$1:C$7,ROW()-ROW(C$1)+1),C$1:C$7,0)-1,0)

Now select or highlight cells E1 and F1 and copy and paste them for
the other rows as you did previously above using Ctrl-C and Ctrl-V.

You should now have a list of the cells in the correct order starting at cell E1.  

If this is not the case please check these items:
1) If the locations of the lists for your data are different you will
need to slightly alter the formulae used but hopefully this is self
evident to you.
2) To double check the formulae once you have typed them in, they need to be exact!

If you have any questions or queries regarding this please ask for
clarification.  In this event please let me know the location in the
spreadsheet of the restaurant names and the food cost % (ie which are
in which cell numbers).

---

Request for Answer Clarification by dreamangel247-ga on 28 Jun 2004 10:14 PDT

Great that works, one small favor though.  Right now the restaurants
are ranking from correctly but with the lowest food cost % on the
bottom.  How do I put it on top (put the order from smallest to
largest - from top to bottom)?
In reality, the list will be more than 50 restaurants but I can figure
it out from here.  Thanks.

---

Clarification of Answer by palitoy-ga on 28 Jun 2004 11:05 PDT

To rank the restaurants in the other order simply change the =RANK
line in the answer above to:

=RANK(B1,$B$1:$B$7,1)

This should then be copied down the rest of the column as you followed
before so each =RANK line ends in ,1)

If you need anything else please ask for clarification and I will try
my best to help you.

Totally awesome.  Thank you for the great answer.  You explain it so
well, it seems almost easy...Thanks.

Thanks for the 5-star rating, tip and kind words, they are much appreciated.