formula for a board Game

[hvddrift]

Hi,

I am playing a board game. I found the paper record keeping too tedious so I started a spread sheet that tracks different parts of the game. This is all pretty simple, for example, add a city and it will automatically add income and CIV points.

So... Here is my question:

My current spread sheet has a final result like this

Rome Carthage Greece East
GOP 0 8 8 4
VP


The Victory points for this is 7/4/2/0 (7 for 1st, 4 for 2nd, 2 for 3rd and 0 for 4th) Note in this case Carthage and Greece are tied so they get the following (7+4)/2=5 (round down)

I would like to find a formula to automatically calculate the VPs and display the result below. Like this:

Rome Carthage Greece East
GOP 0 8 8 4
VP 0 5 5 2

Any one who has an understanding of Excel formulas that could answer this it would be much appreciated.

Thanks

Hans

[davesexcel]

Hi,
How is
7/4/2/0
displayed??

[hvddrift]

They are displayed as Victory points

For example:

Player 1 has 10 GOP (a game term)
Player 2 has 5 GOP
Player 3 has 2 GOP
Player 4 has 1 GOP

7/4/2/0 would be displayed as follows:

Player 1 has 10 GOP = 7 VP
Player 2 has 5 GOP = 4 VP
Player 3 has 2 GOP = 2 VP
Player 4 has 1 GOP = 0 VP

if 2 players have an equal score then you add the VP together and divide by number of shared players (round down)

eg:

Player 1 has 10 GOP (a game term)
Player 2 has 5 GOP
Player 3 has 5 GOP
Player 4 has 1 GOP

7/4/2/0 would be displayed as follows:

Player 1 has 10 GOP = 7 VP
Player 2 has 5 GOP = 3 VP ((4+2)/2=3)
Player 3 has 2 GOP = 3 VP ((4+2)/2=3)
Player 4 has 1 GOP = 0 VP

Thanks

[ChemistB]

I used RANK function in an intermediate row and then calculated based on RANK and using VLOOKUP in a table of your VP values. If you want the numbers to be a whole number, you'll need to add in the ROUND function to your formulas. Let me know if this works for you.

Row 3 formulas (e.g. B3)

Please Login or Register  to view this content.

Row 4 formulas (e.g. B4)

Please Login or Register  to view this content.

ChemistB