Author Topic: Drop Calculator on Google Docs - Final Edition  (Read 21246 times)

0 Members and 1 Guest are viewing this topic.

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #60 on: January 25, 2012, 08:45:40 PM »
I uploaded the alpha version I have to Google Docs if you guys want to look at it. I haven't really done any formatting yet, so it's all just plain text, but all the functionality should be 100%. Let me know if there's any glaring mistakes I missed.

https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdERQQ3FQaWJWd3dFT0R1djd5LXJkZEE&hl=en_US#gid=6
  • Because of the nature of this new version, it's not typo friendly. Unit names have to be entered one way and one way only. Spaces in the name are still irrelevant though. Huit is acceptable, I don't expect anyone to type Huitzilopochtli every time. Hollander in place of Hollander II, as well (technically, the Holly II is just a variant of the Hollander anyway). Mad Cat mk II is correct, Mad Cat mk 2 won't work anymore.
  • Most of the error checking is gone. Since I'd have to use IF(ISERROR()) for it to work with OpenOffice, that would require two VLOOKUP() functions in place of every one I have now. It's probably not worth halving performance just to make it look a little bit cleaner. Besides, #N/A is a more clear indication that you fapped something up. ;)
  • I'm going to integrate tonnage into the main calculators, and get rid of the separate tonnage calculators. I'll probably still keep the mini versions of the calculators.
  • I think I might also decouple the calculator and the asset reference sheets. Since I'm not drawing data from them anymore, I can put them in a separate document, so the calculator book isn't as cluttered. I'll keep a copy of the new "Lookup" sheet (or possibly pin it to the calculators) on the calculator though, and probably an ammo cost table along with it. Just the basics that you need for making drop configs.

Offline thEClaw

  • Star Captain
  • ***
  • Posts: 976
  • l33tp0intz: +75/-0
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #61 on: January 25, 2012, 09:59:22 PM »
Nice to see you are making good progress. Two things I would have to address:

1. The "lookup"-sheet needs to cover entries up to "F", the Novacat F has been around in the XMLs for quite a while and may suddenly appear in the game.

2. How about making Excel/OO/etc. autocomplete the names of vehicles somehow? The quick and dirty solution would be to use column B of the "dropcalc"-sheet to copy available names below the actual calculator (like I did in the sheet I uploaded some time). There probably is a more elegant way, but it sure saves a lot of work to have all the names autocompleted.

Other than that, everything looks pretty much perfect. Or at least it functions perfectly, as far as I can see. Very, very nice work. :)

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #62 on: January 25, 2012, 10:23:39 PM »
On #1: I'm not worried about that for now, but I'll add it to the lookup sheet anyway. There's no other F's in the game at this point, so it'd just be wasted real estate if I decide to pin it to the calculator.

On #2: Done, thanks for reminding me. Unfortunately it doesn't work that way in Excel or Google Docs. I haven't found a good way to make it work in Excel yet even, other than doing dropdown lists which I'm not a fan of.

Offline thEClaw

  • Star Captain
  • ***
  • Posts: 976
  • l33tp0intz: +75/-0
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #63 on: January 25, 2012, 10:32:24 PM »
There's no other F's in the game at this point, so it'd just be wasted real estate if I decide to pin it to the calculator.
There is the Hawkmoth F. And it is definitely part of 95% of all the drop-lists you could ever imagine.
:P ;)

On #2: Done, thanks for reminding me. Unfortunately it doesn't work that way in Excel or Google Docs.
I know it works like that in Excel. Maybe you tried something different? (I meant this ugly idea.)

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #64 on: January 25, 2012, 10:42:21 PM »
Ah, forgot about the Hawkmoth F, which is really stupid because there's no Hawkmoth D.

On autocomplete, yeah, that would work, but I'd need a full list of around 50 vehicles copied above every single mech name cell in the calculator. Maybe I'll mess with it though, it probably shouldn't impact performance too significantly. The thing about Excel though, I can't just put a single list somewhere in the B column like you did in your OpenOffice example (that was how you did it, right?).

