Cultural 20K Calculator (uses excel)

[Greebley]

Since I have never done a 20K win, I made an excel spread sheet to do some calculations for me. I thought others might get some use out of it too, so thought I would upload it.

20K Calculator


Edit: My calculator was improved by Xevious and then by AlanH, both who know excell better than I. Alan's version and a calculator by Sir Pleb are located in this thread:

SirPleb's 20K Calculator

Alan's 20K Calculator

Note that you enter dates differently in them. A building is built "between" turns. For Sir Pleb's Calculator you enter the date after its built and for Alan's you enter the date before (Which is also the date listed if you use F5 to look at the culture of the city). I recommend one of these two over my own.


Its pretty easy to use. Just enter the dates that you build (or plan to build) an improvement, or wonder in the spreadsheet (I shaded these squares light blue to indicate where to enter the values).

The top 3 rows give the culture your city will have in the year 1750 AD, 1950 AD, and 2050 AD, assuming you get the listed items on the listed dates.

BC years need to be inputted as negative numbers. Make sure you put in actual years that exist in the game. If you put -2000, then the program fails as -2000 is not a year that is used. You have to put in -2030 or -1990 instead.

[Edit: I updated the values from the PTW 1.21 Civopedia]. Definitely report any problems you see with the culture for a given improvement or wonder. I tried to be very careful as any error would cause big problems. I also left 2 unused slots in case I missed a culture producing item.

It may also be you need to enter the year before the cultural item is built. It depends on if you get credit for the build the turn it is built (which I think you do). So if your temple is built IBT -2030 and -1990, then I think -2030 is correct.

I tried to test the program, but I am the only one to have used it so remember it may have a subtle error. If anyone finds such, post and I will fix. I am not the worlds most knowledgable excel user, so I am sure there are better ways of doing things. Such suggestions are also welcome.

I wasn't positive if a utility already did this, but I didn't see one. If I am duplicating something that already does this and more, then tell me. Conversely, if you think this is actually useful beyond the GOTM then tell me that too. I could post it in the utilities forum. I am still fairly new and not completely sure about the procedures for doing things like this.

Edit: Updated the cultural points of wonders to match PTW 1.21 Civopedia.

[jeffelammar]

This calculator looks great except for 1 thing. The culture values you used are in many cases wrong.

The ones in the Info center on civfanatics are wrong.

You had a great idea here, but in order to get the numbers right, you will have to use the civilopedia rather than relying on the CivFanatics site.

[Greebley]

Nuts. Ok I will fix.

Edit: I looked at the PTW 1.21 Civopedia and matched the values to that rather than website.

Changes:
Apollo became 2 culture (was 3)
Forbidden Palace became 2 culture (was 3)
Shakespeares became 8 culture (was 6)
Cure Cancer became 3 culture (was 4)
Hoover became 2 culture (was 3)
Bachs Cathedral became 6 culture (was 5)

Added a sum of the culture from all improvements as a double check. The sum was 127
17 from normal buildings
17 from small wonders.
93 from great wonders.

[Sailorstick]

Yikes. This is going to be hard. I just kept realistically adding wonders and I still couldn't get 20K by 2050.

[Xevious]

I hope you don't mind Greebley, but I took the liberty of making some modifications to your spreadsheet. I put in Culture per Turn and Total Culture per Turn columns so that you can look down at any year and see where you are at, just like in the game. There is also a box above the wonder chart that shows the 20k date and culture per turn as well as what it would be in 2050AD. The only problem with this implementation is you have to sort the wonder list by date built ascending in order for it to work. I copied the original wonder table to the right so you can copy it back over after playing with it.

I input Karasu's numbers to test it out and it is close, but not exact. I'm not sure why this is. The formula I used is pretty complicated but I will try to explain it if anyone is interested. The reason for the complicated formula is I was trying to keep it as written, to update automatically as you entered the dates of the wonders. The problem with that is, you have to sort the list, so it won't be right until you do unless you build everything in the listed order (which is unlikely). Since you have to manually sort the list anyway, I should just write a macro to figure out all the data and then I wouldn't have to sort the list. If I get time before I leave for vacation Friday I might give that a go.

