FANDOM


This is going to be the first in a series of posts that explain how I do what I do on the wiki or in the game. As my interest in the game falters, I don't want to leave a hole on the wiki when I leave, but rather leave the tools so that my major contributions won't be missed.

To produce the ISO builds for the ISO Blog, I built a spreadsheet in Microsoft Excel where I can see the builds before slotting any crystals. The original purpose and design was to redo the ISO on a hero because of the new reactive isos, but it remains useful for building heroes from scratch, or updating heroes based on changed assumptions.

Docbobm and I have created a set of instructions that will enable you to duplicate my spreadsheet. Before I give you the recipe, we have to talk about requirements and assumptions.

Requirements:

  • Microsoft Excel or Open Office/ Libre Office Calc
    • The spreadsheet uses the Solver Add-In
    • If you are not interested in the Solver functionality, building the first part of the spreadsheet in any software should be sufficient.
      • The Solver functionality is really the main attaction here.
  • A basic understanding of spreadsheet software (cells, rows, columns, labels, formulas)

Assumptions:

The spreadsheet is built to create heroes in the shape that I prefer, which is a certain high level of health, 5 bar accuracy, 6 bar equivalent in either defense or evasion, and then 6 bar and beyond in attack. If you want to add other stats, you will have to tweak the sheet on your own to get there.

The spreadsheet is built to my use, and I have been level 300 for a long time now. So, it is scaled to L300. I have a guide to converting your hero and agent stats to L300, based on the stat bars, which will allow you to use the spreadsheet to find your level scaled crystals.

Heroes are rated in their six abilities from 1-5 (with a couple weird exceptions). Skills rated "1" through "3" all present as 1 bar, 2 bar or 3 bar. Skills rated "4" and "5" both present as 4 bars, at some levels. Discerning between the 4 and the 5 is important, but easily done when you know how to recongize them. For a quick primer, take a look at Juggernaut. His base, iso-free, attack is rated a 5 while his defense is rated 4. That will help you sort them.

The Recipe

The sheet has two sections. The first section we can call the workshop table. It’s where we take apart the hero’s stats, factor it down to his base stats, set goals, and check the solver’s work. The second section is the solver table. It’s where we let the Solver add-in do our work for us.

Workshop Table

The Workshop Table is the only table you will need if you are not running the Solver portion of the sheet. It will enable you to look at builds and see the final result, in numbers (adjusted to L300) to see how they stack against PKB standards.

Row and Column headings

  1. Starting in cell B1 and going across to O1 type the following in each cell and then <tab> to the next cell:
    1. Stats
    2. Modifiers
    3. Base Stats
    4. Goals
    5. ISO Total
    6. First
    7. Second
    8. Third
    9. Fourth
    10. Fifth
    11. Sixth
    12. Seventh
    13. Eigth
    14. Total Stats
  2. Starting in A2 and going down to A7 type the following and hit <enter> after each one to go to the next cell:
    1.  Health
    2. Stamina
    3. Attack
    4. Defense
    5. Accuracy
    6. Evasion

Working Formulas

  1. For Base Stats
    1. In cell D2, type the formula: =b2-c2
    2. Right click on cell D2 and the click copy.(or {ctrl}+C)
    3. Highlight cells D3 thru D7 and right click and choose paste. (or {ctrl}+P)
  2. For Goals
    1. In cell E2 type: =9000-D2
    2. In cell E3 type: =9000-D3
    3. In cell E4 type: =2000-D4
    4. In cell E5 type: =2000-D5
    5. In cell E6 type: =1800-D6
    6. In cell E7 type: = 2000-D7
      1. Note: numbers will start to appear, do not be concerned, we are still building the sheet.
  3. For the Worksheet
    1. In cell F2 type: =sum(g2:n2).
    2. Right click on cell F2 and the click copy.(or {ctrl}+C)
    3. Highlight cells F3 thru F7 and right click and choose paste. (or {ctrl}+P)
    4. In cell O2 type: =F2+D2
    5. Right click on cell O2 and the click copy.(or {ctrl}+C)
    6. Highlight cells O3 thru O7 and right click and choose paste. (or {ctrl}+P)

Solver Table

