Variables in numbers?

If I totally understood how this works, here is a solution (click on the image to enlarge it)

The formula in cell B2 =B$2/LOOKUP(SUM(H$2:H2),Table 2 :: $A,Table 2 :: $B)

Fill this to the rest of the cells under the vote counts

The formula in H3 =IF(B3=MAX($B3:$G3),1,0)

Fill this to the rest of the cells in that section of the table.

Table 2 is a lookup table for the divisors

The first formula counts how many seats the party has been given so far. It then looks up what the divisor should be and divides the party's votes by that divisor.

The second formula simply determines which comparison number is highest in the row and assigns them the seat. If two parties have the same winning comparison number, they would both get assigned a seat. This will result in extra seat asignments. I didn't try to solve that part of the problem.

Posting two links pointing to Password protected pages is really boring !

Yvan KOENIG (VALLAURIS, France) jeudi 2 février 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

I'm used to programing syntax

Then why not just use applescript?

Jason

Badunit wrote too quick (I was dining).

Here is what I baught:

In table "Calculations"

in cell B3, the formula is :

=ROUND(B2/1.4,2)

In cell B4, it's :

=IF(B3<>MAX($B3:$G3),B3,ROUND(B$2/3,2))

In cell B5, it's :

=IF(B4<>MAX($B4:$G4),B4,ROUND(B$2/5,2))

In cell B6, it's :

=IF(B5<>MAX($B5:$G5),B5,ROUND(B$2/3,2))

In cell B7, it's :

=IF(B6<>MAX($B6:$G6),B6,ROUND(B$2/7,2))

In table Sieges

In cell B3, the formula is :

=IF(1+MATCH(MAX(calculations :: $B3:$G3),calculations::$B3:$G3,0)=COLUMN(),1,"")

Fill down then Fill to the right.

The row 8 is a footer row.

In cell B8, the formula is :

=SUM(B)

Fill to the right.

Yvan KOENIG (VALLAURIS, France) jeudi 2 février 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

As I understood it, if a party has no seats yet, their divisor is 1.4. If they have one seat already, their divisor becomes 3 for the next round(s). If they have two seats already, their divisor become 5 for the next round(s), etc. This wasn't exactly clear in the example.

It seems that you are right.

As I had no idea of the way the coef was defined I embedded it in the formulas missing the idea to store them in a table allowing us to work with a single formula.

Happily, we get the same final result 😉

Yvan KOENIG (VALLAURIS, France) jeudi 2 février 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

There was an error in my post. The image is correct but the text in the post was not. The first formula goes in B3, not B2. Row 2 has the votes.

I'm puzzled.

Your files match my proposal but you wrote to Badunit

which use a different (and better) scheme.

Yvan KOENIG (VALLAURIS, France) jeudi 9 février 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

Hello

Your formula was wrong.

In B3, insert :

=B$2/LOOKUP(SUM(J$2:J2),Table 2 :: $A,Table 2 :: $B)

Fill to right

Fill to bottom.

Yours was :

=B$2/LOOKUP(SUM(J$2:J3),Table 2 :: $A,Table 2 :: $B)

which failed because the cell J3 contain a formula referencing B3.

I changed a detail in the other set of formulas.

In I3 I inserted :

=IF(B3=MAX($B3:$I3),1,"")

which give a table easier to read.

Something is bothering me.

To explain more easily, I inserted a column after the H one.

In the new cell I3, I inserted :

=LOOKUP(SUM(K$2:K2),Table 2 :: $A,Table 2 :: $B)

which is the subset of the main formula extracting the divider to apply.

As you see, the scheme use the divider 1.4 twice. Is it normal ?

Yvan KOENIG (VALLAURIS, France) jeudi 9 février 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k