FFXI Melee Damage spreadsheet v3.2 (update 7/13/2009)Follow

Just a note (leaving the post below for reference only):

The project has moved to google code. I'm no longer the main driving force (I don't have time). Use 4.0.3 for Excel 2003, the 4.1.0 works for 2007 only it seems, I even tried 2010 beta and couldn't get it to work (I don't have 2007 myself).

I removed the link below as its no longer checked.
==========================================================
Please go here to add gear to the template. Make sure you add it on a white back ground and preferably to the right, I will change it to blue after I add them before the next release.



Newest Version 3.2


Version History
v3.2

Fixed a bunch of bugs people identified below, no other changes.

v3.1

Most edits can be done on the compare page now, merits are there if you scroll down.

Added more WS, mobs, equipment.

It takes into account skill level and only should only add skill for the right one now, (ie +5 sword on suppa will only affect swords - DW still has to be manually done). Sea Gorgets should affect only the appropriate ws now.

Fixed an issue with acc. food from first setup affecting 2nd setup.

Double and Triple attacks are accounted for in Jumps now (and tp gain from said jumps).

Other minor stuff.

The sub weaponskill and ranged attack do not work yet.

v3.0

A bunch of small fixes, more than I can remember.

Added more WS, mobs, equipment, food.

Should auto calculate stats based on race, level, job and sub job.

Allows you to set 2nd set of gear for WS.

Most of the updates involve the above with the interface see the usage below.

Notes on v2.4 and older.


Usage
In this version 95%+ of your editing should be done on the Compare tab.

Most of the editing will be in column B and C. But any green fields are meant to be edited. A few other fields can be edited too, the Time (A2, in seconds). HP, MP, DEF, and Evasion. (although these fields do not matter except for HP for Souleater). Your weapson skill levels need to be edited (D12, J12), this is important. The extras columns (E and I) can be used for merits, blu spells, gear set bonuses, or testing augments.

The answers are in blue (L2-T3). K2 and K3 will list what is different between columns B and C (except WS gear is not included). The default example shows a very big list, if you're just testing one or 2 pieces of gear, food, etc... the list will be much smaller.

What you might need to edit that is not on the compare tab:
merits in the JA and Job Traits tab, I have some examples in there. Just go to level 75 and make sure the potency, recasts etc... are correct.

You can add more gear, mobs, food, ws etc... doing this may cause errors, recommended to replace in alphabetical order if you don't know how to adjust your ranges (there is some info on doing this in the previous thread though).

If you want gear added, please see the note in the top of the previous thread, make sure its on a white background, I will change it to blue after I add it to my copy of the spreadsheet.

Important note on the gear tabs: gear with specialized features such as the Suppa, adds 5 sword skill and DW reduction, if you're not using a sword go to the Ear tab and make sure skill level is blank (conversely if you are using a sword make sure it says 5), likewise for DW, if you're not using DW make sure that is blank!

Feel free to look through other tabs for reference curiosity or to check my work, if you find mistakes I'll be happy to fix them.

How accurate is this?
I think this should be pretty accurate, I don't know what the Fortifications stats are, I made them up I just put something in to fit the curve I was more interested in testing the DoT equations I came up with. I'm sure the fortifications have high defense, and somewhere around 80-90% damage reduction (reflected in the skew).

I was trying to get a nice parse to post and compare, the ratio of ws to melee hits are coming out correctly (main thing I was testing). However, my original data Kparse didn't track the time correctly and I didn't record it myself, my 2nd try my timing started coming out correct and then lots of people started beating on the same fortification as me and the lag was significant, so the data became invalid. But since my small sample validated the missing piece from my larger sample I've decided to just post my larger one and the sheet itself, rather than make everyone wait until whenever I can get a good sample.

So I fought a Fortification for about 15 min. (870 seconds), here is my parse:

Dmg  Melee % Hit/Miss M.Acc % M.Low/Hi M.Avg #Crit C.Low/Hi C.Avg Crit% 
5433   76.01 % 542/28 95.09 % 5/13 8.24 151 10/19 14.66 27.86 % 
  
Player    WSDmg  WSkill % Hit/Miss    WS.Acc % WS.Low/Hi WS.Avg 
Brimstone 1715   23.99 %  27/0 100.00 % 37/87     63.52 

total dmg: 7148

From the sheet you can download:

AvgHit	# Hits	# WS	Avg. WS	TotJA	TotMel	JAWS	WS Total DMG 
9         540	27	65	0	4860	0	1755	6615 

