# identifying varying data and rank formula

1. ## identifying varying data and rank formula

i am trying to work out how to use the rank formula to rank numbers in column B and keep them in unison with Column A.
So Column A has say 5 1's with column B having different scores then continuing under 1 in A is 2 and so on is there a way to continue the ranking formula without manually changing the cell ranges ??

so =rank(B1,\$B\$1:\$B\$7,1) but can i do that if A =1 and then A=2 etc ?
so if A=1,rank(B1,\$B\$1:\$B\$7,1)

thanks heaps  Register To Reply

2. ## Re: identifying varying data and rank formula

To rank within each category in column A try this formula in C1 copied down

=SUMPRODUCT((A1=A\$1:A\$24)*(B1>B\$1:B\$24))+1

...or in Excel 2007 only you can use COUNTIFS, i.e.

=COUNTIFS(A:A,A1,B:B,"<"&B1)+1  Register To Reply

3. ## Re: identifying varying data and rank formula

looks good thanks so much !
just 1 more thing - how can i get the inverse rankings with that formula?
generally determined by 1 or 0 but im not sure with how you have done it.

thanks again  Register To Reply

4. ## Re: identifying varying data and rank formula

...how can i get the inverse rankings with that formula?
in either case (SUMPRODUCT / COUNTIFS) change the operator from > to <  Register To Reply

5. ## Re: identifying varying data and rank formula

donkeyoye,

thank you also, greatly appreciated!  Register To Reply

6. ## Re: identifying varying data and rank formula

guys running what you said was perfect but to my dismay i then come up with a problem where further down column A the numbers 1,2 etc reappear so then that formula runs into the error that it takes into account the above data.

could i possibly include say another column C that includes words and do these words always have to be the same?
and what about if 2 words start with first same letter eg mother and the other was maiden etc?

thanks again  Register To Reply

7. ## Re: identifying varying data and rank formula

A COUNTIFS function can have numerous criteria tests so yes,

E1: =COUNTIFS(A:A,A1,B:B,"<"&B1,C:C,C1)+1

(on an aside although COUNTIFS is pretty efficient I wouldn't advise using entire column references unless really necessary)

Re: partial matches - you can use Wildcards in COUNTIFS but we'd need more info - I'm not sure I follow exactly, dll might though!  Register To Reply

8. ## Re: identifying varying data and rank formula

donkeyote,
no by testing from my page im pretty sure that will work perfectly.
once again i thank you very much   Register To Reply