A&A Spreadsheet

[mtngoatjoe]

I wasn’t so much bored, as I had some free time and needed to work on my spreadsheet skills. If you have a Mac and Numbers '08, you can download the file: files.me.com/mtngoatjoe/mo1660.numbers.zip

It’s a bit too complicated to export to Excel, and I don’t have Excel, so please don’t ask for an Excel version. Sorry.

I used it a couple of times. My buddies gave me crap about it as I worked out the bugs, so I stopped using it. Then they asked where it was. Since they all work at Microsoft, I took that as a bit of a compliment.

Anyway, if you want to make an Excel version, you’ll first need to figure out how to make a popup menu and apply conditional formatting so that it changes color depending on the country selected.

Then you’ll need a cell for each country that adds up all the IPCs for the territories they control. My code looks like this:

• =SUM(SUMIF(C4:C34,“Russia”,B4:B34),SUMIF(G4:G13,“Russia”,F4:F13),SUMIF(K4:K33,“Russia”,J4:J33))

The SUMIF commands look at the first range of cells in the parentheses (the popup menus), and for the popup menus that equal “Russia”, then it adds the IPC values listed in the next range of listed cells (which are to the left of the popup menu cells.

For the victory cities, I do a little bit of visual trickery to keep things looking nice, so you can’t usually see these cells in the screenshots. Basically, the Victory Cities and IPCs summaries are separate tables sitting on top of the table with all the popups. I don’t think it’s possible to do this in Excel, so you’d have to use a separate sheet. In the screenshot below, I’ve moved the tables and showed the underlying cells.

Anyway, I list all the territories that have victory cities and set the cells to the right of them to equal the corresponding popup menu. Then I have two cells, one each for the Axis and Allies, that has the following code:

• =SUM(COUNTIF(F23:F34,“=United States”),COUNTIF(F23:F34,“=United Kingdom”),COUNTIF(F23:F34,“=Russia”))

• =SUM(COUNTIF(F23:F34,“=Germany”),COUNTIF(F23:F34,“=Japan”))

The COUNTIF function basically looks at a list of cells and adds up the number of times the content of the cells equals the specified text. If you do this for each partner and add the results using the SUM function, you get the total number of victory cities each side controls.

Well, that’s about it. Questions, comments, and suggestions are appreciated.

Ps. Maybe I do have too much time on my hands :-\

[ashoka]

That’s pretty neat. I don’t have a Mac so I’d have to recreate this in Excel but it’s a great template to start with.

Have you used it much in gameplay? It seems like it would be fairly easy to manage. I know in my board games, we always have to re-calculate income a couple times a game to make sure it’s right.

Also, is there a reset function to set everything back to default or do you just make sure not to save any changes?

Thanks

[mtngoatjoe]

I’ll be interested to see how you make it work in Excel. There are differences between Numbers and Excel; each has it’s strengths and weaknesses. Excel is designed to crunch numbers. Numbers is designed to make numbers look pretty. Anyway, please post some screenshots so I can see what you come up with.

I’ve used my spreadsheet three times. The first time was to work out some bugs and my buddies gave me a lot of crap because I was using a Mac and they are all Microsoft employees. Once I got the bugs worked out, the next two games went very smooth, but my buddies still gave me crap. For the fourth game, I didn’t use it, and my buddies seemed strangely disappointed every time we had to calculate how much to pay someone  :-|

I’m bringing it to the next game and I’m hoping to hook my laptop up to my buddy’s HDTV. We play in his family room and I think it would work pretty well.

I’ve quickly checked into adding a reset button, but I haven’t figured it out yet. I don’t think it’s possible with the version I have. Apple is supposed to release a new version of iWork soon, so maybe I’ll upgrade and see what happens. At a minimum, with the new version, I’ll be able to update the name of the territory and the IPC value with the color of the country that owns it. With the version I have, I can only change the color of the cell that shows who owns a territory.

[el_che]

hi there

looks very nice but unfortunately i don’t have a mac and i was wondering if there is already somebody who made a similar spreadsheet like this but in excell then, it would help alot with the calculating during the game and it would save us alot of time too.

again, nice work though!!

greetings

[77stranger77]

Wow, that seems like a lot of work. One little correction: Brazil is worth 3, not 1.

[mtngoatjoe]

Good catch on Brazil! I’ve updated my link with the correction. Please let me know if you spot any other mistakes.

This spreadsheet is basically a rewrite that simplified the formulas (they used to be really ugly and unreadable). But now they are much shorter and easier to manage. I haven’t used this version for an actual game yet, but I thought I’d smoke tested it enough to post here. On the positive side, at least my formulas were adding things up correctly! All I had to do was change the IPC value for Brazil and the U.S. went from 40 IPCs to 42.

[mtngoatjoe]

Ok, Here is an Excel Spreadsheet version of my IPC Tracker. I have not tried this in an actual game, but it seems to work. If you find any bugs or typos, please let me know and I will fix them. Download the file at https://files.me.com/mtngoatjoe/yhgc63

I do not think it looks as good as the Numbers version, but part of that is because I did not have a lot of time to work on it. Also, I created it on a computer running Windows in Spanish. And since I do not speak Spanish, it was a bit of a process just to get this far.

Let me know what you think of the graphs. I have mixed feelings about them, but they solved some design problems.

Let me know what you think!

[el_che]

thanks for the excell spreadsheet, i will try it out as soon as i play my next game, i can’t promise it will be anytime soon but i will definitly let you know if it works good

thanks again dude

greetz

oh, and the graphics, don’t worry about it, looks great and all that counts for me is that it works during gameplay, i’ve had a quick look already and it really works, now i just need to test it during the game, again thank you very much

[ragnarok628]

wouldn’t it be great if some gracious soul reworked that as a java applet and hosted it on this site?  then we could just come here and voila, easy keeping track of everything!

[el_che]

unbelievable, but true, we finally got together to play another round of A&A and i was able to try the excell spreadsheet, it works great, everybody was happy to use it so many thanks, graphics doesn’t matter, the attention went to the game, 8 hours and allies won! history repeated itself

[mtngoatjoe]

@el_che:

unbelievable, but true, we finally got together to play another round of A&A and i was able to try the excell spreadsheet, it works great, everybody was happy to use it so many thanks, graphics doesn’t matter, the attention went to the game, 8 hours and allies won! history repeated itself

Glad to hear it worked. That made my day!

[tastysub]

It looks like the me.com link is broken (thanks, Apple).  Any chance you could post the spreadsheet again?  I am numbers/excel agnostic.

Thanks!

[mtngoatjoe]

Ok, I’ve made a minor update so that the color matches the controlling country. This works well in Excel 2010. I’ve not tested it in other versions. https://dl.dropboxusercontent.com/u/99318978/Axis_and_Allies_Excel_Spreadsheet.xlsx

I’ve also uploaded my unit purchase tool. This is good for those that have trouble formulating their purchases. It works well on my iPhone, but I have no idea if it works on other platforms. Note: First enter the available IPC and Industrial Capacity, and then use the plus and minus buttons for making purchases. https://dl.dropboxusercontent.com/u/99318978/Axis_and_Allies_Purchase_Tool%204.html

Edit: I’ve created an iOS Numbers version of the unit purchase tool. You’ll need iOS and Number to use it, and it isn’t quite as fancy as the html version above, but the interface is a little cleaner.
https://dl.dropboxusercontent.com/u/99318978/Axis_and_Allies_Purchase_Tool_iOS.numbers.zip