Offline thEClaw

  • Star Captain
  • ***
  • Posts: 976
  • l33tp0intz: +75/-0
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #65 on: January 25, 2012, 11:07:21 PM »
On autocomplete, yeah, that would work, but I'd need a full list of around 50 vehicles copied above every single mech name cell in the calculator. Maybe I'll mess with it though, it probably shouldn't impact performance too significantly. The thing about Excel though, I can't just put a single list somewhere in the B column like you did in your OpenOffice example (that was how you did it, right?).
Not every cell, by far not. Excel and similar programs pull the autocomplete-suggestions out of the adjacent 2500 rows (or some value in that region) in the same column. For your current table that would mean you only need to put the names at one place. (maybe rows 500 and upwards so the user doesn't ever see them, or put them somewhere high up and hide them)
Just pull the data out of the B-column of the "droplist"-sheet, it doesn't really matter if they occur more than once.
Screenshot of the completion-process:


There seems to be another possibility using some kind of validator, but I never got it to work properly.

PS: I don't think you need to worry about performance with this kind of spreadsheet. Lookup-functions are probably very well optimized in Excel, it shouldn't matter if you try to find one entry in one thousand or one in a billion.

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #66 on: January 25, 2012, 11:42:44 PM »
Yeah it works that way in OpenOffice, maybe it even works in other versions of Excel, but in 2007 at least it doesn't. Both the spreadsheet you sent me (the one in your screenshot there, I presume), and in my current one set up in exactly the same way, it won't auto-complete anything unless the mech list is directly above the cell I'm entering stuff into.

Offline Az

  • MechWarrior
  • **
  • Posts: 274
  • l33tp0intz: +49/-0
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #67 on: January 26, 2012, 12:28:46 AM »
Hey, hey, stop being so diligent both of you. You're making me look lazy ::).

Here's an update of my file:

I removed the FreeTons, added the 'x's, took them off when I saw your edit, and added the APC manually.
I also removed the header line, it doesn't really look neat and it makes "Name Asset" a valid asset name...
Maybe you can pin it to the top and exclude it from the sort and the formulas?
I understand your concern about the headers, but I doesn't take a genius to figure out what the fields are. Anyway I simply replaced that line manually when I added the APC, it's still in the output.

Well, I guess I'm done for now?


Oh and I you're going to allow for a F variant, add G as well. Variants usually come in pairs (cf. Partisan). The Hawkmoth D is a running joke in the forum and will never make it in-game. Except in an easter egg poster in one of the maps, but I can't remember where.

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #68 on: January 26, 2012, 12:48:14 AM »
Yep, looks good. Thanks a lot man! Hopefully this script works for a least a few updates before it gets broken by one of the MWLL patches. :P

Offline Az

  • MechWarrior
  • **
  • Posts: 274
  • l33tp0intz: +49/-0
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #69 on: January 26, 2012, 01:33:34 AM »
It wont. The incoming DHS and chassis pricing changes will break it.

DHS were a special case already and the chassis will change not the values but the formula. Hopefully it will be straightforward and I'll be able to figure it out easily. I spent lots and lots of time on the armour pricing because of wonky floating point rounding...

The changes on the DHS may require me to change the way I'm parsing the heat-sinks, I might use this opportunity to retrieve the HS type even when there is no additional ones. I didn't mind adding a new output but I'm really reluctant to change the way the data is read and stored. I tried to keep things extremely generic, the next patch could add disco balls and holovid players and if they loosely follow the current template, they'll be picked up by the script. That very same flexibility prevents me to precisely find what I need in the data I gathered. It's more idealogical than pragmatical but if I can help hard-coding any assumption in the parser, I will. But I might have no choice with the heatsink technology, and have to store it separately.

I'm long overdue for a rewrite anyway, my script is full of old, unused or redundant code and I can barely reread it. Which is partly why it took me so long to get anything done. It will be a long and difficult job for me however, and unless I really am in a coding mood I'll probably settle for a quick 0.5.5 fix.

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #70 on: January 26, 2012, 01:57:59 AM »
Gah, that blows. Well at least my calculator can keep some minimum functionality with just the prices dump, hopefully it won't take more than a week for your or somebody else's script to get the rest of the data.

Offline thEClaw

  • Star Captain
  • ***
  • Posts: 976
  • l33tp0intz: +75/-0
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #71 on: January 26, 2012, 08:30:54 AM »
Maybe take a look at this old thing: Excel autocompletion, and maybe take a look into the help.
more on autocompletion with Excel

If it doesn't work right now, there hopefully will be some way to make it work. I myself wouldn't mind a combobox combined with an autocompletion-system, but I don't think that's possible.

I'll just reinstall Office 2007, I only switched a couple of years ago because I didn't see why not rather use and support something free instead.

EDIT: Ok, the completion behaves different from what I knew from OpenOffice. Seems like you have to put the data directly above the table, with no empty lines separating it from the table itself. That way it works for me (for all cells in that column, so there is no need to paste the data several times).

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - Now with tonnage!
« Reply #72 on: January 27, 2012, 06:09:39 AM »
Alrighty, got the more or less complete calculator uploaded now: https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdERQQ3FQaWJWd3dFT0R1djd5LXJkZEE&hl=en_US#gid=0

I set up the autocomplete the way you suggested, thEClaw, and it works in Excel just fine. Oddly, it also works in Google Docs, but only on the "Lookup" sheet. I'll have to figure out how to make it work on all of them, because that would be a great feature to get in there.

Offline thEClaw

  • Star Captain
  • ***
  • Posts: 976
  • l33tp0intz: +75/-0
Re: Drop Calculator on Google Docs - 0.5.4 Edition
« Reply #73 on: January 27, 2012, 09:18:56 AM »
Finally! ;)

