24-05-2005 21:07									#31
	Support Apolyton, buy Call to Power 2

ET's sheets is one of the most complex sheats I have ever worked with. I used it a lot when I was active here. Also, because of how complex it is, I was working on several of my own that were a bit easier to use. Unfortunately, because of time committments and the fact that it was comming from a newer version of excell, I ended up dropping them. I posted a few of them a while ago, but as absolutely nothing was labled.... well, I doubt that anybody who looked at it would be able to understand it.

Thanks for continuing your work here ET. Great job.

I was looking to do military work in this DG, as that is an area that I am lacking in, and would like to improve my skill.

	30-05-2005 04:09									#32
	Get a bigger avatar today!

I've replaced the Spreadsheet Zip file with a new version that has the WF Automation setup in all of the sheets.

Now, sometimes, I've found, that If I open the file, that the Macro doesn't activate for most of the sheets. THis is simply corrected.

1) Highlight one of the cells in the WF Allocation Columns (the one with the =WFdef($AE11,"1-20") Formula in them) and Copy that cell.

2) then, move to another cell, DIRECTLY above or below (because the Value in that column's text to fine are the same). Then Paste into that Cell. There will be a short period, where you can't do anything as ALL of the sheets now Update.

You will then see that the WF Columns are now set to the proper values and you can then check and be sure that your other sheets are also the same.

Granted, it's not perfect, but it's a sight better than it was before....

E_T

p.s., I'm currently writting the next "chapter" in the use of this tool, so, look for it very soon.

	30-05-2005 04:26									#33
	Suffering from ads?

Thanks E_T

	30-05-2005 08:20									#34
	Suffering from ads?

indeed

The lads at the pub rib me that I may be too old to learn new tricks... but then maybe I am not that old afterall

	30-05-2005 21:36									#35
	Help yourself to an AD-FREE life

Oh, i've also changed the color of the background & text in the WF automation area (as I can't do conditional formatting) for ease of viewing as to the state of the WF (either 1 or 0). this also is an indicator that its got the automatic WF defination Macro setup.

E_T

	30-05-2005 21:51									#36
	Manipulating the City spreadsheet or how do I get this bloody thing to do what I want Support Apolyton, pre-order Civilization IV

Part I

First off, save the main sheet off into a new file (save as whatever fits for you). This way, you can make the Copy & Paste changes without making a mess of things. (or if you do, you can go back and reload from the original).

Copying, Cutting, Inserting, Pasting & Deleting of Cells, Rows and Columns

Because of the nature of cell references, not only on the city sheets, but on other sheets (mainly from the city sheets), it is essential that you understand when you can and can NOT just copy and paste things. A few cell links use the Absolute cell designation (a $ in the cell reference). For instance, a cell formula that references $C$4, will always point to that cell, no matter where it’s copy and pasted. A cell that has $C4 can be copy and pasted the number reference changing (the row number) and the Column Letter staying the same. And a Cell that references C$4 will have the Absolute Row number where the Column Letter will change, when copying and pasting.

Some of the Formulas use Absolute References and some don’t. So, what can we do and can’t do with this? Lots, actually, if your careful. First off, it’s safe to Copy OR Cut and INSERT Rows and Columns and it’s safe to Copy and Paste the same things in the same column types.

Example. Your working on your sheet, you see that you can produce 1 warrior,, 1 spear and then a settler and can repeat the same cycle, while the city grows and shrinks (from size 4 to 6) with different WF changes, over time. And you don’t want to have to retype all of tat stuff for several cycles of builds. First off, highlight the block of builds/shields needed cells for the Warrior to Settler and copy them. Then highlight the UPPER Left corner of the cell that you want the block to start in and Paste. That block is now repeated. Then do the same for the WF and Size cells for the same turns and you’re mostly done. Because, if you have any "Manually" added Shield cells due to growth, you need to copy and paste them too, at the appropriate points, due to the next growth cycle.

In the Sheet, I have Persepolis (starting in turn 33) planned to do a 2 turn warrior, 3 turn spear and 5 turn Settler, for a total cycle time of 10 turns with the city growing from size 4 to size 6 before the settler is built. At least, that was the original plan for that game, before I had disaster had struck in turns 19 & 20 (more on that soon). I had planned, for a time, to repeat this, until my other worker/settler pump City was built and setup, then to go to other builds, depending other things happening in the game.

So, as you can see, it’s save and even desirable to copy and paste some things, but again, be careful. Always remember, you can hit Ctrl-Z (Control Z) and reverse what you have just done, if you need to. And always save often.

Adding & Deleting Rows

As your game progresses, you will have a fair number of turns (rows) on the sheet that is no longer being needed (it does take up RAM and HD space) and you don’t want the old turns anymore. You follow these simple steps and you can delete the old stuff.

1) find the row/turn that you want to keep (i.e. everything above that point is to be deleted).
2) Manually enter in the turn number in that row’ cell and maybe even change it’s color. (if it says that it’s turn number, say 40, then manually type in 40). This sets a new start point for the turn number column cells to work off of.
3) Manually enter in the value that you see in the FoodBox and maybe change it’s color. This again sets the value that has been calculated up to this point.
4) Manually enter in the value in the accum(ulated) Shields Cell and maybe change the cell color. Same as the FoodBox.
5) Manually zeroize the Accumulated trade.
6) Left Click & Drag to highlight the Rows that you want to delete and Delete them. (you click or drag on the row number to get the whole row highlighted).

