--------------------------------------------------------------------------
As of 1/18/2004 this version is no longer supported...

Visit the following thread:
http://forums.civfanatics.com/showthread.php?s=&postid=1526027#post1526027

for its continuation in stand alone form
--------------------------------------------------------------------------

This is to my knowledge the most accurate and versatile Civ III calculator created to date and is now compatible with C3C...

Open it up and see for yourself...

Calculates battle up to 15hp
Calculates Bombard Attacks
Gives percentages for all outcomes
and much, much, more... :D

NEW FEATURES:
Fully Compatible with Civilization 3 Conquests...

Calculates Defensive Bombarding...
Volcano and Marsh Terrain added...
Barricades added...

Known issues are:
Does not calculate 100% accurate involving battles with armies (fixed in the next one) EDIT: This has been delayed since C3C has revamped how armies operate. All previous work in this area must be scrapped.
Does not calculate combat against barbarians...
6hp+ retreats not calculated properly...


Features for the next version (v1.50-expected release 01/15/2004)
Support for Greater than 5hp Retreat Calculations
Support for Anti-Air Combat Calculations


Features for future versions:
Support for Army Combat
Support for Battles up to 24hp [(4)-6hp units in an Army]


Enjoy, and comments are appreciated...

Also, macros need to be enabled to use this spreadsheet...

THE FILE BELOW IS VERSION 1.42
Version 1.0 removed after 19 downloads
Version 1.1 removed after 25 downloads
Version 1.2 removed after 15 downloads
Version 1.3 removed after 94 downloads

Screenshot... VERSION 1.42

Version History:

