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

Rating/Ranking System

N

navinsq

New Member
Hi.. I am currently trying to finish a rating/ranking system for squash in excel. It has over 100 names (C13:C147) and it works fine for one direct entry for the rating (D13:D147) for each player and it gets sorted and rearranged in descending order.


I want to update it every week, firstly by calculating how many points a player earns on that day into one particular cell in each row (Q13:Q147)-(depending on how many games won/lost + any bonus/penalty points for each match played), and then by adding the points in that particular cell (Q13:Q147) to their rating (D13:D147) to get an updated rating.


Then I would like to automatically clear cell contents in (Q13:Q147) and retain the updated ratings in (D13:D147) so that I could do the same thing the following week.


How do I do this particular part?


Thanks!


Navin
 
Hi navinsq,


Welcome to the Forums, Can't you dump your data in one master sheet and retrieve only the updated one from it as the final result? Can you upload a sample file?


Faseeh
 
Hi Faseeh,


I don't quite understand what you mean.


The sample file is at

https://docs.google.com/file/d/0B4kPRTo-4Sd2T3pwU3FNVmtQNnc/edit?usp=sharing


Go through the whole thing carefully so that you would understand what I want to do. The ranking & sorting is already done. It's the calculations (columns H to Q) and updating of the ratings (columns D,E,F) that need attention.


Thanks
 
Hi navinsq,


Can you see the following file and comment on the concept i am following??


http://dl.dropbox.com/u/60644346/Squash%20Score.xlsx


Regards,
 
Hi Faseeh,


I understand what you are trying to do... but how would I update for the 2nd week, 3rd week, and so on, on the same column (column D) while keeping the updated rating from the previous week?


In addition, your formula is "subtracting the points that should be added" and "adding the points that should be subtracted" --> {=C2-SUM(IF(E2:F2=K10:K17,L10:L17))}


And also, the rows that follow are:


{=C3-SUM(IF(E3:F3=K11:K18,L11:L18))}

{=C4-SUM(IF(E4:F4=K12:K19,L12:L19))}

{=C5-SUM(IF(E5:F5=K13:K20,L13:L20))} etc.


This is moving out of the "range" of the table.


I hope you are clear about what my requirement is :)


Thanks
 
Hi navinsq,


There might be some error in formula but concept is more important, the formula will show the updated status for the latest week in a column based on the score that is updated for the latest match result...i will revert tomorw with an example...its already 1 am.


Faseeh
 
Hi Faseeh,


I agree that the concept is more important.


The formula does show the updated status for one week.. but then I would have to have a new rating column for every new week and also new columns for every new match played, which I have to do manually. I can do that already and its time consuming.


I want to automate this process with a maximum of three matches being updated at a time, and one column for the rating.


Once the rating is updated I want the contents in the three results columns to be cleared automatically, but without losing the updated rating.. so that this process could be repeated over and over again.


Hope you could do this.


Thanks
 
Hi navinsq,


Please try replacing the previous formula with this one:


=C3+SUM(IF($F3:$K3=$D$12:$D$19,$F$12:$F$19))


Enter with CSE. I will upload a sample file soon.


Faseeh
 
Hi Navin ,


I think that any worksheet which needs to be updated , and needs to retain its updated status while the input data changes , cannot be created using only formulae ; any formula will always give the current status , and cannot keep track of earlier statuses and their changes.


If I have understood your requirement correctly , it can only be done using VBA.


Narayan
 
Faseeh - I replaced it with your formula and it works.. but only for one week.. do you get my point?


Narayan - Could you please show me how to do it using VBA?


Thanks!
 
Hi navinsq,


Please see this file, if you keep the formula dragging to the right, it will keep you giving updated scores.


Please inform me for right/wrong working of the sheet.


http://dl.dropbox.com/u/60644346/Squash%20Score.xlsx


Faseeh
 
You must log in or register to reply here.
Back
Top