The changing of colors is used to help you to remember that these are the "new" starting cells for the downward calculations.

To Add Rows (turns) is relatively simple. I always try to add rows in multiples of 10, but then that is just me.

1) highlight the last 10 rows of the sheet (right clicking & dragging to highlight them) and copy them.
2) highlight the ROW that has the Bottom of sheet Column descriptions and then INSERT the copied rows. You do NOT want to Paste. You will then see that the turn numbers and all of the other downward calculations are correct, you can do some more.
3) Make any changes to WF, Size, Build, Shields Needed AND the tile values. You must be especially careful, if copying and Inserting any new rows that the rows that your copying from fully incorporates any tile improvements in the new rows.
Example: you copy turns 30 to 39 and insert them at the bottom. On turn 32, you had a worker road a tile and mine the same tile on turn 38. When you copied and Inserted those rows, those changes are also copied, so you just copy and paste the correct values for the new rows that you just did.

It’s basically a good idea to double check your tile columns, after adding new rows (turns), just to be certain of no mistakes of this kind.

Very Important Note: It is a generally good practice to add & delete the same number of rows/turns too ALL of your sheets at the same time. This is again, because of the various other sheets that use the main city sheet cells as working references for them. It’s especially Important to make sure that you also Manually set some of the values in the Budget sheet before deleting any turns from city sheets. You Also want to Add and Delete in the city sheets before doing the same in the other sheets.


Moving Columns around and adding/deleting from them

I know that some people will want to move things around, somewhat, to better suit how they do thing or want them too look. This can be a bit more tricky than simply adding and deleting rows, so save before you make changes.

You are best to either Copy and Insert OR Cut and Insert your columns. DO NOT PASTE whole columns!! Some of the columns are better to Copy and Insert whereas, some are better to Cut and Insert. You will have to experiment somewhat, as it depends on the other cell references. I generally have used cut & Insert mostly, but then there is times when you need to copy and Insert. If you need to change a cell reference in a formula (maybe fixing a "broken" reference after moving things around), then fix the topmost one on a column, copy it and paste it though out the rest of the column cells.

Now. Down to the nitty gritty

I’m going to use the Persepolis Sheet for this explanation, but you need more to give you an idea as to what I’m talking about. Here is some picture of that game for reference:



In a way, this is a kind of ARR for this game. The first shot is my starting point and what I was able to see. Even though I had started next to the river, because of the mountains and Hills and the Plains, I had figured to move to a slightly better spot and decided to move my Settler 9 and the Worker over to pop the Hut. I also wanted a bit more trade from the River, so the move would be a good one. I had gotten Pottery from the Hut, so I could start on some serious War Techs from the very start. With Warrior Code (with this being an elimination Game and Sir Ralph as one of my opponents, having Archers ASAP would be priority.



I built my city and I had (with Border Expansion): 4 Flood Plains; 3 Mountains; 10 Plains; 1 Bonus Grass;1 Forrest w/Game & 1 Forrest tile. 10 of the tiles (including the City Center) were River tiles.. So, for my spreadsheet, this is how it’s setup for the first part:

Food.Shields.Trade
City Center: 2.1.3 (with the 3 as Orange/Red because of Despotic Penalty)
#1: Flood Plain - 2 (Orange/Red).0.1
#2: Flood Plain - 2 (Orange/Red).0.1
#3: Plains - 1.1.0
#4: Game/Forrest - 2 (Orange Red).2.0
#5 - Plains - 1.1.0
#6 : Plains on River - 1.1.1
#7: Mountain on River - 0.1.1
#8: Plains on River - 1.1.1
#9: Flood Plain - 2 (Orange/Red).0.1
#10: Flood Plain - 2 (Orange/Red).0.1
#11: Mountain - 0.1.0
#12: Plains - 1.1.0
#13: Plains - 1.1.0
#14: Bonus Grass - 2.2.0
#15: Mountain - 0.1.0
#16: Plains - 1.1.0
#17: Forrest - 1.2.0
#18: Plains on River - 1.1.1
#19: Plains on River - 1.1.1
#20: Plains - 1.1.0

Putting them into the sheet like so, with the Orange/Red color indicating the parts of the tiles that have the Despotic Penalty. This is so that, when the government changes, I know what needs to be changed, other than the trade amounts (for Republic & Demo).



I decide to go 2 Warriors before starting a Granary and then the first settler. I switch the WF between the FP & Game tiles (#2 & #4) so that I get a bit more Trade and get my 2 Warriors in 4 turns each, Then, with my Worker having Roaded the game, I then decided to Irrigate then Road two of the FP’s and then to Mine/Road one of the Riverside Plains to give me a bit more Production with the river trade, helped to slow down the growth when I needed it too.

Now, one thing you need to understand, is the sequence of events, when you load up a game.

1) You load up the game, inputting your password
2) Multi-turn Worker things are completed. In PTW, trade isn’t effected, but it is in C3C. Thus, if a tile is being worked AND a multi-turn worker improvement is completed, you get the additional benefit from that improvement AS IF it was completed the last turn.
3) Trade for the whole Nation is figured. This will be the exact amount as shown at the end of the last turn UNLESS things like losing a city to War or a Road is built/pillaged. Civil Disorder comes after this.
4) Diplo Stuff is done.
5) City by City things are done:
a) Food is calculated. If Surplus and this fills the foodbox, the city grows. The new WF is placed depending on food surplus and Governor settings.
b) Shields are Calculated. If the Item is a worker or Settler, pop is decreased by the appropriate amount. So, if a city grows, you first get the extra shields from that growth, if the growth is on a tile that has shields AND you don’t lose them to corruption/waste.
c) Border Expansion due to culture is done if enough culture. WF is changed if not set at the "Optimal" setting and depends on the Governor settings.
6) Start of Movement Phase.
7) END OF TURN!