Version 1.1 -- Added ability to factor in retreat ability of units in combat...
Version 1.2 -- Tweaked retreat ability
Version 1.3 -- Retreat ability functioning correctly... see
this (http://forums.civfanatics.com/showthread.php?s=&threadid=64755) for a detailed explanation
Version 1.42 -- Fixed Minor errors with Retreating and added compatibility with C3C.

A CALL TO ARMS!!!

I am looking for anyone interested in creating a stand-alone windows version of this calculator (like CivLackey's). Anyone who is interested in donating the time to this project must be very familiar with Excel and a programming language. If you are interested PM me. I would do this but I am very familiar with Excel, but have no clue when it comes to any programming languages (except 8086 Assembler) :D

I may see about taking this project on, if I can find the time.

:D

sweet tool. I have one question. when a defender with defensive bombardment is pitched up a fast attacker the survivability for the defender goes down versus if you leave the defensive bombardement out. If the defender faces the same attacker but this time NOT a fast attacker the defensive value goes up with bombardment. Is this is a mathematical glitch?

If not.. it certainly opens up possibilities in assigning which units to defend against what. for instance a defender with defensive bombardment seem to be a poorer defender versus a fast unit then against a slow unit in terms of survivability percentage.

example:

attack: 24 fast
defend: 14 slow (def bombard = 0)
== Defender win% is 19.9%
with def bombard = 6 the Defender win % is 11.3%
??

attack: 24 slow
defend: 14 slow (def bombard = 0)
== Defender win% is 30.1%
with def bombard = 6 the Defender win % is 34.3%
!!

What gives? Any thoughts?
:)

hmm i know this post might seem a bit wierd, that i'm trying to say is that Defensive Bombardment works better against slow attackers then it does against fast attackers.
Which in turn makes the TOW Infantry in C3C not as effective defenders versus the modern armour as it could have been had they left the defensive bombardment out on the TOW.

I'm still hoping its just a glitch in the calculator!
Thanks.

Originally posted by Martin4444
...hmm i know this post might seem a bit wierd, that i'm trying to say is that Defensive Bombardment works better against slow attackers then it does against fast attackers.
Which in turn makes the TOW Infantry in C3C not as effective defenders versus the modern armour as it could have been had they left the defensive bombardment out on the TOW...

Seems strange, I know... Maybe this will help...

Without Defensive Bombard:

Fast Regular Attacker = 24 Attack
Slow Regular Defender = 14 Defense
Terrain = Grassland

Attacker Wins: 67.04%
Attacker Loss: 20.66%
Attacker Draw: 12.29%
Defender Survivability = 20.66% + 12.29% = 32.95%

With Defensive Bombard:

Fast Regular Attacker = 24 Attack
Slow Regular Defender = 14 Defense w/6 Defensive Bombard Defense
Terrain = Grassland

Attacker Wins: 59.44%
Attacker Loss: 12.95%
Attacker Draw: 27.62%
Defender Survivability = 12.95% + 27.62% = 40.57%

In the case of Defensive Bombardment the survivability of the Defender is what must be looked at, not just the chance that the defender will win. As DB increases so does the survivability.

In the example above, the TOWs will force more redlined modern armor, which cannot retreat. The key is to have counter-attacks poised to take out those redlined modern armors. The TOWs are actually better defenders with the DB, if the criterion for a better defender is surviving the battle, not just winning it. Rotate those damaged TOWs to the rear for healing and in two rounds they are back to full strength again, saves a lot of rebuilds.

As a side note a veteran attacker with a 3 attack has about a 50-50 chance of defeating redlined modern armor on grasslands. In light of this fact, I usually relegate outdated cavalry for cleanup operations, where the odds increase to 70-30.

Just to be sure I will run a simulation over the holidays on this example to make sure this calculator accurately reflects this combat in C3C...

Yes! You are correct. I was not looking at the draw factor. Thank you very much for that enlightment. To me a defender who can force a draw is good enough, especially against a unit with high attack values.. simply because I will counterattack the following turn and finish them off.

Excellent tool.

Nice tool. do you have a tool, or will this one help me determine what my chances of a successful bombardment are? I play AW alot, and it seems that cats are very good at bombing units on flat land next to a city, but are less effective when attacking fortified units inside a city, or on a mountain.

Do you have any stats on the chances of a successful bombard with cats, cannon, artillery, ships, etc?

Originally posted by BomberEscort
I am looking for anyone interested in creating a stand-alone windows version of this calculator (like CivLackey's). Anyone who is interested in donating the time to this project must be very familiar with Excel and a programming language. If you are interested PM me. I would do this but I am very familiar with Excel, but have no clue when it comes to any programming languages (except 8086 Assembler) :D

CivLackey apparently disappeared shortly after creating his calculator. I use it and think it is great. You might want to try PMing Moonsinger. I know she was working on a utility to automatically log moves in the Quick Start Challenge (QSC) portion of the Game of the Month (GOTM) and had tried to use a stand-alone pop-up such as that used by CivLackey.

Originally posted by handy900
...will this one help me determine what my chances of a successful bombardment are?

Yes it will. There is an option called 'Bombardment?', check this and use the section of the spreadsheet for bombardment. It handles bombardments up to 9 ROF and even notifies you of a potential lethal bombard. There is also a section that gives percentage chances for each ROF and a total chance of 1 to ROF rounds that are successful.

FWIW, I have not yet had time to work on a stand-alone version of this. :(

I will not feel slighted if some other programmer-type tackles this before I do ....

:)

You mentioned that anti-air combat calculations would be added in the future.

Could you give a brief rundown on how anti-air combat is decided? I can't seem to figure out any kind of relation between the anti-air rating and the chance that the air unit will get shot down, except that higher is better of course.
Fin.

Looks awesome but one problem.

I don't have Excel. :( :cry:

Originally posted by impactblue
You mentioned that anti-air combat calculations would be added in the future.

Could you give a brief rundown on how anti-air combat is decided? I can't seem to figure out any kind of relation between the anti-air rating and the chance that the air unit will get shot down, except that higher is better of course.
Fin.

Based on preliminary testing of a very small sample of cases, I am reasonably convinced that:


Stacking AAA up to four in a tile increases the chances of a hit, anything over four in one tile seems to have no effect.
It appears that if the AAA wins one round, it wins the battle. Basically all it takes is one hit to win the encounter, there have been some anomolies with this so this may not be entirely accurate.
The effects of terrain are not statistically signifigant. It doesn't seem to matter if the AAA is on a mountain or grassland.


I have this scheduled for further testing, RL concerns have me working 60+ hours per week until the end of the year. But I am expecting a new release sometime (with the AA option) by the end of december. I am running out of space on the spreadsheet... :) and armies will probably be the last thing I do.

I ran tests on the following:

============================================
Slow-Attacker v. Slow-Defender w/DB
============================================
Regular Attacker is 1.1.1
Regular Defender is 1.1(99).1

300 Trials: 71 Attacker Wins (23.7%) , 229 Defender Wins (76.3%)

Standard Deviation = SQRT(300*0.2794*0.7206) = 7.77%

Calculator: 27.94% Win 72.06% Loss
Actual Test: 23.7% Win 76.3% Loss
============================================
Slow-Attacker v. Slow-Defender w/DB
============================================
Regular Attacker is 1.6.1
Regular Defender is 1.1(6).1

300 Trials: 104 Attacker Wins (34.6%) , 196 Defender Wins (65.3%)

Standard Deviation = SQRT(300*0.3031*0.6969) = 7.96%

Calculator: 30.31% Win 69.69% Loss
Actual Test: 34.6% Win 65.3% Loss
============================================
Slow-Attacker v. Slow-Defender w/DB
============================================
Regular Attacker is 1.3.1
Regular Defender is 1.1(9).1

300 Trials: 91 Attacker Wins (30.3%) , 209 Defender Wins (69.7%)

Standard Deviation = SQRT(300*0.2954*0.7046) = 7.90%

Calculator: 29.54% Win 70.46% Loss
Actual Test: 30.3% Win 69.7% Loss
============================================

I will do more testing with:

Fast Attacker v. Slow Defender w/ DB (MA v. TOW-I)
Fighter/Jet Fighter/F-15 v. Flak and Mobile SAM

soon...

Originally posted by BomberEscort


Yes it will. There is an option called 'Bombardment?', check this and use the section of the spreadsheet for bombardment. It handles bombardments up to 9 ROF and even notifies you of a potential lethal bombard. There is also a section that gives percentage chances for each ROF and a total chance of 1 to ROF rounds that are successful.

Thank you, Thank you very much. :)

