Understand your cities' corruption better and tackle it

[Tarzus]

After reading Alexman's "Everything about Corruption", I had done a worksheet to estimate the empire's tax and sci performance. The worksheet allows us to sum a total of 81 cities' tax and beaker performance (more can be easily added).

Corruption For All Cities version 1.1beta

Updated 1.1beta to check for error.

To use this worksheet first you need to read and understand Alexman's thread :
http://forums.civfanatics.com/showthread.php?t=76619&page=1&pp=20

Just to highlight a few benefits of this worksheet:

1) With this worksheet we are able to learn whether building anti-corruption improvements are useful for our far away cities, simply by testing the Ni column with 0, 1 and 2. One of the most disturbing issue to us was we have to spend 80 turns to build a courthouse in a far away city to see whether it will benefit that city. Worst still, if Courhouse didn't help, should we spend another 160 turns to build Police station ? Now we can test our city with 0, 1 and 2 anti-corruption buildings in Ni clolumn to see if we can bring corruption down at one glance.

2) We can compare different government's performance with our current city-improvements status (though we need to consider military unit cost seperately) before actually wasting several precious turns to change govt. Ever experience change to Republic too soon ?

3) We can also test, city by city, for the best location for Forbidden Palace and Secret Police HeadQuarter. Now this is critical. If you dislike building ineffective Courthouse & Police Station, imagine building Forbbiden Palace and found out from forum civfans that you had actually build it on the worst location.

One thing to take note :
The tax and beaker performance however, are result before adding specialist effects and (for tax) buildings' maintenance. Be careful when you are choosing government. Also, Republic and Democracy will cause you to change your commence output of each city.

I hope this worksheet will help many of you out here, and your improvement / suggestions are very much welcome.

Regards

 

[alexman]

Good job Tarzus!
I see you put a good amount of work into your spreadsheet, and it looks very nice. If somebody has the patience to type in all the information, it should be very helpful, especially for GOTM, succession, or PBEM games where you have lots of time to do analysis.

However, it's a bit difficult for me to check if everything's correct because I am so clueless at excel. Perhaps somebody else can double-check for you.

One note of caution: the formulae in the corruption thread are intended for examining the factors that influence corruption, rather than for predicting the exact number of beakers and gold lost to corruption. The reason is that it's difficult to figure out all the internal rounding in the intermediate calculations in the Civ3 game engine, so the actual corruption in a city might be slightly different than what the formula predicts.

 

[Nad]

quick point: isn't OCN for standard maps now 20 in conquests, not 16 as it says in your table?

 

[syndicatedragon]

How do you indicate the city with the FP? Shouldn't that city have zero distance corruption? It seems like you need two distance columns - one for distance to the real Palace (for rank determination) and the other for distance to the nearest "palace", for distance corruption calculation. Or maybe I missed something?

 

[Tarzus]

Alexman Thanks. Actually we do not need to use all 81 cities (rows), just as many as you need.

Nad, I'll check and change accordingly, Thanks.

Syndicatedragon, good point and Thanks. Actually we don't need two column for distance corruption, but just a change in formula:

Distance Corruption (column N)
=IF(L35=0,".",IF(G35,0,M35/$M$8))
where
L35 is to check if there is an entry of info
G35 is checking if Anti-corruption Wonder exist in this city
if yes, put distance corruption to 0,
else distance corruption = M35/M8 = da/MaxD
Either way, final city corruption will be cap to 0% in Cmax and Ctotal column.

I had changed the formula and will update to version 1.1, before I post I am checking the final detail with Alexman under "Everything about Corruption".

 

[syndicatedragon]

I guess I still don't understand how other cities will take the distance to the FP into account with this change. Won't their distance still be calculated from the real Palace (columns D&E), regardless of where column G is "1"? According to alexman, they should use the distance to the nearest "palace" instead (for distance corruption).

 

[Tarzus]

What an major error found ! thanks Syn, I had misunderstood Alexman's article and given a wrong solution.

Distance corruption is calculated to the nearest palace while Rank is found from Capital alone.

I can modify the worksheet by adding a few columns to auto calculate the relative distance from FP/SpHQ via Capital, then choose the shortest distance (auto distance choosing). However a few points here:

1) x and y no longer are interchangable - previously (ver1.0) if you exchange the distance NW/SE with NE/SW from capital, the formula works the same. Now because we use the x and y value to find out distance from of FP as well as distance from SPHQ, x=NW/SE and y=NE/SW must be treated accordingly.

2) Before I change the worksheet, I like to ask people out here : anyone prefer entering x and y from FP and SPHQ manually (no auto distance choosing, I believe most people like auto right) ?

 

[syndicatedragon]

If I understand correctly, the SPHQ is only active during Communism, in which the distance to the nearest palace isn't important for calculating corruption. It seems you just need to find the distance to the FP for each city. It would be interesting though to support multiple FPs, for mods and such.

 

[Tarzus]

In the worksheet, distance is chose between "D for communism" and normal d.
In column L:
d = IF(govt.=comunism,give d for comunism,else give MAX(x,y)+MIN(x,y)/2)
round down.

So when you choose govt. communism, distance auto take care of this.

Working towards multiple palaces..

 

[Tarzus]

syndicatedragon,

I had updated a version of 1.1 beta for checking.

Updated events:

1) worksheet now supports FP and SPHQ wonders for distance calculation.

2) active/inactive SPHQ available (based on government).

3) distance x and y are now seperated into x1 y1 (from Capital) x2 y2 (from FP) and x3 y3 (from SPHQ), where user just have to enter x1 and y1.
(for mod, how many FP shall the game support ? I'm considering whether to add more)

4) x and y are now vector (direction) oriented. NE has been assigned as positive x distance from capital, NW as positive y, SW as negative x and SE as negative y. This is because we need to find distance from all three palaces. For example FP may be on the East side of your map (with Palace at the centre of map) and a city may be at the west side. The distance x2 y2 of this city is further from FP than Palace. Without vector-orientation, x2 y2 may becomes closer to FP hence giving wrong distance.

5) OCN for all map sizes changed to C3C version

After finalising, version 1.1 will come with some explaination and notes later.

Regards

 

[syndicatedragon]

I still don't think you need the distance to the SPHQ. In Communism, distance corruption is fixed, and in other governments the SPHQ does nothing. The distance to it never figures into any calculation.

Maybe you could keep those columns, though, and name them something else (fp2?). That way your sheet would already support 2 FP type buildings, plus the SPHQ.

Once you get 1.1 knocked-out, I'm planning on importing it into OpenOffice Calc. Unfortunately it didn't seem to import very well, so if I succeed in "porting" it to Calc, are you interested in distributing that as well?

 

[Tarzus]

syn,

I included distance for sphq because i thought that's what you prefer (for mod you said). Your suggestion is good and I'll make those changes.

OpenOffice - Yes you are welcome. You can send me some articles later through private message and I can add them into the first post.

regards