So, what does this all mean? It means that you can plan for worker actions and even try to time them in an appropriate manner. My Worker , in this game, I first had move to pop the hut (turn 1). Then move to and start Roading the Game (Industrial - 4 turns), with the move on turn 2, starting to road on turn 3 and the road done on turn 6. The turn that you start counts as 1, just count rows and you know when it’s done. Then, I modify (with the Color Purple) that tile value. Because, I’m working that tile, when its Improved AND the extra is NOT lost to corruption, I can modify the Trade value for the turn before (see picture below) and that value is used in the Budget sheet part for whatever % values you have setup.



So, how do you know if Corruption effects are going to allow you to make use of it, by making a quick mod on whatever tile and seeing if there is any difference. Ctrl-Z is your friend here, as you can hit it numerous times and "toggle" the mod and see what differences there are. This way, if your planning worker actions on a city that has mild corruption and you, say want to mine a Bonus Grassland for instance, you can see if there will be any benefit from it, AT THAT POINT IN TIME. If you do it and you don’t get the benefit without, say more growth to give you more shields output, then you can wait until you need to and plan for your worker actions a bit better. The same with what your planning to do with your city, how much you plan to let the city get up in pop, especially while REX’ing. Why waste the worker actions on tiles that you won’t be using for 30 more turns?? Some of this is intuitive, some of it, especially, after working with the sheet, just jumps out at you and tells you what you need to know.