Another question for you.
Was wondering if you know for sure whether or not marines get a times 2 attack rating for amphibious assaults?

It's in the conquests comprehensive features list thread, but I just wanted to know if you observed this or not.

Thanks.
Fin.

Originally posted by impactblue
Another question for you.
Was wondering if you know for sure whether or not marines get a times 2 attack rating for amphibious assaults?

It's in the conquests comprehensive features list thread, but I just wanted to know if you observed this or not.

Thanks.
Fin.

I haven't tested this, but I believe the answer is no. IIRC, it was a planned feature in the beta that was later dropped. I remember reading that a beta-tester said this. I will add this to my list of things to be tested just to be sure...

This project is currently on hold until it is determined if C3C will use the modified combat system as proposed in the beta patch or will not. If it doesn't, I will continue with this project. If it does... I won't :(

False Alarm... It has been confirmed that the new combat system will not be in the beta-patch v1.10 for C3C... This is good news for me and the 280 fellow fanatics who downloaded this program... Now I can continue! :cool:

Originally posted by BomberEscort

But I am expecting a new release sometime (with the AA option) by the end of december.
BomberEscort, love the calculator. :goodjob:

Have you gotten anywhere on the AA combat mechanics? I've been doing some simulations and would be happy to share my results if it saves you some time in the editor. I haven't quite got the mechanics exactly figured out, but I think I'm close. The interesting part is that the AA defense seems to influence the probability of interception and the success of the AA in the ensuing combat that decides if the aircraft is destroyed. Give me a holler and I can give you the results in an Excel file . . .

I am still working on it, and any data you have would be appreciated. Go ahead and post it...

I am about 75% finished with the data collection... Then it is on to the formula discovery... ;)

