Avatar of Doug Van
Doug Van
Flag for Canada

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
(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):

Thank you so much for your help.
SpreadsheetsGoogle WorkspaceMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Doug Van

8/22/2022 - Mon