Your spreadsheet is a great tool! I am new to Civ Fanatics... just joined, but have played Civ II off and on for over 2 years (I play diety/raging hordes, mostly on large worlds).
Unfortunately, there is at least one additional spreadsheet error that is rather subtle...
Further, because everyone's scores are normalized to the GOTM winner, and the GOTM 4 winner launched a spaceship before 1850, everyone's Final Normalized Scores are skewed to a lower number that what they really earned (by approx. 5%).... In effect, almost everyone actaully scored 1 to 4 normalized points higher in GOTM 4 than what is currently reported in the GOTM 4 results.
OK, Here are the details... bear in mind, I am an Engineer by education (math=no problem). <IMG SRC="http://forums.civfanatics.com/ubb/smile.gif" border=0>
The error is in how the speadsheet computes the turn number. A bit of review is in order. When a spaceship is launched, Civ 2 begins counting by years, regardless of the yearly increment that should have been used if the spaceship was not launched.
In Shadowdale's case, his spaceship landed in 1763. Assuming he built a full spaceship (fusion), the flight time is 11.7 years. The launch date was therefore 1752. The spreadsheet does not know this. It (mistakenly) *assumes* Civ II is still counting by 2 years per game turn between 1750 and 1850.
Since the year of 1763 was input to the spreadsheet as the end year, the spreadsheet computed turn # 207 from that end year... when in fact, Shadowdale ended on turn # 212.
Here is the math:
CORRECT: 5274*((50^((420-212)/420))/200 = 183.0
INCORRECT: 5274*((50^((420-207)/420))/200 = 191.7
As you can see, 183 is the "correct" score that Shadowdale earned, not 192 (a 4.9% difference). In essense, the spreadsheet gave Shadowdale an unearned 4.9% "boost".
Since no one else launched a spaceship before 1850 in GOTM 4, only Shadowdale's score was computed incorectly.
However.... now for the bad news (good news to everyone but Shadowdale and Matrix
).
Since Shadowdale's score was used to normalize everyone else's score... well, everyone's normalized result is affected to some degree. The higher the original score, the greater the normalized score's error.
For Example: Cactus Pete should have a normalized score of 83, not 79:
(151/183)*100 = 82.51
Similarly, Kev should have 40, not 37. Smash's score should be 4, not 3 (there is also a rounding issue embedded in the spreadsheet... Smash's was originally 3.6, which should round to 4 anyway... not 3).
Note that individual score error is introduced only if a player (human *OR* computer) LAUNCHES any spaceship anytime before year 1850 (any level of play, any size map). ***Everyone's*** score is affected if the normalizing score was based on a spaceship launch before 1850 (which is the case in GOTM 4).
A clever person can see that you can actually gain this boost if you can manipulate your computer opponent into launching a SS before 1850, but you still win by conquest after they launch. That's because the years switch to one per turn when *any* spaceship is launched, even AI spaceships.
The "work-around" solution is to manually compute the actual game turn at which the game ends, and everything should compute fine.
Of course, a more complex spreadsheet could be created that simply requires another variable to be input... the year of any spaceship launch. The spreadsheet would then look up the game turn at launch, and add the number of additional years until landing/conquest to get the actual (the *CORRECT*) ending game turn.
If this is not changed, a fairly large bonus is artifically awarded to anyone launching a SS significantly before 1850 (at any level of play). And get this... the slower the Flight Time of the spaceship, the greater the bounus! If a SS launches before 1750, that "bonus" is increased, but I will leave the math to the reader.
I know, this is a headache... but hopefully the spreadsheet can be tweaked to account for spaceflight before 1850. <IMG SRC="http://forums.civfanatics.com/ubb/smile.gif" border=0>
NOTE: I checked everyone's situation in GOTM 4, and no relative results were affected (e.g., no one leapfrogged anyone or changed positions)... just the scores.
NOTE: Fixing this error will cost me points in GOTM 5 (my first GOTM), since I will otherwise get this undeserved bonus (I launched a SS in the 1700's).
NOTE: I just found and downloaded an entire results spreadsheet (another version?)... it seems that cell F5 (Shadowdale's ending game turn number) is input manually in that spreadsheet. Changing it to a value of 212 seems to recompute the entire results correctly.
BOTTOM LINE... Simply compute the true ending game turn number manually for game in which a SS is launched before 1850, since the spreadsheet does not do it correctly.
Also, since Shadowdale's GOTM 4 score was inflated, everyone else's normalized scores were lowered by about 4.9%.
[This message has been edited by starlifter (edited June 18, 2001).]
.
Just download that new Excel file here...
. The symbol you are confused about, as I recall from you previous posts, is the caret ^ which means "raise to the power of". Example:
