Author Topic: FFVII Enemy Database & Calculator V1.1.0  (Read 814 times)

DynamixDJ

  • Crazy poster
  • *
  • Posts: 239
  • Karma: 22
  • 1111 1111 0111
    • View Profile
FFVII Enemy Database & Calculator V1.1.0
« on: 2019-02-17 21:17:25 »
FFVII Enemy Database & Calculator V 1.1.0

This database has been built using Microsoft Excel and acts as a multi-purpose search engine containing Enemy, Attack, Formation, Item, Materia and Field Map data all taken from the scene.bin, kernel.bin and flevel.lgp respectively. You will also find a fully working Damage Output Calculator, a Damage Overflow Calculator, and an 'Average Spoils Per Screen' Calculator.

Created for:
  • Casual players
  • Speedrunners
  • Modders (Database can be used as a canvas/template for designing a new mod)
  • Walkthrough/FAQ writers

Download here:

Normal Version: https://drive.google.com/open?id=0B8OpCQuDA-lKZkN5cVdYbXA1OFk (12.8 MB)
Pictures Version: https://drive.google.com/open?id=1dZKZvFfiosQPiSpAVRNJstbAUpglTdgZ (236 MB)

Both files are too large for Google Sheets to open, so you will need to download them to use them. The files are macro-free. Only download the Pictures version if you know your computer can handle the file. Additionally, you may experience compatibility issues if not using Microsoft Excel.

The Normal version and Pictures version both work the same; the only difference between the two is that the Pictures version contains embedded pictures of all Enemies, Items, Field Maps and Battle Backgrounds within the "Database" section, whereas the normal release includes hyperlinks to uploaded versions of the pictures.

DATABASE FEATURES:

Enemy:
Spoiler: show

Search via Enemy Name or ID to display all basic stats for the searched Enemy, along with Steals, Drops and Morphs; Elemental and Status attributes; and "Lookalikes". Also shows the Formation ID which the searched enemy appears in, and the Field Screens/World Map areas where you can encounter that enemy.

Lists detailed attack information for all 16 Attack slots for the searched enemy, as well as the three assigned Manipulate Attacks.

"Advanced Search" feature available, which allows for search criteria such as:
 - All Enemies that are Lv 24
 - All Enemies that have a Defence value of 60
 - All Enemies that take Double Damage from Fire








Attack:
Spoiler: show

Search via Attack Name or ID to display the basic stats for searched attack (MP Cost, Attack power etc.); Elemental and Status attributes; Target Attributes; Attack Formula used; and other info such as additional effects used, random variation on/off etc.

Lists all enemies in which the searched Attack can be found upon, as well as a base damage calculator in which one can use the Enemy ID to calculate the base damage value quickly.

"Advanced Search" feature available, which allows for search criteria such as:
 - All Attacks that have an MP cost of 30
 - All Attacks that use the Lightning element
 - All Attacks that use Attack Formula 26

A list of "Additional Effects" and their descriptions can be found by navigating past the Advanced Search feature.








Formation:
Spoiler: show

Search via Formation ID (decimal value) to display all stats for searched formation: Escape Counter; Battle Background used; Battle Type and Setup Flag used; and whether or not the Formation is used within the battle square.

Shows each enemy found within the Formation (3 max), along with the Qty of each enemy (6 total for the Formation), EXP, AP and gil earned, and all basic stats for those enemies. Also indicates the row positioning of the enemies within the Formation, the cover flag used, and the enemy flag used. Lists all Field Screens/World Map areas in which the searched Formation appears in.

Navigate past the Advanced Search feature for a description on "Setup Flags", "Battle Types", "Enemy Flags", how the Cover Flag works, and also a list of the Battle Backgrounds used (along with embedded pictures for the Battle Backgrounds).

"Advanced Search" feature available, which allows for search criteria such as:
 - All Formations that have an Escape Counter of 3
 - All Formations that are used in the Battle Arena
 - All Formations that use Battle Type 4 (Pincer Attack - Enemy attacks from both sides)






Item:
Spoiler: show

Look up all Items/Weapons/Armour/Accessories. Note that the search priority runs from left-to-right, meaning, if you search via item Name then you cannot search via Weapon Name until you have cleared the Item Name search criteria.

Shows all relevant stats pertaining to the type of Item you are searching for (weapon/armour etc.) - the result boxes will appear/disappear accordingly.

Lists all enemies that the searched Item can be stolen/dropped/morphed from, along with all Field Maps and Shops that you can find the Items in.

Shop look-up available, along with Key Items.

Five "Advanced Search" features are available, which allows for search criteria such as:
 - All Items that are not sell-able in shops
 - All Weapons that are not throw-able
 - All Armour that boosts the Magic stat
 - All Accessories that Defend against Petrify
 - All Shops that are Shop Type 6 (Accessory Shop)















Materia:
Spoiler: show

Search via Materia Name or ID to display all relevant information for that Materia. The effects of the "Modifier Types" vary from Materia to Materia; see the "Materia Modifier Notes" to clarify what those effects are.

