In Civ 3, I would look at a tile, and instantly know its potential FPC values for almost every possible in-game effect that could modify its values for better or worse. The numbers were just there in my head the instant I looked at a tile.

Now we have Civ 4, I haven’t got the first idea what FPC a tile could be generating in 50 turns, 2 civics, an improvement and a couple of techs time from now. It takes me 5 minutes to find reference and think through all the options.

I want Point > Click > Answer, for any possible terrain, feature, resource or improvement, or for any legal combination of the four. That’s why I made a start on it.



Notes:

1 - This is for finding out the potential FPC values of a particular terrain tile, it is not for finding out whether a certain resource or improvement can be located on a certain base terrain type or terrain feature.

What that means is: if you want to see how much commerce a tile generates having Gold with a Mine located on an Ocean tile, go ahead, the spreadsheet will (probably) let you calculate that. You’ll never actually work that tile in a game of course.

So to use the spreadsheet, it helps to know what tiles resources can appear on. This is only a problem if you are making up a tile yourself. If you are looking at a tile in the game and enter the relevant resource info for that tile, you shouldn’t have trouble.

2 - This will likely contain errors. It hasn’t had a thorough testing. If anyone spots an incorrect value being used for something or spurious results, please let me know.

3 – The fold out poster thing you get with the game was used as the source data for the entire spreadsheet. If there are any errors on there, they will be in the spreadsheet.

4 – While I am no noob on previous Civs, Ive only played through about five Civ 4 games, and am therefore no expert on many things. As far as I know this spreadsheet could contain some alarmingly embarrassing gaps, resulting in entire aspects of the game that have an effect on the FPC values being omitted or being incorrect. Please let me know if there is anything missing.

5 – It was written using Excel 2003 SP1, I don’t know if it works with earlier versions.

6 - The only Improvement formula that isnt doen yet is Farms. I appreciate farms are prettu important to the food thing, but Food also the only improvement that improves by differing amounts both a base terrain tile without resource and an improved resource tile. I couldnt get it working at 1st try but will do in a day or two.

Info Required

Rivers.

Currently, if the river checkbox is on, it simply ads 1 to the tile commerce value in a blanket fashion, ie regardless of any other user selections, so if certain base terrains or terrain features do not get the 1 commerce bonus, the spreadsheet is wrong.



General Possible Errors due to my lack of knowledge on Civ 4

If im honest, I have no idea if a Lumbermill can be built on a Jungle tile. There are probably many other such “I don’t know’s” that could cause errors in the calculation. This is probably the most likely area to cause problems.

Any of these issues should be easily resolved though, so if someone spots one, please let me know.




Valid Base Terrain and Terrain Feature combinations

As the spreadsheet validates whether or not the Base Terrain and Terrain Feature selections are compatible, ie a legal combination that can appear in the game, it has to get this info from somewhere.

Im not 100% on what features go on which base terrains, so Id appreciate if those in the know on such matters could check the values that are currently being used and let me know if any are incoorect.

For each possible combination of Base Terrain and Terrain Feature, a value of either 1 or 0 is assigned, if the combination is possible the value is 1, if the combination is not possible then the value is 0.

Here are the values currently being used in the calculator…


coast no terrain feature 1
coast fallout 0
coast floodplains 0
coast forest 0
coast hills 0
coast ice 1
coast jungle 0
coast oasis 0
desert no terrain feature 1
desert fallout 1
desert floodplains 1
desert forest 0
desert hills 0
desert ice 0
desert jungle 0
desert oasis 1
grassland no terrain feature 1
grassland fallout 1
grassland floodplains 0
grassland forest 1
grassland hills 1
grassland ice 0
grassland jungle 1
grassland oasis 0
snow no terrain feature 1
snow fallout 1
snow floodplains 0
snow forest 1
snow hills 0
snow ice 1
snow jungle 0
snow oasis 0
ocean no terrain feature 1
ocean fallout 0
ocean floodplains 0
ocean forest 0
ocean hills 0
ocean ice 0
ocean jungle 0
ocean oasis 0
peak no terrain feature 1
peak fallout 0
peak floodplains 0
peak forest 0
peak hills 0
peak ice 0
peak jungle 0
peak oasis 0
plains no terrain feature 1
plains fallout 1
plains floodplains 0
plains forest 1
plains hills 1
plains ice 0
plains jungle 1
plains oasis 0
tundra no terrain feature 1
tundra fallout 1
tundra floodplains 0
tundra forest 1
tundra hills 1
tundra ice 1
tundra jungle 0
tundra oasis 0

