Election formulas

[garon]

in my small community we have an Election date coming up on 25th of May. There are 4 partys against each other trying to force their people in to a 9 person town council. Is there any simple way to set up an Excel file in which you can give each party an imaginary number of votes and then you could see the number of persons they get elected in the town council.

Is there any way to get a decent formula behind these calculations so I can see in an instant how many men each party gets after I have entered the votes in the first column??

[Chris Davison]

I would imagine so....

if there's 2,000 votes and Party A gets 200 of them, would it mean that they get 200/2,000 x 9 seats on the council ?

etc etc ?

what are the rules when there is no whole number ?

[garon]

The rules are:
f.e. 2000 votes
Party A gets 800 Party B gets 400
Party C gets 600 Party D gets 200.
Then you divide in each party´s votes first with 2, then 3, then 4 and so on. Then you find out the highest 9 numbers.
F.e. Party A has 800 behind their 1. man, 400 behind their 2. 266.67 behind their 3.man and 200 behind their 4. man.
Does this explain?

[Chris Davison]

a bit long-winded, but does this help ?

Microsoft Excel - garon.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
K2		=
	A	B	C	D	E	F	G	H	I
1	Party	Votes	1	2	3	4	5	6	7
2	A	1234	:alert('=+$B2/C$1')>1,234.00	:alert('=+$B2/D$1')>617.00	:alert('=+$B2/E$1')>411.33	:alert('=+$B2/F$1')>308.50	:alert('=+$B2/G$1')>246.80	:alert('=+$B2/H$1')>205.67	:alert('=+$B2/I$1')>176.29
3	B	1758	:alert('=+$B3/C$1')>1,758.00	:alert('=+$B3/D$1')>879.00	:alert('=+$B3/E$1')>586.00	:alert('=+$B3/F$1')>439.50	:alert('=+$B3/G$1')>351.60	:alert('=+$B3/H$1')>293.00	:alert('=+$B3/I$1')>251.14
4	C	1563	:alert('=+$B4/C$1')>1,563.00	:alert('=+$B4/D$1')>781.50	:alert('=+$B4/E$1')>521.00	:alert('=+$B4/F$1')>390.75	:alert('=+$B4/G$1')>312.60	:alert('=+$B4/H$1')>260.50	:alert('=+$B4/I$1')>223.29
5	D	1244	:alert('=+$B5/C$1')>1,244.00	:alert('=+$B5/D$1')>622.00	:alert('=+$B5/E$1')>414.67	:alert('=+$B5/F$1')>311.00	:alert('=+$B5/G$1')>248.80	:alert('=+$B5/H$1')>207.33	:alert('=+$B5/I$1')>177.71
6	Party	Seats
7	:alert('=+A2')>A	:alert('=SUM(C7:I7)')>2	:alert('=IF(RANK(C2,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(D2,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(E2,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(F2,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(G2,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(H2,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(I2,$C$2:$I$5,0)<=9,1,0)')>0
8	:alert('=+A3')>B	:alert('=SUM(C8:I8)')>3	:alert('=IF(RANK(C3,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(D3,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(E3,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(F3,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(G3,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(H3,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(I3,$C$2:$I$5,0)<=9,1,0)')>0
9	:alert('=+A4')>C	:alert('=SUM(C9:I9)')>2	:alert('=IF(RANK(C4,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(D4,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(E4,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(F4,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(G4,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(H4,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(I4,$C$2:$I$5,0)<=9,1,0)')>0
10	:alert('=+A5')>D	:alert('=SUM(C10:I10)')>2	:alert('=IF(RANK(C5,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(D5,$C$2:$I$5,0)<=9,1,0)')>1	:alert('=IF(RANK(E5,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(F5,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(G5,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(H5,$C$2:$I$5,0)<=9,1,0)')>0	:alert('=IF(RANK(I5,$C$2:$I$5,0)<=9,1,0)')>0
11		:alert('=SUM(B7:B10)')>9
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

[Chris Davison]

Colo / Ivan : this is a fantastic tool

thanks from all of us who use it, for putting the time and effort into its development

sincerely,
Chris