Anyway, I then had the worker go and move to the FP (WF tile #2) to first Irrigate and then road it. Why the one and not the other first?? Well, I had looked at several options and with the growth plan and the overall build plan, over time. I do this by copying the full sheet, (by right clicking on the page tab, hitting more or copy, checking copy sheet box and telling excel where you want the new sheet. The copied sheet will have the same name as the original with a number after it (Perse would be Perse (2) ). So, is way, you can make several sheets, of the same city wit all of it’s characteristics and try different approaches to building things or growth rates vs. same item build times, with worker actions involved (at least to the point of WHEN a particular Improvement is needed by and then you can work on getting it scheduled, with other worker tasks. Say, you want to look at the difference between mining and Irrigating a certain tile, how that will effect things, 10 to 30 turns down the road?

[EDIT]
After you figure out what plan you want to go with, you then COPY & PASTE the appropriate things (WF, size, build, shields needed, notes, manual mods, ect) from the copied sheet to the Original. This is because the other sheets the reference the different city sheets will ONLY reference the Original sheet, unless you go an manually change all of the different references. Trust me, it's much, much easier to copy & paste the different things than to go and do all of that other work. At that time, you can just delete the sheet copies (after you double check all of the stuff has been copied). And always, always, save often....
[/edit]

I think that it’s time to post this and start n part II

E_T

Last edited by E_T on 30-05-2005 at 22:28

	30-05-2005 22:32									#37
	Support Apolyton, buy GURPS/ Alpha Centauri

I-N-S-A-N-E

	30-05-2005 23:56									#38
	Manipulating the City spreadsheet Avatar Enlargement: We've got the solution

- or how do I get this bloody thing to do what I want it to do? Part II

Now, back to the "AAR"….

With my worker Irrigating, & Roading first one and then a second FP, I would have a good amount of Food surplus, which would make sure that, with the CITY governor set to emphasize Production, it should grow onto a high production tile and get a some extra shields towards my builds. And because I can start building my Granary sooner, I only built 2 Warriors, at first. Now, as anyone knows, you want your granary to be built as close to 1 turn before your city is set to grow, so that your Foodbox is as full as possible, when you next grow. So, after looking at a few different scenarios, I went with the plan that you see.







So, when turn 12 had come around, I was working the Game tile (WF #4) and the growth, because of the small size of the city and only having a 2 surplus, would grow onto one of the Irrigated FP tiles. So, I colored the accum shields cell for that turn a light gray and "Manually" added in a +0 (you can see that in the formula for that cell. And if I want to, I can copy and paste that to any "later" turns, of the same happens.

The worker, after Irrigating/Roading WF #2, moved to do the same to WF #10 (another FP), then it was set to move to WF #6 (Plains on River) to Mine & Road and then to WF #14 (Bonus Grass) to Road & Mine. After that, it was set to move towards one of the other cities and this city was fairly set with worker Improvements for a while.

On turn 18, the city had grown to size 3 and the WF settings (4-10 both FPs) would have the growth WF on the Game, giving me 2 more shields, which would have to be manually added (the Gray Blue cell). At this time, I got one extra trade from a worked tile from Worker actions, on turn 17, but no food or shield extras, at this time. At this time, things were looking good, I was already on my second Tech (Wheel) and was planning to have a very good REX phase while having a fairly strong Mil Unit construction while REXing with a good trade balance to give me fairly fast techs.

Things were going great! I had made Contact with SirRalph and had traded several techs. I was #1 in GNP & Productivity and was only 4th on Manufacturing at 4 Megatons (at 5, I was second and first at 6), so I was looking good in comparison to everyone else. Bigfree, being Dutch and likely being on a River, had gotten to size 3 first, but I was the second Civ to size 3 and my plan had me growing at an extreme rate.



That was the turn before the disaster!!!

On turn 19, I had disease strike, lowering me to size 2. Well, I had to change my plans somewhat, but it wasn’t too big of a deal, just delay things by small amount and I would have to do a few more Mil; Unit builds before me second Settler was built, so that I could still bounce between size 4 and 6 and have a fast production. This was recoverable……

Then it happened again, second turn in a row, I lost a pop. I was now down to size 1 and all of my plans were, literally, out the window. This was unrecoverable enough that, I knew that I wasn’t going to win this game, no matter what happened. At that time, I had quit updating my sheet and with the other things going on in my life, almost entirely gave up on this game.

Afterwards, I had gotten back into updating this and, because of needing to plan for upgrades and my GA, I had finished and updated the GA calculations and added the Budget sheets. It was still a forlorn hope, with BF being the 900lb Gorilla, if I could just get enough tech parity and some time to keep him off of me, I could at least make an effort to keep going. Alas, that didn’t happen, because of being backstabbed in the tech deals with Paddy (he turned out to be BF’s Vassal) and just not having enough time to get enough setup to survive long enough, I had just given up. But I had lasted 90 turns after the disaster and was becoming a pain in BF’s a$$, before I had run my course….

But this will help give you an example as to how the city sheets work. Sometimes, you just can’t plan for some things….

Any questions on how the city sheets work?

E_T

Last edited by E_T on 31-05-2005 at 00:02

	31-05-2005 00:07									#39
	Get a bigger avatar today!

quote: Originally posted by Master Zen I-N-S-A-N-E

Maybe to a simple Warmonger like you....

hehe, in a way, it IS almost Insane, but it works and works very well. You can't argue with that.

E_T

	31-05-2005 00:39									#40
	Support Apolyton buy from Amazon

The city sheets are clear. With your macro or my suggested formulas they are almost fully automized.

The only thing I wonder is why should I set the corruption values in the beginning of the sheet when they could be calculated? And why aren't they in the first columns but in the middle of the sheet?

	31-05-2005 09:48									#41
	Suffering from ads?

quote: Originally posted by ormuzd The city sheets are clear. With your macro or my suggested formulas they are almost fully automized.

Yes, the automation on the WF settings DOES make things much easier, especially when woring with different senarios.

quote: The only thing I wonder is why should I set the corruption values in the beginning of the sheet when they could be calculated?

O.k., the corruption value settings. Each city has different values and for "simplicity sake", I had set it up with those constant values. Calculating the actual corruption values has been kind of difficult, with excel. There are rounding errors that have crept into the calculations that I have done on the Corruption sheet. So, what I've done has been to firure the corruption from the number of shields & Trade available in relationship to the waste and the rounding errors in excel. I use the AI (Real Corr), AJ (Lower), AK (Upper) AW(Total Trade) AY (Actual Corr) Columns on the corruption sheet to help "Narrow" down the real values. A sim of the game also helps with this.

1) I first Set whatever city that I'm looking at to have a WF with the Max Shields or Trade (without any Modifiers, like Markets or Libs). I then look at the amount of corruption/waste for that city. I then input the amunt into the AW (Total Trade) column and then change the AI (Real Corr) column Value until the right amount of waste is in the AV (Actual Corr) Column. I change it until I get an upper range value, then I look for a lower value. I then change WF to get the next lower trade/shields until the corruption/waste value changes. Then, I input that into the columns and see if the upper & Lower values have changed (which would narror the range). I do this until I can't lower that raw value an more. I then take the upper & lower values, take the average and use that as my corruption value setting for that city.

Example: I have a city that has a max RAW total (before corruption) of 9 with 3 waste. I input 9 into the total trade column and then work on getting an upper value of real corr to get that amount of waste, then do the same for a lower value. In this example, that would be 38.88% for the upper and 27.78 for the lower. Then, I manipulate the WF in the ciy to where I then get 2 Waste, which for this city is a Total Raw amount of 8. So, reimputting 8 into the total Trade, I then check the upper and lower values to see if they still work for that amount. In this case, the Lower value is still in but the Upper value now needs to be lowered to work. It will now works at 31.24%, and the range is now 27.78% to 31.24%. I repeat the WF manipulation to get the RAW value downward, checking the ranges, until the Waste changes to 1. In this example, a RAW total of 6 gets 2 waste and a Raw of 5 gets 1. Again, I do the upper and lower for both of those, In the example, the lowwer didn't need to be raised, but the upper did need to be lowered, to the new value of 29.99%. So, the new range is 27.78% to 29.99%, much narrower than before. Doing the same WF manipulations to go from 1 to 0 waste (at Raw of 2, you get 1 waste and Raw of 1 is 0 waste). Again, checking against the upper and lower values, which are this tie the same. SO, for a maximun, at this time RAW shield/Trade Value of 9, we have a working range of 27.78 to 29.99% with an average of 28.40% (rounding up), which I then use in the Corruption settings.

Granted it's a lot of work, but it does work.... With a Sim, you can change the Pop totals and stuff to allow higher numbers to be used to check to see if the values still hold.

I had worked on and off with the corruption calculator part and just couldn't get it to work right, but If I can get relatively accurate values of corruption to use for the sheets, it's good enough. ANd you don't have to do that very often either.

quote: And why aren't they in the first columns but in the middle of the sheet?

Well, when I had first started building this, I had the values closer to where I was doing the Flag and calculations on the sheet for debuging all of the decision tree parts of it. Considering that this

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)))

	31-05-2005 10:00									#42
	Inflate your Upload Space

