• 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.

IF a cell contains largest value

Busymanjohn

Busymanjohn

Member
Hi guys, I have a table of data, 12 x 12 that contain numbers, what I want to do in column to the right of the data is have a formula which returns a 1, 2, 3 ( thru 12 )if a cell within the table contains the highest value, second highest value etc ... so if Col C had a 10 cells that are zero and two cells which contained the value 5 and 4, the column to the right would show a 1 for the corresponding cell in column C ( i.e. that contains the value 5 ,, and similarly, a value of 2 for the corresponding cell in column C for the value 4 ,,, make sense?
 
Hi John,


try this formula


'=(LARGE($A$4:$L$4,1)=A$4)*1+(LARGE($A$4:$L$4,2)=A$4)*2+(LARGE($A$4:$L$4,3)=A$4)*3+(LARGE($A$4:$L$4,4)=A$4)*4+(LARGE($A$4:$L$4,5)=A$4)*5+(LARGE($A$4:$L$4,6)=A$4)*6+(LARGE($A$4:$L$4,7)=A$4)*7+(LARGE($A$4:$L$4,8)=A$4)*8+(LARGE($A$4:$L$4,9)=A$4)*9+(LARGE($A$4:$L$4,10)=A$4)*10+(LARGE($A$4:$L$4,11)=A$4)*11+(LARGE($A$4:$L$4,12)=A$4)*12


this assumes you are testing row 4


Cheers

kanti
 
Hi, I have loaded a sample file in the below link .... the results I want to achieve are in column N.


http://www.mediafire.com/view/?rp3427olav09n61
 
Hi Faseeh,


I was also looking the file but could not see any value at N1 as you have pointed out. Do you mean to say 8 at N3. If yes, then below could be the possible explanation of 8 being at N3.


In Col C there tow values 5 (C6) and 4 (C11). Hence, the corresponding cells at N6 and N11 contain 1 and 2 respectively.


In Col D there is only one value 4 at D9 and hence the corresponding value at N9 is 3.

Values in Col E are zero.


Col F contains 4 at F14. Hence, the next available rank is 4 at N14.


Col G contains three 4s at row 5,7 and 12. Since all the values are same, the corresponding cells at N4, N7 and N12 are having the same (next) available rank as 5.

Col H contains 4 at row 4. Hence, the next available rank at N4 is 6.


Values in Col I are zero.


Col J contains 6 at row 10. Hence, the next available rank at N10 is 7.

Col k contains two 4s at row 3 and 8. Since all the values are same , hence, the next available rank is 8 at N3 and N8


Col L contains 5 at row 13. Hence, N13 contains 9 as next available rank.


Regards,

Kaushik
 
Hi Faseeh, the numbers in Col N relate to the position of each contestant after scores have been added up ,,,, in this example though there are some which have the same score, so N6 = 1 means 1st place, N11 = 2nd place etc etc ,, so N1 =8th place as does N8 as they both had the same score. The formula I need ( rather than do this manually as this is just a sample of the data )would be to return the same results in col N but using a formula.
 
Hi ,


Can you check the file here ?


http://speedy.sh/2Wp2a/Test-ScoringV2.xlsx


I have not been able to maintain the numbers consecutively , but the order is correct. Is this acceptable ?


Narayan
 
perhaps, im not following..... but could you just use the Rank function?!


If your values are in A1:A12 the formula would look like: '=RANK(A1,$A$1:$A$12)'


i tried it out and it seems to be working... but maybe i'm not understanding your question/need.
 
You must log in or register to reply here.
Back
Top