Calculating top 5 weekly stats automatically?
Using a formula that another EE'er brilliantly and generously offered me, I am able to display cumulative top five stats, but what if I also want to display the current week's top five?
This formula gives me the cumulative top five:
=QUERY(A20:D170, "SELECT A, D ORDER BY D DESC LIMIT 5")
With a small change, this formula gives me the current week top 5:
=QUERY(A20:J170, "SELECT A, (I+J) ORDER BY (I+J) desc LIMIT 5")
But I need to manually change (I+J) to (K+L ) for the next week, and so on. Is it possible to automate this using the (7 days <= date) in row 11?
Here is the test sheet (feel free to edit it):
https://docs.google.com/spreadsheets/d/191QC7RkTOjPpIPWttTFQYHrkYrNZ79x54O7eXbCpIAA/edit?usp=sharing
See the yellow highlighted section. :)
Thank you so much for your help.
This formula gives me the cumulative top five:
=QUERY(A20:D170, "SELECT A, D ORDER BY D DESC LIMIT 5")
With a small change, this formula gives me the current week top 5:
=QUERY(A20:J170, "SELECT A, (I+J) ORDER BY (I+J) desc LIMIT 5")
But I need to manually change (I+J) to (K+L ) for the next week, and so on. Is it possible to automate this using the (7 days <= date) in row 11?
Here is the test sheet (feel free to edit it):
https://docs.google.com/spreadsheets/d/191QC7RkTOjPpIPWttTFQYHrkYrNZ79x54O7eXbCpIAA/edit?usp=sharing
See the yellow highlighted section. :)
Thank you so much for your help.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Do you need any more assistance or have you achieved whatever you wanted?
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Thank you so much for your solution. At first, I thought it wouldn't work because I envisioned it working based on reading the dates in row 11 and comparing to the current date. In the completed sheet, the Weeks will be displayed across the page in row 10, but you couldn't have known that. :)
Anyhow, I can make your solution work by placing a conditional statement in row 10 to only display the "Week x" heading when data is entered. Example: =If(K14>0,"Week 6","")
Out of curiosity, I tried to incorporate "=INT(((Today())-1)/7)*7+7" (find the next Saturday), but had no luck reworking your solution.
Thank you.