I like it, it is nicely done.

Woo! It's a great tool!
Congratulations. it's the tool I dream about!

Good job fret

I like this tool very much and I am looking forward to using the next release with the farms working.
One remark for completeness: Could you include a "leader" switch for financial leaders which adds 1 commerce for every field producing 2 commerce or more?

Moved to utility programs

In the release 0.2, the farm improvement is not working.
Is this normal ?

Thanks for the feedback chaps :)

@ LeSphinx - No, this is still the 1st version with the farms not working. Things have got really busy at work with the christmas rush, meaning I havent had time to address the farms issue. I will be doing it soon though and will post the updated version as soon as possible.


@Ronald - Thanks for pointing that out, adding a switch for the financial leader should be pretty easy. I'll ensure that gets done along with the farms before posting the new update.


oh, and merry christmas everone, only 2.5 days of work to go!
fret

Ok no problem fret.
Good job. Looking foward for the v1 release..... ;-))

A forum member who is working on a similar project has asked if they can share the code for this, which is cool, but I cant remember the bloody password, but have the formulas in a text dump. I also dont have any more time for this at the mo.

No poking fun at un-efficient formulae! This was thrown together.

So here goes...


Validated Values

C2 = Terrain Variable
C11= Terrain Feature Variable
C20= Resource Variable
C54= Improvement Variable


Switches

Yes = TRUE

C4TC'!F36 = Is tile adjacent to a river?
C4TC'!F38 = Have Universal Suffrage?
C4TC'!F39 = Have Free Speech?
C4TC'!F40 = Have State Property?
C4TC'!F42 = Have Biology?
C4TC'!F43 = Have Electricity?
C4TC'!F44 = Have Guilds?
C4TC'!F45 = Have Printing Press?
C4TC'!F46 = Have Replaceable Parts?





Validated Value Options
C2

1 Coast
2 Desert
3 Grassland
4 Snow
5 Ocean
6 Peak
7 Plains
8 Tundra


C11

1 No Terrain Feature
2 Fallout
3 Floodplains
4 Forest
5 Hills
6 Ice
7 Jungle
8 Oasis


C20

1 No Resource
2 Aluminium
3 Banana
4 Clam
5 Coal
6 Copper
7 Corn
8 Cow
9 Crab
10 Deer
11 Dye
12 Fish
13 Fur
14 Gems
15 Gold
16 Horse
17 Incense
18 Iron
19 Ivory
20 Marble
21 Oil
22 Pig
23 Rice
24 Sheep
25 Silk
26 Silver
27 Spices
28 Stone
29 Sugar
30 Uranium
31 Whale
32 Wheat
33 Wines



C54

1 No Improvement
2 Camp
3 Cottage
4 Hamlet
5 Village
6 Town
7 Farm - NOT WORKING
8 Fishing Boats
9 Lumbermill
10 Mine
11 Offshore Platform
12 Pasture
13 Plantation
14 Quarry
15 Watermill
16 Well
17 Whaling Boats
18 Windmill
19 Winery
20 Workshop

Calculation Step 1 - Base Terrain

Food

=IF(OR(C2=2,C2=4,C2=6),0,IF(OR(C2=1,C2=5,C2=7,C2=8 ),1,IF(C2=3,2)))



Production

=IF(OR(C2=1,C2=2,C2=3,C2=4,C2=5,C2=6,C2=8),0,IF(C2 =7,1))



Commerce

=IF(OR(C2=2,C2=3,C2=4,C2=6,C2=7,C2=8),0,IF(C2=5,1, IF(C2=1,2)))



Calculation Step 2 - Terrain Feature
Food

