The section in blue, labeled “step 2” is where we convert their top ten rankings to a point system. So if someone were ranked as a person’s number 1 choice it would convert to 10 points, number 2 is 9 points, etc. and we use this section to get a sum of their total score. I would like to have this section automatically pull the rankings from section 1 and convert to the correct points, so the formula would be “if a 1 appears in specified cell, a 10 appears in this cell” and then also have a formula set up to total the points across the row.
The “sum” of their total score is then moved down to Step 3 and a weighted average is calculated – meaning their sum is divided by the total number of selection committee members…and a column is also added that shows the number of times (“count”) they appeared in someone’s top 10. I would like for the sum to automatically flow down from step 2 to step 3, and have the weighted average and count automatically tabulate from the previous data entered.
Right now, this spreadsheet is not set up for all those numbers to flow automatically from one step to the other and I need help on setting it up so we have a template that does that