google spreadsheet - Filter to the latest month and then filter to the best score per person -
i've got google sheet holds results of monthly competition. format is
name | date | score -------------------------------- alan smith | 14/01/2016 | 500 bob dow | 14/01/2016 | 450 bob dow | 16/01/2016 | 470 clare allie| 16/01/2016 | 550 declan ham | 16/01/2016 | 350 alan smith | 10/02/2016 | 490 bob dow | 10/02/2016 | 425 declan ham | 12/02/2016 | 400 declan ham | 12/02/2016 | 390 clare allie| 12/02/2016 | 560 i want 2 things data
i want create new sheet holds latest 'best' results. data presented here be
alan smith | 10/02/2016 | 490 bob dow | 10/02/2016 | 425 declan ham | 12/02/2016 | 400 clare allie| 12/02/2016 | 560i.e. results february 'best' score per person. here declan ham's lower score of '390' removed.
i want sheet hold tournament ranking. people ranked top 3 monthly scores. i.e. best score each person each month obtained , top 3 scores combined give place in tournament.
so far i've attempted use google queries, vlookups, filters these new sheets. but, focusing on 1), best i've been able achieve is
=filter(results!$a:$b, month(results!$b:$b) = month(max(results!$b:$b))) which me results latest month. not remove duplicates entries people.
does have suggestion how can achieve these requirements? feel i'm treading water @ moment.
rather trying remove duplicates, need identify maximum score each person; can grouping values person, aggregating using max(). here's how look, month of february 2016:
=query(results!a1:c,"select a,max(c) todate(b) > date '2016-2-1' group a") instead of using fixed value start of latest month, can year , month using spreadsheet formulas, , concatenate our query them:
=query(results!a1:c,"select a,max(c) todate(b) > date '"&year(max(results!b2:b))&"-"&month(max(results!b2:b))&"-1' group a") 
that addresses first question.
tournament ranking
your second goal complex single spreadsheet formula, in opinion. here's way accomplish multiple formulas, though!
the x & y axes filled out spreadsheet formulas. on x axis (orange), populate participants names using in cell a3:
=unique(results!a2:a) the y axis consists of dates (green). these start dates of each unique month there scores for, calculated using following formula in cell d2. results in strings, e.g. 2016-01-1, , format required later formulas work.
=transpose(sort(unique(arrayformula(text(results!b2:b13,"yyyy-mm-1"))))) here's formula cell d3, calculate sum of 3 highest scores recorded user name appears in a3, month appearing in d2. (copy & paste formula across full range of participants & months, , adjust.)
=sum(query(results!$a$1:$c,"select c a='"&$a2&"' , todate(b) >= date '"&b$1&"' , todate(b) < date '"&if(isblank(c$1),text(today()+1,"yyyy-mm-dd"),c$1)&"' order c desc limit 3 label c ''")) key points formula:
the query range needs used fixed values isn't transposed when copied additional cells. however, it's still open-ended, absorb additional rows of scores on "results" sheet.
results!$a$1:$ca
whereclause used select rowsresultssheet given participant (a='"&$a2&"') , fall within month heads column (c$1)....and todate(b) < date '"&if(isblank(c$1),text(today()+1,"yyyy-mm-dd"),c$1)&"'the best 3 scores month found first sorting above result descending, limiting result 3 rows.
...order c desc limit 3finally,
queryheaders suppressed little trick, single number result:...label c ''
individual tournament totals appear in column c, range sum across row, e.g. cell c3:
sum(d3:3) the corresponding ranking in column b then:
rank(c3,c$3:c) tidy
for simpler copy/paste, can error checking in these formulas, can placed in sheet before corresponding data - example, @ start of season. using if(isblank(... or iferror(... can effective this.
b3 & down:
=iferror(rank(c3,c$3:c))c3 & down:
=if(isblank(a3),"",sum(d3:3))d3 & rest of field:
=iferror(sum(query(results!$a$1:$c,"select c a='"&$a3&"' , todate(b) >= date '"&d$2&"' , todate(b) < date '"&if(isblank(e$2),text(today()+1,"yyyy-mm-dd"),e$2)&"' order c desc limit 3 label c ''")))
Comments
Post a Comment