Also,

I am currently teaching myself Visual Basic 6.0. I have completed the main form and now I just need to create the form for the output of all the calculations. Hopefully, I will create a stand-alone version of this calculator within the next month or two. If this happens I will no longer support the Excel calculator...

Sorry about being slow to get this up - work is interfering with my "research." :rolleyes:

I attached an Excel file with most of the trials I've done on attacks of bombers/stealth bombers on SAM/MSAM. I think I've got the SAM defense figured out, but the MSAM I haven't quite pinned down yet . . . I've found AA combat to not be quite as straightforward as the regular ground combat. :crazyeye:

I noticed as you did that only about 4-5 anti-air units attempt to intercept an attacking aircraft, but haven't figured the exact number, yet.

I also noticed that setting the anti-air value for MSAMs to 1,000 resulted in a 100% aircraft kill rate, so I reasoned that the anti-air value must influence the probability of intercepting an aircraft. Also, even though AA combat is "all or nothing" (aircraft are destroyed, never damaged), I noticed that altering the hitpoints of the air and AA units altered outcomes. So, I reasoned that air combat likely had two stages: the aircraft must first be "intercepted," then the units combat to determine if the aircraft gets destroyed.

I made the assumption that the units fight a "normal" C3C combat using the anti-air defense as the attack value and the air combat defense as the defense value and that if the air unit loses the combat, it is destroyed. Of course, the expected outcome of such combat can be easily calculated. When the conditional probability of an aircraft kill given an interception is known, the probability of interception can be easily estimated given the overall aircraft kill rate. Trials with a variety of different combinations of hit points and air combat defense values but the same AA defense all yielded similar estimates of the probability of interception for regular and stealth bombers. Thus, I concluded that the probability of interception was solely a function of the AA value.

When I varied the AA values for attacking bombers and stealth bombers from 1 to 1,000, I found that the estimated probability of interception followed a predictable but non-linear form relative to AA values (see charts). It looks like some kind of inverse function. When you plot the AA values on a log scale, the curves take the nice sigmoid shapes of a logistic regression. I kind of doubt the C3C programmers are as into logistic regressions as I am; so, even though I can reasonably predict the probability of interception, I don't think I've hit on the data generating function, yet. Also, the minimum probability of interception appears bounded at ~0.2. The curves for the bomber and stealth bomber appear to have the same origin and ending, but the stealth bomber appears to have a lower probability of interception across the range of AA values even though values are quite similar around AA values used in C3C (2-8).

The SAM AA mechanics appear simple. I think there is an invariant probability of interception for bombers (50%) and stealth bombers (5%). Aircraft hit points don't seem to affect outcomes, and I think the aircraft and the SAM "fight" a single round of combat, and the aircraft is destroyed if it loses.

I hope this agrees with what you've already found out or nudges you along in your quest for knowledge. I made some guesses on the MSAM mechanics to save time on simulations, so let me know if you think I'm on the wrong track. I'm also assuming that the fighters function in the same way as the corresponding bombers. Give me a holler if anything doesn't make sense; I spend too much time hobnobbing with statisticians to make much sense to most people. ;)

In addition to nailing down the MSAM function, I still have many unanswered questions . . .
Can AA attack>1x in the same turn?
What about MSAM's stacked with a SAM?
Does intercepting an aircraft cause abortion of the bombing run, even if the aircraft "wins" the combat?
Can an aircraft be attacked by >1 AA unit in the same attack?
Why does the stealth fighter have an air attack value?

If you haven't tackled these, I might be able to find time to chip away at a couple.


Looking forward to the stand alone calculator with ground and air combat.
:D

Thanks, the data is helpful... and it agrees with what I've been doing so I think all is well :goodjob:

I've completed several test (I will post all data when I'm finished)

BomberD is = AAA Defense
BomberD is 2x AAA Defense
BomberD is 4x AAA Defense
BomberD is 8x AAA Defense
BomberD is 0.5x AAA Defense
BomberD is 0.25x AAA Defense
BomberD is 0.125x AAA Defense
BomberD is 1/24x AAA Defense (just had to throw this in :D)
BomberD is zero, AAA Defense is non-zero