Thanks a lot for your work and effort! (And the same obviously goes for Az, the helping hand when it comes to needing random game-data. ;) )

EDIT:
May I suggest something? Would you add another invisible tab including short information about how to update the calculator? Just in case you ever decide to not keep it up-to-date. Maybe add a link to the forum-post including the script and things like that - just basic information so it isn't too much of a problem for somebody else to update the calculator.

And something else: I have played with one particular clan who used this to create his drop-lists. After a while they had come up with a good amount of drop-lists and started copying the whole file over and over to make room for all the created lists. That is not really a bad thing, but maybe there is a more elegant solution? More tabs? More tables inside the tabs? Something even better?

Last thing: Thanks for mentioning me in the "credits". :)

Offline Snyp3r

  • Recruit
  • *
  • Posts: 169
  • l33tp0intz: +28/-0
  • Boom! Legshot!
Re: Drop Calculator on Google Docs - 0.5.4 Edition
« Reply #74 on: January 27, 2012, 07:21:57 PM »
Yeah, good ideas, I'll get to work on those.

I got auto-complete working in Google Docs now, too. For some reason I think having that one big merged cell (the one that says C-Bill calculator - x players) at the top of the calculator was throwing it off even though it was okay in Excel. I moved the hidden rows with the mech names to just below that merged cell, and now it works in both.

Edit: Updated the calculator again. 8 calculators per sheet now. I was going to have 16, but with that many, Google Docs really started to chug when trying to scroll the page on my netbook. I think 8 per sheet is enough though, by the time you've filled up a full screen of tabs, I think you'd have more than 100 calculators. I also finished the reference sheets, which are in a separate document here: https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdGtldHMxOXlLSWNWWkVvNjZ1c3lPdmc
« Last Edit: January 27, 2012, 09:46:50 PM by =KH= Snyp3r »