This creates a table that we can use the Solver Add-in on. First, we'll build the table, then, when our sheet is complete, we will explain how it all works

  1. In Cells A13 thru A16, type:
    1. Health
    2. Attack
    3. Accuracy
    4. D/E
  2. In cell B12, type: Goal
  3. In B13, type: =E2
  4. In B14, type: =E4
  5. In B15, type: =E6
  6. In B16, type: =MIN(E5,E7)
    1. Note: Generally, I do not use Stamina.
    2. Note: Generally, I use the higher of Defense or Evasion.
  7. In C13, type: =(B13-O13)/5.1
  8. In C14, type: =(B14-O14)*1.2
  9. In C15, type: =B15-O15
  10. In C16, type: =B16-O16
  11. In C17, type: =Sum(C13:C16)
  12. Put the following headers for D12 thru O12:
    1. Powerful
    2. Focused
    3. Sturdy/Deft
    4. Violent
    5. Bulky/Proficient
    6. Exact/Dexterous
    7. Steady
    8. Stalwart/Skillful
    9. Patient/Athletic
    10. Forceful/Mercurial
    11. Chaotic
    12. ISO Total
      1. Note: In Excel 2010 if you wish for the words to wrap so that you can see what is in each header, highlight D12 thru O12 and on the home ribbon click Wrap text. In earlier versions, you have to go to the format cells window.
    13. Fill in the table so with the following values:
      1. IsoSheet4_zps5392640b.png
    14. In cell O13 type: =SUMPRODUCT($D$17:$N$17,D13:N13)
    15. Copy and paste that to cells O14, O15, and O16
    16. In each cell from D17 to N17, type: 0
    17. In cell O17, type: =SUM(D17:N17)

That's the working part of the spreadsheet. Let's add a little conversion chart at the bottom, as a handy reference.

  1. In B19, type: H/S
  2. In C19, type: A/D/A/E
  3. In row 20, starting at column A, type: 1 <tab> 5722 <tab> 1144
  4. In row 21, starting at column A, type: 2 <tab> 6438 <tab> 1288
  5. In row 22, starting at column A, type: 3 <tab> 7154 <tab> 1431
  6. In row 23, starting at column A, type: 4 <tab> 7868 <tab> 1574
  7. In row 24, starting at column A, type: 5 <tab> 8584 <tab> 1717

As an explanation, if your hero has 3 bars of health or stamina, the L300 value that you will need to use my spreadsheet is 7154. If he has 2 bars of evasion, it's 1288. Remember what I said about 4 bar values. You need to differentiate the true 4s from the 5s.

Operation

The first step is to get the base stats of your hero. If you have not iso'd the hero, you can just put your base stats in the cells B2-B7. Please note, these need to be converted to L300 numbers, using the conversion table at the bottom. If you are reisoing, and L300, you can put your Iso'd values in B2-7, the value of the ISO (the green numbers) in C2-7, and D2-7 will present your base stats and your goals will be moved down to B13-16.

If you are <L300, you will have to figure out how to remove the ISO values from the base values, and put in the L300 adjusted base values using the conversion chart.

With that complete, we are ready to rock and roll with the solver add in. Assuming you are working in Excel 2010, go to the data tab. If you don’t have Solver listed under Analysis, you will need to install it, by going to the File menu, open Options, click Add-Ins, and then add the Solver Add-in. If you are using an earlier version of Excel, the Solver Add in will be listed, once installed, under the Data menu. I believe you can also install it from the Data menu in pre2010 Excel, and you can probably find instructions on the web.

A word about Solver. Solver is a tool that solves for a single number. It can get that single number from a series of inputs, which it will run in a series of iterations to come up with the correct solution. It can solve to specific values, or maximum or minimum values. In business school, where I learned how to use Solver (and other excel goodies), I got good enough that I could solve problems with negative exponents, logarithms, and all sorts of other math I'd cut class on in high school. Also, my algebra started getting very bad, because I leaned on Solver pretty heavily.

Opening Solver brings up a window with a series of inputs. I will walk you through the inputs. They are the same regardless of the goals, so you only need to enter inputs once, then you can tweak the data and rerun Solver all you like.

Click on cell C17. This cell is the sum of the excess stat points over our goal. Since Solver works to find a single solution, I created this cell to be it. Click on the data tab and choose Solver from the analysis section on the right. The results in the box shown below only empty. Make sure that the Set Objective is set to $c$17 and the To: is set to Min. Based on how the table is set up, the more excess we have, the larger the negative number our target will be.

In the By Changing Variable Cells field, you should enter $D$17:$N$17, if you have set up your sheet exactly by my directions. If you have modified the sheet, you can click the button at the right side of the field, and use your mouse to highlight the row of 0's under the second table.