quote: Originally posted by E_T Maybe to a simple Warmonger like you.... hehe, in a way, it IS almost Insane, but it works and works very well. You can't argue with that. E_T

As a professional economist I can assure you I have worked with much much worse things than that (like Stata™) which is the main reason I try to avoid them when I'm supposed to be having fun.

	02-06-2005 10:37									#43
	Inflate your Upload Space

Please De-top this thread

E_T

	04-06-2005 02:47									#44
	Support Apolyton, buy Civilization III: Complete

ET, why did you not move the worker onto the southern mountain on turn 1? While I understand, and fully endorse, the mantra "Pop is Power", and you would lose two food from wasting two worker turns getting the worker back to the plains Game, you have the possibility of uncovering more food when you look from the top of the mountain. In this case, you missed a four turn settler pump (should have settled on the hill 22 of Persopolis, running @pop 4-5), and currently only have a 2 turn worker pump in the capital, which is lacking in shields. Nice as workers are, I would rather have the extra settlers...

---

Can I have the save please? It looks like a decent start for a 5CC

	04-06-2005 20:51									#45
	Support Apolyton, pre-order Civilization IV

That was a posibility, but that hill that is 2-2 of Persepolis is NOT on the river, so it would have to build an Aquaduct (and have Construction tech) before it would get to size 7. With Persians, being Industral, they get a bonus shield in the city center when they get to size 7. So, a river tile is best.

Also, I was taking into account the settings in this game (tiny map for one), which meant that I really couldn't wonder far to find someplace better. Besides, I was in a tourament game, where 2 of us would be eliminated to go onto the next level, so IF I was to move my settler at all, it would be one tile only and I was already next to a river, but I did want to get a little bit away from the mountains, which wouldn't be much use in the arly game. Once you get behind in the curve, it really takes a lot of work to get back on top (or just even with the others).

That had also decided where I had sent my worker. Even with an Industrius worker, they aren't as powerful in C3C as they were in PTW, so every lost turn of movement, for "scouting purposes" would reqire me to play catch up with the others. As it was, If the disaster hadn't had happened, things in that game would have turned out very, very different. I was well inline to becoming the dominate power in the game. I had a very high trade balance with the potential to continuing that curve.

As it turned out, the decision to move my start was the best, when taking into not only the terrain that you can see, but what was later reveiled. My Capitol would be pretty much in the center of any expansion and I would be able to "back against the sea" if I had needed to. Unless gallies were used, any land forces would come from the north and northwest, which would give me a better defensable border.

I think that that game is still going, so at this point in time, I really can't give you a copy of it, unless BF, Paddy & OPD all agree to it. Also, it's a PBEM and I'm not certain you can even convert it to SP play (but then, I might be wrong, as I've never tryed it - no reason to). You might be able to get a copy of the original map from Beta to use.

As it was, Originally before the disaster, I had planned for my next city to have been 9-9-8 of Perse, which would, with another granary, give me another Worker/Settler pump. I had basically done most of my scouting, with my 2 warriors to get the lay the land and figure out where I would be building my empire, before I was really concerned with finding everyone else. As it was, I had run into SR's scouting warrior, by chance, as he was on one hill and I had gotten on another. We had worked out a NAP and tech trade and from all indications, at that point in time, I was on my way to the top. But that didn't happen....

E_T

	04-06-2005 23:25									#46
	Remove this text

I believe you could get the world seed from a save using Gramphos' multitool, so long as you had the admin password.

But, since the game is still going, that's moot.

	05-06-2005 03:36									#47
	Increase Your PM Length

You lost 2 pop? How could that hurt with the FP and Game? You only need three pop points to have +3 food...4 turns to regrow...

	05-06-2005 09:36									#48
	Support Apolyton or Terrorists Win

My Granary was still under constuction, THEN I had to wait until I had gotten my pop up to size 3 before I could get my first settler out the door. Of course, this had dropped me back to size 1, when I had done that, so it was going to be a while still before I was able to get me next settler out the door.

I also had other things to worry about, like my closest neighbor, Sir Ralph, who was infamaous for Archer Rushes (atleast in SP Play). He was later replaced by OPD, because he didn't like the way that C3C was going with the patches and had removed himself from all of his C3C PBEM's.

I had just kept myself going, in the hope that I would atleast be able to work against the strongest, because it was obvious that there was no real way to recover.

Think about it, it was 17 turns from when I had first built my capitol. I had, the very turn before, gotten to size 3. I was, shield wise, just over 1/3 into the build of my granary (although, the projected build time was right at 1/2, with my anticipated growth). My Granary was only 2 turns later, but my first settler was around 10 turns (IIRC) later than origially planned, with a base pop of 1 (after settler) instead of 4. Do the math, it was unrecoverable.

Also, remember, it was still in Despotism, so you still get that Penalty.

E_T

