For a Gunnery competition in our Sim Turret at GHRVPK I need something to show the scores and standings for each of 40 contestants. Contestants is split into 4 groups.
About the Sim Turret
I have LibreOffice and my first thought is to use a Calc spreadsheet with 3 sheets. It could be that a Base database would be better suited, but I have no knowledge about databases whatsoever.
I would like a document, that automatically sort the standings per group and totally as the different scores are filled in.
While Group 2B is in the sim, Group 2A is being shown around the facility and Group 1A and 1B is getting rides in our vehicles. Then Group 2A goes into the sim and Group 2B gets shown around while 1A and 1B is still riding. Then Groups 1A and 2B is shown around or in the sim while Groups 2A and 2B are getting rides.
I should have asked sooner as I would like to do a run trough with the document with the helpers on Tuesday 18 April.
It could be pushed until Tuesday 25 April, but the event is the day after, so I would like to avoid that especially if something is not working correctly.
This looks like a fun coding challenge! No promises, but I might give this a shot tomorrow. I think I won’t be able to finish it and I probably need more details after looking at it to get further.
But for starters, a quick demo with a sheet to enter scores and another to show them sorted should be easy1. That should help.
1: the author of this comment routinely underestimates the time it takes to complete a coding task and you should apply a correction factor of ~ π2
I thought this would be a good opportunity to learn some Excel-fu but quickly gave up on that. Easiest solution is probably to do it all by hand with copy and sort.
Sorted, per group
You can select the inputted data, then go to Data → Sort and select the column by which you want to sort the data. You can even select a second column as tiebreaker.
Sorted, all
You can either hide the columns, or set up some columns to the right, out of screen, to display the value of the cells where you did the input per group, but without the spaces / headers between.
Just enter the formula =A8 in a cell and copy/paste/edit as necessary.
Then copy the values from there, paste them elsewhere, and again do sort.
“Look ma, no hands!”
However, if you really hate using the mouse, then you can use this python script I made. Read the README for instructions:
I told you using built-in sort would be easier!
Glad you appreciate the script though.
OK, I can add a calculation based on all columns for the “final score” to sort by, but you’ll have to be more specific than that.
We can add the formula to the script, but you can also easily do it in the sheet itself, using a hidden column to sort by.
Without maths, we can simply sort by score and have one of the other columns as tiebreakers.
But I see you want them all to be weighed. You assigned a “priority” to each column. But that still does not translate to a formula 1 on 1.
Is 5000 user score and 1 fratricide better or worse than 4000 user score and no fratricide?
I guess to narrow it down, let’s just make a linear model to calculate a total score taking everything into account,
and you can tell me how much score we should:
subtract per fratricide: …
subtract per unit of time used: …
add per kill: …
subtract per 10% off from 100% accuracy: …
subtract per unit of time to kill: …
This is just a proposal, we can also make it more complicated: for example, we can halve the total score for every fratricide committed.
If you give me some initial ideas for these numbers, I’ll put them in the spreadsheet so you can see if the total score ranking does what you would expect and you can tweak the numbers more or ask for different rules etc.
I don’t know how this affects the difficulty of the project.
This may be messy.
The User Score is the most important. Unless it is dumped to the bottom if there is a fratricide.
If there are more with an equal User Scores, Number of Kills should determine the placement.
If both of these are equal, Time should determine the placement.
If these 3 are equal, Hit % should determine the placement.
If these 4 are equal, ATK should determine the placement.
1 in Fratricide should put users on the lists below all users without Fratricide, but be sorted in the same manner.
The score and other items are calculated by Steel Beasts. The six items is shown on the score page when the scenario is finished and are ready to be filled into the spreadsheet. (Probably by hand from a picture taken of the outside monitor on the turret by somebody else than me.)
The score page in English with the sources marked with red.
(This was my best run trough since I made the scenario and I didn’t use dynamic lead!)
Thanks, this is perfect! I’ll update the script this weekend.
As an aside, you won’t be surprised to hear that figuring out exactly what the software should do (requirements gathering) is often the most difficult part of a software project.
The pseudocode you gave me above is as clear as requirements can get, really well done!
@DanTDBV One more question: should all players with 2 in fratricide get scored below players with 1 in fratricide, etc.?
Or is there only a two-way split: any fratricides or no fratricides?
@DanTDBV Updated. Took more effort than I thought as I ran into issues with parsing and serializing types and decided to fix them the ugly way. I am reasonably sure now that it works as intended, but have a look at the example sheets and test whatever you like. I can probably easily fix it if something’s wrong, it’s finding the bugs that’s the tricky part.
Incidentally, I might be looking for new employment soon, so I’d like to hide this from my public page when you’ve downloaded it
I found a LibreOffice macro that I after some difficulties could convert to output the 4 sheets as 4 csv,s with the correct parameters and they are now working with v1.
To be honest, apart from understanding that it takes effort to solve, I have no idea what ”issues with parsing and serializing types and decided to fix them the ugly way” means.
I have been thinking if it could be a help if we took out Fratricide from the equation?
It should still be entered in the spreadsheet, but could it be a nonfactor when sorting the scores?
We could also take out time as it is already considered in the User Score.
I should also have asked sooner, but I would like it in Danish. Blushing.
English Danish Danish if æøå gives problems
Name Navn
User Score Karakter
Kills Nedkæmpet Nedkaempet
Time Tid
Hit % Træf % Traf %
ATK GTN
Fratricide EBS
Non factor text field below the contestant fields with description of GTN and EBS like:
GTN = Gennemsnitlig tid til nedkæmpelse x sekunder
Is the script not working as intended? I fixed the issues I found. Sorting should work as you described before. Please send me examples of things not working as you want. Also you should @ me or send me a direct message, I almost missed this message.
And translation is simple.
Hey, if you need extra pair of eyes with this, I am happy to help with the Python part (review etc). Or maybe it would cause more overhead in this late stage, your call if it’s a good idea.
@Freak
In the earlier reply that starts with “Well the bed could wait.” There is a description of what is happening and all files are included in the file attached to that reply. I hope that is helpfull.
