 |
| |
|
|
E_T
|
 |
Orlando, Florida
Mar 2001
time: 00:19
|
|
This is a thread for the teaching of the use of my planning spreadsheet. Because of the complexity of this "tool", I will add to this as time progresses. Discussion is welcome.
This Excel Spreadsheet comes from a LOT of work through various games. If you want to use it, please use it for either this game OR for your own personal game. Please do NOT take this to another game that is competeing against Apolyton (like a certain someone *cough* Trip *cough*).
This has several work sheets, sone for the cities, some for flagging any conflicts between tiles that have more than one city using them, A sheet for figuring cash & research budget (newest addition), a general worker task tracking sheet, a worker time sheet, a sheet for beaker calculation for techs & a sheet for Corruption (not quite working, but still useful for some calculations). Also, a Master WF/build sheet for all cities.
The purpose of this massive work (and when you have LOTS of cities, really massive) is to plan things, over a long term time frame. For instance in a particular city, you want to build a Barracks, then 2 Spearmen, then a Library and then 2 more Spearmen, this allows you to plan for when these things will be completed, over time, with a changing enviroment where workers compete improvements, the city grows and changing WF's. This can allow you to Plan your WF to help be more effiecent with both food and sheilds. This is also particularly good to use for planning shortrushes, as you can see when the best time to do any of them to save on your cash reserves.
And ofcourse, as you change your cities WF's, you also change your trade balance. With the Budget sheet, you can plan for Cashflow Items like Shortrushes, Upgrades & Deficit Research, because you can see when you will run out of cash with a particular Tax % over time. At the same time, you can better plan for when your research will be completed and "tune" your Research to keep from wasteing as many beakers as possible. And if you need to add Lux slider, you can also see how that will effect your cashflow, over time. The Budget sheets also figures in Unit support, depending on a paricular Government setting.
Excel, for those that don't know how to use it, is a glorified calculator program, dealing with Rows and Columns of individual cells. The main thing about this program, is that you can set a calculation that takes any input into any cell and show the results into another cell. This calculation can be repeated in the Colums & rows. You can even take values for one page and use them for calcuations in a separate page, so you can have a worksheet that is very complex, and the values can change from just a few inputs.
This tool works really well with an accompaning Simulation, but that is not neccisary. This Tool is best used in a PBEM enviroment, as SP & Realtime MP Play is just too fast to even think about this kind of detail planning, but is perfect with the time between turns in PBEM to do some long term planning & Micromanaging.
This spreadsheet that is available to download, was from my PBEM Tournament game.
Spreadsheet Zipfile - updated 6/26/05. Turn 110 was the last one that I had played in that game, when I had quit it.
I'll be adding to this thread, over then next several days, but this will get this started.
E_T
Last edited by E_T on 26-06-2005 at 09:14
|
|
| |
|
|
|
ormuzd
|
|
I have watched the spreadsheets - they seem quite complex and some things are obsolete (like the 40 turns research).
It seems you have some kind of colour legend which I did not understnad very well. Some of the tables seem to be not updated regularly but it is possible I just did not understand their purpose.
What I can add to them is about tech costs:
Bronze Working is 3, not 2
Add Fascism (130) and ironclads (100) to industrial age techs
|
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
quote:
Originally posted by ormuzd
I have watched the spreadsheets - they seem quite complex and some things are obsolete (like the 40 turns research).
|
Yes, the 40 turn research part of the Beaker Calc page was a first cut at figuring out Research times and %'s needed to get the most for your research "dollar". But because of it's unweildiness, it was replaced with the Budget page, which is much more versitile than the Original.
Still, the Beaker Calc page still has some uses, like figuring out how many beakers a tech might cost, if others already have it. It's a good tool for Intelligence and also for figuring out the worth of a tech, if you sell it.
quote:
It seems you have some kind of colour legend which I did not understnad very well. Some of the tables seem to be not updated regularly but it is possible I just did not understand their purpose.
|
Ah yes, the colors.....they mean different things on different pages, it all depends. Part of this, is the limitation of "stock" colors that Excel has available (atleast the older version that I'm using). Also, some of the Color combinations don't really show us that well.
Mostly, they are used as visual referance flags, to tell me, for instance, if I've mod'ed a calculation tile, to take account of things like city growth and tile improvements (or forrest chops or shortrushes), in the Cities Accumilated Sheilds and Food Box Values. This is extremly important, because, if I change my mind or as I will often do, copy the whole sheet, change it to look at different paths, like faster growth Vs Slower growth over the time of a long build (or a set of several builds) and see what the overal differences will be and then decide on the "best overall" path. The colors help me to remember to change those back to the "stock/standard" calculation (and help to remined me to use them in other instances, incase I've forgotten). Although, I do try to be consitant with most of the colors, sometimes, I just have to repeat some of them. One of the colors I use most regularly is the Medium Gray, to denote Turns that are now in the past and the Light Yellow for the very next turn that is to be played. I also like to use a Redish Orange Background with a bright yellow lettering for Shortrushes, let's me know that I'll be spending money at that time....
Some colors, like Lite Gray, helps to denote Input Cells. I also use Purple to show when a tile is Improved and then, if that tile is being used, show the same color (or similar, if a growth is also happening - Plum) in the Accumulation Columns.
quote:
What I can add to them is about tech costs:
Bronze Working is 3, not 2
Add Fascism (130) and ironclads (100) to industrial age techs |
I'll add them fairly soon. Anyways, I have a cat sequestered in a flea free room, while I play cleanup & Flea eradication thoughout the rest of my house, so I'll post more in a day or so.
E_T
|
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
quote:
Originally posted by Hot_Enamel
I use excel spreadsheets daily at work, and have only 1 thing to say ....
 |
Shoot, this was nothing, compaired to the one that I had for the PTWISDG. That one weighed in at more than 5 Meg when Zipped up. Something like, IIRC, 40 cities. Hehe, I'll tell you what, that sheet did allow me to get all of that new land settled in so short of a time as possible.
* E_T remembers the astonishment of the Consuls & ministes, as we had Settlers moving all over the place, multiple cities founded every turn and 2 turns in a row with 6 settlers in transit (and not all of them the same ones). It was fun, especially making sure that they didn't go to the wrong place.
All planning provided by this tool and Simulations to help verify them.
E_T
|
|
| |
|
|
|
ormuzd
|
|
Thanks for the clarifications. I know the colors mean different things and didn't even think that each color has an unique meaning.
quote:
I'll add them fairly soon. Anyways, I have a cat sequestered in a flea free room, while I play cleanup & Flea eradication thoughout the rest of my house, so I'll post more in a day or so. |
You don't need to post the excel file anymore. If I have the time I'll try to apply the spreadsheets to one of my current games just to figure out how could I work with them and eventually help for the finals with these matters.
|
|
| |
|
|
|
Sipahi
|
|
Aachen-Germany
Jul 2003
time: 06:19
|
|
quote:
Originally posted by conmcb25
And I want to bring up that the use of these spreadsheets in an APOLYTON thing only.
No using this outside of the C3C ISDG without the consent of E_T.
He put a lot of work into this and it stays here. |
Don't worry. We promise bindingly that GWT will never use this tool to play against APO in any PBEM DG in the Future. And as you know our word counts.
Last edited by Sipahi on 10-05-2005 at 13:51
|
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
This is the part dealing with the City Page(s). This part is the heart of the whole shoot'n works, so pay attention.
First off, I have all of the city Pages (and a few others) set to have Panes Frozen, on this one, the part towards the top with the city name and the Column descriptions set at the top. I also have the Turn number and Date set at the side. This allows me to move around the sheet and know where I'm at.
The first thing is there is one part of each city sheet that does NOT show up when the Panes are frozen. This is the part that sets the Values for Corruption in the city. See Picture.
As you can see, it's located above the Flags and Total/Corrupt Column Headers. There are 4 values that can be set (I use x.xx% format for more exacting calculations). Values are in Percent. The 4 values are Divided into the one for when the city is Not Connected to the trade network and the other 3 are for when it is Connected (you aren't very likely to have a city with a courthouse AND/OR in WLKD while it's not connected, so for simplicity, I didn't concern myself with that part). They are the Basic Corruption/Waste Value, Corruption/Waste for when you have a courthouse OR WLKD and Corruption/Waste when you have a courthouse AND WLKD. The Values are used for both Shield & Trade Calculations, depending on what Flags are Set. If you'll notice, you will see 3 flags in the Main Flags Column that are Connected, Court & WLKD. Depending on what Flag is set, the Appropriate Corruption/Waste Value is used for the Total/Corrupt Shields and Trade Values for that turn (depending on the Current WF settings).
In this example, Persepolis was the Capitol and in C3C, it has at least a 1% value of corruption, in Despotism. The other values were not used. A city is either (for the Calculations) Not Connected of it Is Connected. Then, if it IS Connected, then the Court and WLKD Flags come into play. So, as it currently is setup. you can have the Court and WLKD flags set and the Connected Flag NOT set and it will use ONLY the Not Connected Value for the Computations. All of the Flags are either 1 or 0.
Just to give you an idea as to why I didn't include all Possibilities, take a look at the formula that I had to use for the Total Shields Calculation (taken from another intermediate Calculation and the flags).
code:
=IF(IF(S10,IF(T10,IF(U10,EH10-ROUND(Z$6*EH10,0),EH10-ROUND(Z$5*EH10,0)),IF(U10,EH10-ROUND(Z$5*EH10,0),EH10
-ROUND(Z$4*EH10,0))),EH10-ROUND(Z$2*EH10,0))=0,1,IF(S10,IF(T10,IF(U10,EH10-ROUND(Z$6*EH10,0),EH10
-ROUND(Z$5*EH10,0)),IF(U10,EH10-ROUND(Z$5*EH10,0),EH10-ROUND(Z$4*EH10,0))),EH10-ROUND(Z$2*EH10,0)))
Also, as you look at the Picture, you will see things like Size, Food Surplus & Food Box, Trade, Building (What?), Shields Needed, Shields Output & Accumulated & Remaining Columns. In the Flags area, you also have Despotism Flag, GA Flag and Granary Flag. These all are used for Shield, Food and Trade Calculations, further in the sheet.
All Add some more to this soon, time for a break.....
[EDIT]
O.K., there is one thing that is very useful when it comes to the Corruption/Waste settings. If you have a changing situation, like you have the FP being built at sometime or you build a city that is closer to your Palace or you change Governments, your Values are going to change. So how do you take that into account? One way is to, first Sim out the situation, figure your new Values, then plug them into the Sheet in a particular fashion. Lets say, that City #5 has a Connected Value of 12.5% in Despotism and 10.7% in Republic. How do we allow for the difference in our long term calculations?
Simple, you Set the Unconnected Value to the Connected Value (IF and ONLY IF you are NOT going to have any other Corruption/Waste Modifying Events happening beforehand) and you set the new value in the Connected slot and you have the flags set to show that difference.
Computations over time OR How am I going to get that Settler (or Mil Unit) Pump going
The majority of the calculations on the City Sheets are Horizontal (within a Row), some of them are going back and forth, but mostly from left to right (as much as I could, that is). There are 4 Calculations that are done from Top Down (over time).
They are:
Turn Number
Food Box
Shields Accum(ulated)
Accum(ulated) Trade
ALL of these need a set starting amount at EITHER the top of the page OR on any turn.
Turn Number - Simple +1 Sum as the Rows go down. Keeps track of the turn number as you progress through the game (Inserting and deleting Rows as as you add new and remove old stuff).
Food Box - the Value in this Cell is actually taken from the Food Box Calculation Section (way over to the right of the sheet), but this value, in this cell is used for the next row's calculation. You might want to set this value to something other than calculated, if events in the game changes this value from what was expected. That might be from the Growth tile that you had anticipated the game to use was the wrong one and thus changing the actual value from what was anticipated.
Shields Accum(ulated) - This is the actual Accumulated Shields Calculation Cell (unlike the foodbox one). This is a twofold calculation, as it first checks to see if the Build was completed (subtracting the previous Shields Still Needed from the previous Shields Output and sets to 0 IF the result is Less than Zero, i.e. 2 Shields Still Needed, 3 Shields Output, 3-2 = -1, i.e. build done & Shields Accum{ulated} is Zeroed). It then, if not Zeroed, adds the previous Shields Output to the previous Shields Accum(ulated). This value, like the Food Box, might need to be Set Manually, due to Unanticipated things happening in the game (which can include an unanticipated ShortRush).
Accum(ulated) Trade - this Column is also a simple addition of the Previous Accum Trade and the Previous Trade (due to Corruption/Waste). This Column has no real use, except to help in deciding between different courses of action. Say you want to look at one particular city with a goal in mind (like a Wonder build or several different approaches to a multi-build queue? One of the things that you can look at, to help decide between the different courses is how much trade, over the time that your looking at, did that city put out. Believe it or not, that can be a factor in deciding on what your going to be doing with (and how your gong to be doing it) in a City over a 30 to 50 (or so) turn time frame.
Now, it's getting late and I'll put off adding more to this until Tomorrow (or so, as Lost is on Tomorrow night....). But this should give a start for how this major work of S$$$ (or Art, depends on your point of view  ....) does what it does.
A quick caution, Be VERY VERY careful when Copying & Pasting, especially when moving columns/rows around (if you don't like how things are quite setup). Save often and make sure that your ready to Hit Cntr-Z to undo something. I'm not saying NOT to do any Copying & Pasting, just be very careful. I'll say more on that on another session.
[/EDIT]
E_T
Last edited by E_T on 21-05-2005 at 21:06
|
|
| |
|
|
|
ormuzd
|
|
thank you for the information
|
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
The basic of any city is your Population, what it’s doing and what effects come from that. Simple you think, just set your WF (WorkForce - more on that in a bit) to whatever setting will get you that build a bit sooner OR your Population Growth Sooner OR to get the most out of your trade and then let it go about while you Move around Units and Contemplate you next Invasion with your neighbor. But then, to get more out of your Cities, you need to do something (to the Average Warmonger, abhorrent) that has the airs of actually Micromanaging them. And the task can get more cumbersome as your Empire Increases and your Cities Grow, requiring you to hake sure that they stay happy and not waste the turns of your careful plans for that Main Wonder build (or whatever). And it gets very, very complex and the Average Person goes all glassy eyed when having to deal with all of that, they want to kill the others and have done with it….but that isn’t how this game works.
The evolution of the Spreadsheets had it’s inception with the SPDG 1, where I was (for a time) the Domestic Minister/City Planner (same task as MoI, just a different game). I was having to get orders ready for an expanding and Growing Empire for our 5 turn Turncoats. I was having to think of what I needed to do for several turns in advance, what to build and where, what growth is going to do over time to those builds, does a city need to grow as fast, until we get happiness support into them, etc., etc., etc.….
I had realized what I had needed, but at that time, didn’t have any spare time to create what I had needed.
Then I had some time, towards the end of that game and during the beginning of the PTWDGI to put together the first version of my sheets. I had found, not only could I plan for the next few turns, but I could plan for as many turns as I wanted to, by just adding more and more to the bottom. An outstanding Tool to use, if only people knew how to use it (If they even had Excel to begin with). Arnelos (and a very few others) was able to understand it and with proper teaching, was able to use it for their own fiefdoms. Arnie was the mayor of Madrid and with the sheet, had that city as our best producing city of all of the others. He had then disappeared and I had gone over to take over the PTWISDG MoI from Dominae. But my sheet had gone though some different Versions before that time, to account for different things that I had wanted or needed it to do.
This version, as I had stated in a previous post, is almost perfect (at least within what this program will allow me to do). I have a very good working knowledge with Programming in general (I was a Computer Engineering Major a long time ago, never got the Degree) and I know how to Program in C++ (Amongst a few others, but C is my main program language of choice). But for me to program something like that from scratch would take a VERY long time and would likely be as big as the Civ Program itself and would likely not do exactly what I wanted it to do. Excel, with some of it’s limitations, would be better at this, as most of the figuring is just rote addition, subtraction, multiplication and division, with a few decision trees in the formulations. And with it’s ability to Cut/Copy and Paste/Insert, it would be best for many iterations of the same formula, over a time frame. Did I say that it had some Limitations  , well this is how it works.
Again, you have the 3 main things in a city: Food, Shields and Trade. They accumulate to complete builds, conduct research (or raise money of use for Luxuries) and Grow the Population. Simply put, Shields and Trade requires Population. This sheet, in all of it’s apparent complexity, just keeps track of those 3 things.
Let’s take a look at the city sheet:
In this picture, you see the leftmost part of the sheet, what I call the ‘main page’ This is where it all comes together, so to speak. It was setup for relative ease of displaying in a JPG format (I use Coreldraw to import the file or screenshot and make a picture out of it).
Your Columns, from Left to right:
Turn # (number); Year; WF (WorkForce); Size; Num(ber of) Spec(ialists). Then Food Surplus & Food Box, followed by: Trade; Building (what?); Total Shields Needed (for what your building); Shields Output; Shields Accum(ulated) & Shields Still Needed. Then you have 3 columns of notes/worker things, used to help keep track of things like something getting built to a reminder for needing Lux slider to a Shortrush notification to a worker Improving a particular city tile.
Then there are the Flags Columns (3 for corruption figuring, 2 for GA calculations and 1 for Foodbox calculations). The Connected, Court & WLKD Flags are discussed in the Previous Post, so we won’t go into them here. The Desp(otism) and GA flags deal with the calculation of Shields and Trade, when in a GA and if you are In or Out of Despotism for how the GA is calculated (because of the Despotism Penalty). The Gran(ary) Flag is for the Foodbox Calculations.
Details of this section, before we go on..
Turn number - self explanatory, the turn number of that row of the sheet. Simple +1 of the previous cell. Can be set to any number value and will do +1 as it goes down.
Year - currently has to be manually entered, but I’ll have a master sheet made up, for copying and pasting, to make things a little bit faster. As you don’t always know what turn number it is in the game without going to the F11 page, it helps you to orientated yourself to the sheet when playing a turn.
WF - Input Cell. The WF settings of the city. Manually entered, but it’s easy to copy and paste a Cell or sequence of cells for repeats. Just type it in, copy and highlight where you want to paste and you don’t have to do a lot of repeat typing. Anything typed in here is repeated to the right and on the Master WF sheet.
Size - Input Cell. City pop level. Used for Foodbox size and Budget sheet Unit Support (due to Government type) calculations. Anything typed in here is repeated to the right and on the Master WF sheet.
Num(ber of) Spec(ialist) - Input Cell. Currently, no use in any calculations, but is still useful for a quick note of any specialist that you need or use.
Food Surplus - Calculation Cell. Your food surplus, after your food use due to city size. Modifiable due to Worker actions. Example, a multi-turn Irrigation is being done on that tile WHILE that tile is still being used, then the Surplus for that turn will change and the excess will have to be accounted for, i.e. manually added into that tile’s calculation. This is redisplayed on the Master WF sheet.
Food Box - Calculation Cell that is used in downward calculations (over time). Modifiable to set a new start point in the time calculations. I use a red text (added manually) to better show at a glance when the foodbox has been emptied due to Pop Growth. This way, If I forget to change a size or WF, it helps to remind me. [EDIT] The size of the foodbox is determined (in the foodbox Calculation section, way to the right, by the city size, i.e. size 1-6 gets a 20 foodbox, 7-12 gets a 40 and 13+ gets 60).[/EDIT] This is redisplayed on the Master WF sheet.
Trade - This is a Redisplay (i.e. showing value of another cell) of another Cell that is further to the right. It redisplays the Total/Corrupt Trade (the Trade left over after corruption/waste). Not used for any other calculations.
Building (what??) - Input Cell that is only used for keeping track of what your building. This is redisplayed on the Master WF sheet.
[EDIT]
Total Shields Needed - Input Cell. Used for the Shields Calculations. Change this, if you change your build to something other than currently set. For instance, you Auto upgade from 30su Horsemen to 70su Knight. Or, you want to build something else, like using a Barracks for a Granary prebuild, so that you can accumulate your shields and know when you have to have Pottery before your prebuild is wasted.
[/EDIT]
Shields Output (or SPT) - Redisplay Cell. Redisplayed from the Total/Corrupt Shields (the Shields left over after corruption/waste). Used in the next turn’s Shields Accum(ulated) Calculation. Modifiable for later calculations. I only modify this cell if a Growth/Shrink happens and you need to properly account for it. Growth/Shrink happens when you build a Worker/Settler at the same time as you have Pop Growth. More on Sequence of Events a little later. This is redisplayed on the Master WF sheet.
Shields Accum(ulated) - Calculation Cell that is used in downward calculations (over time). Taken from the last turn’s Shields Output and Shields Accum, resets to Zero when a Build is done (determined by subtracting Shields Output from Shields Still Needed and checking if Zero or negative). This Cell is Modifiable for things like extra shields from Growth & Worker actions. Also can be set to a Constant value, so that any changes in how shields are calculated won’t effect things that have already been done in the past (i.e. changing Corruption values and having to account for them in the future). This is redisplayed on the Master WF sheet.
Shields Still Needed - Calculation Cell. This subtracts the Shields Accum from the Total Shields Needed for this turn. I don’t normally do any Modification on this cell.
Taking a break, still have add some stuff to this soon.
E_T
Last edited by E_T on 21-05-2005 at 21:47
|
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
First off, before we continue, I need to explain the WorkForce Annotation (for those of you who aren’t familiar with it). For the ease of writting out orders, I had come up with the WF (WorkForce) terminology. It’s relatively simple:
In this picture, you start at the 1 O’clock position of the City tiles and count clockwise and spiral outwards. The term WorkForce was also a way to distinguish between the Workers in a city (and what they are doing) and the regular Worker Units. So, when someone says something about sending a Worker over somewhere OR we need a WF change to work a particular tile, we can easily distinguish between the two of them. In this Example (taken from the old SPDG 1):
Apolyton has a WF of 2-6-9-13-15. Simple as that. The only problem has been coping and pasting, for ease of changing, but then forgetting to change the values to what they were supposed to become. Simple error in City Orders, but can be disconcerting when the Consuls play the turn….
So, now that we are all on the same page in relationship to WF, lets continue, because this next picture shows the section dealing with WF:
From Left to Right: The last of the Flags Columns; then a spacer Column (this is used in the Corruption Settings area, for text spacing. It can be narrowed down, as you see fit); then the Total/Corrupt Columns of Food, Shields & Trade; then Accum(ulated) Trade,; then size and WF again and then the WF settings for all 20 City Tiles and then the start of the City Center Tile value Columns.
Here they are in more detail, starting with the Total/Corrupt Columns:
Total/Corrupt Food - Calculation Cell for the Total Food produced in the city. The food surplus is figured from this, from the City size (2 food per pop). This value is figured from the Sum of all of the Food in the tiles that are selected by the WF Columns and the City Center.
Total/Corrupt Shields - Calculation Cell for the Total Shields produced AFTER Corruption/Waste. This value is set by the Corruption Values and the Flags dealing with Corruption/Waste (Connected, Courthouse and WLKD Flags) This it taken from the Raw Shield total at the far right of the sheet.
Total/Corrupt Trade - Calculation Cell for the Total Trade produced AFTER Corruption/Waste. This value is set by the Corruption Values and the Flags dealing with Corruption/Waste (Connected, Courthouse and WLKD Flags) This it taken from the Raw Trade total at the far right of the sheet.
Accum(ulated) Trade - Calculation Cell over time. Added last turn’s Accum Trade to this turn’s Total/Corrupt Trade. Useful for figuring out different scenarios of City Growth vs. Build completion times as it gives you another measure as to what the city will be producing over whatever time frame that you want it to. Set the "start" cell to Zero and look at the point, further in time that you want to examine. Not used for any other calculations.
Size & WF - both of these are Reprinted from the same Columns to the left (in the previous post). This is to allow making changes in WF much easier, as whatever size and WF settings you have for that row have been scrolled off of the page and you don’t want to have to scroll right & left while making changes, you can get lost easily….
WF Settings Columns, #1 to #20 - Input Cells. Set to either Zero or One (0 or 1). This is used for the calculation of Food, Shields and Trade by setting to Zero (for not being worked) to One (for tile IS being worked). This value is multiplied with the specific tile’s value and added to the City tile value for the totals. I use a light gray background here with size 8 Black Zeros and size 10 Red Ones for ease of viewing. I do a LOT of copying and pasting here. This is one of the parts that takes the most time to work with. If Excel had some way to Parse a string of text, then I would have this section set up to parse a WF text string and set these values accoringly. An example: WF text string of 2-3-6-7-18 would have only the #2, #3, #6, #7 & #18 WF cells set to one, but because of the limitations of Excel, that isn’t possible (at least with my knowledge of Excel, that is).
[EDIT]
I also set to zero and Black out WF Columns, when I don't have full border expansion, so that I know that I can't use those tiles. As cities get built and there is border Expansion due to overlap of nearness of another city, then I unblackout those tiles. As I plan what cities goes where and when, I can also plan for those tiles to use. Another problem with this, is when you have overlaping tiles, how do you resolve them?? I have a separate Conflict resolution sheet that I'll explain at a later time to take care of that.
[/EDIT]
The City tiles values are here:
The Columns are City Center & tiles 1 to 20, with Food, Shield & Trade values for each of them. These are all Input Cells with changed made via Worker Infrastructure (or even Despotic Penalty - red Cells at top). As worker tasks are completed, the values change (purple cells). I make the cells for a particlar tile Blue to visually show that that tile is as fully improved as possible (i.e. Irrigated/Mined AND Roaded). Now, I could make this section more Complex, by having columns for setting flags for if a tile is Roaded/Railroaded, Mined OR Irrigated and to figure out things like Effects of governments and certain wonders (like Colossus), but that would make this much more complex. I might still do that, in the future, but not right now. The Green Columns are Forrest tiles, one that has Game and one without, so it can still change by chopping that tile and then Irrigating/Mining it. So, it’s also a visual for what can still be done. The light cyan color is for congectual changes, like If I’m going to Improve a tile, what effects will it have on future turns and when I will Need to work on (if I deside to Improve it) and have it done by.
Then we have the Total Food, Shields & Trade Columns - Calculation Cells. These are figured from the WF Settings and Tile Values. All added togeather. This is a Raw Value, before GA and Corruption/Waste. Those are figured in the last section.
The GA & FoodBox Calculation Section:
GA Sheilds & GA Trade - Calculation Cells. Each tile is checked for if it is being used (WF set), and if it has a value greater than Zero (i.e. a basic Grassland with no road and not next to a river will NOT get a GA Bonus for either Shields NOR Trade added, even if worked). It then checks for the application of Despotic Penalty and reduces that from the Bonus. Example, a Bonus Grassland that is next to a river (base of F2, S1, T1) is Mined and Roaded (making it F2, S2, T2). In a GA, it would get both a Shield and Trade Bonus for (F2, S3, T3) BUT if in a Despotic Government the Despotic Penalty is applied and it would have a GA Value of (F2, S2, T2) which is the same as not even being in a GA, for this tile. The Bonus Values (IF IN A GA) are added with the Total Sheilds and Trade Values and adds 1 (for the City tile, always gets the bonus) for the final GA Total. If Not in a GA, then the Value is the same as the Total Column. These values are then used to figure the Total/Corrupt Values.
After that, we have 4 more columns: 1 that was originally used as an Intermediate value for figuring the Accumulated sheilds (no longer used) and the 3 FoodBox Calculation Columns They are:
Intermediate Foodbox Addition Column - adds this turns food surplus to the Food Box for next turn’s foodbox value.
Foodbox size determination Column - takes the city size and makes the Foodbox size to the appropriate size (20 for 1-6, 40 for 7-12 and 60 for 13+).
Foodbox Emptying Determination and what is now in it - this Column checks to see if the Foodbox is now filled from the Intermediate Addition and Foodbox Size Columns. If not, then this is the new value used for the next turn. If the Box is filled, then sets to either Zero OR to one half the Foodbox size (with the Granary Flag set). When the city changes from size 6 to 7 (or 12 to 13), the foodbox size doesn’t change until the next turn, so then, the foodbox has the correct amount in it, for the next size. Example, the city changes from 6 to 7 and it had a Granary, so the foodbox was set to 10 for that turn. The next turn, the size is 7 and the foodbox size is now 40, BUT the amount that is in the box is still the 10 from before, just like they have set the game up to do (anyone remember them changing that because of people using settler/worker pumps exploit between those 2 sizes??).
And that’s it. The city sheet in detail. I’ll go into some specifics of it working in a later post.
E_T
Last edited by E_T on 22-05-2005 at 00:59
|
|
| |
|
| |
|
| |
|
| |
|
|
|
ormuzd
|
|
This is how two of the things in this sheet may be automated.
First thing - I'm working with MS Office XP. I'm not sure what features are added later and what were in the previous versions.
But here are my suggested improvements:
About sells with worker action the formula could be
=IF(OR(LEFT(C10)="1",RIGHT(C10)="1",NOT(ISERR(SEARCH("-1-",C10)))),1,0)
for the tile #1
and
=IF(OR(LEFT(C10,2)="10",RIGHT(C10,2)="10",NOT(ISERR(SEARCH("-10-",C10)))),1,0)
for tile #10
Additionally I found a way to make the 1's red and the 0's black without the need to chane it manually. It is the option Conditional Format and it is located in the Format menu. But I really doubt this was in the earlier versions of the Office.
I could post a blank sheet with lots of formulas just to make the thing more automated with less input from a human. I was able to set a building plan with setting things in only two columns - column C (worker force placement) and column ... (Total shields needed).
Everything else worked amazingly good. I need some expirience only and I'm going to use it in one game just for this purpose
|
|
| |
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
quote:
Originally posted by ormuzd
This is how two of the things in this sheet may be automated.
First thing - I'm working with MS Office XP. I'm not sure what features are added later and what were in the previous versions.
But here are my suggested improvements:
About sells with worker action the formula could be
=IF(OR(LEFT(C10)="1",RIGHT(C10)="1",NOT(ISERR(SEARCH("-1-",C10)))),1,0)
for the tile #1
and
=IF(OR(LEFT(C10,2)="10",RIGHT(C10,2)="10",NOT(ISERR(SEARCH("-10-",C10)))),1,0)
for tile #10
Additionally I found a way to make the 1's red and the 0's black without the need to chane it manually. It is the option Conditional Format and it is located in the Format menu. But I really doubt this was in the earlier versions of the Office.
I could post a blank sheet with lots of formulas just to make the thing more automated with less input from a human. I was able to set a building plan with setting things in only two columns - column C (worker force placement) and column ... (Total shields needed).
Everything else worked amazingly good. I need some expirience only and I'm going to use it in one game just for this purpose |
O.k., this works (the formula) sort of. For instance, if you have a WF of 10 to 19 by itself (WF of 11), it sets both the 1 and 11. Same with 20 and 2. AND it you have 11-20, then it sets 1, 11 and 20. I'll do some more testing and see what I can figure out.
As for the conditional format, what exactly did you use for that??
E_T
Last edited by E_T on 23-05-2005 at 04:15
|
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
Well, I got it to work, but I had to delve into creating a Macro (VB can be so different from C...) but I did it. I no have a function that will do what I want it to do, which is to automate the WF process
Here is the Code:
Input into a Cell is
code:
=WFdef(C28,"4")
Where it's (currently) set to read the reprinted WF column next to the main WF Allocation Columns and the Value that it looks for. In this case, it's looking for the text number 4 (and not the 4 in 14).
code:
Function WFdef(inputstr As String, number As String) As Integer
Dim tempstr As String
Dim temp As Integer
Dim pointer As Integer
temp = Len(inputstr)
If temp <= 2 Then
If temp <> Len(number) Then
WFdef = 0
Else
If inputstr = number Then
WFdef = 1
Else WFdef = 0
End If
End If
Else
Do Until temp <= 2
pointer = Application.Search("-", inputstr)
tempstr = Left$(inputstr, pointer - 1)
If tempstr = number Then
WFdef = 1
Exit Do
End If
inputstr = Right$(inputstr, temp - pointer)
temp = Len(inputstr)
If temp <= 2 Then
If inputstr = number Then
WFdef = 1
Exit Do
Else
WFdef = 0
End If
End If
Loop
End If
End Function
NoW if I can get the Font sizes and colors to change depending on if it's a 1 or 0, that will really make it great.
I'll post a new version when I have it all updated, but not tonight.
E_T
Last edited by E_T on 13-06-2005 at 12:17
|
|
| |
|
|
|
ormuzd
|
|
Apologize for the not well tested function. Here is an improved version. I think it generates the right results. I have changed the address from 'C19' to '$C19' to allow copy the function between the columns.
=IF(NOT(ISERR(SEARCH("-1-",CONCATENATE("-",$C26,"-")))),1,0)
for tile #1
and
=IF(NOT(ISERR(SEARCH("-6-",CONCATENATE("-",$C26,"-")))),1,0)
for tile #6
(this is obviously for row number 26 - Turn 17, 3200 BC)
Last edited by ormuzd on 23-05-2005 at 12:57
|
|
| |
|
|
|
E_T
|
|
Orlando, Florida
Mar 2001
time: 00:19
|
|
Well, it's definately not in this version. The Macro works perfectly, just copy and paste to a blank Macro Moduile sheet. You go to Insert, Macro and select Module, then copy that into the page and save. Then you change the WF cells from 1 or 0 to
code:
=WFdef(C10,"1 to 20")
just have each column set to 1 to 20. once you replace a whole line, copy the line and paste down. It works great, I've done enough testing to be satisfied.
The Function parses the string (I do so love DO Loops), looking for the text that you have it set up to be. It then either returns 1 or 0 in the proper cell.
The Function first checks to see if the entry is a single digit. If so, it then checks to see if they are the same lenght (if not, returns a 0), then checks to see if same text and returns 1 or 0.
If it's a string of numbers (like 4-8-12-16-17), it then looks for the first "-". From that point, it spits the string (in this example, into "4" and "8-12-16-17") and checks the single number against what you want. If not, repeats until the string is finished. If it finds it at any time, it returns a 1 and if it goes through the whole thing and doesn't find it, then it returns a 0.
Again, I do love Do Loops.... And topdown programing....
E_T
Last edited by E_T on 13-06-2005 at 12:18
|
|
| |
|
| |
All times are GMT. The time now is 05:19.
Apolyton Time is 00:19. |
top of page
|
|
|
Forum Rules:
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
|
HTML code is ON
vB code is ON
Smilies are ON
[IMG] code is ON
|
|
|
|
|
|
09-05-2005 08:22
|