Excel Rankings function

Hello all.

I am at a impasse with trying to use the Rank function in Excel. Basically, I'm lost. I'm trying to have excel rank a column of numbers. The rank goes below each number--such as: (The rankings are supposed to be centered below the number)
17.5---17.8
--1-------1
17.4---17.7
--2------2
I had a person formulate them for rank a set of 12 numbers. Unfortunately, that person passed away before he
could finish this project. I the need the formula to rank up to 60 numbers. I've tried following the functions he did, but I get lost very easily. I'm not very well versed in Excel.
Please contact me if you can help me with this. I really would appreciate this. I can also email you the formula that I have so I can show you what I have so far.
Thanks in advance.

Gregg

Crash1 wrote...
>I am at a impasse with trying to use the Rank function in Excel.
>Basically, I'm lost. I'm trying to have excel rank a column of numbers.
....

If the column of numbers were A1:A20 containing something like

19.3
16.2
16.9
16.0
19.8
16.6
17.9
18.0
19.7
18.0
18.8
17.3
17.7
18.5
18.0
18.7
19.4
16.4
19.1
16.9

Then rank them in B1:B20 using formulas like this in B1

=RANK(A1,A$1:A$20)

Then fill B1 down into B2:B20. Multiple instances of the same number in
column A have the same rank. A1:B20 is now a table of rankings.

Your example was inconsistent with your description above. Are the
values to be ranked in a column or in a row? All together in a *single*
column or row or spread between multiple columns or rows?

Hopefully this helps

------Colm 1----Colm 2
Score--17.5------17.8
Rank-----1---------1
--------17.6------17.7
-----------2---------2

Each column would be ranked seperately. There would be multiple columns involved on a given sheet.
Go to the following link--this should give you a better idea of what I'm looking for. http://www.fromthepressbox.com/20060728dcimasters.htm
The columns would eventually rank up to 60 numbers in a
given column. Also, I would sometimes need to delete and change some of the column names. When I've done this in the past, it screws up the ranking formulas. Is there a way around that?

Thanks again for your help.
Gregg

Crash1 wrote...
>Hopefully this helps
>
>------Colm 1----Colm 2
>Score--17.5------17.8
>Rank-----1---------1
>--------17.6------17.7
>-----------2---------2
>
>Each column would be ranked seperately. There would be multiple columns
>involved on a given sheet.

No, it doesn't help. Not enough detail. Are there multiple rows of
scores and ranks? If so, how are the scores placed into their different
rows?

>Go to the following link--this should give you a better idea of what
>I'm looking for. http://www.fromthepressbox.com/20060728dcimasters.htm

Sorry. I'm not getting paid for this. I don't try to decipher pictures,
and I don't open strangers' workbooks. You'll need to describe the
problem in detail in plain text if you want help.

Your setup is screwed up.

You should (at least try to) set it up like this then it would be very
simple.

.....A...........B..........C...........D.............E
Score......17.5......Rank.....17.8........Rank
................17.6......Rank.....17.7........Rank
................17.9......Rank.....17.5........Rank

Biff

"Crash1" <[email protected]> wrote in
message news:[email protected]...
>
> Hopefully this helps
>
> ------Colm 1----Colm 2
> Score--17.5------17.8
> Rank-----1---------1
> --------17.6------17.7
> -----------2---------2
>
> Each column would be ranked seperately. There would be multiple columns
> involved on a given sheet.
> Go to the following link--this should give you a better idea of what
> I'm looking for. http://www.fromthepressbox.com/20060728dcimasters.htm
> The columns would eventually rank up to 60 numbers in a
> given column. Also, I would sometimes need to delete and change some
> of the column names. When I've done this in the past, it screws up the
> ranking formulas. Is there a way around that?
>
> Thanks again for your help.
> Gregg
>
>
> --
> Crash1
> ------------------------------------------------------------------------
> Crash1's Profile:
> http://www.excelforum.com/member.php...o&userid=36727
> View this thread: http://www.excelforum.com/showthread...hreadid=564548
>

Sorry. I'm not getting paid for this. I don't try to decipher pictures, and I don't open strangers' workbooks. You'll need to describe the problem in detail in plain text if you want help.

Harlan, You arrogant a--!! No kidding your not getting paid for this. I just wanted a little help with this and you give me this kind of attitude? Thanks for nothing. It's not a workbook--it's a web page genius. There's nothing on there would hurt your crappy little computer. It's an example of how the page looks. Have fun with your blow up doll later tonight.

---------
Biff,
My excel sheet orginally had the ranking on the side. But because some of my other worksheets would get pretty long going accross with the columns, I thought it might be easier to try the rankings this way. I never thought it would be this much of a hassle. Then again, I never thought I had to deal with pricks like Harlan.

Crash1 wrote...
>SORRY.
....

Yes, you are a sorry excuse. Have fun through life paying for
programming since you're incapable of providing meaningful
explanations. Get used to paying for the first few hours of billed time
figuring out WTF you mean.

Why would I pay for it? I thought coming here I could get some free help. I didn't know I would be slammed by "guy" named Harlan. Now I've reached a new low. I guess the part of you went running down the sides of you mother's thighs when you were conceived.

What's so difficult about understanding this? The top row is the number. The row below it is the rank for the number on top. And this continues down the row. The next column is the next set of numbers with the rank below each row.
Number--than rank. That's all there is to it. Have fun on your next victim.

Crash1 wrote...
>Why would I pay for it? . . .

Because in the Excel equivalent to your love life, your only
alternative is your right hand, and you're left-handed.

>What's so difficult about understanding this? The top row is the
>number. The row below it is the rank for the number on top. And this
>continues down the row. The next column is the next set of numbers
>with the rank below each row.

Rows are horizontal. They go left to right, not up and down. Presumably
you mean it goes down the COLUMN, and it seems different columns
represent independent data to rank separately. If so, an awkward
layout, as you've discovered.

You'd be FAR BETTER OFF storing the data in simple tables with no
interleaved ranks, just data in each row with one data point following
the next in the next row. Then you'd need to pull them into the
interleaved display. If the data were in B2:B9 and the B2 value would
be displayed in X99, the X99 formula would be

=INDEX(B$2:B$9,(ROWS(X$99:X100)/2)

Copy X99 and paste into X101, X103, . . ., X113. The rankings would
begin in X100 with

=RANK(X99,B$2:B$9)

Copy X100 and paste into X102, X104, . . ., X114.

If you just have to enter the data in nonadjacent cells with
intervening ranks between data values, it can be done. It requires
using FREQUENCY, INDEX and SUMPRODUCT functions and multiple area
ranges. However, it's such a bad idea someone else would have to
provide the details. And if you don't think I know how to do this, I'll
post it somewhere else and provide a link to it after the weekend.