EDIT: Just realized this might be considered spoiler, so I'm removing it. Will put back up without Karasu's numbers.

[Xevious]

Ok, here is my modified version, with blank dates.

EDIT: Fixed the problem noted in next post.

[Greebley]

Modifications are good

I wanted to print out the date when you hit 20000, but didn't see a way to do it.

One oddity. The sort function is greyed out (so I can't select it)when I load it up. It fixes itself if I go to sheet 3 and I can do the sort just like you mentioned.

It seems that going to sheet3 re-enables the sort. Any idea why?

[Xevious]

Ok, I've fixed the problem, somehow I had the tables on both sheet1 and sheet2 and they were grouped somehow. I deleted both sheet 2 and sheet3 and it works now.

The formula's I used to find the culture per turn use lookups to find the current turn's date in the list of built wonders and add the associated culture amount. After 3000BC it also starts looking for the 1000 years ago date to see if it's time to add the culture again for doubling. It adds both of these values to the previous turns cpt to get this turns cpt. The problems I ran into with the LOOKUP function is that if it doesn't find an exact match, it uses the next lowest value. This of course really screws things up, so I used another function called MATCH which will return a #N/A error if the item isn't found. I then used ISERROR to detect the error indicating the nonexistance of the item I'm looking for.

Finding the 20k mark was also a chalenge, since it finds the next lowest value, I have to take that value and add the cpt to it and then find that number in the list to get the accurate year.

It still doesn't account for anarchy periods, which I just found out don't accrue culture.

[flexo]

Don't know if you had managed to update it again. but the one i downloaded and used didn't do a very good job of prediction, it was off by more then 100 years. Prediction was mid 1830's, which in reality became end of the 1960's.

I don't know if even subtracting the total of 9 turns i spent in anarchy would make up the difference (4t to monary, 5t to democracy).

[Xevious]

flexo, would you mind sending me a list of build dates and when your anarchy periods were exactly so I can try and find what's wrong?

Please don't post them if they are for the current GOTM.

[flexo]

@xevious: you got pm

[Xevious]

Please be sure to sort your data table if you use my version of the spreadsheet. It returns incorrect results if the build list is not in chronolgical order. There are instructions above the build table on how to sort the list.

[flexo]

ok i'll try that and see if I get another result.

[Creepster]

flexo and Xevious please use PM's to discuss details instead of cluttering the thread.

[AlanH]

I had a feeling there must be a simpler way that would avoid having to sort the data. So here's my attempt at a solution using sumif() functions. You just enter the dates and it does the rest, and your dates don't need to be exact turn dates.

[edit]For those of a nervous disposition, you don't need to enable macros either. There are none used in this spreadsheet.

[edit 2] Kudos for greebley for coming up with the idea and the basis for this tool. Now you've got me going, I thought I'd better finish the job by adding a facility to stop culture accumulating during anarchy. So I've removed the first one and attached version 2 instead.

You just enter each date when an anarchy period starts and the number of turns of anarchy you suffer. I've allowed room for up to eight periods - should be enough for most of you. The spreadsheet automatically puts ONEs in the anarchy column against each of those turns and culture doesn't accumulate during them.

I've put worksheet protection on this version to help avoid accidental changes to the calculation cells, but there's no password if you want to modify it.

Let me know if there are any problems with this version.

[edit 3]I just found SirPleb's 20K culture calculator in the Utilities department. As there was a request for shield cost calculation to be dependent on civ attributes, and also an anarchy handler, I have added cost calculations to my attached version 3 here. SirPleb's calculator also does exciting things like goal seeking, which I haven't tried to replicate ... yet

[edit 4 !! ] Here's a more polished version with a help screen and a generally tidier interface. It now has a space where you can enter your own target date and see what the model estimates the city cuture to be at those dates.

It also corrects a small error in the calculations, and I have tested it with three cities from my GOTM21 submission. The test results are interesting. The most noticeable fact is that the per-building culture seems to accumulate the same as the model (with one small exception in my testing), but if you add up the culture for each building in a city in the F5 screen it does not necessarily come to the total displayed for that city. In the saved files I checked there appered to be some extra culture points added to the city's total as time went on. So the model is several turns pessimistic by 1740 AD, the date of my last test file. I have no idea how to compensate for this "error" as it does not seem to affect all cities at the same rate.

[Last Edit (I hope)]: I've now released version 6, and posted it in Sir Pleb's thread in the Utilities forum. So I've removed the earlier version download link that was here.

[Greebley]

Alan, I like your spreadsheet

One minor thing I noticed was that the help text tended to overrun the text boxes... as if my text was bigger than it was for you. They last word would often wrap and be unreadable. Any idea why this would be happening for me? The help page also went off the bottom cutting off the last line.

I finished the GOTM. I will try it out on my culture city when I get home and see how it does on my city.

[AlanH]

I did it on my Mac, so it's possible that the fonts are different. I used 14 point Ariel. I've just tried opening it in OpenOffice.org, which probably emulates the Windows environment better than my copy of Excel for Mac, and I see what you mean. I tried reducing the font size to 12 and that seems to fit better, although it looks small in Excel on my Mac. I think the problem is to do with the different definitions of a "point" between Mac OS and Windows, but you'd think M$ could have resolved it in two versions of the same product.

As OpenOffice.org doesn't handle the SUMIF function I've made the changes in Excel and produced a new version, v5. It's attached to this post. Please let me know if this works better on your machine. I haven't changed any of the functional aspects of the spreadsheet, only the font sizes.

My GOTM22 is still an early work in progress, so not much of a test for the system yet. Note my comments about the errors I found. I'd be interested if you can count up the values of your individual improvements in F5 and see if they add up to the same value as is given for the city.

[Edit] The current version (v6) is now in Sir Pleb's thread in the Utilities forum so I have removed the v5 download link that was here.

[Dianthus]

AlanH/Xevious,

There seem to be some situations where your spreadsheets give different results than Sir Pleb's. I haven't checked this against Civ, so I'm not sure which is the right one, just that they're different.

An easy example is with just 1 building, The Great Library in 450 BC. Sir Pleb's results in 4848 culture in 2050 AD, yours gives 4842 culture in 2050 AD. This appears to be due to Sir Pleb's doubling the culture in 540 AD, yours in 550 AD.

It looks like Sir Pleb has done quite a bit of research into the doubling point, so wouldn't be surprised if his was the correct one.

Dianthus

[AlanH]

Changing the doubling point would be trivial in my version if it's necessary. However, I have been checking it against my current GOTM22 as I have gone along, and it's spot on so far. I'm a bit slow, so I only have 3 improvements/wonders that have reached 1000 years old, but if there was a consistent error then it would already have shown up.

I also have PM feedback from one player who has finished GOTM22, and the spreadsheet predicted his 20K culture date precisely. I'll ask him to confirm that the calculated culture value at that date was the same as the in-game value.

Please note that, in this spreadsheet, you need to enter build dates as the date BEFORE the announcement that it is completed, which happens between turns. This is also the date shown in the F5 screen for each building.

If you have evidence that the spreadsheet conflicts with an in-game city culture score then I'd be very interested to investigate. I have seen some errors, as detailed in my notes, but, save for a single odd-ball building, they only relate to the in-game culture summing being inconsistent.

[Edit] I did fix an error in the spreadsheet between versions 3 and 4. Have you used one of the current versions - preferably v5?

[Dianthus]

I'll check it out on my GOTM 22 game tonight when I get home. I've got a number of Wonders, though I guess I'd better not mention any Spoiler info here .

[Edit: Yes, I was using the version 5 spreadsheet.]