Compulsively picked up excel to model game data... and as such am lost -

[Duncan MacLeod]

Hello,
I've been rehabilitating myself to the brilliance of Excel after years of cruelly shunning it due to poor handlings of it by my teachers of the old school:
I didn't count then on my obsessive compulsive nature, which now neccessitate a prompt return to Excel and its functions. Microsoft Office has generally been good as its readily available all the time...
So I'd like to use Excel for the following task -

Basically, I have this game (Patapon,) and it has crazy mechanics in place for attributing its various stats.
And, because I'm crazy, I find myself wanting to first understand these hidden mechanics through repeated in-game trials - ad nauseam - and then because I am crazy again to calculate the best possible way to play the game. And then play the game...
Well - its regarding the calculations step that I am at loss and turn to you forumites.

This is what I want to do:
Chart every possible combination of warrior type to every possible equipment type. Through my coefficients I can deduce the attribute benefits that the articles yield to the stat and through yet more house-made coefficients I can deduce the absolute value of 'greatness' for a particular combination of warrior type/articles from the stats. In terms of just one absolute value.

Well, its just that, now I need to compare values of absolute greatness together with other values of absolute greatness and there are so many possible combinations that I would be a fool twice to track them manually:

The army, is consistant of six-warrior troops. So that's 6 sets per troop. (I'll be manually considering one troop at a time, so that's okay)
- 3 warrior types (I'll be considering) that can be used throughout for the 6 sets in every troop.
- And there is one other warrior type that is mutually exclusive and can only be used once in the troop. So 4 warrior types all together.
Now there's equipment,
- All equipment is mutually exclusive and may only be issued once, once given are out of the count of available equipment, but there's a shield slot and a sword slot for every warrior. I'll have chosen 6 optimal shields and 6 optimal swords to test out and am looking for the most efficient combination of 6 'warrior-types'/shields/swords possible out of the available consortium - Considering that one particular warrior-type *(out of the 4 types available) is mutually exclusive of the other sets and may only be used once, if its used at all where as the other 3 may be used at will and for however many sets you wish.
A set is synonymous with a one warrior/shield/sword combo, I may need to elucidate.

All warrior-types have stats, all equipment types have stats also. I know how a set of stats compares both to each other and absolutely in terms of overall effectiveness of the trooper. Now I need to know what combination of 6 of these warrior-type/shield/swords I should use for maximum effect of the entire troop. I don't want to use an item or warrior type that may be used only once for instance, in the wrong situation... Some equipment complements a particular warrior-type, some detracts from it, relatively speaking.

So, is there a function in excel that will present to me, say: the best/2nd best/3rd best combinations of troop-arrangement within the confines of all equipment being usable once and all warrior types being re-usable apart from the one warrior type that may only be used once?

How would it work? Maybe there are more than one functions to use; then how? Please, help me forumites.
My acumen is only rudiment. Improve it and I shall love Excel forever!

Edit: I'll be inputting all the 4 trooper types and all the 6 shields and all the 6 swords manually, together with their stats and I can provide formulae and coefficients to appropriate the stats to their absolute value in terms of efficiency for each item, right next to the real stats, but then there's also a divergence in terms of how these stats stack together with stats of their kind from other equipment/warrior-type; sometimes they add, sometimes they multiply together. So this feature is going to have to be hard-wired into whatever function it is you suggest for this order. But I won't give any order of preference, I need excel to work this out for me.
I have done all of this manually making faint recourse to excel before, but on a much smaller scale. Using my own discretion. This time, however, there is no other option.
<!-- / message --><!-- edit note -->

 

[mikerickson]

6 trooper types X 4 shields * 4 swords = 69 warrior/equipments types

Number of possible 6 warrior bands = 6^69 > 5*10^74

Number of cells / pre2007 worksheet = 65536*256 = 16777216

Number of worksheets needed to hold all possible warrior bands (one band per cell) = 3.00469E+67 worksheets.

Your computer's memory?

Listing or looping through all possible combinations is seldom an efficient way to do anything, in any platform.

 

[Duncan MacLeod]

I said I'd be inputting the values for the equipment and the values for the warrior-types manually,
So at maximum:
4 Warrior Types
6 Shields
6 Swords
input, 16. But not that I'd be doing them in bands.
The reason you dellineated is presicely why I need excel to work out the best combination based on the weighting (in coefficients) I place on stats and also on some formulae in terms of how they stack (whether by addition or multiplication) and some IF functions to simulate limits. For instance you can't have more than 100 percent crit rate, so more =IF(CEll >= 100,100,actual value)...

 

[mikerickson]

Yes Excel can probably do what you want.
It would probably be best for you to look in the PasteFunction dialog box to see what functions are avaliable, start laying out your spreadsheet and post here when any problems come up.
The project (at this point) is so vague and un-formed that "Yes, Excel can probably do what you want" is the best I can answer your question.

 

[Duncan MacLeod]

Alright I'll lay it out. Give me a mo.

 

[Duncan MacLeod]

http://img360.imageshack.us/img360/5169/pataponya1.png

That's it, in essence. The Shields didn't fit but they are just the same as the Swords above them; everything has all the attributes. 6 swords, 6 shields. 4 Warrior Types. Mock data. Azureus is torrentially downloading Flash Gordon (1980) in the background, I'm NOT listening to Stevie Nicks.
...

The real stats for the warriors listed are made up of what's listed for the 4 Unit Types (Barsala, Mogeyoon, Tikulee and Helmet (who's just a basic warrior with a helmet, really) *added unto what's listed under the Base Stat, because they are effectively 'upgrades' of the most basic warrior type and their benefits always stack-on to those of the basic unit.

