Doug Van

asked on

# Spreadsheet formula: Dynamic sorting of top 5 value in an array

I might be tired or clueless but I can't figure out why my formula is failing in not finding a value match.

I have 3 columns:

(A) is the position (1 - 5)

(B) lists the name of one of the top 5

Formula:

The data is in the lower rows of A21:A and D21:D

I'm using Google Sheets, but it shouldn't greatly differ from an Excel solution. I think "ArrayFormula" might be the only difference.

Here is the test sheet (feel free to edit it):

https://docs.google.com/spreadsheets/d/191QC7RkTOjPpIPWttTFQYHrkYrNZ79x54O7eXbCpIAA/edit?usp=sharing

Thank you so much for your help.

I have 3 columns:

(A) is the position (1 - 5)

(B) lists the name of one of the top 5

Formula:

*=ArrayFormula(INDEX(A$26:A$175,MATCH(C2,(Round($D$26:$D$175)+ROW($D$26:$D$175)/10000),0)))*

(C) is the top 5 values picked from the data. The top five is calculated with the formula:*=TRUNC(ArrayFormula((LARGE((Round($D$26:$D$175,2)+ROW($D$26:$D$175)/10000),A2))),2)*The data is in the lower rows of A21:A and D21:D

I'm using Google Sheets, but it shouldn't greatly differ from an Excel solution. I think "ArrayFormula" might be the only difference.

Here is the test sheet (feel free to edit it):

https://docs.google.com/spreadsheets/d/191QC7RkTOjPpIPWttTFQYHrkYrNZ79x54O7eXbCpIAA/edit?usp=sharing

Thank you so much for your help.

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thanks again, Norie.

I have a follow-up question if you're available.

I want to also want to automatically provide the current week stats. Using your formula, I can manually do this with:

=QUERY(A20:J170, "SELECT A, (I+J) ORDER BY (I+J) desc LIMIt 5")

Where (I+J) is update each week. But I am wondering if this can be automatically updated so that each week the query is reading the current week's columns?

If you are able to assist, I have created a new EE question so that you can get proper credit.

https://www.experts-exchange.com/questions/29218219/Calculating-top-5-weekly-stats-automatically.html#questionAdd

I have a follow-up question if you're available.

I want to also want to automatically provide the current week stats. Using your formula, I can manually do this with:

=QUERY(A20:J170, "SELECT A, (I+J) ORDER BY (I+J) desc LIMIt 5")

Where (I+J) is update each week. But I am wondering if this can be automatically updated so that each week the query is reading the current week's columns?

If you are able to assist, I have created a new EE question so that you can get proper credit.

https://www.experts-exchange.com/questions/29218219/Calculating-top-5-weekly-stats-automatically.html#questionAdd

ASKER

Thank you so much. Your solution is so much more elegant than mine. :)