Excel form help

[Enuch]

Okay so not 100% off topic but still not so on point that I would post this in the Rogue thread, or a lockpicking thread for that matter. I am curious about trying to compile some data as it pertains to lockpicking ranges for boxes from given creatures. From what I understand and have heard through my years of on and off playing it is to some extent random (or at least an inaccurate science). This may have been done before and if it I will gladly bow out, otherwise if it hasn't I would like to make a 6 month - year long study, or even do a continuous update for an excel sheet for creatures I hunt and pick, as well as any brave soul who wishes to offer their inputs.

The only problem is I am about as tech saavy as dog poop. And for this task I would at least need to know how to find the ranges, and the mode, and the medians (god if I even picked the right terms). If someone has an idea of how to put these together and is willing to provide some logic bombs on me, I am thinking I will give this a go. I am starting LM soon and since I will be box farming things my level in general it gives me something to do, plus if others seem even 10% excited to provide their insights, I may just turn all my gaming attention to farting around with this as it may be a cool achievement (or months of completely wasted time with no true answer which is even better!).


So to reiterate, has this been attempted on this kind of a timeline before to attempt to get decent enough numbers (I'm thinking if I can 200-500 boxes per critter should give me a pretty good idea of what kind of ranges on average one would work with, while also discovering modes, medians, and outliers). If not, would anyone be interested in also suppling me their daily or weekly boxes picked from where-ever? and most importantly how the bananas would I set this up in Excel! Any thoughts are appreciated, please reserve all clubbing and trolling to negative rep only!

[Tgo01]

Wish I could but I don't know a whole lot about excel.

[Whirlin]

I'm in the top 200 contributors over at Excelforum.

I don't understand requirements... It's my understanding, while I haven't been a lockpicker, that the equations have been solved for maximum contribution from lockpick type, potential bonuses, etc... I'm not sure what is left that would require an excel to solve. Additionally, I'm unsure why a form would be the best implementation of data display... They're rather cumbersome, and require macros to be enabled to run, whereas the same functionality can be delivered upon a sleek looking sheet, macro-less, and faster to implement.

[Enuch]

And I am naive to what is the best type of database medium to utilize. I am not trying to find out what lockpick is best for what etc. I am curious if someone has ever made something similar to a beastiary for lockpicking -- I.E. Swamp Trolls -- Box pick Average -150, range -40-220, outlier -290. That type of thing. If its been done then by all means ignore me, but I thought something like this could be handy in the future if in fact the data shows some kind of trend.

[Enuch]

excel probably is not what I need then, I just need something that can hold like 200+ lockpicked box numbers, and then find the range, median, mode for the given numbers obtained. It could be a fool's errand I was just curious is all.

[Latrinsorm]

Excel is perfect for what you want. I don't know the lockpicking messaging specifically but it has to be standardized as it's a GemStone output, so paste a few examples and I can even give you a formula to parse the data directly from logs for you, which beats having to manually type in every box's information.

For the analysis part, range is in general a bad way to analyze a broad distribution, especially if there are open rolls involved. I've used average and standard deviation to crack distributions in the past and it was pretty efficient. I got a 20d20 + 400 with only 14,000 data points, to try and do that with range would have taken trillions of trillions of data points. I don't think anything in GS uses that many dice, but you get the idea.

Good luck!

[Enuch]

holy moly thats intense! Once I start cracking open a couple boxes I will send you a PM and maybe you can help me from there. I hadn't even thought about that, I am just more trying to determine what levels the boxes vary at and less my ability to pick them but I had not even thought about all that stuff (I type reports *not technical or involved in any facet haha* for a living and barely scrapped through anything involving numbers).

[Whirlin]

Excel is perfect for what you want. I don't know the lockpicking messaging specifically but it has to be standardized as it's a GemStone output, so paste a few examples and I can even give you a formula to parse the data directly from logs for you, which beats having to manually type in every box's information.

For the analysis part, range is in general a bad way to analyze a broad distribution, especially if there are open rolls involved. I've used average and standard deviation to crack distributions in the past and it was pretty efficient. I got a 20d20 + 400 with only 14,000 data points, to try and do that with range would have taken trillions of trillions of data points. I don't think anything in GS uses that many dice, but you get the idea.

Good luck!

The problem with standard GS output is that it doesn't include the information requested... When you get your hands on a box... you have no idea what dropped it, where it came from, what level the critter was, etc... Hell, there are some critters that even have some level variance when they spawn. So, that data would need to be manually enriched in addition to the logs generated for the lock/trap evaluations.

[Enuch]

Ah so essentially a fruitless endeavor then?

[Latrinsorm]

The problem with standard GS output is that it doesn't include the information requested... When you get your hands on a box... you have no idea what dropped it, where it came from, what level the critter was, etc... Hell, there are some critters that even have some level variance when they spawn. So, that data would need to be manually enriched in addition to the logs generated for the lock/trap evaluations.

Shouldn't be that bad for personal use, no? If it's all in the same log file you could just look up what creature dropped it, and unless you're hunting multiple creatures that happen to match condition material and noun on their box drop you'd be okay. What are the chances of that, (1/5) * (1/6) * (1/5)? I like those odds. My understanding also was that we're looking to map boxes per-creature anyway, so that some have level variance isn't a big deal either.

Ah so essentially a fruitless endeavor then?

Automating the data entry may not be as straightforward as I said, but the fundamental project is definitely doable.