*Or multiplied, depending on how the stacking operates for the particular stat, more on that below...

Its a One Warrior-Type/Sword/Shield per option. With the goal of getting 6 options, that's 6 Warriors with a shield and a sword each that fill my platoon. Where nothing has been entered is obviously taken as a zero, which is okay for most stats but not for Speed and Armour.

Health is additive. That is, the end Health is the result of summing up all component (Unit type, sword, shield) bonuses to Health.

Avg Dmg is what's used and is the sum of 'Low Dmg Limit' and 'Up Dmg Limit'; over 2. Its additive.

Speed is multiplicative. That's why I only enter the 'real' speed in the whitened cells left to where the values are displayed for processing, and if there is no entry the IF function forces a value of 1 (don't want to multiply by zero). The Base Stat for Speed you'll note, is 2; the end stat is the result of multiplying all components' speed stats (from Unit Type, Sword and Shield) by the 2 of the Base.

Crit Chance is additive and in the end you'd usually add 100 to the value for balancing purpouses because a crit scored can effectively be modeled as just a second basic hit scored (its double damage for a Crit). So 100% to Crit Chance is like scoring a free second blow every time, so its a 200 multiplier. 0 percent to Crit means you get no bonus blow but it still shouldn't reduce your effectiveness to 0 (which, if the end value was zero, would be the result when you multiply the absolute crit value by the absolute values for all other stats -) so we add 100 and without a Crit Chance you're only half as good as someone with 100% . I add only 50 to account for opponents Res v Crit, which is usually about 50, I surmise (and 100-50=50). Any solution that you suggest for this, will need to feature a way of inserting an IF function because when evaluating Crit Chance it is important to inform Excel that any amount of Crit Chance in excess of 200 for this Stat will be irrelevant as you can only Crit once per attack and a 200 score (ie 50,+150 your bonuses, -50 for the Resist v Crit I guess the opponent has) achieves that. And equally, any amount of Crit Chance below 100 does not inflict a penalty. So, something like =IF(Crit Chance>=200,200,IF(Crit Chance<=100,100,Crit Chance)).

Armor, like Speed, stacks through multiplication. Fortunately its very hard to get 0 Armor as an increase in Armor is a decrease in damage taken and I've chosen to enter the real Armor left of the value which is displayed for processing purposes, which itself displays 500-Armor (for the damage reduction granted by the armor bonus). It should be '100-Armor' as 100% armor is a 100 percent reduction in damage taken making the unit infinitely great in absolute terms, but the actual impact of armor isn't so great in-game, I've decided to reduce the effectiveness of armor in general by making 100% armor versus a particular type of attack only relevant a fifth of the time, Ie 100 armor vs infantry is 500-100 damage taken. Ie 400 versus the 500 of someone with no armor. In the end I'll have to take the inverse of these values when processing, because as it stands someone with no armor gets a higher multiplier than someone with a 100% bonus.

KB (Knock Back) is similar to Crit Chance for simplicity. But I figure its only half as effective in-game, so the end function needs to feature something like
=IF(KnockBack>=200,150,IF(KnockBack<=100,100,100+(U10-100)/2)) to accurately evaluate the advantage in KB of one Warrior/Shield/Sword combo over the other.

CnC (Concuss) is similar to Crit Chance for simplicity.

Resistances are slightly more complicated than Crit - I'm still working on how to correctly model them, based on in-game observation.
Still, the end result I'm looking for is a function that evaluates these stats simultaneously, adding appropriate weighting to end results where weighting has been outlined; multiplying all of these End Stats together, comparing all combinations sensitively to the fact that Unit 4 is exclusive to one Warrior, as are all the Shields and Swords but the first 3 Unit types may be used as much as is advantageous. And presenting me with the best configuration. Preferably in 6 cells, with something like:

'1,C,IV' -Barsala/Third Sword/Fourth Shield.
'2,B,V' -Mogeyoon/Second Sword/Fifth Shield

And so on, 6 times.

 

[Duncan MacLeod]

Yeah, I'm doing it manually. Thanks alot.