Lists all Field Maps and Shops that hold searched Materia. Also shows info on the five abilities used by the Materia.

Enemy Skill look-up available, along with Command look-up.

Three "Advanced Search" features are available, which allows for search criteria such as:
 - All Materia that deplete Strength by -2
 - All Materia Attacks that are reflect-able
 - All Commands that cannot toggle multiple












Maps:
Spoiler: show

Search via Map ID, Name or Description to bring up a range of info for that map, including: Enemies and Enemy Skills learned on map; Formations used on map along with basic stats for each enemy; Enemy Drops, Steals and Morphs on map; Items, Materia and Key Items collected on map; and, Shops found on map.

Also lists Elemental strengths/weaknesses, Elements used by enemies, Status vulnerabilities, and, Statuses used by enemies.

















Map Average Calculator:
Spoiler: show

Search via Map ID, Name or Description to list all Formations that appear on that map, along with all basic stats for the enemies found within those Formations. An average is then calculated to display the average EXP, AP, gil, and all other stats for that map.

Note that the average taken can be considered a true average, seeing as the calculator takes into account the individual encounter rates for each formation, and also the way in which the game first considers the "Special Battle" chances, followed by the "Normal Battle" chances (big thanks to NFITC1 for help with the maths behind this).

The averages calculator can be modified using the appropriate Materia modifiers, such as Weapon Growth, Exp Plus Level, Gil Plus Level, Enemy Lure/Away Level, and, Pre-Emptive Level.









Damage Overflow Calculator:
Spoiler: show

First enter the player's Lv and Att, followed by the enemy's Def value (or use the enemy search field to find Def value) to make the calculator work. You can optionally enter the Hero Drink qty and Power value to calculate damage output to see if damage overflow has occurred at the Base Damage stage.

Regardless of the Hero Drink and power values entered, the calculator will take the player's Att, Lv and the Enemy's Def values to calculate the Power value required to cause overflow at the base damage stage (before any damage modifiers such as Critical come into play) for each qty of Hero Drinks. Only Barret and Vincent can overflow at the Base Damage stage, and their respective "kills needed" and "AP required" values are shown accordingly.

Continuing with the same Att, Lv & Def values, the calculator will also work out the Power value required to cause overflow at the Random Variation stage (after things like Back Damage and Critical have been considered) for each amount of Hero Drinks. All characters barring Aeris can overflow at the Random Variation stage.

Scroll to the "Useful Info" section to find the max Power value attainable for each character.

Lastly, a "reverse calculator" has been included which will calculate the maximum Def value you can overflow with based on the Power value and Hero Drinks used (and of course Lv & Att).













Damage Output Calculator:
Spoiler: show

The cells highlighted in bright red can be edited here, unlike the other sheets in this database.

If you are unfamiliar with the various formulas used, then you would benefit from scrolling down to "Formula Info" to familiarise yourself with the various battle formulas in-play. The most common physical attack formula is 11, and the most common magical attack is 22. Usually, the Power value is 16, which indicates 1x normal Power. If the Attack had a power value of 32, then it could be considered 2x normal power.

If you know the Power value and formula for the attack used then enter those values into the first search field; alternatively, you can search via Attack name or ID to find the Power and Formula used. Note that the unused search fields will grey out accordingly when you fill the search fields; to use the greyed out fields again clear the current search fields. Turning Morph on will divide the Power value by 8.

Secondly, you must either look up the actor's Lv and Att if the attack is Physical, or Magic Att if the Attack is Magical. Alternatively, search via Enemy Name or ID to find the Lv and Att/MAtt used. Hero Drinks will increase Att and MAtt by 30% and will stack up to a maximum of +100%.

Lastly, enter the target's Def if the attack is Physical, or MDef if Magical, or, you can search via Enemy Name or ID to find the Def/MDef values used. Hero Drinks increase Def and MDef by 30%, and Dragon Forces by 50%. Both will stack together, but will not exceed a total maximum of +100%.

Toggle the Elemental Attributes and Status modifiers to vary the damage output in a number of ways. If you want to activate the Weapon Modifiers, then make sure to be using a damage formula beginning with A (also known as AX; see the "Formula Info" section).

The last part of the calculator will work out the various hit chances. If you have selected an Attack/Actor/Target in the search fields above, then their relevant stats will be displayed here which can be then used to fill out the fields in green. Make sure that the "Attack Type" (Physical/Magical) and "Target" (Player/Enemy) are set correctly to see the relevant stats.







Database Section:
Spoiler: show

If you explore the "Enemy", "Item" or "Map" database sheets you will find hyperlinks to pictures of every Enemy, Item and Field Map. If you have downloaded the "Pictures" version, then the pics will be embedded within the notes for each cell; hover your mouse over the cells to bring up the pictures.

Generally speaking, you shouldn't need to mess around with the database section, unless you have the notion of using this database as a canvas for designing a new mod. In which case, only edit the cells highlighted in Green or Red (some cells will change colour from one to the other). If you do intend on modifying the Database, then it's worth hitting me up on my email, seeing as certain aspects have been designed in a way that isn't very self-explanatory.

