troubleshooting Question

Calculating top 5 weekly stats automatically?

Avatar of Doug Van
Doug VanFlag for Canada asked on
Microsoft OfficeMicrosoft ExcelSpreadsheetsGoogle Apps* google spreadsheets
3 Comments1 Solution30 ViewsLast Modified:
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.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros