Unit Comparison Spreadsheet - UPDATED! - Age of Empires III Heaven Forum

Topic Subject: Unit Comparison Spreadsheet - UPDATED!

Cyclohexane
Skirmisher

posted 06-16-07 06:07 PM EDT (US)

UPDATED 06/17/2007
A few forumers and clans mates noticed some typos (and small errors like Lil' Bombard being 2000 gold instead of 1200 gold) and I added in a "Damage per second" grouped column. Adding this column made me realize I had some of the melee ROF damage incorrect as well but it is now fixed. I will continue to update and improve, the link remains the same. Stay tuned!

DOWNLOAD HERE: UNIT COMPARISON SPREADSHEET

This spreadsheet was created to mathematically compare the performance characteristics of different civilization�s units in the same classification. I posted an earlier version of this spreadsheet to get some initial feedback but this version is much superior to the previous. I eventually expanded this spreadsheet to every unit in the game with optional upgrades and the capability to compare base, veteran, guard, and imperial units. If you are familiar with my AOE3 Quick Reference Guide, you will be blown away by this spreadsheet.

I did all of this work to improve my game and provide a useful tool to my clan, N3O. Sure I have a sick fascination with mathematical models but instead of keeping this spreadsheet to N3O members only, I decided to promote the N3O Clan by giving this spreadsheet to the community in the hopes of attracting a few good members or even some friendly opponents.

N3O has been following ES RTS games for over 5 years and has membership spread all over the globe and many contribute positively to the community. For example, MNBob is one of the officers. He is the creator of the popular online unit, building, HC card, and technologies tables MNBob of N3O Work . His program pulls the base statistics directly from the game files. My work uses the base statistics to calculate upgrades and simulate HC shipments automatically. Excel makes the organization of this data using filters easy and makes a unit comparison possible.

Most of the clan members are lieutenants or better but skill is not a factor, we are interested in any player with a positive attitude. We have training officers to improve the skills of new players. I have more fun losing with a teammate that does not cry about it than winning without sportsmanship. If you feel the same way and enjoy playing tons of rated or unrated 1v1 or team games at any time without a long wait, then apply in the �Join N3O� section located in the link below:

N3O CLAN

What the clan is missing is cheaters, immature kids, and jerks. These guys ruin online games and are not tolerated. Do not bother if you are a cheater, make frequent incoherent threads, or have a bad attitude when playing. You will quickly be banned and if you cheat, you will be reported to ES. As a matter of fact, do the community a favor and do to yourself what your parent(s) should have done a long time ago, smash your computer over your head until some sense is knocked into you. I did not spend my time working on a free spreadsheet for punks.

If you decide the clan is not for you, or you are already a member of a nice clan with your friends in it, I respect that and keep in mind we are always up for an evenly matched clan war. Feel free to drop in our forums and challenge us. Having friendly fair matched teams to play against is the greatest part of online gaming.

The Unit Comparison Spreadsheet is professional and at the level ES should be publishing. It is easy to use but does include instructions for Excel noobs. Any upgrade in the game can be simulated including HC shipments, arsenal technologies, big button technologies, fire pit dances, etc. Upgrades to speed, hit points, and damage (split into 3 categories of range, melee, or siege) can be included and then these unit types can be compared. Except for a few gaming issues such as cavalry path finding and extensive micro against a less skilled opponent, the results are similar to my in-game experiences.

There may be a few typos since I am the only editor but the majority of the kinks have been ironed out. If you find any problems or have any questions, please post them here and I will get it fixed. I use Yahoo and Skype and my information is available to clan members. Here is a link to the first version of this spreadsheet that can compare Colonial Units only. ORIGINAL UNIT COMPARISON THREAD I am linking this thread to show the spreadsheets power and possible unit comparisons types (includes a detailed unit comparison summary). This thread is already a long read (I was stuck on a plane when writing it) and I do not want to add a few pages of comparison notes losing more ADD readers.

If you do not like the way this spreadsheet compares units, maybe because micro is not included or bugs like path finding cannot be accounted for, you can still easily calculate every unit statistic in all of the ages with any upgrade. If you read the instructions and learn how to use the spreadsheet, all of the games statistics will be at your fingertips. For example, if you want to find out the damage and hit points of a fully upgraded Forrest Prowler with all the HC shipments and WC aura, you can, and compare this to other light infantry units against heavy infantry, cavalry, villagers, artillery, etc, etc. If you combine this spreadsheet with my "Quick Reference Guide", there will be no secrets to this game and your only reason for playing badly is not enough practice. Because I know people will ask (without reading the instructions), this is the formula used to calculate effectiveness:

EFFECTIVENESS = [Speed * EHP (Effective Hit Points to include armor) * Attack in 9 seconds (includes ROF, setup animation, and area of effect) * Range] / [Population * Villager Second Cost]

All the important unit attributes are compared in the same manner and can be filtered and sorted to obtain a qualitative value for unit ranking. Ultimitsu pointed out in a previous thread (here) that some civilizations get cheap resources early (i.e. silk road), have more important synergistic units (i.e. skirms + Lancers), etc. and the game cannot be judged based on numbers alone. I agree game-play factors into a unit�s effectiveness that cannot be calculated. For example, Cossacks rank high because of their low cost, nice speed, decent damage, decent ranged hit points, and low population. However, cavalry path finding makes cavalry massing ineffective. Game play shows high hit point cavalry in smaller numbers are more effective. Some thought is required to efficiently use the comparison features because there are a few exceptions. However, the results offer a mathematical starting point which can be used as a tool to help balance the game. I hope it falls into the hands of an ES employee!

One last thing, I have also updated my "Quick Reference Guide" with new information and corrections.

AOE3 QUICK REFERENCE GUIDE

.

Lead, Follow, or Get the Hell out of the way!................CYCLOHEXANE'S FREE GUIDES:

-TAD GUIDE: AOE3 TWC TAD Quick Reference -or view online: On-line
AOE3 TWC TAD UNIT COMPARISON SPREADSHEET: Unit Comparison Spreadsheet

See other guides, technological advancements, interviewing tips, and more at my website All Things Miscellaneous

[This message has been edited by Cyclohexane (edited 06-17-2007 @ 10:04 PM).]

Author

Replies:

George_uk
Skirmisher

posted 06-16-07 06:30 PM EDT (US) 1 / 20

Awesome, I've been waiting for this ever since you said you would do it. I'll leave feedback when I've tried it.

Previously known as MoNo Ager

George_uk
Skirmisher

posted 06-16-07 07:44 PM EDT (US) 2 / 20

Ok first things first, wow! This is a HUGE effort and I have to thank you for such a great tool.

I love playing around with it and comparing units, it's a scaringly accurate representation of the real deal, that's right, ERK's are OP here too

But bow riders are very close behind, and better in quite a few cases.

I cannot understand why Mamelukes are so low down the list, they're very good. But this happens to be the only 'anomalous' result I can find. It's amazing how tashunkes benefit from the upgrades (I love btw the manual upgrade % you can give them) than other cavs. With full upgrades and firepit dance, they are only beaten by imperial cuirs with thoroughbreds and spahi. In industrial, they beat everything (with full upgrades bear in mind, applied to tashunkes, axe riders, DS and cuirs I tested it on).

I love the new pets (and healer) thing you've added but could you add an option to compare vs infantry/cavalary as well? I would love to see how coyotes do against infantry compared to other pets.

Also I recommend to make the default gathering rate for food 0.84, (hunts). More people use hunts than berries by far.

Overall, 5 stars, fantastic job. I will regularly refer to this.

Previously known as MoNo Ager

Cyclohexane
Skirmisher

posted 06-16-07 10:13 PM EDT (US) 3 / 20

I cannot understand why Mamelukes are so low down the list, they're very good. But this happens to be the only 'anomalous' result I can find.

I got a little note about the cavalry thing in there. Basically, Mamelukes appear lower on the list because of (indirectly) cavalry path finding and high population cost. If cavalry path finding was not an issue and all cavalry could easily attack their targets, low hit point low population cavalry (take Cossacks for example) would be useful. It is because of their high hit points that they reach there target and are useful in the game. If math always equaled game play, then they would not be as useful as say Cossacks since they are so easily massed. This is one of those discrepancies, I agree. This is a math model and close to perfect, but I have no way to include cavalry path finding mathematically.

In other words, Mamelukes perform better because the other units perform worse than they actually should. If ES took out the unit size, the way they do for ships, it would make cavalry much more useful. Of course, it would look funny in the game having 3 or 4 cavalry units on top of each other. But that is the only solution to make low hit point cavalry as powerful as their statistics say it should.

I love the new pets (and healer) thing you've added but could you add an option to compare vs infantry/cavalary as well? I would love to see how coyotes do against infantry compared to other pets.

To compare pets against infantry just use their base attack ("Melee: Base"). Pets do not get bonuses to anything except guardians (and penalties to villagers) so using the base attack will compare against infantry, cavalry, artillery, etc. the same. It is included already.

Also I recommend to make the default gathering rate for food 0.84, (hunts). More people use hunts than berries by far.

I choose berries because they are a stationary food source. It is a better apples to apples comparison for villager seconds (wood and gold also stationary). Yes hunting is faster but runs out and you would be surprised how much time is spent walking around during the hunt. You do not get 0.84 per second. However, you will be happy to know that you can change this value to whatever you want. Just go to the first worksheet in the workbook "Instructions & Notes", scroll to the right under the heading "Resources gathered per sec for calculating villager second costs", and type in 0.84 instead of 0.67 in the yellow input cell. As a matter of fact, you can type in whatever value you like. This is linked to the entire workbook so it will affect every unit villager second cost automatically!

When 1.04 comes out and I update this file, I may add a drop down menu to select whatever gathering type you like (hunting, berries, fishing, mills, etc.) and another input cell for upgrades (type in a percentage for HC shipments). That will allow an easier comparison but it can be simulated now by typing in the value.

Edit: I just updated it to include a drop down list for a few gathering rates. Granted the French and German SW will gather differently, they cost differently as well. I know it is not exactly perfect, but keep in mind, you can still type in whatever values you desire.

Overall, 5 stars, fantastic job. I will regularly refer to this.

I hope that�s 5 out of 5! Thanks for the comments!

THANKS FOR THE FRONT PAGE GUYS!

[This message has been edited by Cyclohexane (edited 06-16-2007 @ 11:52 PM).]

Cyclohexane
Skirmisher

posted 06-17-07 01:23 AM EDT (US) 4 / 20

For those of you who do not have Excel, here are some suggestions from other users to view the reference guide. I personally have not used either of these but they look good. This is a free Excel viewer that allows you to view but not edit:
Excel Viewer 2003

OpenOffice.org is a multiplatform and multilingual office suite and an open-source project. Compatible with all other major office suites, the product is free to download, use, and distribute.
Open Office

George_uk
Skirmisher

posted 06-17-07 06:25 AM EDT (US) 5 / 20

Ok I've had more time to look at it now and I've changed the gathering rate successfully.

Coyotes are the 2nd most effective trainable pet after wardogs which I thought they were. Also it shows you how damn good the siege and water dance are with canoes and war canoes

I just need now the water dance values (how much their stats increase by) to compare against the 221% attack boost that the siege dance gives. I never knew that canoes were more effective than war canoes.

And yes 5 out of 5 stars

EDIT: Just a spelling mistake on Wakina in the light infantry comparison slot. You have spelt it Wanika.

Previously known as MoNo Ager

[This message has been edited by George_uk (edited 06-17-2007 @ 06:42 AM).]

LordBob13
Skirmisher

posted 06-17-07 11:54 AM EDT (US) 6 / 20

hmmmm....I tried to download it but it said that my zipped folder was corrupted or invalid or something like that. Is it possible for u to put a copy of it online just like u did the other guide?

From a poster at Agecommunity (Talking about the 4 cow age up option):

"Only a noob like you doesn't realize how valueble it can be. That's 2000 food waiting to be gathered"
LOL

Cyclohexane
Skirmisher

posted 06-17-07 12:22 PM EDT (US) 7 / 20

I just need now the water dance values (how much their stats increase by) to compare against the 221% attack boost that the siege dance gives. I never knew that canoes were more effective than war canoes.

That information is available in my Quick Reference Guide. Go to the "Native Fire Pit" worksheet and type in the number of villagers dancing.

hmmmm....I tried to download it but it said that my zipped folder was corrupted or invalid or something like that. Is it possible for u to put a copy of it online just like u did the other guide?

Works for me, maybe you caught it just as I was uploading a revised version.

[This message has been edited by Cyclohexane (edited 06-17-2007 @ 12:23 PM).]

George_uk
Skirmisher

posted 06-17-07 12:30 PM EDT (US) 8 / 20

Mine didn't download fully the first time, I just clicked the link again and worked fine.

Previously known as MoNo Ager

Cyclohexane
Skirmisher

posted 06-17-07 10:32 PM EDT (US) 9 / 20

Notice the updated version: UPDATED 06/17/2007

And thanks for the comments guys, keep them coming and I will keep the updates coming.

DrWho42
Skirmisher

posted 06-17-07 11:23 PM EDT (US) 10 / 20

Wow, beautifully done. Thank you for all your hard work. This and your other guides have certainly helped me and countless others.

Keep up the good work!

Beatnik Joe
Skirmisher

posted 06-18-07 10:44 AM EDT (US) 11 / 20

Holy cow, this is amazing! It took me a minute to really get the hang of this spreadsheet, but this is a major-league powertool. And look at those ERK! Oh my goodness. No wonder those guys do such a great job against so many units.

As is the nature of power-tools, I think it pays to read the manual and ask some questions, so I happen to have a couple of them.

First, I was wondering how to properly add gather rate upgrades. Is the best technique to simply change the cumulative gather value on page 1 or is there someplace else where I should be putting % gather rate upgrades for particular resources? Doing this would be no problem, just didn't want to overlook such a feature if it exists.

Also, there were a few results that I couldn't quite figure out. Grenadiers, in particular, do a good bit better vs. ranged units than I would have expected. They come in ahead of horse artillery and great bombard, both units that I find fantastically good against ranged units.

Anyway, this is really nice... can't wait to do more experimenting with it. Thanks once again for providing such a great resource (along with the Quick Reference Guide, of course!)

Strategies:
- BWKiC - A British Fast Fortress strategy
- The Aztec Scout Slam- An Envoy Rush, Native-Style

Cyclohexane
Skirmisher

posted 06-18-07 01:30 PM EDT (US) 12 / 20

And look at those ERK! Oh my goodness. No wonder those guys do such a great job against so many units.

The Eagle Runner Knight (a.k.a. swiss army knife), performs very well versus light infantry (amazing ROF, speed, decent range with upgrade, nice HP, etc.). However, what is not included here is the fact that all light infantry gets a 1.5 bonus against it. I do not have a way to model this due to the fact the Eagle Runner is really not light infantry. You can compare versus light cavalry to determine the best skirmisher unit but then you are not comparing against LI. I knew this was an issue but decided to include them in the light infantry anyway. It is included as a note at the bottom.

First, I was wondering how to properly add gather rate upgrades. Is the best technique to simply change the cumulative gather value on page 1 or is there someplace else where I should be putting % gather rate upgrades for particular resources? Doing this would be no problem, just didn't want to overlook such a feature if it exists.

The gathering rates on the front page change everything automatically. If you are not interested in calculating VS costs, I would not worry about this since adding an upgrade or changing the collecting resource (hunt versus berries for example) will change everything simultaneously. Therefore, from a comparison point of view, everything will change together (the delta will stay the same). The qualitative number will change but the rank will not. Changing resource gathering is only useful if you want to know the different in VC cost of hunting versus berries. In my opinion, this is minimal since hunting requires so much walking time and runs out. Very rarely (except Sioux) will you be hunting late game. I also did not have an option to change to French or German settler wagon gathering rates since this would change units that don�t belong as well.

Also, there were a few results that I couldn't quite figure out. Grenadiers, in particular, do a good bit better vs. ranged units than I would have expected. They come in ahead of horse artillery and great bombard, both units that I find fantastically good against ranged units.

Against infantry in Industrial + Upgrade (when all units are available), here are my results (remember all is upgraded to Guard).

Ram
Mantlet
Petard
Arrow Knight
Mortar
Culverin
Grenadier
Grenadier (RG)
Light Cannon
Organ Gun
Gattling Gun
Li'l Bombard
Rocket
Falconet
Great Bombard
Heavy Cannon
Horse Artillery

Of the units that can attack infantry, I see Grenadiers as last and Horse Artillery as one of the best (fast, good range, nice HP and damage (especially with upgrade), and you can set factories to gold and make locally from artillery foundry. Versus other unit types (cavalry, buildings, ships), there are better choices and different results (once you learn to use tool, can see for yourself), but in the Age of Light Infantry series, it is an excellent cannon. Here is a brief run down:

1.) Select "Compare Against" = Siege: Infantry

2.) Select "Age to Compare:" = Industrial + Upgrade (remember some artillery units are only available in Industrial but also have a cheap upgrade option available when all other unit types do not)

3.) Select any optional upgrades you want such as WC aura, HC shipments, etc. = In example above I did not

4.) Type in values into the yellow input values to increase hit points, attack, speed, etc based on HC shipments (Siege combat for example gives 15% HP and damage) = In the example above I didn't use anything

5.) Select the effectiveness you want to compare. Versus ranged units, go to the "Effect vs. Ranged Damage" column and select "Sort Ascending" with the auto filter function (little arrow at top of column). The reason there are 3 columns is there are 3 damage types: melee, ranged, and siege. Versus artillery, use "Effect vs. Siege Damage". Versus hand infantry, use "Effect vs. Melee Damage". This allows for the effective hit points (EHP) to be included since a bonus exists to hit points versus ranged damage of artillery units.

If you still cannot repeat the results above, give me a holler on Yahoo / Skype and we can go through it.

I am interested in others learning and using this tool. It is not perfect as there are exceptions (micro and cavalry path finding), but gives a realistic view of the game (at least from my humble perspective).

LordBob13
Skirmisher

posted 06-18-07 02:32 PM EDT (US) 13 / 20

for me it still says that I cant download it....

From a poster at Agecommunity (Talking about the 4 cow age up option):

"Only a noob like you doesn't realize how valueble it can be. That's 2000 food waiting to be gathered"
LOL

Cyclohexane
Skirmisher

posted 06-18-07 05:09 PM EDT (US) 14 / 20

for me it still says that I cant download it....

Check your firewall settings or try a different browser to download. I have no trouble downloading from a few different computers I tried and you are the only one expressing problems. It is hosted on FileDen which allows me to quickly change 1 file for an update to many different forums I posted it on.

I do get a popup security message asking me to confirm the download. Perhaps you security is set to high. I do not know. Can you download the Quick Reference Guide (also hosted at FileDen)? If not, then the problem is on your end. If so, then I would say that it is possible that there are too many people downloading at the time you tried and to try again later.

Unfortunately, I cannot post an online version with interactivity since the features used in Excel to create the spreadsheet are not supported by .html. I could post a version with no interactivity but that would defeat the purpose of this spreadsheet. There are no macros but I have filters, data validations, conditional formatting, vlookup tables, and some complex formulas.

With the Quick Reference Guide I could post online since there are very few interactive features and is mostly a report so I could save without interactivity. The interactive features of the Guide (such as Tashunke Prowler damage / hit points based on number of units) will not function on the website version (in other words, cannot change any of the input values on the spreadsheet online).

[This message has been edited by Cyclohexane (edited 06-18-2007 @ 05:12 PM).]

George_uk
Skirmisher

