Spreadsheets
- A
- B
- C
Description
The Confederation Formula requires manual inputs of the population of the provinces under the current census, the population of the provinces under the previous census, and the number of MPs per province under the previous census. After 1915, it also needs manual entry of the number of senators per province under section 22 of the British North America Act, 1867.
The Confederation Formula contained four substantive rules. First, Quebec gets 65 MPs. Second, the ratio of Quebec’s population under the latest census to 65 determines the federal electoral quotient for the other provinces, which, divided by their populations under the latest census determines the raw number of MPs per province. Third, normal rounding applies to those raw numbers such that any fractional remainder over 0.5 rounds up the number of MPs and anything below rounds down the number of MPs. Fourth, the One-Twentieth Clause held that a province would only lose MPs under the application of rules 1 to 3 if its share of the total population of the provinces between the previous census and the latest census decreased by a ratio of at least 5% relative to its share under the previous census.
Rules 1-3
I combined Rules 1 and 2 and made them light green in the workbook in columns B, C, and D. Under column B, I copied the population under the current census from the PDFs of the Dominion Bureau of Statistics. Since the electoral quotient comes from Quebec’s baseline of 65 MPs, it goes as follows into column C: =B$7/D$7
In column D, the raw numbers of MPs per province (except Quebec) equal: =B4/C4
In column E, Rule 3 of the Confederation Formula (light red) applies normal rounding in the adjacent column D, expressed as follows: =ROUND((D4),0)
Rule 4: The One-Twentieth Clause
The One-Twentieth Clause takes up the most space on each sheet in the workbook and requires both raw data manually entered and a series of formulas to calculate. I made this Rule 4 light yellow. It takes up columns F, G, H, I, J, K, and L. In columns F and G, I entered the populations of the provinces and their numbers of MPs under the previous decennial census. I also applied a SUM function to the total provincial population and the number of MPs. Column H then displays the share of the populations of the provinces under the previous decennial census as =F4/F$13
Column I shows the shares of the populations of the provinces under the current decennial census: =B4/B$13
Column J finds the difference between the two: =H4-I4
Column K contains the most crucial calculation of the ratio of the decrease in shares relative to the shares under the previous decennial census: =J4/I4
Column L uses an if-then function to determine whether a province would still lose MPs under the One-Twentieth Clause such that if the ratio of the decrease in shares of population relative to the previous decennial census is greater than or equal to (because rule 4 says “One-Twentieth Part or Upward”) 0.05 (i.e., one-twentieth or 5%), then the province can lose MPs under Rule 3, and the province’s total number of MPs reverts to the calculations under Rule 3; if, however, the ratio is less that 0.05, then the province cannot lose MPs under Rule 3 and would have to obtain the same number of MPs as it had in the previous decade.
=IF(K4>=0.05, E4, G4)
However, the One-Twentieth Clause does not apply to Quebec. And even if a province could lose MPs under Rule 4, it would still not lose MPs if the calculations under Rule 3 awarded it more MPs than it held under the previous decennial readjustment. This is where the calculation under column M comes in: the province’s total number of MPs equals the larger of the calculations under Rule 3 versus those under Rule 4.
=MAX(L4,E4)
In some decades, I have also added a column to show that Parliament awarded new provinces more MPs than their populations warranted under the Representation Formula.
Section 51A: The Senatorial Clause
From 1915 onward, section 51A obliges an additional series of calculations, which I shaded in light blue and added as columns M, N, and O. In column M, I entered manually the number of senators per province under section 22 of the British North America Act, 1867 (as it then was). Column N then determines whether a province needs more MPs over and above those allocated to it under the main part of the Representation Formula and, if so, the number of additional MPs. If the province’s number of senators is greater than its number of MPs under rules 1 to 4, then it needs an additional number of MPs equal to the difference between its number of senators and MPs awarded under rules 1 to 4; if not, then the provinces gets 0 extra MPs: =IF((M4>L4),(M4-L4),0)
Column O then includes the sub-total number of MPs under the One-Twentieth Clause and the Senatorial Clause: =L4+N4
However, since a province could still have ended up with more MPs under rules 1 to 3 than under rule 4, the final and total number of MPs still comes to maximum of either the number of MPs under rule 3 or the sub-total under the Senatorial Clause: =MAX(E4,O4)
James Bowden's Blog 