RegisterDonateLogin

Does not grant you the rank of Master.

Welcome Guest Active Topics | Members

Excel Mini Cost Calculating Generator Options
surf_rider56
Posted: Friday, April 22, 2016 1:40:27 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 9/19/2008
Posts: 1,740
Location: Orange County, CA
I've got an excel program I got from the old WOTC forum's back in 07-08 that could use a little updating. It actually works pretty well but its getting out of date and I'm not an Excel expert by any means. I tried to do what I could to update it with new abilities but at a certain point it goes "wonky" and just doesn't work right.

Anyone good enough with an Excel program that's willing to try overhauling it?

Cheers,

Surf
kobayashimaru
Posted: Friday, April 22, 2016 7:55:46 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 8/26/2011
Posts: 915
I have dabbled with just such a thing...
I am no tech-wizard, though I certainly would also like to see that eventuate BigGrin

if you pull up the equation page, or open the operator tab,
you can see how the rows and columns were coded to make the algorithm.
I think from that pageview mode, you can then change those rows and columns to make the algorithm different.

That is how it works, if it's the WotC spreadsheet -
on the left is

HP
DEF
ATK
DMG

SA's

and on the right, is the weighting column.

it basically goes through and you get a base figure which is 10 HP, 14 DEF, +4ATK 10DMG for 4 points,
with 2pts per 10 HP, 1pt per DEF, 1 pt per ATK until +10ATK then 2pt for >10ATK
5pts for +10DMG, +8pts for +20, +15pts for +30DMG etc.

So, the algorithm reads the right-hand column, and does a sum of those
(with an operator for the rules on those squares).

You then can plug in anything you want and get a roughly well-costed piece. BigGrin.

if you try to tweak the SA column to have more than 8 or so abilities, I find it produces weird results.
if you try to tweak the CE column with more than 3 abilities, or with a net-negative integer output,
I have crashed Excel reading that before BigGrin

I would be keen to hear where you think the problem might be, after looking at the operator column in pageview tab.
I'm sure us Bloomilkers will come up with something BigGrin

@ SWMPlay,
your costing page idea might come in handy here,
I was wondering if you've had a look at the spreadsheet that Surf is talking about?
I suspect the problem may be in producing negative integers from SA weighting or from CE weighting,
or the rapport/camaraderie weighting.
kobayashimaru
Posted: Friday, April 22, 2016 11:31:36 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 8/26/2011
Posts: 915
using our v1.8 system,
a character with
110HP,
23 DEF
13 ATK
20 DMG
3 SAs (Unique, Twin, Melee)
Force Rating 1, Force Renewal 1
3 force powers not exceeding level 2,
it works out to around 51points.

that's before doing rigorous costing of all SAs and FPs etc,
so I think there's a little wriggleroom there, without needing to have explicitly costing all of them,
it's somewhat arbitrary, but that weighting of abilities happens after the base card is finished
(so, accounting for synergies both on the card itself and potential meta combos).

thats very close to early vset and late WotC costing.
As to more recent VSet costings,
I suspect the way force powers and SA's are weighted has changed - more negative points which lower the cost seem to be in those areas.
probably the more prominent low-cost abilities are Savage, Single Shot Blaster and Ponderous Relentless,
they're quite nasty abilities, so they tend to lower costs.
I've seen Loner costed as a negative cost ability too hehe.
surf_rider56
Posted: Saturday, April 23, 2016 8:27:21 AM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 9/19/2008
Posts: 1,740
Location: Orange County, CA
... you lost me after "Using our 1.8 system" which is why I asked if anybody else wants to try working with it BigGrin
kobayashimaru
Posted: Saturday, April 23, 2016 4:31:19 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 8/26/2011
Posts: 915
@surf -
have you done an Excel spreadsheet variant and costed for all the force power, special abilities commander effects and so on?
or, do you think the 'wriggleroom' approach there is the way to go?
surf_rider56
Posted: Saturday, April 23, 2016 6:25:39 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 9/19/2008
Posts: 1,740
Location: Orange County, CA
kobayashimaru wrote:
@surf -
have you done an Excel spreadsheet variant and costed for all the force power, special abilities commander effects and so on?
or, do you think the 'wriggleroom' approach there is the way to go?