P.S. Plus, After what Had happened, I was very hesitant with using the FloodPlains. And really, I just didn't care as it was esentual;ly over. There was no way for me, realistically, to advance to the next level in this tourament. And with working both FP's, after Irrigated AND with the Granary, I would still have 3 turns to grow 1 pop. And at the same time, the other players are doing the same, have ore cities than me with more other thingsd besides.

As I ad said, I was now way behind in the curve, compaired to everyone else.

E_T

Last edited by E_T on 05-06-2005 at 09:43

	11-06-2005 12:18									#49
	Conflict Resolution Inflate your Upload Space

Conflict Resolution when dealing with multiple sheets

Now that you (should) know how the city sheets work and you can use this great tool to figure our the best way to plan your empire’s builds for a nice time frame. You do the work to get the most out of your cities, both built and planned for the future and tweak everything to get the upper hand for your empire. You then play the turns and build the cities, while manipulating your WF as you had originally planned. Then, you get a turn, where it turns out, you had more than one of your cities using the same tile at the same time (because you had closer than 5 tile city spacing, your not the AI after all…). All of that planning work if wasted, because you didn’t realize that one of the tiles that you had planned to use for one of your well planned builds (or growth or both) was being used by another city. DOH!!!!

But, with the conflict resolution sheet(s), you can check to see, at any time, to see if there is a possibility of that happening. What is the conflict resolution page and how does it work??



In this picture, we see part of the conflict page. It had each city, with all of the WF tiles listed, in column format, with each row as each turn (like in the city sheet). Now, because of the limitation on the number of columns that any sheet can have, you are limited to a maximum of 12 cities PER conflict page, so, if you have, say 13 or more cities, you just have to use a separate conflict page for those cities.

So, how does it work? Simple, it looks to see if the WF in more than 2 cities is using the same tile. It’s just a simple check for a 1 (WF set) in both of the tiles that are shared by 2 cities. The basic formula is this:

code:

=IF( AND(Perse!AI136=1, FALSE() ), -1, 0)

code:

=IF( AND(Perse!AR131=1, Gold!AX131=1 ), -1, 0)

code:

=IF( OR( AND(Perse!AS134=1, Gold!AW134=1 ), AND(Perse!AS134=1, Pasarg!AO134=1 ) ), -1, 0)

code:

=IF( OR( AND(Pasarg!AO134=1, Perse!AS134=1 ), AND(Pasarg!AO134=1, Gold!AW134=1 ) ), -1, 0)

code:

OR (  AND ( city1tile, city2tile ) , AND( city1tile, city3tile ) , 
     AND( city1tile, city4tile ) , AND( city1tile, city5tile )   )

So, if any of the 5 cities, in this case, are using the same tile, the case would be true and the ‘flag’ would be set. Understanding this is important, because you have to manually set these up for whatever your game has setup.

Oh, you cry, that’s insane, how do I go and do all of that work?? Well, the main part of it’s already setup (with me having done that work…), with all of columns having at least the FALSE state. Some of it will be simple copy and paste and yes, there will be some work to get it fully setup, but really not as much as you might think.

One thing that excel lets you do, is to let you have several windows OF THE SAME workbook open at once. For example:



go to the Windows:New Window and then you have 2 of the same sheet, do again for 3 windows, size them and move them around for ease of quickly flipping back and forth (as in the picture). On the newly opened windows, you WILL have to setup the "frozen panes", as they will not be setup for those windows (why, I don’t know, one of those micro$oft quirks, I guess ). You just set the one window with the conflict page, then the other windows for the city pages that your working off of, for that one tile. The nice thing, is that you can change what page each window works with, so it’s fairly quick getting things set up. With the picture as an example:

code:

=IF( AND(Perse!AR119=1, Gold!AX119=1 ), -1, 0)

We are looking at Perse WF #13. We have already done some form of annotation (i.e. writing down things beforehand, KISS principle here….) and know what conflict tiles are what. KISS again, It’s best to have the first of the AND part as the city that your working with (city something:tiles 1-20 sequentially) and the other one as the one in conflict (as in the 5 city example above). Just highlight that part of the formula, double click on the City:WF# (first click selects the sheet, second selects the cell that you what) and that changes it with all of the proper cross sheet linking that is needed.

Starting like this:


to this



Just do one line, for that one city, then copy and paste for the rest and the references will get changed properly. So, it is some work, but not as much as you might think. Not as bad as you might think. A little time here will save you lodes of time at a later date. The nice thin about planning ahead for what cities are going where, is that you can set this part up quite some time ahead of when you will actually need it. In this game, I had not only the conflicts setup for this city, but for a few others that had just been built or were about to be built, some time before I had even built the settlers for those cities. P6 = Prior Planning Prevents Pi$$ Poor Performance…..

Another nice thing about Excel, when you have a sheet that is referenced from another sheet, like is done here, if you change the NAME of the sheet, the reference changes. BUT, as pointed out, in the City sheet previous part, If you make a copy of that city sheet, it DO NOT get referenced by any of these other sheets. So, they are good for figuring what path you want to take, as long as you copy and paste the main things (Size WF, etc.,) to the main sheet that is referenced by the other sheets.

Remember to always save often and if your not really sure as to what your doing, to save as some other name, make your changes and if they are good, to go from there.

