Aug 12 2020 08:34 PM
Aug 12 2020 08:34 PM
I'd like to ask, again, any opinion and advice on my drone race spreadsheet.
I have created a basic version of what I need, it has a user part and an analysis part. I need a user to be able to input and change data in a "user table" freely, but I need analysis part to stay intact.
What analysis table needs to do is receive data from user table, sort it, and perform calculations (on the side) for each pilot's top 3 results. I'm running into several problems: I cannot install several filters in analysis table (I need each pilot's results to be sorted by the number of laps and then by time). Based on pilots' 3 best results, calculations will be performed counting total No. of laps and total time. The winner will be a pilot with most laps and least time (simultaneously).
I'm not sure how to have 3 separate filters in one table for each pilot AND I am not sure how to keep data integrity for user to be able to change input information, yet, have a correct data analysis in analysis table.
Any advise would be highly appreciated. Thank you.
Aug 13 2020 09:31 AM
I'm hoping you have the most recent version of Excel, the one that includes the FILTER and SORT dynamic array functions. Hoping that because they are what I used to resolve your situation.
First, though, I reorganized your basic table so that it's an Excel Table as shown here:
That done, I nested the FILTER function in two (!) SORT functions to do the sorting you wanted
And that produced these results.
Aug 13 2020 05:23 PM
@mathetes I would like to say thank you very much! I have updated my Excel and that is exactly what I needed, the spreadsheet works perfect. I truly appreciate your advise, thank you for your time.
I, honestly, thought it was impossible to do this task in such a clear and concise manner.
Aug 13 2020 05:58 PMSolution
Good. I'm glad it worked for you.
Those new Dynamic Array functions are amazing. I've been having a ball seeing what they can do...your situation was one that was fun working out. In all candor, it took me a while to figure out the nested SORT as the way to do the two level sorting. There is a SORTBY function that will work as well, but I couldn't make it work with the FILTER function.
Maybe another one of the Excel Microsoft MVPs will come by and enlighten us both.