Spreadsheets
- A
- B
The “Compensation” Formula
Description
The Compensation Formula would have tacked on one additional rule at the end of MacEachen’s Modification from 1971 and 1972. First, the “Compensation” Formula would have awarded additional MPs under the Senatorial Clause over and above the electoral divisor, as his earlier bills from 1971 and 1972 had proposed. Second, the new Compensation Clause would have awarded one additional MP to the fast-growing provinces to compensate for each that the Senatorial and 15% Clauses secured for the “protected provinces.” Under this rule, a new electoral divisor would have come from adding the number of extra MPs awarded to the protected provinces (6) to the sub-total of all MPs granted to the ten provinces (267), and then subtracting the total number of MPs granted to all the protected provinces (26); the resulting smaller electoral divisor (244) would then have determined the number of MPs awarded to the fast-growing provinces and increased the total number of MPs allocated from 267 to 273.
Rule 1: The Raw Number of MPs per Province
Rule 1 calculates the electoral quotient and the number of MPs per province while preserving the electoral divisor of 261 and takes up columns B, C, D, and E in light green. This remains the same as from the operative Flexible Floor Formula.
Rule 2: Allocating the Remainders
Rule 2 – which appears in light blue in columns F, G, and H – would have allocated the remainders in precisely the same way as MacEachen’s proposed amendment to the Flexible Floor in 1971 and 1972. The rounding therefore would only have happened once instead of twice, both before and after applying the Senatorial Clause.
Rule 3: The Senatorial Clause
Rule 3 would have handled the Senatorial Clause and also appears in light red in columns I, J, and K with the same arithmetic as in the spreadsheet for the operative Third Formula and MacEachen’s Modification from 1971 and 1972.
Rule 4: The 15% Clause
Rule 4 would have kept the 15% Clause from the Flexible Floor Formula and MacEachen’s Modification; it relies on the same calculations as under the spreadsheet for the operative Third Formula but now appears in light yellow in columns L, M, N, and O instead.
Rule 5: The Main “Compensation Clause”
Finally, Rule 5 would have contained what I call the main Compensation Clause in columns Q, R, S, T, U, V, W, X, and Y in light orange. This Compensation Clause would have layered on top of any extra MPs doled out under both the Senatorial and 15% Clauses.
First, column Q indicates for each province the number of extra MPs awarded under the Senatorial and 15% Clauses combined in comparison to the sub-totals under Rule 2 that would approximate pure representation by population. Q14 also lists the sum of these extra MPs, which came to 6.
=IF(H4<P4, P4-H4, 0)
=SUM(Q4:Q13)
Second, column R lists what MacEachen called in his testimony before PROC the total number of MPs granted to the “Protected Provinces” under Rules 1, 2, 3, and 4 – which in the 1970s included only Prince Edward Island (4) and New Brunswick (10) thanks to the Senatorial Clause and Saskatchewan (12) because of the 15% Rule. All the other provinces therefore show 0 in this column. R14 adds up the total number of MPs granted to the three protected provinces, in this case, 26.
=IF(P4>H4, P4, 0)
=SUM(R4:R13)
Third, column S restates the populations of the seven non-protected provinces under the census of 1971 and excludes those of the three protected provinces from the sub-total.
=IF(R4=0, B4, 0)
=SUM(S4:S13)
Fourth, column T, in turn, lists the new electoral quotient taken from dividing the populations of the seven unprotected provinces by the new electoral divisor (247), which expresses the total number of MPs of the seven unprotected provinces.
=S$14/T$14
=(P14+Q14)-R14
Fifth, column U shows the raw number of MPs now assigned to the seven unprotected provinces using their new electoral quotient and divisor. This mimics the main calculation under Rule 1.
=S4/T4
Sixth, column V rounds down this raw number, in another analogue to the calculations under Rule 1.
=ROUNDDOWN((U4),0)
Seventh, since rounding down the number of MPs for the seven unprotected provinces yielded a sub-total of 244 instead of 247, column W then highlights the three provinces with the largest remainders in grey with that same conditional formatting as under the operative Flexible Floor Formula:
=U4-TRUNC(U4)
Eighth, column X then redistributes those remainders (in this case, 3) in accordance with the conditional formatting and the grey highlights.
Ninth and finally, column Y lists the new sub-total number of MPs for the seven unprotected provinces, which now once more adds up to the new electoral divisor of 247.
=V4+X4
=SUM(Y4:Y13)
Results
Rule 5 contained the clause that would have compensated the seven unprotected provinces with additional MPs in direct proportion to the surplus MPs awarded to the protected provinces under the Senatorial and 15% Clauses. In this case under the census of 1971, Quebec would have obtained two more MPs relative to MacEachen’s Modification (from 73 to 75), and Ontario would have gotten three more, from 93 to 96; the others stayed the same. The House of Commons would then have increased from 267 under MacEachen’s Modification to 273 under the Compensation Formula.
The “Qualified Parity” Formula
Description
The “Qualified Parity Method,” would have heavily modified the Flexible Floor Formula as opposed to building upon it iteratively like MacEachen’s Modification and the Compensation Formula. First, the electoral divisor would have increased by 10% each decade relative to the total number of MPs allocated to the ten provinces, so from 262 under the Representation Order, 1966 to 288 in the 1970s. Second, it would have repealed not only the 15% and Alberta Clauses but also section 51A of the British North America Act, 1867 altogether (as Ollivier had said in 1952 that Parliament could do) and replaced all three with one single compensatory rule: that a province could lose a maximum of only one MP upon each electoral readjustment but would do so only if its electoral quota came in at least 25% lower than the federal quotient. In the 1970s, Prince Edward Island would have lost one MP. Third, this formula would have then redistributed the new MPs from the electoral divisor combined with the MPs lost ((288-262)+1) only to those provinces the quotas of which exceeded the federal quotient. These rules would have assigned 285 MPs to the ten provinces in the 1970s.
Rules 1 and 2: Calculating the Federal Electoral Quotient and the Ten Provincial Quotas
This Qualified Parity Formula would have used the number of MPs per province and the total number of MPs of the ten provinces under the previous Representation Order to calculate the new federal electoral divisor, the new federal electoral quotient, and the new electoral quotas for each province. Rules 1 and 2 appear together in light green in columns B, C, D, and E. In column B, I entered the number of MPs that each province held under the Representation Order, 1966, which added up to 262. In column C, I entered the populations of the provinces under the decennial census of 1971.
The new federal electoral divisor in D14 equals the previous plus 10% and then rounded, based on MacEachen’s figures.
=ROUND((C14*1.1),0)
The federal electoral quotient then equals the total population of the provinces divided by the new electoral divisor:
=B$14/D$14
The new electoral quotas for each province equal the population of the province in the current census divided by the number of MPs that the province held under the previous Representation Order.
=B4/C4
Rule 3: Losing a Maximum of One MP per Redistribution
Rule 3 appears in columns F, G, and H in light blue and would have determined whether a province could have lost the maximum of one MP per decennial electoral redistribution or not. Column F shows the federal electoral quotient of 74,705 minus 25%, which comes to 56,029.
=D4*0.75
Column G contains the logical test: if a province’s electoral quota falls below the federal electoral quotient minus 25%, then the province loses one MP.
=IF(E4<F4, 1, 0)
Column H then subtracts the one MP lost from the number of MPs that the province under the previous Representation Order.
=IF(G4>0, C4-G4, “”)
(The condition could also be expressed as G4=1).
Rule 4: Redistributing MPs to the Provinces with Quotas Larger than the Federal Quotient
Rule 4 takes up columns I, J, K, L, M, and N in light yellow and would then have redistributed MPs to the provinces the electoral quotas of which came in higher than the federal electoral quotient.
First, column I contains the logical test to determine whether or not the province’s electoral quota ended up being higher than the federal electoral quotient – in other words, if the province is over-represented or not. In this case, “yes” means over-represented, while “no” means under-represented.
=IF(E4>D4, “Yes”, “No”)
This formula would have then redistributed the new MPs from the electoral divisor combined with the MPs lost ((288-262)+1) only to those provinces the quotas of which exceeded the federal quotient. I14 lists this figure as 27.
=(D14-C14)+G14
Second, column J then excludes the over-represented provinces, coded as “No” in column I, from the calculation but carries over the populations of the provinces which scored “Yes”, and J14 adds up the populations of all the under-represented provinces – what some of these other formulas called the “unprotected provinces.”
=IF(I4=”Yes”, B4, 0)
=SUM(J4:J13)
Third, column K re-uses the number of MPs that these under-represented provinces held under the previous Representation Order (from column B) as their new electoral divisors under Rule 4.
=IF(I4=”Yes”, C4, 0)
I14 adds up the total number of MPs held by these under-represented provinces under the previous Representation Order plus the number of new MPs to be redistributed to derive another smaller federal electoral divisor, which came to 244.
=SUM(K4:K13)+I14
Fourth, column L lists the new federal electoral quotient applied to this under-represented provinces obtained by dividing the sum of their populations under the current census by the new electoral divisor.
=IF(I4=”Yes”, J$14/K$14, 0)
Fifth, column M calculates the raw number of MPs, which column N then rounds down.
=IF(I4=”No”, C4/D4, J4/L4)
=ROUNDDOWN((M4),0)
Rule 5: Allocating Remainders
Rule 5 would have allocated the remainders in a similar way to the operative Flexible Floor Formula; it appears in columns O, P, and Q in light orange.
Column O shows the remainders discarded from column M, and O15 adds up their total. Conditional formatting new highlights in grey the nth highest remainders where n comes from O15. In this case, the remainders added up to 5.
=M4-TRUNC(M4)
=SUM(O4:O13)
Column P then redistributes these remainders accordingly (entered manually based on the grey highlights), while column Q shows the sub-total number of MPs per province.
=N4+P4
Results
Finally, column R lists the total number of MPs per province by taking the larger of the sub-totals under Rules 3 and 4.
=MAX(H4, Q4)
The results in this spreadsheet produced different final tallies in fully half of the provinces compared to the figures that MacEachen tabled before PROC, almost certainly because he used estimates instead of the true populations of the provinces under the decennial census of 1971, given that the final tallies differed by only one MP in all five.
The “Quebec Plus Four” Formula
Description
“Quebec Plus 4” would therefore have replaced the Flexible Floor Formula outright. Quebec would once more have served as the pivot province to determine the number of MPs allocated to the other nine, as it had under the Confederation Formula; in the 1970s, Quebec would have obtained 75 MPs, and then four more MPs each passing decade, so 79 in the 1980s, 83 in the 1990s, and 87 in the 2000s. The Amalgam incorporated this feature. Quebec Plus Four would have rounded down (another rule that the Amalgam took on) and would have retained the Senatorial and 15% Clauses.[i] This method would have allocated 271 MPs to the provinces in the 1970s.
Rules 1 and 2: Calculating the Raw Number of MPs per Province
Rules 1 and 2 appear in light green in columns B, C, and D and show the population under the latest census, the electoral quotient and electoral divisor, and the raw number of MPs per province, respectively. I entered the censal populations by hand in column B and Quebec’s fixed number of 75 MPs (the new electoral divisor) in C14. Column C then calculates the electoral quotient in the same way as the Confederation Formula had done: dividing the population of Quebec by the number of MPs assigned to Quebec:
=B$8/C$14
Column D lists the raw number of MPs unrounded based on that electoral quotient, which works because Quebec’s figure comes to 75 arithmetically.
=B4/C4
Rule 3: Rounding Down
Under Rule 3, column E then rounds down the raw number of MPs in light red:
=ROUNDDOWN((D4),0)
Rule 4: Applying the Senatorial Clause
Rule 4 applies the Senatorial Clause in light blue in columns F, G, and H in exactly the same manner as the other spreadsheets have done.
Rule 5: Keeping the 15% Clause
Rule 5 applies the 15% Clause in light yellow in columns I, J, K, and L, also in precisely the same manner as before.
Results
Finally, the total number of MPs equals the larger integer derived from the sub-total under the Senatorial Clause and the Minimum Number under the 15% Clause:
=MAX(H4,L4)
The total number of MPs for the ten provinces in M14 would have come to 271 in the 1970s, as compared to 273 under the Compensation Formula.
The “Amplified” Formula
Description
The “Amplified” Method would also have replaced its predecessor entirely but would have kept the Senatorial Clause. It would also each decade have increased the electoral divisor and therefore ballooned the House of Commons, up to 305 in the 1970s alone. This method would have used the number of MPs per province under the previous representation order combined with the latest decennial census to calculate each province’s electoral quota; the province with the smallest electoral quota which would also not benefit from the Senatorial Clause would then have served as the federal electoral quotient used to calculate the number of MPs per province. This latter provision might also have drawn inspiration from one of Pickersgill’s plans from 1963.
Rule 1: Calculating the Provincial Electoral Quotas and the Federal Electoral Quotient and Divisor
Rule 1 involves calculating the provincial electoral quotas and the new federal electoral quotient and federal electoral divisor. It takes up columns B, C, D, E, F, and G in light green and incorporated the Senatorial Clause.
In column B, I manually entered the populations of the provinces under the census, in column C, the number of MPs of each province under the previous Representation Order; and in column D, the number of senators of each province. Column E then applies a logical test to determine if a province qualifies as “protected”.
=IF(C4=D4, “Yes”, “No”)
Column F, in turn, calculates the electoral quotas of each of the unprotected provinces.
=IF(E4=”No”, B4/C4, 0)
This formula defined the federal electoral quotient as the lowest provincial electoral quota of the unprotected provinces, which column G identifies.
=MIN(F4:F13)
The new federal electoral divisor then equals the sum of the populations of the unprotected provinces divided by the new federal electoral quotient.
=H14/G11
Rule 2: Calculating the Number of MPs for the “Unprotected Provinces”
Rule 2 calculates the number of MPs for the unprotected provinces and appears in columns H, I, and J in light blue.
Column H carries over the populations of the unprotected provinces, and H14 calculates their sum.
=IF(E4=”No”, B4, “”)
=SUM(H4:H13)
Column I shows the number of MPs of the unprotected provinces, which equals the population divided by the federal electoral quotient, rounded down in column J.
=IF(E4=”No”, B4/G$11, 0)
=ROUNDDOWN((I4),0)
Rule 3: Allocating the Remainders
Rule 3 shows and allocates remainders in columns K, L, and M in the same way as the previous workbooks and spreadsheets.
Column K shows the remainders, K15 shows their sum (n), and the conditional formatting highlights the top nth in grey.
=I4-TRUNC(I4)
=SUM(K4:K13)
Column M shows the total number of MPs for the unprotected provinces, taking the remainders into account.
=J4+L4
Results
Finally, column N shows the final tally of MPs for all provinces, both protected and unprotected and adds them all up in N14.
=MAX(C4,M4)
James Bowden's Blog 