• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help on Formula

Felix

Member
We need a formula(s) that can reflect a person's position compared to his team in different categories, e.g. 1st place(1), 2nd place(3), 3rd place(3) forth place(4), etc...
In our sample file we show that GOLF won the 1st place for COUNT category, and MAURICE was 10th place.

How could we automatically have all of these results displayed using the data from the top table?

Thanks.
 

Attachments

  • COMPARISSON.xlsx
    13.7 KB · Views: 0
Hi

I didn't care about duplicates, so this is managed as default for function Rank.EQ
hello sir,
Can i know and understand y u had used 2 commas here in between the formulae...as i had also tried but only with the single comma but it is not working...what is the reason for it??
RANK(INDEX($C$4:$R$13,MATCH(C$15,$B$4:$B$13,0),MATCH($B16,$C$3:$R$3,0)),INDEX($C$4:$R$13,,MATCH($B16,$C$3:$R$3,0)))

regards,
Jaya
 
PERFECT!!! EXACTLY WHAT WE NEEDED!!! THANK YOU FOR THE QUICK REPLY.

Hi

Yes of course you need to know:)

This first parameter in function Rank delivers a single value. So e.g. for cell c16 it would be 607

INDEX($C$4:$R$13,MATCH(C$15,$B$4:$B$13,0),MATCH($B16,$C$3:$R$3,0))

the second parameter needs to contain array of values, in which we rank the value from first parameter . For c16 cell the array is {607;591;588;470;659;515;390;416;427;329}

INDEX($C$4:$R$13,,MATCH($B16,$C$3:$R$3,0)))

And What you are asking about ,so between two commas is actully omitted parameter rows. What this means in case c16 is that formula looks on array

$C$4:$R$13

will look on column 1 which is result of this MATCH($B16,$C$3:$R$3,0)

and take values of all rows in that column {607;591;588;470;659;515;390;416;427;329} because the value of rows parameter is ommited.

I think this is like specific property of index function if you leave out a parameter
 
hello sir,

And hearty thanks for ur explanations one more doubt/query u can say...do we need to follow in every case/time if v are using index and nested match function anywhere in need..??

Regards,
Jaya
 
I dont understand exactly what you are asking . If you ask about parameter "row" which I left out. This is mandatory parameter and options are:

1. you pass a specific number value e.g. 2
2. you can pass function and have the value as a result of function dynamic- most of time match is used
3. or you leave parameter empty as I did a this means take data from all rows in column number which you pass in another parameter.

Of course ask if anything not clear
 
I dont understand exactly what you are asking . If you ask about parameter "row" which I left out. This is mandatory parameter and options are:

1. you pass a specific number value e.g. 2
2. you can pass function and have the value as a result of function dynamic- most of time match is used
3. or you leave parameter empty as I did a this means take data from all rows in column number which you pass in another parameter.

Of course ask if anything not clear
I mean to ask according to requirement we need to follow ,, thing or else every time if v are using nested match
 
Back
Top