The underlined part was what I was trying to check.

Other things:
Typical WS TP 108%
WS Tp Return: 13.6%

From parse:
Avg WS TP: 105.2
Avg TP return: 12.1

I tracked these by hand with this macro, no gear swaps:
/echo TP=<tp>
/wait 1
/ws "Blade: Ku" <t>
/echo TP=<tp>
/clock on

Which means the numbers are doing what I intend them to do, I used fortifications because you don't stop swinging, and don't get hit or waste time casting. I'm also going to use this point out: yes its not the most realistic circumstance but if you're comparing gear you want ideal circumstances or else you're comparing circumstances and not gear.

Also note:
For DW its calculated in rounds and based off 1 skill level, if you're a Axe/Joytoy WAR you should probably adjust your skill level towards the sword more and half the DA rate of the joytoy. Its not perfect, but that will yield best results, similarly with other atypical circumstances.

Feel free to use this thread to ask questions point out issues (I prefer it over PMs since all can see it).

Edited, Mar 23rd 2009 5:07pm by BrimstoneFox

Edited, Mar 23rd 2009 5:09pm by BrimstoneFox

Edited, Apr 7th 2009 5:09pm by BrimstoneFox

Edited, Jul 13th 2009 10:19am by BrimstoneFox

Edited, Jan 29th 2010 10:29pm by BrimstoneFox

Thanks for the update! And go Kparser! I love that thing. This will really help me out along with Kparser as I level my DRK. :3

I don't have excel and I have no idea what quattro pro did to the data, but it's useless now.

I can fix the Acc in the next release (you can add it yourself now), originally before the WS sets were in I didn't want to do that before because its only valid on ws, but no one should use those except for ws now.

If you mean base attack in cell D13 its because that is from job traits only. Attack is calculated all over the place so I just picked that one, the 8 does get added in (B36 on the 'set' pages if you care), every time I've checked the final, its been right, but I haven't checked it extensively.

I can't comment on using it outside Excel, good luck if you try though.

Great Job again. Thank you for putting in all this time and effort. This is a very useful tool.

I found one minor bug so far: On the WS Set 1 tab, cell N9 was accidentally left blank. After I corrected it the formulas work fine with WAR or /WAR.

So first I want to say THANK YOU SO MUCH FOR MAKING THIS!!!!!

I recently started playing FFXI again after playing WoW for a bit and I was hoping someone would take on the crazy task of making a sheet such as this for FFXI. This sheet is awesome and I force all my friends to use it.

Now I want to do my part in contributing to this sheet so here are a few bugs I found.

Bug #1 - Bad Reference on the Compare Sheet
The Total WS Value C29 Reads

Q3='TP SET 2'!$I$54

It should be referencing the WS sheet

Q3= 'WS Set 2'!$I$54



Bug#2 - Bad Reference on WS SET 1

The Haste Parameter is being taken from the WS Gear

C29 = WSGearStats!S28

Should be taken from the TP gear

C29 = TP GearStats!S28



Bug#3 - Bad Equation for Accuracy on the Neck Piece of WS Set

This is due to the recent change of the Sea Gorget. I don't know exactly how the equation should work. I'm not very smart but I noticed that any neck piece that is not this special Gorget will give an acc of 0 even if it does have acc.

Example: Equip the Chivalrous Chain on both the WS and TP set. You will Notice in the TPGearStats it will read Neck Accuracy = 5. But on the WSGearStats it will read Neck Accuracy = 0.

Thank you guys for feedback on this, i'm helping Brimstone with this spreedsheet. And yep im mainly working on Setup1 Melee/Ranged, so i didnot expect a release already since setup 2 doesnot have the new changes yet.

As for next version, im working atm on a TP function based on probability to DA/TA or use of multi hit weapon/Jailer weapon -> should get an average TP/round and dmg/round lot more accurate, maybe.

Also, New dmg caps have been found, thks to Seedo @ MNK KI forum.

For Troper, ok for Bugs #1 & 3, but #2 is normal:
- Haste isnot used in any calculation for WS, so useless XD
- and even if it was used somewhere, should always be referenced to WSgearStats, since yu changed gears before readying WS.

Edited, Jun 1st 2009 1:30pm by Masamunai

Sorry, I have not had time to work on this the last couple of months.

I just got what will hopefully become v4.0 from Masamunai. Although I need to start looking at it.

I just corrected the mistakes people noticed in this thread and uploaded as v3.2 so there's at least something people can use in the meantime.