Click on the Add button and add the following constraints:

  • $C$13:$C$16 <= 0
    • Ensures each stat meets the goal as a minimum.
  • $D$17:$N$17 >=0
    • Ensures that Solver won't use negative ISOs to achieve goals.
  • $D$17:$N$17 = integer (drop down middle box to get int)
    • Integer_zpsfc3a85b8.png
    • Ensures that Solver won't use fractional ISOs to achieve goals.
  • $o$17 <= 8
    • Ensures that Solver will have to fit the solution into no more than 8 ISOs
      • Since Solver is looking for a minimum value, it will always use 8 ISOs, as there is no 7 ISO or fewer solution that produces a lower value than an 8 ISO solution.

It should look like this in Excel:

IsoSheet5_zps5affd931.png

Or this in Open/Libre:

2RUEQ8c.jpg

Click Solve. When you do, it will run through a bunch of solutions then notify you that it has come up with a solution. The solution will be the numbers that have replaced the zeros in cells D17-N17. The numbers correspond to how many of each types of crystal you should use.

Occasionally, the solver will not come up with a workable solution, and I will let it ignore a constraint (typically, if it can use fractional ISOs, it can solve nearly anything). The fractional iso allotments give me an insight into which way I want to bend my requirements, using the work check table (G2-N7).

I will put the values from the solver into the table at the top (Cells G2-N7), to see the total shape. If there is excess accuracy, I will remove crystals with accuracy until it's down between 1800 and 1875. Accuracy has a diminishing return beyond 1800 or so, and anything that is excess there, can more profitably be turned into health, attack, defense or evasion.

I will tweak based on abilities. If someone can naturally double their evasion/accuracy with a quick action, I don't feel the need to build those out to goals. If a hero has very few attacks and nothing driven by the attack stats, I might not build the attack. But the Solver result is a good way to get pointed in the correct direction.

A Walk Through

So, you've built your sheet and you're eager to run it. Let me take you a bit deeper into the workshop and show you how it works. In conversation yesterday, I determined that the Invisible Woman would be a good one to do, since she has base stats that fit and ISO that could be tweaked beyond the basic pattern.

Base Stats

Looking at IW's base stats on her page, we see 2 bars for health and 3 for stamina, which translate to 6438 base health at L300 and 7154 base stamina at L300. Her At/De/Ac/Ev bars are 3/2/4/4, resulting in L300 equivalent ratings of 1431/1288/1574/1574. If we chunk those numbers into cells B2-B7, we get the following goals: 2562 Health, 569 Attack, 226 Accuracy and 426 Evasion.

We run Solver, and get a solution that produces 333.6 (This number will be negative, based on how we built the sheet) adjusted points over overage using 2 Steady, 4 Skillful, and 2 Mercurial (It can produce a different solution, but it should produce the same adjusted overage). We then enter that on the top table, in cells G2-N7. Column O tells us what our fully iso'd stats will be, in this case, 9012 H / 7153 S / 2111 At / 1288 De / 1914 Ac / 2084 Ev.

Looking at that spread, I then look at her abilities. She has a passive where she will dodge things, so I think I can dial down the evasion by 1-2 crystals. Also, the accuracy is maybe 1 crystal higher than I like, which I can accept as she has deadly crits. If I replace one crystal worth of evasion, I can do a couple things. First, I can change a Mercurial to a Violent. I don't think that's a good solution, but it's workable. I could also go with a Steady or a Powerful. I like the Powerful, as it build more health and the most attack, while walking back a bit of accuracy.

Switching one Mercurial for one Powerful results in 9584 / 7153 / 2140 / 1288 / 1829 / 1999. I could live with that, but I feel like the evasion is still excessive, given her passive. Now, I know her passive kicks out after a couple rounds, but still, it's strong when I need it most, at the start of combat, so I can count on it a bit more. I think that second Mercurial would look better as a Violent. Doing that produces 9584 / 7153 / 2169 / 1288 / 1858 / 1914.

Those numbers translate as 5 bar + health, 5 bar and way beyond Attack, 5 bar accuracy, and 5 bar plus evasion. It's not the highest amount of surplus I could achieve (that was the original solution) but, given what she actually does, this build should produce better results than the highest amount of surplus. The accuracy at this level produces 96%/28% hit/crit on the Force Spheres and 94%/27% on the Volley.

I think that gives a good overview of the total process. Please let me or Bob know how you like our project and let us know if you need any help putting it together.

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.