Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Dragging Excel Ranking Function - Help please


  • Please log in to reply

#1
PGH

PGH

    Member

  • Member
  • PipPip
  • 36 posts
Hi,

I have a list of 40 numbers in Excel (2002), and what I want Excel to do in an adjacent column is give each of the 40 original values a rank number (eg. put a 1 next to the higest number out of the 40, and a 40 next to the lowest - or maybe not a 40 if there are duplicates...)

Anyway I am using the formula =Rank(number, array, order), which I hope is right for what I want to do, however I am having problems when I drag this forumla down the column as the wrong parts of it automatically change.

To start with I want to find the rank of A1 out of A1:A40, and I set the order to 0, so I have this:

=Rank(A1,A1:A40,0) and it works fine and tells me how this number is ranked out of A1-A40

however I want to find the rank of all the numbers A1-A40 out of A1:A40, but when I drag the forumla down the column it goes wrong.

I want the forumla to do this

=Rank(A1,A1:A40,0)
=Rank(A2,A1:A40,0)
=Rank(A3,A1:A40,0)
=Rank(A4,A1:A40,0) etc.

but not just the number, but also the array changes. In reality it does this:

=Rank(A1,A1:A40,0)
=Rank(A2,A2:A41,0)
=Rank(A3,A3:A42,0)
=Rank(A4,A4:A43,0)

Is it possible to set it so only the first part of the forumla changes, and the array from which it is taking the rank stays the same - so all the numbers can be ranked out of all the numbers, and not some strage moving rank is acheived?

Thanks so much :tazz:

Edited by PGH, 17 February 2006 - 12:12 PM.

  • 0

Advertisements


#2
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
i think if ou put a $ before the array...like this

=Rank(A1,$A1:$A40,0)

it should do it...
  • 0

#3
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
nope...just tried it
  • 0

#4
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
got it

=Rank(A1,$A$1:$A$40,0)
whatever you put a $ before will stay the same in the formula...so if you just want the collumn to stay the same...put the $ before the a...if you want the fixed cell you have to put it between both
  • 0

#5
PGH

PGH

    Member

  • Topic Starter
  • Member
  • PipPip
  • 36 posts
That is great, thank you very much!
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP