DPS Spreadsheets

[Motenten]

Bah. Fixed again.

[Vyvian]

On the MNK spreadsheet, and maybe other ones, I haven't looked, Manibozho Brais have the right stats. Rank 2 properly adds attack and accuracy. Rank 15 adds 10 STR and 10 Accuracy, but doesn't add the 15 augmented attack to the 15 it starts with (total 30).

[Motenten]

Fixed.

[noodles355]

Thanks for the help there on the Thf stuff. One last question

Edit:
Ah, wait. Found it.

[Langly]

Edit: I learnt something today.

Level can effect Hit Rate. That is all.

[Fireon]

Any plans on updating the Blu sheet to handle various ranks of Manibozho/delve gear? Also, I noticed there's no options for club weapons. Until I get a Buramenk'ah, I've taken to off-handing the Mondaha Cudgel, and while I could just input a new entry in the weapons category, the difference in skill is skewing my numbers. Would it be easy to just add a section for clubs?

Edit: Another thing I noticed. When I put a Whirlpool Mask in my TP set, I get an error code in the WS Damage section. Just thought I'd let you know.

[Motenten]

Updated augmented stuff for blu.
Adding an entire new weapon section (especially just for one weapon) is a bit of work. Might get to it later.
I don't see the Whirlpool Mask error. I'll need more specific information to track down the problem.

[Byrthnoth]

A few things about the magic damage sheet:
1) Seidr Cotehardie gives me a divide by 0 error for Stone 1 in the magic damage spreadsheet.
2) MP cost and damage are not getting floored before damage/mp is calculated. Water 1 gives me 530.6 damage/MP, but 440.4 damage with the set I'm using.
3) You may want to add a field for "number of targets" next to the -ga and -ra spells, as that will dramatically affect their damage/mp.
4) Vivid Strap is missing
5) It appears the Affinity casting time/recast has not been implemented yet

Seidr is by far the best option for Stone/Water nukes, in my opinion. Stone V jumps from 27.1 damage/MP (Bokwus rank 15 MAB) to 43.6 (Seidr), but the higher you go the less pronounced the gains are. Aero V goes from 20.4 to 27.3. Thunder V only goes from 14.3 to 16.7. Also, it drops your damage/nuke substantially. I will probably start farming Meebles again if I can, though, as it definitely seems like a useful piece of gear to have on layaway.

[Motenten]

1) That's normal. You've reduced cost to 0 or less, so damage per MP becomes infinite. I'll see if there's a nicer way to represent it. [Note: Changed it to an infinity symbol]
2) Since damage and cost are calculated after an averaging effect (Magical Critical Hits for damage, Conserve MP for MP cost), I don't floor them after that point. They -are- floored before that point, though.
3) That's specified on the Setup page, by "# of Targets (ga)".
4) Added.
5) If you mean it doesn't show up on the Gear page, that's intentional. There's no room to put it there (same for affinity and staff damage), so I do the lookup on the Data page.

Note: I've added the option for old vs new damage formulas, however you won't get correct data for the old formulas yet. They all depend on knowing the inflection point where M becomes M/2, and from running over the sample chart on the official forums, there is no simple, standard value in use. I've put completely bogus values in the tables just to have something to work with, but I have no idea if anyone will spend the time to find the correct values before this all gets changed.

[Motenten]

Actually, I think I've figured out at least some of the inflections. Also fixed another bug in the inflection difference calculations.

Given a spell of a certain damage tier (T1, T2, etc), and a certain element tier (ET1 = stone, ET2 = water, etc), the first inflection point L occurs at a dInt equal to the V of the next highest elemental tier spell. For Thunder, the next highest spell is Stone of the next damage tier. The second inflection point occurs at an additional dInt of 2x the first dInt.

So:

Stone:
V = 10
Max = 42

Water:
V = 16
Max = 66

Aero:
V = 25
Max = 92


For Stone:
L1 = 16
L2 = 16 + 2*16 = 48

For Water:
L1 = 25
L2 = 25 + 2*25 = 75

etc.


This formula matches the official table for all T1-T5 spells except Aero V. On that, predicted is 963, official is 967. That indicates an M of 2.333 instead of 2.299. Adjusting that.

However, since I can't actually verify that the pattern extends indefinitely, I'm capping all of them at 100 for L1.

-ga spells are -close- to ET(n+2), T(n) values, though not quite the same. Also, Aeroga is wildly off the predicted pattern. Putting in values that match official results.

A number of the -ga spells seem to cap at 99 rather than 100.

Stonega III also uses a lower tier M (Thundaga II is bumped up to 1.5, but Stonega III reverts to the 1.0 of Blizzaga II or earlier; probably a transposition error in SE's code), and has a very restrictive L1.

All -jas look like an M of 2.28, except Aeroja which has an M of 2.32.

As far as I can tell, all the inflection points work correctly now. Only caveat is that I can't verify if any spell has L1 inflections higher than 100. Used 100 as a max for all spells except ancient magic, which I arbitrarily set with L1 of 150.

[Raelia]

Totally borked for anyone else?

Can't make copies through Drive, can't open directly to sheets, saving and reuploading to own drive seems to break everything (or at least the DRK sheet)...

[Motenten]

Tried opening the magic spreadsheet, then opened in Google Sheets, and.. wow.. colors all messed up.

Weather/Day set 2 has conditional formatting active when the value isn't equal to IV1, when it should be checking cell F4. In fact, every single conditional formatting is comparing with cell IV1, plus a lot of cells that shouldn't have conditional formatting at all.

If I download a copy, Excel 2007 tells me it's corrupted and tries to fix it. However all the cells still refer to a conditional formatting referencing cell IV1.

If I download directly from the viewer (ie: didn't open in Google Sheets), then everything seems to be fine. The direct download is an Excel 2003 .xls file, while the Google Sheets download is an Excel 2007 .xlsx file. The original file appears to be fine, but Google's conversion of it is complete garbage.

[Motenten]

I tried converting the thf spreadsheet to .xslx in 2007 to see how it would work on Google Sheets, but opening it gave me this error:

Sorry, an error occurred while exporting your document. Details: The formulas on this sheet could not be calculated. Try removing some formulas or modifying complex formulas, like VLOOKUP and HLOOKUP, and recalculate. For more information on size and complexity limits in Google spreadsheets, visit the Google Docs Help Center.

Though... looks like I get that same error for the basic Thf spreadsheet, so that's not really a useful limit to consider. Given that I've had to trim down and simplify stuff even for Excel just to get things working, it'd be a real pain to further refactor the spreadsheet formulas. Not to mention, it doesn't tell me which ones are problematic, so it would just be guesswork.

Let's try the drk spreadsheet...

Ooo.. Right. Conditional formatting in 2007 is a real pain. Had forgotten about that. With 2003, I can do a conditional formatting check for any cell to the left of a given cell, and each one is considered unique. Berserk set 2 checks Berserk set 1. Last Resort set 2 checks Last Resort set 1.

However in 2007, every single one of those is considered the same check. Berserk 2 checks Berserk 1, but Last Resort 2 checks Berserk 1, and Hasso 2 checks Berserk 1, etc.

So after converting the 2003 to 2007, none of the conditional formatting is useful. Oddly enough, even though the formula is constructed the same way in Excel 2007, it actually works there; just not in Google Sheets.

I also tried rewriting the formula to be more generic; it doesn't reference a specific cell, but contains a formula that defines the cell to its left, so the common usage across all cells with the same formatting shouldn't break. However Google Sheets still messes that up.

Plus there seems to be lots of issues using .xlsx files in OpenOffice and LibreOffice, so moving to that doesn't seem to be a useful solution.


Current situation is unstable, and I'm not sure which way to go with things for now. If anyone has any recommendations, please post your ideas.

[Raelia]

I think it has something to do with Docs dumping support of old Microsoft formats (2003 and prior) on October 1st 2012. It could be a bug, though, that it's converting .xls to .xlsx and breaking everything instead of letting you have the clean original file...

Compatibility Pack?

[Motenten]

You -can- have the clean original file, if you download it from the Drive page, or from the Viewer page, before you open it in Google Sheets.

Limitations on Google Sheets:

Aside from other limits, a max of 40,000 cells that contain formulas. Each weaponskill table contains 11,220 cells with formulas. The thf spreadsheet has 6 weaponskill tables. Therefore, from that alone, the thf spreadsheet will not be usable with Google Sheets.

I downloaded the latest version of LibreOffice and saved a version of the spreadsheet as an .ods file to upload to Google Drive, however Google Sheets won't recognize that at all.

Attempting to open .ods files in Excel 2007 resulted in much breakage. It thought the file was corrupt, so did a file-level repair, but whatever it did lost lots of data. So, making use of .ods files is probably not a good idea.

I fixed an external reference error in the drk spreadsheet, plus revised a formula that Excel 2003 accepts but later versions don't, and tried again as an .xlsx file. That version seems to work normally on Google Sheets, except for the conditional formatting coloring on the Setup page, and a lack of a dropdown option for grips on the Gear page (which is a result of handling multiple weapon types).


It -looks- like things would work better if I switched to 2007's .xslx format. The only people who would have trouble would be those using Excel 2003, and not having either 2007+ or LibreOffice/OpenOffice. Since Libre/OpenOffice are free for anyone to use, that seems an artificial limitation.

The .xlsx file format also reduces the file sizes by about 65%, so helpful there, too.

Unless there are any notable objections, I'll start converting them to .xslx files shortly.

[Motenten]

All spreadsheets have been converted to Excel 2007 .xlsx format.

All spreadsheets have been updated to use formal fStr formula.

[Motenten]

Fixed up some att and acc issues on the war spreadsheet. Also made it easy to change to the new stat conversion ratios (change 'Old' to 'New' on the Data page if you want to try it out).

[Fake]

Sam sheet could do with mars's ring, dyanmic belt/+1 and letalis mantle adding next time you update.

[Motenten]

Added.

[Foldypaws]

Anyway to handle offhand sword on the THF spreadsheet? I've got a Halchuunic Sword and was wondering if it was better on old content, but I can't figure out how I'd enter that.