CivCombatCalculator (Excel)

[BomberEscort]

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

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

 

[BomberEscort]

Screenshot... VERSION 1.42

 

[BomberEscort]

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

 

[Padma]

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

 

[Martin4444]

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.

 

[BomberEscort]

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

 

[Martin4444]

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.

 

[handy900]

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?

 

[zagnut]

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)

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.

 

[BomberEscort]

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.

 

[Padma]

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

 

[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.

 

[Civrules]

Looks awesome but one problem.

I don't have Excel.

 

[BomberEscort]

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.

 

[BomberEscort]

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

 

[handy900]

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.

 

[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.

 

[BomberEscort]

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

 

[BomberEscort]

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

 

[BomberEscort]

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!