posted 06-18-07 07:19 PM EDT (US) 15 / 20

Hey Cyclohexane, just a minor spelling mistake on the main tabs, you have Unit-Artilary (Unit-Artillery).

Sorry about being picky but you did ask for people to point out any spelling mistakes errors they could find!

Previously known as MoNo Ager

Cyclohexane
Skirmisher

posted 06-18-07 10:42 PM EDT (US) 16 / 20

^
The one place the spell checker does not work. I'm going to wait to update this one...

Zongo
Skirmisher

posted 06-20-07 04:34 PM EDT (US) 17 / 20

once again, outstanding work!

N3O clan is lucky to have you, I'm sure you share some sneaky game-stats just with them

Old Indian: Once upon a time, a woman was picking up firewood. She came upon a poisonous snake frozen in the snow. She took the snake home and nursed it back to health. One day the snake bit her on the cheek. As she lay dying, she asked the snake, "Why have you done this to me?" And the snake answered, "Look, bitch, you knew I was a snake."

Unthinking_Pain
Skirmisher

posted 06-21-07 11:12 AM EDT (US) 18 / 20

Really nice tool, here, thanks a lot!

One note - the Explorer Dog cost. Considering that most explorer dog shipments also buff the Explorer and often give him a special ability, I think the cost could be lowered. A minor note, and something one could fiddle with depending on how much they value the Explorer's special ability.

SevereBeating
Skirmisher

posted 06-21-07 04:46 PM EDT (US) 19 / 20

_{Zongo
Colonial Militia posted 06-20-07 04:34 PM EDT (US) 17 / 18
------------------------------------------------------------ --------------------
once again, outstanding work!

N3O clan is lucky to have you, I'm sure you share some sneaky game-stats just with them}

We ARE lucky to have him. However, there a lot of great guys in this clan. All ages, diverse nationalities & backgrounds, all looking to have fun & get better.

Zongo
Skirmisher

posted 06-27-07 03:45 AM EDT (US) 20 / 20

I can only agree with you, it's a very nice and friendly clan, with a great forum and even some training officers who will help you out with your civ/stats etc...

The Unit Comparision sheet is really useful for any player who wants to get a better understanding of the game!

Age of Empires III Heaven » Forums » General and Strategy Discussions » Unit Comparison Spreadsheet - UPDATED!

Top