help

[repoman]

Hi,



I Run a indoor cricket team and I taught myself how 2 use the basic functions of excel so I could give the guys some statistics each week to ensure that they all stay competitive right to the end of the games.



I have used a few basic functions but I am having trouble working out how to use the rank formula

I have a column that I am hoping that will be able to constantly rank the guys batting, bowling and fielding but I cant understand what excel is

Referring to when it says



Number

Ref

Order



Sorry to hassle you could you possibly tell me how to use the rank formula

I have attached the document and hope that you have the time to look at it



Yours Sincerely

Alex

[Paul B]

To use rank
If the numbers you want to rank are in A1:A5 in another column put =RANK(A1,$A$1:$A$5) and copy down

[Yogi Anand]

Hi Alex:
To add to Paul's suggestion on using the RANK function ...
say if you had the following in cells A1:A5

42
105
13
9
72
then the formula =RANK(A1,$A$1:SA$5) say in cell B1 will give you 3, and then replicating that formula in B1:B5 will give you the following corresponding ranks:

3
1
4
5
2

If you wanted to dynamically sort the batting averages from top to bottom, use the following array formula in say cell C1

=LARGE(A1:A5,ROW(1:5))
click in cell C1, highlite C1:C5 and CTRL+SHIFT+ENTER

You will see the following in cells C1:c5

105
72
42
13
9

If any number changes in A1:A5, C1:C5 will dynamically adjust to retain the descending order.

Hope This Helps

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

[repoman]

excuse my stupidity but i need further help
with the formula that you gave me
=RANK(A1,$A$1:SA$5)

rank(number,ref,([order])

Can you please expain how the syntax works. I'll explain what i have grasped so far

A1(ref) are the cells of all the battihng scores

$a$1(ref) I still dont understand

:SA$5(Order) I am not following.

In my excel doc the batting scores cells are
c19,f19,i19,l19,o19,r19,u19,x19,ab19

Where i want them ranked from 1 - 9 is
c23,f23,i23,l23,o23,r23,u23,x23,ab23

Sorry to be a hassle
Alex

[anno]

alex
probably the easiest way to explain how this works is to get you to actually do it.

using your example, in cell C23 put =RANK(C19,battingscores,1). ‘battingscores’ is a named range you’ll have to create (i’ll explain how in a minute)which will consist of cells C19,F19,I19,L19,O19,R19,U19,X19,AB19. the reason you have to do it this way is because if you try =RANK(C19,C19,F19,I19,L19,O19,R19,U19,X19,AB19,1) you’ll get an error. it would be easier if your scores were in a continuous range of cells, but no matter. anyway, to continue, in cell F23, put =RANK(F19,battingscores,1). In cell I23, put =RANK(L19,battingscores,1), etc. you get the idea.

to create the range called battingscores (actually you can call it what you want as long as there’s no spaces or some characters – “&”,”#” and similar), first select the cells C19,F19,I19,L19,O19,R19,U19,X19,AB19. to do this select the first cell, then hold down the ctrl key and select all the others in turn. with these cells selected, go to the name box to the left of the formula bar (it should have AB19 in it, the last cell you selected) click and type ‘battingscores’ (without the quotes) and press enter. this is probably getting a bit more complicated than what you expected but I couldn’t think of another way to do it.

to explain the formula, the ‘C19’ is the cell reference you want to rank (the first batting score), ‘battingscores’ refers to the range of cells (all of the batting scores) you want to rank C19 against – “compare this score with all these scores and tell me where it ranks”. the 1 is not necessary but makes the ranking in ascending order. Leaving it out will make the formula rank in descending order.
hope this helps