The wheel reinvented: a combat probability calculator

Boco · July 19, 2001, 00:06

I've written an Excel spreadsheet that calculates the probability of an attacker winning a battle. I know it's been done before (at least one program was written by Mark Wagner), but I needed to brush up on my VBA for work, and this was my idea of fun.

Hopefully, a screenshot accompanies this post. If not, could someone explain how to upload c:\games\mps\oddscalc\scrndump.gif (76k) from my hard disk?

The algorithm is a direct unmodified transposition from Marquis de Sodaq's writeup in the Apolyton GL found here: http://www.apolyton.net/forums/Forum.../001944.html?6 .

From the posts, the algorithm seemed fairly sophisticated and did pretty well in predicting combat results with a variety of units.

The interface for the Excel 2000 workbook is a dialog asking for pertinent information. Besides the standard input for Af, Df, Hp, Fp, and terrain modifiers, it also has toggles for things such as vet status, entrenchment, coastal fortresses, pikeman special, fighter scramble, sneak attack, etc.

If you're interested, here's where I could use some help.

1) Where and how can I post this xls file? I could use some testing/feedback.

2) Is the algorithm by Buenos, Eggman, & Marquis de Sodaq, et al. generally accepted as accurate? I know it doesn't handle 0 Df situations, but it seems pretty good otherwise.

3) If I've overlooked a more thorough program written by someone else that does the same thing, let me know (gently).

Thanks

master on high · July 19, 2001, 10:07

that looks like an interesting little program...I'm not sure if people have made that before...but it would certainly come in handy for certain situations in scenario design. Testing unit statistics before implementing them into the game would be a nice little addition.

Zip the xls file and attatch it to your post.

Boco · July 19, 2001, 12:13

MoH, thanks for the tip. The zip should be attached.

As for my lack of reading the fine print about attached file extensions -- well I do have an appointment with an eye doctor today.

Boco · July 19, 2001, 12:20

Here's a closer view of the main dialog. Let me know if you see errors in the modifiers. It's far from ideal -- e.g. doesn't check attacker fp/hp before enabling/disabling the checkbox for the pikeman special.

Marquis de Sodaq · July 19, 2001, 13:06

I'm not sure you've reinvented the wheel - mine is still in parts, yet to roll. I, too, started to make a VB combat calculator, but have just not had the time to finish it. The old Mark Wagner program uses math that has not held up to testing. Freshman recently posted in the CivII Strategy forum his work compiling tables of the results using the math you use.

I will download it from home later and have a look. Two suggestion I can make from the screenshots:

1) Verify the multipliers. I see you have 2x next to city walls, but this is actually 3x. Some differ from what the manual tells you. See my loooong thread for all that has been discussed!

2) Maybe provide a way for the user to enter hit points manually. Scenario designers might want to use damaged units. For example, a half-dead unit with 5 hp instead of the full 10 used in calculation (1hp unit). Entering a value would allow odds for these situations.

Some modifiers change firepower, others work only in certain situations. It will take much number validation to include all variables. If you feel ambitious, you'd need to include unit domain, type, and others. In VB, I've taken the approach to have pull down unit selection, the hp/fp/domain values were entered automatically (I hadn't planned to account for damaged units, either

). Terrain, barbarian status, etc, are check boxes that modify the unit's values.

Boco · July 19, 2001, 17:35

Marquis, I was hoping that you would see this! Couldn't email you prior to posting -- you've disabled that option. Any chance you could email your reaction to the address in the file? Particularly your thoughts on the cells that implement Euclid's equation.

If you've a program close to release, just let me know. I'll stop public development and posting of this one -- you know much more of the combat details that I do.

1) I'll review the modifiers later tonight. Most were done from memory during a moment of programming frenzy.

2) Partial Hp's is a great idea. I'll try to incorporate it.

As far as approaches, I originally intended this as a development tool. Designer's could plug in 'what-if' values. As you have no doubt concluded, hard-coded unit and terrain values aren't very useful for a scenario designer.

Your solution may be to write some code to open and read a user-selected rules.txt. That's a possible future step for me, but my initial aim is more modest: get the main engine debugged first. Same applies for domain, type, and specials checking.

Thanks for the suggestions

Boco · July 20, 2001, 21:36

This version has many of Marquis' suggestions incorporated (thanks!). These include:

(1) Hit point effects (e.g. fighter vs. helo, ship vs land)

(2) More modifiers. These have been doublechecked to be in agreement with the GL thread.

(3) Results match those posted in the GL thread.

The zipped attachment is 55k.

Screenshot will be shown in next post

Boco · July 20, 2001, 21:40

btw Will this work on a Mac? MS documentation seems to imply that it should.

Marquis de Sodaq · July 21, 2001, 17:31

For anybody interested in learning everything there is to know about CivII combat, my summary thread has moved. It is now in the CivII Strategy forum at

Info: Combat (GL)

The text has been updated for the first time in two months.

Henrik · July 22, 2001, 02:44

Quote:

btw Will this work on a Mac? MS documentation seems to imply that it should.

Maybe I am doing something wrong, but all I get when I press the "Calculate" buttons is a small window whit two buttons which will seemingly calculate the odds, I press the one titled odds and suddenly I have two error messages instead

Boco · July 22, 2001, 09:26

Henrik, thanks for the feedback.

You should get the small window. Since I've used this when Civ2 is running, I didn't want it to take up screen real estate except when inputting data. The two error messages are a obviously a bug.

A larger dialog like the one posted should appear.

Quote the error messages, and I'll try to solve it.

Which version of Excel are you using?

SlowThinker · June 10, 2002, 17:55

Boco, where is the new version (the version that came from our debate)?

In the thread Everything you want to know about CivII combat ?

Boco · June 10, 2002, 22:29

ST, our 'debate' (more like the education of Boco

) had many stages, but I think the attached version contained much that you suggested. I've since modified it for use with a scenario I'm working on, but I don't think I wrote much more code.

It is missing a few of the discoveries made since last September, such as the lack of a firepower criterion for the Pikemen bonus.

Immortal Wombat · June 11, 2002, 05:08

spelt "Sodaq" wrong.

Boco · June 11, 2002, 07:27

Quote:

spelt "Sodaq" wrong

In which cell or button is the mispelling? MdS has been very supportive.

germanos · June 11, 2002, 07:45

What a wonderfull utility !!! Excellent !!

Immortal Wombat · June 11, 2002, 20:16

In the last screenshot, next to the "Calculate" button, you've got a "Soldaq"

Its a great utility

Boco · June 11, 2002, 23:13

Fortunately IW, those screenshots are out of date. Here's the "About Box". The rest is to big to post, but it does fit on a single screen.

Thanx for the kudos, but in truth they were earned by the guys listed in the credits. This program wouldn't exist without the GL combat thread. That collective knowledge was just begging to be put in a spreadsheet.