=IF(OR(C11=1,C11=4,C11=6),0,IF(C11=2,-3,IF(OR(C11=3,C11=8),3,IF(OR(C11=5,C11=7),-1))))



Production

=IF(OR(C11=1,C11=3,C11=6,C11=7,C11=8),0,IF(C11=2,-3,IF(OR(C11=4,C11=5),1)))



Commerce

=IF(OR(C11=1,C11=3,C11=4,C11=5,C11=6,C11=7),0,IF(C 11=2,-3,IF(C11=8,2)))


Calculation Step 3 - Base Resource

Food

=IF(OR(C20=1,C20=2,C20=5,C20=6,C20=11,C20=13,C20=1 4,C20=15,C20=16,C20=17,C20=18,C20=19,C20=20,C20=21 ,C20=25,C20=26,C20=28,C20=30,C20=33),0,1)



Production

=IF(OR(C20=1,C20=3,C20=4,C20=7,C20=8,C20=9,C20=10, C20=11,C20=12,C20=13,C20=14,C20=15,C20=17,C20=22,C 20=23,C20=24,C20=25,C20=26,C20=27,C20=29,C20=30,C2 0=31,C20=32,C20=33),0,1)



Commerce

=IF(OR(C20=11,C20=13,C20=14,C20=15,C20=17,C20=25,C 20=26,C20=27,C20=33),1,0)



The totals of each set of three values above then need to be summed.

The Improvement calculations are more complicated, 37 seperate formula are calulated on the fly and the sum of the three columns needs to be added to the sum of the first three calculations.

Improvements formulae in next post.

Calculation Stage 4 - Improvements and Modifiers - 1st Half


Camp

Food:

=IF(AND(C54=2,C20=10),2,0)


Production:

=IF(AND(C54=2,C20=19),1,0)


Commerce:

=IF(AND(C54=2,C20=13),3,IF(AND(C54=2,C20=19),1,0))



Cottage

Food: N/A

Production: N/A

Commerce:

=IF(C54=3,1,0)


Hamlet

Food: N/A

Production: N/A

Commerce:

=IF(C54=4,2,0)


Village

Food: N/A

Production: N/A

Commerce:

=IF(C54=5,IF('C4TC'!F45=TRUE,4,3),0)



Town

Food: N/A

Production:

=IF(AND('C4TC'!F38=TRUE,C54=6),1,0)


Commerce:

=IF(C54=6,
IF(AND('C4TC'!F39=TRUE,'C4TC'!F45=TRUE),7,
IF(AND('C4TC'!F39=TRUE,'C4TC'!F45=FALSE),6,
IF(AND('C4TC'!F39=FALSE,'C4TC'!F45=TRUE),5,4))),0)


Fishing Boats

Food:

=IF(AND(C54=8,OR(C20=4,C20=9)),2,IF(AND(C54=8,C20= 12),3,0))


Production: N/A

Commerce: N/A


Lumber Mill

Food: N/A

Production:

=IF(AND(C54=9,C11=4),1,0)


Commerce:

=IF(AND(C54=9,C11=4,'C4TC'!F36=TRUE),1,0)


Mine

Food: N/A

Production:

=IF(C54=10,
IF(AND(C54=10,OR(C20=2,C20=5,C20=6,C20=18)),3,
IF(AND(C54=10,OR(C20=24,C20=15,C20=26)),1,0)),0)


Commerce:

=IF(AND(C54=10,C20=2),1,
IF(AND(C54=10,C20=30),3,
IF(AND(C54=10,C20=14),5,
IF(AND(C54=10,C20=15),6,
IF(AND(C54=10,C20=26),4,0)))))




Offshore Platform

Food: N/A

Production:

=IF(AND(C54=11,C20=21),2,0)


Commerce:

=IF(AND(C54=11,C20=21),1,0)

Step 4 - Improvements and modifiers 2nd Half


Pasture

Food:

=IF(AND(C54=12,C20=8),1,
IF(AND(C54=12,C20=22),3,
IF(AND(C54=12,C20=24),2,0)))


Production:

=IF(AND(C54=12,OR(C20=16,C20=8)),2,0)