Also, I ‘test’ any of these conflicts, to make sure that I have the right referance, setup, after I’ve made the proper changes, by ‘flipping’ the WF settings (appropriate to the particular tile) to see if I had gotten the right WF cells referenced. Mistakes do happen and it’s a good way to just make sure that your doing the right thing.

Also, going back to the first picture, in the situation where you have more than one city that is able to work one tile. If you have a conflict flag showing, what city are you in conflict with and where? Well, your formula will tell you what cities, but the way that this is set up, the only other city that shows the conflict will also have it’s flags set and what WF that it’s also using (see 2a and 2b in the top picture). As you can see, Persopolis WF #14 and Parsegrad WF # 10 are in conflict from turns 124 to 133, so you need to go back and look at those 2 cities, and figure out what you need to do to resolve that conflict.

Maybe, a worker action has given you a tile that will give you the same shield output that helps resolve that conflict OR you might even plan your workers to work on getting that tile to allow you to get that conflict resolved. Or it might be growth related or trade related, or a combination of the2 (or 3). It might mean sacrificing a turn of production to get it resolved, but then, I’ve found, that it helps me to more focus on what tiles are being utilized and what my workers can get to in the mean time, to help set my priorities and allow me to see the bigger picture in the long run. If you now that, to get the build schedule that you want, that your worker are going to have to get so much done, within such and such time frame to make the goals that you want to achieve. And, if you only have so many workers and more important things to dale with first, then some things are going to have to wait (or get another worker from somewhere maybe) to get the tiles ready for you to efficiently utilize them.


I was planning to go into the Beaker calc sheet (and maybe the Master WF sheet) with this post, but It’s getting late and this is getting a bit long, as it is.

Questions about any of this??

E_T

[EDIT]
you might notice a few row number discrepencies in the pictures (where 188 should have been 119). I had accidentally hit the wrong row for that WF tile on those cities (after having to retake those pictures and redoing everything) and had made the mistake. As I had said above, it's a good idea to test the conflict page, because they do happen and it's just good practice to do that. Again, here, Ctrl-Z can be your freind, as you can quicckly 'toggle' back and forth to check that it's working right.
[/edit]

E_T

Last edited by E_T on 11-06-2005 at 12:42

	11-06-2005 12:55									#50
	Increase the size of your Attachments

two thing really quick

1) Please, Please, Please de-top this thread

2) I've still beenhaving a few problems with the Auto WF settings macro, but I think that I've fixed it (becuse of the particular cell I was referencing, because it was referencing another while using an Excel function....at least I hope that was the problem) and it should be working fin now. I've tested reloading from scratch (even cold restarts) and it seems to work fine now. So, I've reloaded the zip file with the new changes

E_T

Oh, please de-top this thread. It can be linked to at a future date.

E_T

	11-06-2005 15:06									#51
	Support Apolyton, buy GURPS/ Alpha Centauri

I think a formula like

if(SUM(city1tile, city2tile, city3tile, city4tile) > 0, -1, 0)

instead of

if(OR ( AND ( city1tile, city2tile ) , AND( city1tile, city3tile ) , AND( city1tile, city4tile ) , AND( city1tile, city5tile ) ) )

will be better and easier to implement. Additionally it will be the same for all conflicting tiles

Otherwise I have a pretty good picture of the things work - with lot of manual work which I will think how to reduce (of course) You know, I'm a lazy bastard.

	12-06-2005 09:16									#52
	Increase the size of your Attachments

OMG! I KNOW Firaxis did not put this much work and/or thought into the game! I think even Soren's eyes would bug out to see this spread sheet!

	26-06-2005 09:11									#53
	Using OpenOffice.org - OOo for this spreadsheet Support Apolyton

I had a couple of questions, concerning the use of my spreadsheet in the Opensource version of Office OpenOffice.org that is equivalant to the XP version of Office. There were problems with the WFdef function not working (the WF automation function).

So, 2 days ago, I finally had a look at the Issue. Just to give you a bit of History, I had looked at one of their previous versions, around 1 year ago. I didn't like it, as I couldn't read the tabs at the bottom of the sheet, which is vital when dealing with many cities and different senarios for some of those cities. So, I had not used it. Well, I had again looked at what the problem was and how to fix it.

* E_T beat his head against the wall....

one day later, on one of my day's off...

* E_T beat his head against the wall....

And after the 2nd of my 3 days off....

It took me a while, but I had fixed the tab font problem with a Option setting, in the various options. But I still won't use it, because it's a memory hog and takes forever to Load/Save & Close. If I ever get a better computer (better than PII-450 w/256Meg RAM), I'll re-explore this program, as I like the Open Source Idea and think that It is ultimately better than anything M$ puts out.

Settings for Page Tabs & Colors

First off, to fix the font in the tabs, you have to go to Tools-Options, then select OpenOffice.org-Accessibility and set the Use System Fonts for User Interface. I had also found out, that you need to make sure that you don't check the Use automatic font color for screen display or you won't have any text colors (only background colors).

So, I had beat my head, and beat my head :wall; and got it to look good to possibly use inplace of this old thing I'm using, but it takes FOREVER to load a file and even worse to save and closing out a saved file is the worst. My HD was still chattering while I was writting the first 2 paragraphs, and I hadn't even started this post until after about 1/2 way through. It takes much less than 1 minute for my sheet to load and save in the Version 5.0 Excel I have and it's almost instantanious to close the worksheet file.

