MatchStatsCalculator v1.2

Well, as most of you know, I like detailed and statistical matchreports, and together with Lolomax, I'm about the only one who makes them. However, that can be changed, since lately I've been figuring out how to work with Excel (I only knew how to work with SPSS so far ^^ ), and I managed to design an easy-to-use datasheet where all statistical matchstats you'd want to have are fully automatically calculated: please welcome MatchStatsCalculator. ^^

Features
This tool is (still) nothing more than an Excel sheet for 3v3 UL-style matches, but it is one where you only have to fill in the times all people drove. The following stats are then automatically created in version 1.2.
- Points per round.
- Personal records.
- Average times.
- Standard Deviation (used to calculate consistency).
- Consistency (a formula I made up myself).
- Total points.
- Average finishing position.
- Amount of rounds won.

Manual
- Only enter the times in the sheet like this: "m:ss,00".
- The first driver (rowB) should have finished all rounds.
- Points for equal times in one round will be highlighted for the user to be fixed manually.
- Sorting is impossible in this version.

Consistency
The consistency is calculated through a formula I made up. The first part of this formula is the personal standard deviation of the driver multiplied by a penalty factor when not having finished every round. The second part is the bonus or malus factor, based on the difference between the fastest time driven that match and the personal average time driven. It also depends on the length of the track (based on the fastest time driven). This result X is then transformed into Y by the following formula: 0,25/X, so the consistency is generally a number between 0 and 1, where the ratings closest to 1 are the most consistent ones. The formula is as follows:

C = 0,25/(sqrt(t1^2+t2^2+...+tn^2/n)*(n/f)*((5+tm+Tb)/(5+Tb*d^-1))^2)

C = Consistency
t = Personal time driven
T = Overall time driven
n = Amount of rounds
f = Amount of finishes
m = Average time driven
b = Best time driven
d = Penalty (standard 60)

Downloads
Download MatchStatsCalculator v1.2

Changes
Here follows a list with all changes regarding older versions.

v1.2
- Added no-finish penalty at the consistency.
- Added no-finish penalty at the average times.

v1.1
- Fixed colorscales.
- Fixed consistency formula.

v1.0
- Added amount of rounds won.
- Added colorscales at the timetable.

List of Planned Fixes
- Overall colorscales.

List of Planned Additions
- ...

n1 JJ.

You could automate extracting the times from dedi or FAST logs, which would save some typing and avoid mistakes. Is importing a CSV file into part of an Excel sheet easy? How about for OpenOffice Calc? I don't have any of the MS Office suite...

well mix, i have thought about that, but only a week ago i didn't have a single bit of knowledge about excel. however, i have looked a bit at automatic entering of the times as well. still no clue about it, though, that's going to cost some proper research.

i'll save that for v2.0 though, at first i'll make a v1.2 tomorrow with the latest fixes, and i'll release it on tmx. if anyone has got any knowledge about importing csv files in excel, i'm one listening ear. :p

i'll keep you posted.

nice, releasing a matchexcelsheet on tmx.
shouldnt you release it on ET? :p

also, how about implementing a graph figure like meister did in his stats normally

Yep, a pie chart would be awesome.

hmm, cant find an example of meisters stats. Per did it as well once..

just a graph with 6 lines which connect the times driven.

nice one mix. ^^

anyway quik, at first this is not tmx. :p

second, v1.1 is more like a beta version. v1.2 which i will finish tomorrow will be released on tmx. ;)

i'll think about the chart, but i guessed the temporary layout feature would be enough telling...

but all the colours were pretty in those graphs :<

then i ask again, why release it on tmx?

I thought about coding this in C++, but I can't be bothered to learn how to make it nice in windows

ah well, i found a solution to my colors-problem, so i'm sure it's fixable for tomorrow. :)

quik: lol ok, i just noticed my own typo. :') i mean ET of course...

bramble: c++?! ^^ loool, don't even think i would do that, i had some classes about it, and i simply hate it. don't understand a thing of it... o.O i even failed for my whole course due to it... -.-'

The software for the experiment I work on is all C++.

It all runs in Linux though, and I assume people would run a mile if I made the program in windows using a command line prompt

Not me.

I'd probably choose bash as the language if I wanted to code something like this up quickly.

*could code in C#*

Nice work JJ!!

updated to version 1.2. i'll release this one on ET as well.

still a problem with the coloring of the times, but i've opened a help topic on an excel forum for that already...

maybe add some FET stuff also? average laptime comes to mind mainly

well, i already have made a sheet for FET, but that's one hell of a job to fill in... o.O

and that's even without laptimes... such a sheet would be nearly impossible without automatic importing of laptimes through matchlogs. though, that still miles away from my current programming skills...