Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #496 Apr 7, 2012 www.energyefficientbuild.com
user said:
How do I exclude cells that do not exceed a minimum threshold from a sorted ranking? I created a data sheet that pulls scores from individual games and tallies them into another sheet into games played, wins, losses, winning percentage, average score, rank based on winning percentage, rank based on average score, and a final rank averaging the last two. I have it essentially automated to where I just enter each score from the new week for each player and the formulas take care of the rest. From there, I used an array formula that automatically sorts these ranks onto another sheet from top to bottom. I want to also be able to have another sheet in which the ranks only include players who have 25% or more of the total games played (i.e.- out of a possible 12 games played, all players with at least 3 games played are included in the ranks, whereas 2 and less are not). I figure (if I have to go this route) that I can tally the total games played by having a hidden row representing each possible game with a "1" and having a cell that adds those up to represent total games played, and then have some type of "ifsum" or "ifcount" formula that refers to the cell with the total number of games played; however, I have been unable to quite make that work. Is there a way for me to exclude players below the 25% games played threshold from the rankings while eventually allowing them to reappear in the rankings once their games played exceeds 25%? I am trying to keep this as automated as possible.
I've tried using the query formula =query(Stats!A3:C75;"select * where C >= 5 order by A asc")
and got it to work somewhat for what I am trying to do. I was hoping to be able, to instead of using a 5 or any actual number, to use a percentage of another cell that has the total games played so I do not have to change it every time I update. That does not seem to work with this formula. Also, I have been trying to re-rank the results after it removes players who have less than the allowed minimum of games played (in the formula provided above in my spreadsheet called Test, which currently has a minimum of 5); however, it does not re-calculate the rank after I change the number (say, from 5 to 6 and so on). The Adjusted Rank sheet is where I tried and failed to dabble with this, so I kept the minimum at 1 to hold all of the players with their rank, because it will only hold the rank when every player is included. Might there be another formula that I can use to make this work or am I just doing something wrong? The spreadsheet is below. https://docs.google.com/spreadsheet/ccc?pli=1&key=0AqLllteL5Z8DdDZtSFU1UzNId2VLREtqNUpCVDIyRGc#gid=4 Thanks for any help!
---------------------------------------------------------------
following is a solution to the problem
user said:
How do I exclude cells that do not exceed a minimum threshold from a sorted ranking? I created a data sheet that pulls scores from individual games and tallies them into another sheet into games played, wins, losses, winning percentage, average score, rank based on winning percentage, rank based on average score, and a final rank averaging the last two. I have it essentially automated to where I just enter each score from the new week for each player and the formulas take care of the rest. From there, I used an array formula that automatically sorts these ranks onto another sheet from top to bottom. I want to also be able to have another sheet in which the ranks only include players who have 25% or more of the total games played (i.e.- out of a possible 12 games played, all players with at least 3 games played are included in the ranks, whereas 2 and less are not). I figure (if I have to go this route) that I can tally the total games played by having a hidden row representing each possible game with a "1" and having a cell that adds those up to represent total games played, and then have some type of "ifsum" or "ifcount" formula that refers to the cell with the total number of games played; however, I have been unable to quite make that work. Is there a way for me to exclude players below the 25% games played threshold from the rankings while eventually allowing them to reappear in the rankings once their games played exceeds 25%? I am trying to keep this as automated as possible.
I've tried using the query formula =query(Stats!A3:C75;"select * where C >= 5 order by A asc")
and got it to work somewhat for what I am trying to do. I was hoping to be able, to instead of using a 5 or any actual number, to use a percentage of another cell that has the total games played so I do not have to change it every time I update. That does not seem to work with this formula. Also, I have been trying to re-rank the results after it removes players who have less than the allowed minimum of games played (in the formula provided above in my spreadsheet called Test, which currently has a minimum of 5); however, it does not re-calculate the rank after I change the number (say, from 5 to 6 and so on). The Adjusted Rank sheet is where I tried and failed to dabble with this, so I kept the minimum at 1 to hold all of the players with their rank, because it will only hold the rank when every player is included. Might there be another formula that I can use to make this work or am I just doing something wrong? The spreadsheet is below. https://docs.google.com/spreadsheet/ccc?pli=1&key=0AqLllteL5Z8DdDZtSFU1UzNId2VLREtqNUpCVDIyRGc#gid=4 Thanks for any help!
---------------------------------------------------------------
following is a solution to the problem
No comments:
Post a Comment