SOLVED

Spreadsheet advise - drone race

%3CLINGO-SUB%20id%3D%22lingo-sub-1585837%22%20slang%3D%22en-US%22%3ESpreadsheet%20advise%20-%20drone%20race%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585837%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20evening.%3C%2FP%3E%3CP%3EI'd%20like%20to%20ask%2C%20again%2C%20any%20opinion%20and%20advice%20on%20my%20drone%20race%20spreadsheet.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20basic%20version%20of%20what%20I%20need%2C%20it%20has%20a%20user%20part%20and%20an%20analysis%20part.%20I%20need%20a%20user%20to%20be%20able%20to%20input%20and%20change%20data%20in%20a%20%22user%20table%22%20freely%2C%20but%20I%20need%20analysis%20part%20to%20stay%20intact.%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20analysis%20table%20needs%20to%20do%20is%20receive%20data%20from%20user%20table%2C%20sort%20it%2C%20and%20perform%20calculations%20(on%20the%20side)%20for%20each%20pilot's%20top%203%20results.%26nbsp%3B%20I'm%20running%20into%20several%20problems%3A%20I%20cannot%20install%20several%20filters%20in%20analysis%20table%20(I%20need%20each%20pilot's%20results%20to%20be%20sorted%20by%20the%20number%20of%20laps%20and%20then%20by%20time).%20Based%20on%20pilots'%203%20best%20results%2C%20calculations%20will%20be%20performed%20counting%20total%20No.%20of%20laps%20and%20total%20time.%20The%20winner%20will%20be%20a%20pilot%20with%20most%20laps%20and%20least%20time%20(simultaneously).%3C%2FP%3E%3CP%3EI'm%20not%20sure%20how%20to%20have%203%20separate%20filters%20in%20one%20table%20for%20each%20pilot%20AND%20I%20am%20not%20sure%20how%20to%20keep%20data%20integrity%20for%20user%20to%20be%20able%20to%20change%20input%20information%2C%20yet%2C%20have%20a%20correct%20data%20analysis%20in%20analysis%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advise%20would%20be%20highly%20appreciated.%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1585837%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1587189%22%20slang%3D%22en-US%22%3ERe%3A%20Spreadsheet%20advise%20-%20drone%20race%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1587189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756612%22%20target%3D%22_blank%22%3E%40AnnaVWilliamson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20hoping%20you%20have%20the%20most%20recent%20version%20of%20Excel%2C%20the%20one%20that%20includes%20the%20FILTER%20and%20SORT%20dynamic%20array%20functions.%20Hoping%20that%20because%20they%20are%20what%20I%20used%20to%20resolve%20your%20situation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20though%2C%20I%20reorganized%20your%20basic%20table%20so%20that%20it's%20an%20Excel%20Table%20as%20shown%20here%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1597335870620.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212256iD5ACEEF32E8008E7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_0-1597335870620.png%22%20alt%3D%22mathetes_0-1597335870620.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThat%20done%2C%20I%20nested%20the%20FILTER%20function%20in%20two%20(!)%20SORT%20functions%20to%20do%20the%20sorting%20you%20wanted%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSORT(SORT(FILTER(Table1%5B%5BRound%5D%3A%5BTimes%5D%5D%2CTable1%5BPilot%5D%3DF%241)%2C3%2C1)%2C2%2C-1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAnd%20that%20produced%20these%20results.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_2-1597336064168.png%22%20style%3D%22width%3A%20701px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212258i0939428BBB5DB60B%2Fimage-dimensions%2F701x254%3Fv%3D1.0%22%20width%3D%22701%22%20height%3D%22254%22%20title%3D%22mathetes_2-1597336064168.png%22%20alt%3D%22mathetes_2-1597336064168.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588140%22%20slang%3D%22en-US%22%3ERe%3A%20Spreadsheet%20advise%20-%20drone%20race%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20I%20would%20like%20to%20say%20thank%20you%20very%20much!%20I%20have%20updated%20my%20Excel%20and%20that%20is%20exactly%20what%20I%20needed%2C%20the%20spreadsheet%20works%20perfect.%20I%20truly%20appreciate%20your%20advise%2C%20thank%20you%20for%20your%20time.%3C%2FP%3E%3CP%3EI%2C%20honestly%2C%20thought%20it%20was%20impossible%20to%20do%20this%20task%20in%20such%20a%20clear%20and%20concise%20manner.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588185%22%20slang%3D%22en-US%22%3ERe%3A%20Spreadsheet%20advise%20-%20drone%20race%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756612%22%20target%3D%22_blank%22%3E%40AnnaVWilliamson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood.%20I'm%20glad%20it%20worked%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThose%20new%20Dynamic%20Array%20functions%20are%20amazing.%20I've%20been%20having%20a%20ball%20seeing%20what%20they%20can%20do...your%20situation%20was%20one%20that%20was%20fun%20working%20out.%20In%20all%20candor%2C%20it%20took%20me%20a%20while%20to%20figure%20out%20the%20nested%20SORT%20as%20the%20way%20to%20do%20the%20two%20level%20sorting.%20There%20is%20a%20SORTBY%20function%20that%20will%20work%20as%20well%2C%20but%20I%20couldn't%20make%20it%20work%20with%20the%20FILTER%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20another%20one%20of%20the%20Excel%20Microsoft%20MVPs%20will%20come%20by%20and%20enlighten%20us%20both.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

3 Replies

@AnnaVWilliamson 

 

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:

mathetes_0-1597335870620.png

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_2-1597336064168.png

 

@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. 

Best Response confirmed by AnnaVWilliamson (New Contributor)
Solution

@AnnaVWilliamson 

 

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.