The "Calculations" sheet is where all the background calculators and search engines do their thing. In honesty, It is a maze of formulas that is probably best to avoid, unless you're me :).


Credit to NFITC1 for the maths behind the Map Average Calculator.
Credit to Gurthawoe for help with the spreadsheet formulas.

Contact email - [email protected]
« Last Edit: 2019-03-14 09:45:06 by DynamixDJ »

DLPB

  • No life
  • *
  • Posts: 9553
  • Karma: 321
  • For I realized that God's a young man, too.
    • View Profile
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #1 on: 2019-02-18 20:53:42 »
An immense amount of work!  I commend you.

Bosola

  • Fire hazard!
  • No life
  • *
  • Posts: 1770
  • Karma: 15
    • View Profile
    • My YouTube Channel
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #2 on: 2019-02-19 11:31:51 »
Yeah, great work Dynamix. I would have absolutely loved this whilst developing Rebirth.

NFITC1

  • No life
  • *
  • Posts: 2897
  • Karma: 72
  • I just don't know what went wrong.
    • View Profile
    • WM/PrC Blog
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #3 on: 2019-02-19 12:18:34 »
This thing is absolutely amazing for what it is and I know you spent lots of time on it. It's a great tool for enemy designers to balance their creations.

I know you sent me drafts several times and I'm sorry I didn't get a chance to really try to break it. It looks fantastic! I'm not great with Excel so I'm not qualified to tell you if anything's wrong or how to fix it. Sorry. :)

DynamixDJ

  • Crazy poster
  • *
  • Posts: 239
  • Karma: 22
  • 1111 1111 0111
    • View Profile
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #4 on: 2019-02-19 15:43:11 »
Thanks guys, I'm already putting the sheet to good use lol :)

In all honesty NFI, I was trying to refrain from sending you a bucket load of updates on this thing; I was trying to wait till it was complete (complete-complete) before sending it your way, but the definition of "complete" kept changing and growing along with the spreadsheet lol.

Now to finally use the sheet as it was intended - to help me finish my walkthrough. I've already made a start on the Enemies & Encounters section, I just wish I had more of an idea as to the mapping of the vars within the enemy AI script..

As I'm designing my guide, I'm checking my work against the other FAQs on GFaqs, and ironing out the errors as I go along. There are bound to be a few mistakes, seeing as all of the info within the Database was copied out by eye, but, I will be releasing future updates once I know that most if not all of the errors have been removed from the spreadsheet (I've already noticed the Guard Scorpion's Dex was incorrectly assigned as 30 instead of 60).
« Last Edit: 2019-02-19 15:50:44 by DynamixDJ »

NFITC1

  • No life
  • *
  • Posts: 2897
  • Karma: 72
  • I just don't know what went wrong.
    • View Profile
    • WM/PrC Blog
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #5 on: 2019-02-19 18:25:18 »
In all honesty NFI, I was trying to refrain from sending you a bucket load of updates on this thing; I was trying to wait till it was complete (complete-complete) before sending it your way, but the definition of "complete" kept changing and growing along with the spreadsheet lol.

Do you even code, bruh? There is no "complete" complete. :) Apart from "final/definitive" versions, which is a developer's way of throwing up their hands and saying "I'm sick of this code and don't ever want to touch it again!", there will always be something that can be added/corrected/optimized.

DynamixDJ

  • Crazy poster
  • *
  • Posts: 239
  • Karma: 22
  • 1111 1111 0111
    • View Profile
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #6 on: 2019-02-19 19:41:54 »
Nah mate, but I've always wanted to look into learning how to code. DLPB was gonna help me create a "Chocobo Breeding Simulator", which is something that I still want to create, but I won't be looking into it any time soon.

And yeah, there will always be areas of improvement for this sheet lol. I'm gonna be adding an "AI Script Notes" section to the Enemy Sheet, which is gonna take a while lol...

codemann8

  • Cool newbie
  • *
  • Posts: 52
  • Karma: 3
    • View Profile
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #7 on: 2019-02-19 20:06:16 »
You would be a total force of nature if you ever got some coding under your belt. Great work on this!

DynamixDJ

  • Crazy poster
  • *
  • Posts: 239
  • Karma: 22
  • 1111 1111 0111
    • View Profile
Re: FFVII Enemy Database & Calculator V1.0.0
« Reply #8 on: 2019-03-14 09:44:48 »
Version 1.1.0 released:

- Corrected a number of errors in Enemy stats
- Added a number of Attack Properties to the Enemy's Attacks in the Enemy look-up sheet
- Added Materia and Limit Break info to the Attack look-up sheet
- Added info on Field Script flags to the Map look-up sheet
- Changed the Formation chances on the Average Calculator sheet to show as a %.
- Fixed an error with the formula that searches for every Map in which a Formation can be found
- Fixed an error on the Map sheet whereby it would not display every Status used by the enemies