asked on

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.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Doug Van

ASKER

Greetings Saqib,

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.

Saqib Husain

Do you need any more assistance or have you achieved whatever you wanted?