All I've done is try to insert a 3 cell row (example cell with Name of SA/FP, cell where you indicate if its used in character, cell indicating what the SA/FP will cost ) and then moving down the cell that totals it all .... it might be easier to email to anybody who wants to play with it or update it to 2016 standards, which was what I had in mind

BigGrin


Or in other words, I'm a History teacher, not a math guy and my Excel abilities are pretty much =sum(A1+B1)
kobayashimaru
Posted: Sunday, April 24, 2016 4:24:35 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 8/26/2011
Posts: 915
its okay Surf, math is anyone's game
and we can't all be uber-mathletes BigGrin
(you might enjoy Bartlett's "The Essential Exponential!" - a wonderful pedagogical text for most kinds of math hehe)

I'm not much better when it comes to math or programming -
basically, you could solve it with nested ifs
(as there are a limited discontinuous range of values, and hence nPr, finite combos of inputs).

For HP, you can do a simple 1pt per 10HP rule,
with if(a2<=|intrange|, quotient[a2/10],iff(a2/>=(rangeMax300, "not a possible input")))
and anything you put in A2 will translate into B2 as the proper points.
For a different sequence past 100HP, i've found you can't subnest simultaneous operators in Excel (I'm used to AIXGL and AIXML)
so, you have to specify the b2 field as a scalar, and then you can multi-op on it for
as limit |user input| approaches infinite hp, |points increment| = A000001 from OEIS
(you could even use a 'get.that' command if you wanted to be super lazy and unsecure...)
So, you can make it exponentially prohibitive per 10HP increment over 100HP...
same for DEF, you can make defense higher than 25 be exponentially prohibitive in cost.

You could also solve each of those boxes as an array, but that will make your spreadsheet look a little messier BigGrin
have the array be say
100HP = no penalty.
110HP = 1pt,
120HP = 3 points
130HP = 5 points...


I am working on having the read command
actually read a CE verbatim and convert it weighted on what it does...
this is annoying in Excel though, as its a powerful framework - I'm just a sh1te programmer in it hehehe

for DEF,
nested ifs...
if(b3=14,0,if(b3=15,1,if(b3=16,2 etc...
you could do a sum.that range, and make it proportional to whatever input is +1 etc,
but nested ifs are quick n easy.
if user input anything outside a defense range in 0-25, then "not a SWMinis defense" is displayed BigGrin
same with ATK:
ATK ranges from 0 to +18 as an unmodified base attack,
so thats only 19 combos for nested ifs.
Damage is easy.
damage is 0,10,20,30,40.
if(b|damagecell|=0,-3pts,if(b|dmgcell|=10,0pts,if(b|dmgcell|=20,+5pts,if(b|dmgcell=30,+8pts,if(b|dmgcell|=40,+15pts))))

then, checkIfXor|number.of.SpecialAbilities > 5, +1pt per 1 whole integer over |5|
same for force powers,
checkIfXor|number.of.ForcePowers > 3, +1,3,5... per 1 whole integer over |3|

this way, it becomes infinitely expensive to have a number of special abilities or force powers which approach infinity... BigGrin

CE costing will simply be a "read.that.|textstring|",
and if it says "6 squares" say, 1pts,
boardwide is +5pts,
per 1pt of increase to ATK or DEF will be 1pt,
and per 10dmg increase, +5pts.

Costing the sum of the whole piece relative to what it does for the meta synergies will be harder to code for -
thats why SWMPlay's ideas were so awesome to count for those... it was a Conway manifold approach,
so it'd look up the others and do an average...

then, at Bx'th cell,
its the simple ol' beauty
=sum(B|O'|:B|x-1th|)
bob's your uncle. BigGrin

If only someone could integrate that counting program with
Bloomilk's database, so we could then also have an additional line
which compares the price of your generated mini,
with the average of similarly-abilitied' actual figures from the database,
and told you how close the average cost was to the cost your figure has,
whether your custom was overcosted or undercosted relative to that average,
and if so, by how much --- that would be an awesome ability to have,
even as a guesstimation to help narrow down the playtesting time.


How close do you think your Spreadsheet generator/that WotC generator's costs are to WotC stats or to VSets?
where do you suspect the 'discounts' are coming from, to explain the 'competitive costing' of VSet figures?
I think most of the discounts come from the removal of penalty of SAs over 5, force powers greater than a certain magnitude etc,
and the removal of CEs costing, as well as more of the SAs being negative abilities (like single shot blaster) etc,
so that's how VSet figures are slightly cheaper than WotC figures with otherwise similar
HP
DEF
ATK
and DMG breakdowns. BigGrin
surf_rider56
Posted: Sunday, April 24, 2016 4:30:54 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 9/19/2008
Posts: 1,740
Location: Orange County, CA
I've shared this in the past; this is the "original" WOTC boards calculator I got from god only knows who back in 07-08.

The following are the "directions" I've given to people to use the Excel program. Following thse rules of thumb, the calculator is surprisingly accurate with the WOTC pieces and the first 5-6 V-Set's; I stopped checking after that.

This isn't perfect and I'm no genius with an Excel so I don't know how to tweak it. Usually it comes out pretty close if I follow the following rules of thumb. The way it works best it seems ....

1. Put a 1 in the red squares that apply to your ability. No if's or buts about this.
2. Any mini's that come out over a total of 20-25 I subtract 4. Min's that are over 25 - 50 I subtract 5.
Mini's between 13 - 19 I usually leave it be but use your best judgment; I somestimes subrtact a
point or two. Mini's under 12 I use the calculated number.
3. This calculator came out a few years ago; it has 95% of all abilities but not all. I sometimes pick an
ability that will approximate what I think the ability would have cost and sub in that ability for a
missing one.
4. There is nothing to calc Commander effects, so I use my best judgment and follow #3 for adding in a
CE.

Play with it and see how it works. I've seen it come out close enough to the WOTC pieces so as to not be worth an argument, and ultimetly All stats can be argued one way or the other.


So few pieces from the old days were over 50; I stick to the subtract 5 rule there up to 65; after that is when it gets dicey.


Would you like me to email you the program Koba?
surf_rider56
Posted: Sunday, April 24, 2016 4:38:12 PM
Rank: Advanced Bloo Milk Member
Groups: Member

Joined: 9/19/2008
Posts: 1,740
Location: Orange County, CA
kobayashimaru wrote:



How close do you think your Spreadsheet generator/that WotC generator's costs are to WotC stats or to VSets?
where do you suspect the 'discounts' are coming from, to explain the 'competitive costing' of VSet figures?
I think most of the discounts come from the removal of penalty of SAs over 5, force powers greater than a certain magnitude etc,
and the removal of CEs costing, as well as more of the SAs being negative abilities (like single shot blaster) etc,
so that's how VSet figures are slightly cheaper than WotC figures with otherwise similar
HP
DEF
ATK
and DMG breakdowns. BigGrin


My best guesstimate is in general, WOTC pieces under 20 were costed 2-4 points too high, over 40 2-4 points too low and over 50 about right. Few WOTC were "aggressively costed" and its obvious which ones they were (JWM, Kel Dor BH, etc.) whereas the V-Setters seem to be more "accurate" now that the systems better understood. I saw someone once say the WOTC included more "useless" pieces simply to increase sales, where the V-Set has no such issues (nor the rest of us.)
Users browsing this topic
Guest


Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Main Forum RSS : RSS

Bloo Milk Theme Created by shinja
Powered by Yet Another Forum.net.
Copyright © 2003-2006 Yet Another Forum.net. All rights reserved.