Commerce:

=IF(AND(C54=12,OR(C20=16,C20=24)),1,0)



Plantation

Food:

=IF(AND(C54=13,OR(C20=27,C20=29)),1,IF(AND(C54=13, C20=3),2,0))


Production: N/A

Commerce:

=IF(AND(C54=13,C20=29),1,
IF(AND(C54=13,C20=27),2,
IF(AND(C54=13,C20=25),3,
IF(AND(C54=13,C20=17),5,
IF(AND(C54=13,C20=11),4,0)))))


Quarry

Food: N/A

Production:

=IF(AND(C54=14,OR(C20=20,C20=28)),IF(C20=20,1,IF(C 20=28,2)),0)


Commerce:

=IF(AND(C54=14,C20=20),2,0)



Watermill

Food:

=IF(AND(C54=15,'C4TC'!F40=TRUE),1,0)



Production:

=IF(AND(C54=15,'C4TC'!F46=TRUE),1,0)


Commerce:

=IF(AND(C54=15,'C4TC'!F46=TRUE),1,0)



Well

Food: N/A


Production:

=IF(AND(C54=16,C20=21),2,0)


Commerce:

=IF(AND(C54=16,C20=21),1,0)



Whaling Boats

Food: N/A


Production:

=IF(AND(C54=17,C20=31),1,0)


Commerce:

=IF(AND(C54=17,C20=31),2,0)


Windmill

Food:

=IF(C54=18,1,0)


Production:

=IF(AND(C54=18,'C4TC'!F46=TRUE),1,0)


Commerce:

=IF(AND(C54=18,'C4TC'!F43=TRUE),2,IF(AND(C54=18,'C 4TC'!F43=FALSE),1,0))



Winery

Food:

=IF(AND(C54=19,C20=33),1,0)


Production: N/A

Commerce:

=IF(AND(C54=19,C20=33),2,0)



Workshop

Food:

=IF(AND(C54=20,'C4TC'!F40=TRUE),1,IF(AND(C54=2,'C4 TC'!F40=FALSE),-1,0))


Production:

=IF(AND(C54=20,'C4TC'!F46=TRUE,'C4TC'!F44=TRUE),3,
IF(AND(C54=20,'C4TC'!F46=TRUE,'C4TC'!F44=FALSE),2,
IF(AND(C54=20,'C4TC'!F46=FALSE,'C4TC'!F44=TRUE),2,
IF(AND(C54=20,'C4TC'!F46=FALSE,'C4TC'!F44=FALSE),1 ,0))))


Commerce: N/A



You then have to sum ALL improvement calculations for each column (food/production/commerce)

These values are then added to the sum values from the 1st three calcuation, and you have your terrain value!


Validating the Base Terrain and Terrain Feature User Selection


=IF(OR(AND(X!C2=1,X!C11=1),
AND(X!C2=1,X!C11=6),AND(X!C2=2,X!C11=1),
AND(X!C2=2,X!C11=2),AND(X!C2=2,X!C11=3),
AND(X!C2=2,X!C11=8),AND(X!C2=3,X!C11=1),
AND(X!C2=3,X!C11=2),AND(X!C2=3,X!C11=4),
AND(X!C2=3,X!C11=5),AND(X!C2=3,X!C11=7),
AND(X!C2=4,X!C11=1),AND(X!C2=4,X!C11=2),
AND(X!C2=4,X!C11=4),AND(X!C2=4,X!C11=6),
AND(X!C2=5,X!C11=1),AND(X!C2=6,X!C11=1),
AND(X!C2=7,X!C11=1),AND(X!C2=7,X!C11=2),
AND(X!C2=7,X!C11=4),AND(X!C2=7,X!C11=5),
AND(X!C2=7,X!C11=7),AND(X!C2=8,X!C11=1),
AND(X!C2=8,X!C11=2),AND(X!C2=8,X!C11=4),
AND(X!C2=8,X!C11=5),AND(X!C2=8,X!C11=6)),
"Valid Base Terrain and Terrain Feature selection.",
"ERROR: Invalid Base Terrain and Terrain Feature selection")