1

I'm trying to set up an AP Top 25 style poll via Google Sheets ( https://en.wikipedia.org/wiki/AP_Poll ). What I want to do is list the voters' choices on one sheet, and have them totaled and sorted on another. I'll set up a set of examples to describe what I'm trying to do.

Under this example voting scheme, a school gets 5 points for a 1st place vote, four for 2nd, three for 3rd place, two points for 4th place, and one point for 5th. Let's say I manually enter three sets of votes as such:

enter image description here

Under this setup, Alabama should have 11 points, Georgia 8 points, Clemson 10 points, Ohio State 9 points, and Washington 7 points. What I want to do is have another tab of the same sheet calculate the point totals of each school based on which cell they're placed in, display that point total, and sort itself from the greatest number of points to least number of points.

Now, I already understand some basic google sheets functions like sum, product, etc. What I'd like to do is have the "totals" tab populate automatically based on whats entered on the votes tab, without having to type out each individual school. So I need a way for the "totals" tab to look at the first entry in B2 on the votes tab, see "Alabama", make its own entry for "Alabama" on the totals tab, and then add the score for each subsequent entry for "Alabama" on the votes tab based on what column position its in in its respective row. Then do the same for any other unique school entry on the votes tab. Am I making sense?

0

assuming your dataset is in this format at same columns under Sheet1:

you can add +1 sheet with calculations where:

A2: =ARRAYFORMULA(IF(Sheet1!B2:B="";;Sheet1!B2:B&"_"&ROUNDUP(COLUMN(Sheet1!B2:B)+3)))
B2: =ARRAYFORMULA(IF(Sheet1!C2:C="";;Sheet1!C2:C&"_"&ROUNDUP(COLUMN(Sheet1!C2:C)+1)))
C2: =ARRAYFORMULA(IF(Sheet1!D2:D="";;Sheet1!D2:D&"_"&ROUNDUP(COLUMN(Sheet1!D2:D)-1)))
D2: =ARRAYFORMULA(IF(Sheet1!E2:E="";;Sheet1!E2:E&"_"&ROUNDUP(COLUMN(Sheet1!E2:E)-3)))
E2: =ARRAYFORMULA(IF(Sheet1!F2:F="";;Sheet1!F2:F&"_"&ROUNDUP(COLUMN(Sheet1!F2:F)-5)))

which will assign points based on your request:

1st place = 5 points
2nd place = 4 points
3rd place = 3 points
4th place = 2 points
5th place = 1 point

next step is sorting all of it under one column with formula:

F2: =SORT({A2:A; B2:B; C2:C; D2:D; E2:E}; 1; 1)

then you need to split the whole column into two columns (one for participants and another for points) with a formula:

G2: =IFERROR(ArrayFormula(SPLIT(F2:F;"_";1;1));)

in the next step, you need to create a feeding ground for =QUERY with a simple formula:

I2: =UNIQUE(G2:G)

now you can feed =QUERY from a range G2:H with unique values from I column by using a formula (which needs to be dragged all the way down):

J2: =IFERROR(QUERY($G$2:$H;"select G, sum(H) where G = '"&I2&"' group by G label sum(H)''");)

and now you can use one more =QUERY formula to get your desired sorted data:

=QUERY(calculations!J2:K;"select * where J is not null order by K desc")

summary:

and here is the whole sheet: https://docs.google.com/spreadsheets/d/

  • I appreciate all your work! It's definitely explained a lot to me, but I'm not sure if it is workable in my situation. The example I used is just an example. For the actual project, I'll have to account for around 140 schools, 20-30 voters, and 25 selections per voter. If we assumed that the sheet did NOT have to dynamically make a new entry for each unique term it found (for example if I were to add schools manually), would that simplify anything? Is there a way to have a function just search the range of each column and assign X number of points to each term it found in that column. – KingKenny04 Aug 27 '18 at 1:22
  • @KingKenny04 well not sure how you couldn't notice but the sheet does exactly that - it searches for unique terms and assigns X points based on provided criteria. so 140 schools are not a problem (it can be even automatized so you wont need to manually type it). 20-30 voters - not a problem. 25 selection per voter - not a problem (its just a question of adding more columns and expanding first five columns on calculation sheet with 20 more columns). the question is if the ranging system stays same (1st-5p, 2nd-4p,etc) or you have alternative system in your mind for that – user0 Aug 27 '18 at 8:28
  • @KingKenny04 here: docs.google.com/spreadsheets/d/… I unlocked few cells so you can edit/test this demo sheet and see how it works and what it does. try to type voter's choices or even add new voters... just play with it – user0 Aug 27 '18 at 8:42
  • @KingKenny04 I've added new sheet with "valid" choices: docs.google.com/spreadsheets/d/… see how it affects voter's tab cells B9, C9, D9... – user0 Aug 27 '18 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.