Well, now that you have the Options that are needed set, you FIRST need to setup the WFdef Macro. For some reason, when the Version 5.0 Excel file is loaded, it loses the Basic Macro sheet and this had to be setup in advance, or your going to have major problems.

On getting the Macro setup

Go to Tools-Macros-Macro..., a popup diolog box will show the different Function/Macro/dialog Libraries that are in OOo. It should have Standard Highlighted (if not, highlight it). Click the New Button and the BASIC for OOo IDE will start up. It will show:

code:

REM  *****  BASIC  *****

Sub Main

End Sub

Sub Macro1

End Sub

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 = InStr(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

Which is the WFdef Function for setting the WF in the WF Columns, depending on what has been inputted into the main WF Column (i.e. 2-5-8-14 for example). Then, at the Bottom of the Page is a Tab that likely has Module1, rename it to WFdef. Close out the Basic Window and you should be setup, but it might be safe to totally restart OOo.

At this time, you can load in the Spreadsheet. One of the Errors was in an older version of OOo (ver 1.1.1 newest is 1.1.4) and kept on getting a #NAME Error showing. It had turned out, after lots of wallbashing , that Excel Function call used a comma between parameters, whereas OOo uses semi-colin (I assume because of Europian numbering, with a comma as a decimal point, or some such difference) that can make the paramiters get mussed up. So, if you get an Error, check to see if it converted from the Excel comma to the OOo Semi-colin

Excel Version of the function call:
=WFdef( $C18, "15")

OOo Version of the same call:
=WFdef( $C18; "15")

But I've tested with restarting from the original excel file, a few times, enough to know that it works right, as long as you have the 1.1.4 version (or greater). Make sure that you download the Security Patch, too.

Now, there are only 2 things that you need to to have everything setup as in my version. You need to Copy this sheet

Beaker Calc formatted for OOo Zipfile.

into the main sheet, delete the old one and rename this back to Beaker Calc. The reason is, that the Text opbject box that has the Basic Editor Values for the Calculator for each Technology, gets lost in the change from the Load into OOo from the Excel 5.0 Version. But this file has already been saved into the OOo format, so you don't have to worry about the conversion loss. Why didn't I just upload and post the OOo savefile itselt, because you learn by doing, and it's not really that hard, once someone else has already done the work..... ....

And the last thing you need to do, is fix some of the Frozen Panes, as they also don't transfer very well (some are fine, others aren't). I found, that unchecking the Window-Frozen will get it back to the right orientation, then click on the first WF cell and then refreeze it. The Budget pages are different, as I like to keep the cashflow part entirely frozen, while I can move the rest (more on the Budget Page in a new post) to do any manipulations that I need to do there.

And That's It, just save as and OOo file and your ready to use it in OOo.

Parting things about the Excel version

If you continue to use the Excel version, there are 2 things that you need to change. First of, move the Module1 sheet to the very front of the worksheet. Apparently, being at the end sometimes prevents the WF from getting updated properly, when you first load up the worksheet save. This will help that.

Another thing that should help that and removes Errors in any conversion to OOo (and maybe later Excel versions) is to make this change in the Macro:

code:

    Else
        Do Until temp <= 2
            pointer = Application.Search("-", inputstr)
            tempstr = Left$(inputstr, pointer - 1)
                If tempstr = number Then
                    WFdef = 1
                    Exit Do

code:

    Else
        Do Until temp <= 2
            pointer = InStr(inputstr, "-")
            tempstr = Left$(inputstr, pointer - 1)
                If tempstr = number Then
                    WFdef = 1
                    Exit Do

This also should take care of problems with versions and different platforms, as it;s now all internal BASIC without the external Excel Function call.

A newer version with this change is also now available from the first post and here, the Spreadsheet Zipfile - Updated this postdate.

I don't know if resaving the OOo into the newer versions of Excel has any problems, but there are cosmetic problems with saving the OOo back into Excel Ver 5.0, so that is also a reason that I'm nt going to be using the OOo, atleast for now. But that still doesn't prevent any of you from using it, to get an idea as to what this tool does, to use it for yourselves and to help out in this game.

E_T


oh,

	26-06-2005 11:47									#54
	Increase Your PM Length

Do bunnies bang walls?

	26-06-2005 17:26									#55
	Support Apolyton buy from Amazon

This thread was topped as per requests of team members.

This thread has be de-topped as posts by E_T.

	26-06-2005 18:16									#56
	Tired of ads?

It's linked into your MofM Thread, which IS topped, That is sufficient.

E_T

	10-07-2005 12:04									#57
	Support Apolyton, buy Civilization 2

I haven't been adding anything to this for some time. The reason, is that I'm Upgrading the Budget part of the spreadsheet, using some more Macro/functions. Originally, I was having to 'edit' in te modification factors to trade, due to things like Markets and Libraries. It now automatically sets that mod factor, due to a text string ('lmubreCNS') for the various Tax and Research Modifying Improvements in a particular city.

I've also been revamping the Beaker Calc section, which was also to be next up to be posted about (I posted a picture of a small part of it, in another thread). Instead of setting the editor value for whatever tech your looking at, it has everything out in table format. Much easier to use. Here is that picture, of it, that I had posted in another thread:



I'll try to post the first part of the Budget sheet, soon (likely a 3 parter). But I think I'll get the rest of the simpler part done first, before tackling the budget parts. We'll see...

E_T