Thank you hellbringer for yur inputs.

1. The TP per hit calculation on TP Set 2 references the column B instead of column C on the TP Reference Table.

Answer: Indeed this version has different formulas than mine, which i completely reworked whole DoT formulas, including BaseTP/hit. So forget that for the moment, or you can fix it yurself :)

2. The number of WSs, TP per hit, etc. on the WS Set sheets seem to be calculated based on the WS Sets instead of the TP Sets (I didn't spend a ton of time tracking down the exact formula references)

Answer: while you right with # of WSs should be dependent of TPsets values, TP/hit calculated on WS sheets on contrary have to be calculated with WSsets. Why? Because those TP/hit are actually used for the WS TP returns, not for TPgain.

3. Zaide is a polearm, not a scythe ;) Answer: OOooops my bad lol, fixed.

4. Double Attack doesn't seem to be calculated into the first two hits of a weaponskill (Wasn't in 3.1 either, just thought I'd mention it).

Answer: excellent notice indeed, it's btw one of main reasons leading me to rework whole dot functions and by extension the WS formulas, so can include DA/TA, multihits/Jailers weapons and Zanshin. So for the moment, same as for 1st notice, DA not supported on WSs.

As for news of my current version, i got the DoT formulas working decently so far, just need to find a parser that can count the # of hits dealt on ONE type of mob... so far im using KParser to verify my results, but it only giving me total # hits, without distinction between (for example) Greater colibris lvl81 or 82... i don't know about Direct Parse.
Also, i have problems getting accurate dmg numbers for WSs based on a modified probability model used for DoT. I'm thinking about parsing Keen Edge, or any single hit WS, but needs time for that :(

So for the moment, don't hesitate to make suggestions, spot "weird" calculations, etc... Brimstone or me will gladly answer/fix.

{Have a nice day}
:D

Masamunai@Hades

ProjectXI could make good use of this information.

rofl lmfao Chaossamurai, nice inputs ok -still laughing- i'll look into those funny bugs... Please continue to spot such inconsistencies, its in beta stages.

For ProjectXI, honestly, i wouldnot look at formulas yet, there are still bugs around leading to rofl situations like chaossamurai pointed.

Edit: ummm... i just tested between DRG/war /sam and /magejob, got always /sam winning. Can you develop which settings yu used Chaossamurai please ?

Also, some things are still unsupported/unfinished like :
- some augments like delay reduction on Manoples/misericorde from ANNM, app will count it like a global delay reduction while actually its a special delay reduction only applying to weapon's delay, nothing more. Unsupported.
- relics' triple dmg unsupported;
- JA'ed WSs like SA/TA/WArriorAssassin charges/FlashyShot/etc..., unfinished.
- linking between JAs dmg and global dmg displays, like Jumps/Kick Attacks/etc..., unfinished.
- and surely some other things i forgot.

Edited, Aug 8th 2009 8:37am by Masamunai

Thank you for cheering me up, gives me more motivation to continue this ^^

Regarding PXI, i find it has a huge potential for figuring out correct dmg formulas :

Let's say yu go solo parse on FF11 Lesser Colibris just outside Whitegate for a significant sample size of hits/WSs for each level of Lcolis.

Then go on PXI, set yurself in invincible mode (take 0 dmg) to make things easier, same jobs/gears/merits etc... and solo those same Lcolis (known stats), parsing with KParser, for approx same sample size.

Compare the 2 parses, modify PXI dmg formulas, re-parse again, etc... till the 2 parses matches.

Profit.

This app is only based on a long & tedious search for infos on different forums/sites, themselves having their data from players who supposedly extensively researched. But actually i rarely found any precise testings with controlled data etc... PXI dont have these constraints.

Anyways, i'm doing with what i have XD.

On topic, i have a new version under hand, just waiting for brimstone to set me permissions on site.

Only think I've found is that cell N36 on the Compare sheet is referencing the wrong cell. (setup 2 job abilities). Outside of that, and my precious double attack in WSs lol, everything looks great. Keep up the good work!

Edit:
One more thing. Since the sheets are password protected on 4.0.1, I can't track down the actual problem, but if I take the sheet as it is downloaded and simply change the Setup 2 main job to DRG, everything weaponskill related in Setup 1 goes to hell. Same thing happens if (from a freshly opened spreadsheet) I change the Setup 1 main job (Setup 2 WS data goes to hell). Apparently some kind of crossover going on between the two setups.

Edited, Aug 11th 2009 4:46pm by hellbringerx