Tested these for 1-5 AAA in a stack...
A minimum of 300 trials for each group...

I'll try to answer your questions, based on my experience...

Can AA Attack >1x in the same turn? Yes. I set up a test with 1 AA in the tile with a AA Defense of 1,000 and the Air Unit defense to 2. It intercepted multiple times, so it appears one AAA gets a chance to attack each air unit.

What about MSAM's stacked with a SAM? I would guess that each one gets a chance, but this is too complicated (I.e.- I don't want to take the time to incorporate this possibility into my calculator, at least not now.)

Does intercepting an aircraft cause abortion of the bombing run, even if the aircraft "wins" the combat? Yes, I noted this somewhere in an earlier thread as a possible bug, along with rebase and attack on the same turn if you set an air unit rally point. Neither were fixed.

Can an aircraft be attacked by >1 AA unit in the same attack?
I have done multiple tests with 1, 2, and 3 AAA in a stack, and it appears that each one has a chance to hit the air unit. Each AAA only gets one attack per encounter though, and encounter being one bombing run.

Why does the stealth fighter have an air attack value? Ahh, the $64,000 question... I have given this alot of though... and my answer is I don't have a clue. It's not for interception, it's not needed in an Anti-Air calculation, Bombing Runs use something else... My best guess is it was to be incorporated with the stealth attack feature (that is broken), but neither are working properly.

Originally posted by BomberEscort
Tested these for 1-5 AAA in a stack...
A minimum of 300 trials for each group...
Jumpin' Jimminy Cricket - How do you manage to do so many trials? I guess this is what separates those who just want to understand AA enough to better thump their opponents (read: me) from those who have the drive and stamina to make the CombatCalculator. :D

Every few days, a new thread gets started in the Conquests forum by people befuddled by the AA mechanics. E.g., http://forums.civfanatics.com/showthread.php?s=&threadid=74723
I feel their pain, because the AA combat values (unlike the ground values) give no intuitive understanding of outcomes. It's hard to decide what units to build or to plan strategy, and I think the first few modern age wars for most players are exercises in surprise and confusion. Would it be premature to start hyping the future release of the new and improved Combat Calculator with AA combat mechanics?

Originally posted by BomberEscort
What about MSAM's stacked with a SAM? I would guess that each one gets a chance, but this is too complicated (I.e.- I don't want to take the time to incorporate this possibility into my calculator, at least not now.)
I might fool with the mechanics of this if I get bored in the near future . . .

Originally posted by BomberEscort
Why does the stealth fighter have an air attack value? Ahh, the $64,000 question... I have given this alot of though... and my answer is I don't have a clue. It's not for interception, it's not needed in an Anti-Air calculation, Bombing Runs use something else... My best guess is it was to be incorporated with the stealth attack feature (that is broken), but neither are working properly.
This elusive problem carries the same allure as those long-standing scientific challenges like Fermat's Last Theorum. The person who solves it will be awarded the Nobel Prize for Civ Mechanics.

I will no longer be supporting the Civ III Combat Calculator (Excel version). Version 1.42 is as good as this will get. I am currently about 50% complete with the stand-alone calculator. My goal is to release a version that is on par with the excel version (I.e. - Everything the excel version has) by the end of the month. I will save AA combat (and army combat) for the next version after the initial one due to 'strange' results that need further testing...

As for the AA formula, I am hoping that Firaxis got lazy and used the same or similar formula to the retreat formula. See my Comprehensive Retreat Study

http://forums.civfanatics.com/showthread.php?s=&threadid=64755

For Details...

--------------------------------------------------------------------------
As of 1/18/2004 this version is no longer supported...

Visit the following thread:
http://forums.civfanatics.com/showthread.php?s=&postid=1526027#post1526027

for its continuation in stand alone form
--------------------------------------------------------------------------

Closed per originator's request.