The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
This is a spreadsheet I made to easily calculate QPD, PD, Snitch Range Games, and quickly rank teams for tournaments.
2
3
Step OneThe Formula
4
The way we write quidditch scores makes it hard to do calculations with them, since spreadsheets wont recognise them as numbers due to the way we denote the snitch.
However, you can write a pretty simple custom formula to get around this.
5
6
1. Click on "Tools" in the bar at the top (between "Data" and "Add-Ons")
7
2. Click "Script editor"
8
3. Enter the following:
9
function sumnums(inputStrings) {
// Function to find ints or floats in text, and sum them
inputStrings = "" + inputStrings; // Forces input object to string
// and automatically concatenates
// if passed an array by the
var total = 0;
matches = inputStrings.match(/(\d+\.\d+|\d+)/g);
for(var j in matches) { total += Number(matches[j]); }
}
10
11
100* =SUMNUMS(#NAME?
12
100(60*)#NAME?
13
60^(30*)#NAME?
14
You'll notice that it adds all the numbers together rather than just taking the last name,
but when you're calculating QPD and PD it doesn't matter because we only care about the difference.
15
16
Step TwoThe Calcuation
17
Because we have regulation and overtime and sudden death second overtime and various amounts of snitches in any one game, scoring Quidditch can get a bit complicated.
So how do you determine QPD?
You need to ask google to count the number of snitches and subtract 30 for each, then compare the numbers, the formula to do that is very complicated, but easy enough to use.
18
The formula to calculate PD is much more simple since you don't need to account for snitches.
Both these formulas also account for a max QPD of 120 and a max PD of 150*
19
FORMULA (QPD) =IF((IF((COUNTIF(C3, "*~**")+COUNTIF(C3, "*^*"))=0, SUMNUMS(C3), IF((COUNTIF(C3, "*~**")+COUNTIF(C3, "*^*"))=1, SUMNUMS(C3)-30, IF((COUNTIF(C3, "*~**")+COUNTIF(C3, "*^*"))=2, SUMNUMS(C3)-60, SUMNUMS(C3))))- IF((COUNTIF(E3, "*~**")+COUNTIF(E3, "*^*"))=0, SUMNUMS(E3), IF((COUNTIF(E3, "*~**")+COUNTIF(E3, "*^*"))=1, SUMNUMS(E3)-30, IF((COUNTIF(E3, "*~**")+COUNTIF(E3, "*^*"))=2, SUMNUMS(E3)-60, SUMNUMS(E3)))))>119, 120, (IF((COUNTIF(C3, "*~**")+COUNTIF(C3, "*^*"))=0, SUMNUMS(C3), IF((COUNTIF(C3, "*~**")+COUNTIF(C3, "*^*"))=1, SUMNUMS(C3)-30, IF((COUNTIF(C3, "*~**")+COUNTIF(C3, "*^*"))=2, SUMNUMS(C3)-60, SUMNUMS(C3))))- IF((COUNTIF(E3, "*~**")+COUNTIF(E3, "*^*"))=0, SUMNUMS(E3), IF((COUNTIF(E3, "*~**")+COUNTIF(E3, "*^*"))=1, SUMNUMS(E3)-30, IF((COUNTIF(E3, "*~**")+COUNTIF(E3, "*^*"))=2, SUMNUMS(E3)-60, (E3))))))
20
FORMULA (PD)
=IF(SUMNUMS(C3)-SUMNUMS(E3)>149, 150,SUMNUMS(C3)-SUMNUMS(E3))
21
In order for this formula to work, you need to enter the scores separately, and put the winner first (on the left).
22
INPUTS
Replace "C3" with the cell that contains the winner's score
23
Replace "E3" with the cell that contains the loser's score
24
Hint: Just highlight the cell and CTRL F to replace all rather than editing them individually
25
EXAMPLEWinnerLoserPDQPD
26
100*v30#NAME?#NAME?
27
60*50#NAME?#NAME?
28
100^(70*)80(70)#NAME?#NAME?
29
120^(90)100(90*)#NAME?#NAME?
30
31
Step ThreeSnitch Range
32
As noted above, the QPD calculation doesn't use the final score, it sums up the score for all periods.
This means that is can't accurately calculate whether an overtime game is in snitch range or not,
since it's not accounting for the score at the time of the catch, just the overall quaffle score.
But, there is a way to fix this.
33
You need to add another column to the initial game scores data that calculates whether it's in snitch range or not.
You can then use this to count snitch range games.
This formula extracts only the last number/score to determine the QPD at the time of the snitch catch.
This is not always the same as whether total QPD was between -30 and 30.
34
FORMULA =IF(IF(countif(B3, "*(*(*")>0, IF(countif(B3, "*!*")>0, SUMNUMS(LEFT(B3, 3))-30, SUMNUMS(LEFT(B3, 3))), IF(countif(B3, "*(*")>0, IF(countif(B3, "*^*")>0, SUMNUMS(LEFT(B3, 3))-30, SUMNUMS(LEFT(B3, 3))), IF(countif(B3, "*~**")>0,SUMNUMS(LEFT(B3, 3))-30, SUMNUMS(LEFT(B3, 3)))))-IF(countif(D3, "*(*(*")>0, IF(countif(D3, "*!*")>0, SUMNUMS(LEFT(D3, 3))-30, SUMNUMS(LEFT(D3, 3))), IF(countif(D3, "*(*")>0, IF(countif(D3, "*^*")>0, SUMNUMS(LEFT(D3, 3))-30, SUMNUMS(LEFT(D3, 3))), IF(countif(D3, "*~**")>0,SUMNUMS(LEFT(D3, 3))-30, SUMNUMS(LEFT(D3, 3)))))>31, 0, 1)
35
EXAMPLEWinnerLoserQPDSnitch Range
36
130^(100*)v110(100)#NAME?#NAME?
37
130^(90*)100(90)#NAME?#NAME?
38
100*40#NAME?#NAME?
39
90^(50)50(50*)#NAME?#NAME?
40
100*30#NAME?#NAME?
41
42
Step FOURTabs
43
Depending on how complicated you wanna get you are going to need a serious of tabs.
For a basic 6 teams round robin tournament though you only need two.
44
I have set up a demo tournament in the next two tabs to show you how it works.
All the other formulae necessary are inbedded in those tabs.
:)
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100