Excel Rankings function

[Crash1]

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 t
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 6
numbers. I've tried following the functions he did, but I get los
very easily. I'm not very well versed in Excel.
Please contact me if you can help me with this. I really woul
appreciate this. I can also email you the formula that I have so I ca
show you what I have so far.
Thanks in advance.

Greg

 

[Harlan Grove]

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?

 

[Crash1]

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

 

[Harlan Grove]

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.

 

[Biff]

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]

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.

 

[Harlan Grove]

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.

 

[Crash1]

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

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

 

[Harlan Grove]

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.