Spreadsheet Province Simulator - Input wanted

[Trommel]

I've recently returned to this game after 5 years absence and just finished updating and pimping my old spreadsheet simulator. I also wrote some instructions for it so that other players might use it as well.
I'd love to get some feedback on it, especially concerning:

• The usability (user interface, instructions etc.)
• If there's anything important missing
• Input on the "durability indicators" I've constructed in order to facilitate easier comparison between different strategies
• Help finding bugs in there (report to me if you get strange numbers when testing it and I'll look into it)

Since I couldn't attach Excel files in the thread, here's a link to the spreadsheet:
http://ge.tt/6Jo9l8r/v/0?c
(gett is a quick-share site, so the file will only be there for 30 days, but I'll re-upload if needed).


Also, if there are any Solver experts out there, I could use some help getting the Solver to work better on this sheet. It works quite well when optimizing with relatively few constraints, for example maximizing military output (H30) by changing the 6 variables TGs/Forts/Stables (C17, C20, C29) and Off specs/Def spec/Elite per acre (G13, G14, G15) with constraints set to minimum income (H9>x) and buildings (C31<100%). You can even add the durability indicators as constraints and add GS/Hosps/Homes/Banks as variables and it still does ok on optimizing the military output. Another example where it works well is optimizing science distribution between Tools/Housing/Alchemy with the objective to maximizing either BE or Income (with a constraint set on the other of those two, and to military output).
However, my ambition when constructing the spreadsheet was that the solver would be able to optimize military output by changing pretty much every variable that's in there and with some 10+ constraints. When I try that, it doesn't return an optimal solution. Quite far from it actually. I'm not an expert on the Solver, so I don't know if it's simply the formulas that are too complex or intertwined for the Solver to manage it, or if I'm using the wrong settings on the solver for this sort of optimization problem.

[Nim]

I can't download it for some reason. Do I have to make an account on Ge.tt?

[Trommel]

No, you shouldn't need an account. I had no problem downloading it to my mobile, and I know several others that have been able to download.

If anyone else are having problems downloading it please report and I'll put it up on another file share service.

[Nim]

I used another browser to download it. (:

It looks really nice. I've been fighting with my Excel for some time now to get libraries included in my science spreadsheet. I'm trying to make one where you put your desired effect % in & that it says how much books you need. Any chance you're going to incorporate that too in your spreadsheet? If so, I can send you my stuff (it works except the libraries thing).

I guess tab six is where you add the age changes? Or do you have to change manually all formula's?

[Dan4GS]

One thing I noticed, and it might be working as intended...you say on the front page that OME is with "sending 3 generals". Do you mean sending 3 ADDITIONAL generals (4 total), or are you leaving 1 at home for ambushes? If you're sending 3 additional generals, the boost should be 9% to OME, not 6%.

[Trommel]

>Nim
I’m not going add anything more for the science, but the functionality you’re looking for is already there…if you use the solver. First set your percentage of Libraries, then use the solver with the objective set to science output, for example Alchemy science effect 10% (L5 = value of 0,1) and then put the bpa in Alchemy as the variable (in this case cell K5). You’ll have to do the sciences one by one though. If you haven’t used the solver before, just search for solver in Excel Help, you will find both a guide to how you load the Solver Add-in and a tutorial on how to use it (although you don’t need to read a tutorial to do the above, it’s pretty intuitive once you open the Solver) .

As for age to age changes, many of them will indeed be administered by simply changing the tables on the Lists tab. However, some formulas will need to be revised as well. How much depends on what sort of changes we’re talking about.

>Dan4GS
I meant 3 generals in total, so the 6% is as intended. (Maybe not a good assumption though, I guess it’s pretty rare to send that many on one attack in war.)

[Nim]

I'll look for the Solver. I haven't used it before.

[moogle]

one sec, and I'll upload my spread... creating a link atm...

[moogle]

Hey, here's my latest spread for comparison sake...
included are potential changes (bugs more than likely present due to this fact)
But really just wanted you to get a feel for the alternative usability... much more utopia-like...
Also, it auto-adjust everything into mOPA... might be related to your solver prob...

https://skydrive.live.com/redir?page...OYiIkedXVaw8Po

BEWARE: YOU MUST DOWNLOAD TO USE AN EDITABLE COPY (race/pers selection appears on drop down tab in THRONE link on side), SO YOU MAY BUG IT OUT BY PLACING A TYPO SOMEWHERE ON DL.

[moogle]

I'll leave this guy up so you guys can get a feel for it, but I probably won't post the one I'll make with final changes here...

...want the final? Should join my k/d, LOL... #razednconfused

[Trommel]

Updated the spreadsheet simulator with Age 59 changes. Download on this link:
http://ge.tt/68fdSvr/v/0

I also did some minor modifications compared to the original version, for instance I added an "ambush defense" value (now that you can't by ambush immunity through pers, I thought it was relevant to add a direct comparison on this between different strats).


>Moogle
I have a couple of comments/inputs on your sheet, but very busy atm with analysing the changes and strat options for next age. Will get back to you after the age has started.

[Aardvarki]

Trommel > A few things:
-BE is not affected by honor (as far as I am aware), and you're using multiplicative BE bonuses, not additive. All BE Bonuses are additive.
-Base attack time is 16 hours (You have this right). In war, the multiplier is 0.8 (yielding 12.8 - you have 12).
- You still calculate human O specs as 4 NW and elf D specs as 5 NW. These are no longer correct in age 59.
- I couldn't check a lot of the other formulas because you crunch basically all your numbers in each given cell, which is fine for function, but impossible to debug. You also don't name your cells, which means that double-checking the math for any cell is nearly impossible. This is a software developer's nightmare. Not suggesting you change anything, just letting you know that there's possibly more that I missed.

Quick fixes for you, but figured I'd point them out. I like the calculator and the layout - it's given me a good bit of inspiration to improve my own calculator. Feel free to drop me a PM if you would like any help or development advice.

[Trommel]

Thanks for the input and spotting those errors Aardvarki. BE Honor thing was a leftover from an "proposed changes" version.
About the BE, are you sure it's additive? Guide says it's multiplicative, but maybe that's outdated? I must admit I didn't bother to verify it in-game.
from guide: Building Efficiency = [ (0.5 * (1 + % Jobs Performed)) * Race * Stance * Science * Dragon ] + Personality

And about the debugging, I'm aware the design is very messy from that perspective. I guess it's pretty obvious I'm not in software development (I work in financial controlling, so by habit I focus on the analytics and outcomes). Anyway, I might drop you that PM after the age preparation and setup is over. I'm always interested to learn more about making proper Excel designs...

[Aardvarki]

Last age, BE was additive. The easiest case I can give a direct example of was an enemy kingdom who had a province at 64% BE - we sent a gold dragon and his BE dropped to 39%. Had the BE from the dragon been multiplicative, he would have only lost 16% BE (bringing him to 48%), not 25%. Upsettingly, we still lost that war.

It's possible things have changed. Then again, I know the guide is very outdated in a lot of calculation aspects. My guess is the guide is wrong, and BE is still additive.

[Persain]

Also, if there are any Solver experts out there,..... 10+ constraints. When I try that, it doesn't return an optimal solution.

It just wont when you get to complicated. Things like local minimum/maximum or under constrained solutions just break the heck out of solver. Part of my group at work dida whole bunch of stuff using solver and eventually they had to abandon it and use R. R is a free stats addon to excel (www.r-project.org/) that i'd recommend you move to if you want to keep developing this to something more useful.