Forum Discussion
Spreadsheet advise - drone race
Good evening.
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.
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.
3 Replies
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
=SORT(SORT(FILTER(Table1[[Round]:[Times]],Table1[Pilot]=F$1),3,1),2,-1)
And that produced these results.
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.
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.
