A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | Welcome to the Data Entry sheet for Barry Farmz Here | ||||||
2 | You can find me in game, or in the forums as Barry Wuz Here; My Line ID: barrywuzhere | ||||||
3 | If you are here to enter your own farming runs directly, go to the sheet "Data Entry Instr." | ||||||
4 | If you are here as part of the "Nth Loot Project" Line chat groups, and ready to enter data from them, go to the "Data Monkey Instr." Sheet. | ||||||
5 | If you made it here by accident, and just wanted to know where you should be farming, you will find what you are looking for in the following sheet; head on over there and you should find what you are looking for. | ||||||
6 | Barry Farmz Here: Summary | https://docs.google.com/spreadsheets/d/1rUwYED0cStHnJFINMhCaRgBq6Db1KO3DIjlMf-iKqaY/edit?usp=sharing | |||||
7 | If you ended up with a copy of this sheet somehow, here's the master / live version of my data entry sheet that you should be using instead: | https://docs.google.com/spreadsheets/d/1cs7Qrinu8gSDugy0traCSKj9MaQRAbmwjcDA43SRKc0/edit?usp=sharing | |||||
8 | If none of the above apply to you will find a bunch of my notes explaining how and what I did in this sheet / project, and you might want to keep reading. Be warned, I didn't edit that part for general readability, just for my own purposes. | ||||||
9 | |||||||
10 | This sheet has different tab color coding. Instructions are green. Blue are data entry sheets. Orange are calculated/generated sheets. Purple is are my master controls output sheets I'll use. Data tabs imported from the master sheet are a lighter blue. | ||||||
11 | |||||||
12 | Master sheet to import data from: | https://docs.google.com/spreadsheets/d/1CjZpzVYwbCN4jgEksYRnfymUpFQ9agqDkOqKQFxalKQ/edit?usp=sharing | |||||
13 | |||||||
14 | Switches status for this sheet: | Generate Data Consolidation | Data Aggregation on Hold | ||||
15 | Because I hate trying to keep information in multiple places up to date, in this project I make fairly heavy use of Google Sheet's @importrange() function. Below this row are notes imported from the README tab of the main sheet. Any notes specific to this sheet, not the whole project will be in rows added above this one. (FYI, the text imports with that function, but I don't know of any way to import the formatting that way, so I had to copy and paste the formatting. If I make changes, I'll probably forget to update it, and it will probably look a little weird, and the description of what color cells are may not be exactly accurate, etc. | ||||||
16 | (This row is the start of the importable/replicated part of the readme tab that makes it into other sheets in the family) | I will write something more informative here. Someday. Really. As an introduction; there's a lot of crap I've already written....probably too much but perhaps not all the right stuff. | |||||
17 | |||||||
18 | Barry Farmz Here is actually an extended family Google Sheets, not just one. Here are the public/published family members: | ||||||
19 | Barry Farmz Here: Master | https://docs.google.com/spreadsheets/d/1CjZpzVYwbCN4jgEksYRnfymUpFQ9agqDkOqKQFxalKQ/edit?usp=sharing | This is the master spreadsheet, and it does most of the calculations. Wherever possible, data here gets imported by other sheets in this project. Including this section of notes in the readme tab! | ||||
20 | Barry Farmz Here: Data Entry | https://docs.google.com/spreadsheets/d/1cs7Qrinu8gSDugy0traCSKj9MaQRAbmwjcDA43SRKc0/edit?usp=sharing | It is pretty well polished up and in live use now. | ||||
21 | Barry Farmz Here: Summary | https://docs.google.com/spreadsheets/d/1rUwYED0cStHnJFINMhCaRgBq6Db1KO3DIjlMf-iKqaY/edit?usp=sharing | http://bit.ly/2BTkap6 | ||||
22 | The "Family" has a few cousins that are useful, but I don't care to share with the public. I'm not saying don't look at them, but you've been warned that they are for some kind of relatively specialized purpose, and most people won't be interested in them, and likely won't make heads or tails of them either. | ||||||
23 | BFH: S3 | https://docs.google.com/spreadsheets/d/1r0n96sq0m4Dnu3gc63qI28fZVj6G7x2HjIMm1PDwLK0/edit?usp=sharing | I'm not (yet) ready to add the levels and provinces of S3 to my main sheet, as I don't want to deal with breaking it. So I made a copy of the main sheet and expanded it. I imagine I'll fold all this good stuff back into the master sheet someday, or just make it the new master sheet. | ||||
24 | BFH: Atlantis Rising re-calc | https://docs.google.com/spreadsheets/d/14sYfEjZoFx19JJvgvRrWCbhcdTf14BpMg09BYYdgN5E/edit?usp=sharing | Takes all the "normal" data and adjusts it to match what will (or at least should as I understand it!) happen during the Atlantis Arising farming boost conditions. | ||||
25 | BFH: Chests and Missions | https://docs.google.com/spreadsheets/d/1JYgKwUxiOX1ieK_SC1O31YqIMeTew15d1mGw5MxDK1k/edit?usp=sharing | Calculates WE needed to fill monster / elemental chests per level, and ranks levels for each mission. Does a quick-and-dirty version for Atlantis Rises, and also now has S2 Mission monster counting included as well. The results make it onto the summary sheet. | ||||
26 | BFH: Custom Calculations | https://docs.google.com/spreadsheets/d/1dWEFD14Ojqy4uGa554rVBoW7WLea6fRfIm1HjcZ57AM/edit?usp=sharing | (Note: I've always intended to do this, but nobody has really used it that I know besides myself and it is out of date enough to be broken 99% of the time) This imports pre-calculated data from the main sheet, as a starting point for custom calculations. I'll try to keep it up to date enough to work. It pulls in Levels, Provinces, Items, Level Counts, and Province Counts. The Level Stats, Province Stats, and summary pages are replicated from the main sheet. | ||||
27 | BFH: More Statistics | https://drive.google.com/open?id=1d4RsTGFsoFuVx3zILP8CCsq1cOQXlspZ2szl6Fhiy4w | This sheet imports the synthetic data from the master sheet and performs some statistical tests to measure how well the synthetic data matches the collected data, and other random analysis I did to figure out how things worked, along with a some graphs. NOTE: All the actual synthetic data calculations are done outside this sheet. | ||||
28 | BFH: Farming XP Calculations | https://docs.google.com/spreadsheets/d/1xWvriGNxb85uLe2reMXt8WHHfyImLgavdSJSLcCNMmo/edit?usp=sharing | Work in progress, goal is to calculate daily feeder XP is generated from any level after you run your training camps in any of a bunch of different possible configurations. | ||||
29 | BFH: Manual Data Aggregation | This is the master archive for all raw, non-consolidated data, and I do a bit of sorting and copy/paste-values to make full lists of data sorted by season or filtering out multiple runs as needed. I tried to make sheets that would automatically generate this, but they kept blowing up instead of calculating successfully so this one is my current/active version. | |||||
30 | |||||||
31 | Spreadsheet conventions and general organizational notes; if you are trying to figure out how I made this spreadsheet work, this is a good place to start reading. If you just want to know where to farm or are here to do data entry, this is a good place to stop! | ||||||
32 | Expanding functions like importrange(), transpose(), filter(), etc. are color coded Magenta like this cell should be. I do this because they "break" if you put any data or forumla in the area they are expanding into, which is easy to do accidentally, even if you do know where they are. | ||||||
33 | They often drive a lot of computation/calculation, so I've used "Switches" that disable/enable these functions are color coded red/green, using conditional formatting and data validation, like this: | On | Off | ||||
34 | Master sheet live switch status: | Consolidation on Hold | Note: I use other copies of this sheet for consolidation in my current process. I'm 90% sure this version is kinda broken. Look in the Import / Data Entry versions for a working consolidator. | ||||
35 | There are a lot of sheets here, and I've tried to color code them. In some sheets in this project, there are tabs that are imported from another sheet, so that I don't have to keep data up-to-date in two different places which generally drives me nuts. If a sheet is locked, it probably is one that only has formatting and an inputrange() function to do that. (If I'm smart I might hide some of those sheets) | ||||||
36 | Blue tabs are where data that gets used elsewhere is entered. | ||||||
37 | Data (Live) is the beginning, with farming data entered here. If you are a data monkey, this is what you are looking for. | ||||||
38 | Levels, Provinces, and Items contain data which doesn't change much, other than the need to put in things like level XP when new provinces open up. | ||||||
39 | Green tabs are output summaries / reports, intended to be readable/useful. | ||||||
40 | Orange tabs are pure calculations, with (very limited) empahsis on formatting, readability and reducing visual clutter. (I used to have some in purple too; I confuse myself that way) | ||||||
41 | Level Counts adds up all the data for each level, and has a little bit of filtering; Synthetic Counts is a drop-in replacement sheet with synthetic numbers instead of raw numbers. | ||||||
42 | Aggregated Levels generates province-wide averages from the level data in Level Counts, plus a bunch of other aggregation categories because I discovered that they were easy to calculate. Easily half of them aren't particularly useful, but I left them there as a way to check if there were statistically valid groupings among them. Synth Rates uses the (most useful) aggregated categories to generate better long-term farming rates for each level, and Synth Sort is a way of figuring out which level I want to use as a "poster child" when there are a bunch of levels that are tied for best in a particular item. Level Stats does a second pass of filtering, and a few more calcs and pulls in results from Level Counts. (It has more-or-less been superceded by Aggregated Levels / Synth Rates, but I'm keeping it around since I had some output/summary sheets that pull from it) | ||||||
43 | Summary and Level Summary pull in results (without the rest of the crap) and format them to display/publish. | ||||||
44 | Data Considation (and its output sheet) I use to turn all the data from a level into (effectively) a pair of loot ticket runs, one with monster counts, one without, to cut down on the number of calculations needed. At the same time, it replicates any levels which fail the error checking and didn't make it into the count, so they can be fixed later or continue to be ignored....but aren't just "lost". (They are purple) Those sheets aren't used and might not be up to date in this version. The ones in the Data Entry sheet and the Import Consolidation sheet are used regularly and kept up to date. | ||||||
45 | |||||||
46 | Archive and working import sheets: | These sheets are ones that I use myself as part of my data import process, which (so far) I haven't involved anybody else in. How the process works is that I have a funnel of sheets that import datasets and generate consolidated datasets ... and between sheets what I do is a copy-paste-values to make a sheet with that (date stamped) dataset, which then goes into the next stage. Consolidated data gets funneled into the master SS; full non-consolidated data gets sent into the separate data aggregation SS, to be used for any analysis that cannot operate on consolidated data. | |||||
47 | Dataset Consolidation archive: | https://docs.google.com/spreadsheets/d/1FgVByIVkJo3E7AvuookjPy-i7ZyF-KpL5wGAIVXx_ns/edit?usp=sharing | This version has all my imported datasets as separate sheets. The main function is to make a single merged consolidated dataset from the (already consolidated) individual project datasets. I'm thinking of taking the non-consolidated sheets out of it. | ||||
48 | |||||||
49 | Import Beirmeister's Data | https://docs.google.com/spreadsheets/d/1h46Ddkgte4Od4nejgazJviYABMGb74lQeHBAkZJEaSA/edit?usp=sharing | |||||
50 | Import Pois1's Data | https://docs.google.com/spreadsheets/d/1-cw5kw15cL39cqbo4ozQPK8w5u9y3CV-GOqW0lv9BNs/edit?usp=sharing | |||||
51 | Import Jakob's Data | https://docs.google.com/spreadsheets/d/1sL0rK_p4QRzaw8vr9ct45WDLg8Ak2Ys2qP9u7xHykrU/edit?usp=sharing | |||||
52 | Jakob's export sheet (target for above) | https://docs.google.com/spreadsheets/d/1TMMjBYghfHc5VMiF96_X0rOFB7GZGMCLYdHXGNP5jes/edit?usp=sharing | |||||
53 | My Copy of HDM, as I imported it on 1/19/19 | https://docs.google.com/spreadsheets/d/1-kujlseH7NXOZhe2RdRi74mPOrpBppPJiHuhDywKR90/edit?usp=sharing | |||||
54 | Import HDM, Single row levels that might be problem children....Still need to do that one! | https://docs.google.com/spreadsheets/d/1YNjnDpdmWMmLs2pfwOHPfGfVytGxDbFoAXOXZlhfGCI/edit?usp=sharing | |||||
55 | Import HDM, S1 | https://docs.google.com/spreadsheets/d/1neoxUKKMlKZ4q5dAU863-X9N2fp4zNxYibspa7fgz4w/edit?usp=sharing | |||||
56 | Import HDM, S2 Normal | https://docs.google.com/spreadsheets/d/1pJ2ztxnwqmYSlNaYdpbk9to-2KId3LA6v6dloe4Oqxk/edit?usp=sharing | |||||
57 | Import HDM, S2 Hard | https://docs.google.com/spreadsheets/d/1CRqkyWsudQlSQ9jvSx85dfhfrKYrJoatFsHw93j71ps/edit?usp=sharing | |||||
58 | Work in progress calculation sheets | These are various sheets that I started on and either they haven't been finished enough for prime time (as in no results are in my main summary sheet yet), or perhaps they've been totally abandoned. | |||||
59 | BFH: Big Data Calcs (Static Copy) | ||||||
60 | |||||||
61 | |||||||
62 | |||||||
63 | |||||||
64 | Extra (Related) Google Sheets that I use less often; if they are mine, they are probably out of date if I haven't needed them lately. | ||||||
65 | HDM's Farming Guide: | https://docs.google.com/spreadsheets/d/1K3R9JPh-czACu5tiEsLGLL_BLeY8WtJMaoltQTeKtLQ/edit?usp=sharing | |||||
66 | CJM's E&P Data | https://docs.google.com/spreadsheets/d/19fcmMLrBiXLt0rhnUXE68ONZIPorWS5qUXdKCm-0br4/edit?usp=sharing | |||||
67 | |||||||
68 | I'm a horrible data packrat and I saved a bunch of things at various points during this project. I doubt any of these are useful anymore, but I'm keeping links here in case I need them for some stupid reason. Or maybe I went housecleaning and deleted them all, and the links won't work. Feel free to look for them, but don't expect anything useful or usable. You've been warned! (Note: my working name for this project early on was "One Sheet to Rule them All" which seemed funny at the time but was pretty unwieldy! | ||||||
69 | My out of date mutli-sheet import | https://docs.google.com/spreadsheets/d/1WAI1DBrg7e8n5bHFLKyZciE49fsvyxQIthpzKl8-ngw/edit?usp=sharing | |||||
70 | My Copy of HDM, before Provinces 12-15 were added; I was using this dataset when I stopped working on the import sheets; they are probably semi-broken unless re-pointed at this one, or fixed to deal with more levels. | https://drive.google.com/open?id=1sxrtlrri_PfdPt2bdyDc_w9odAHMt8YkE3n8Q5Jg2n4 | |||||
71 | Archived copy of what I went live with and opened up for data entry. (at least that is what it is supposed to be! | https://docs.google.com/spreadsheets/d/1n_EOTlx4Cj6PuY_WnBr97MEMfqewwxRIgKzvq6qkANs/edit?usp=sharing | |||||
72 | Archived copy before my final import, "just in case" | https://docs.google.com/spreadsheets/d/1YCtkXShWGGA5UP21VCUJOu6outCof-EtFaza6EQv5CI/edit?usp=sharing | |||||
73 | One Sheet to Archive them All (V2) Live data is limited to HDM data. | https://docs.google.com/spreadsheets/d/1n7ta83HBAJ1H_kfTJ7EulC0vJ8yk2xHFfIsWbk7WPWU/edit?usp=sharing | |||||
74 | One Sheet to Archive them All (Old version) | https://docs.google.com/spreadsheets/d/1RuV5Lc1xGxCbo-QuwBW95VCkPtSPDf6Two1Qi51Q7Eo/edit?usp=sharing | |||||
75 | BFH: Synthetic Farming Calculations (Old version) | https://docs.google.com/spreadsheets/d/1kem4kG8WS3N8pdYq3bCQePRNEQG7Qx0GRwxYsnoDrC0/edit?usp=sharing | Taking the data from the farming project and using patterns I've seen in how loot drops, so I can create better farming drop rates than what I have from (in some cases) very limited samples. | ||||
76 | Another graphs thingie I built. | https://docs.google.com/spreadsheets/d/1ERm_F00dx8U_C1vdKhOGV9yfO13U0Ch0RIwkZBJAy3g/edit?usp=sharing | |||||
77 | BFH: Graphs & Calcs | https://docs.google.com/spreadsheets/d/1QKOUs36WDPVSDh5CXIm1I-zt_RAf3JD28iuQ4HKxF_s/edit?usp=sharing | Working name; version for Beir's graphs :) | ||||
78 | BFH: Full Dataset Aggregation | https://docs.google.com/spreadsheets/d/1eL9rs6xm6mdcmV23vPCxvfm7KVECzpj0pal9RqJYCvU/edit?usp=sharing | (Now depricated/ignored; it is too big and sheets blows up instead of finishing re-calulations too often) This version collects all the various datasets into one monster, it can handle 18k rows right now. It currently does some calculations. I mostly gave up and split it into an aggregation sheet and a calculation sheet instead. | ||||
79 | BFH: Big Data Aggregation (No Calcs) | https://docs.google.com/spreadsheets/d/1BH5q6Dk_YkffHQWdDcpk6w9d-rK7xV3Dr4Bznm7bl1I/edit?usp=sharing | (Now depricated/ignored; it is too big and sheets blows up instead of finishing re-calulations too often) This is the master archive for all raw, non-consolidated data, and it generates a single flat sheet with all of it in one place from all the various individual sub-sets of data. Unfortunately, it seems to be big enough to make google sheets fail half the time. | ||||
80 | BFH: S2 Mission Calcluations (Old, not kept up to date) | https://docs.google.com/spreadsheets/d/1igg2oXbuOGNONEJeQBUealc7po444saYksYbRKAwTIQ/edit?usp=sharing | Depricated, no longer updated -- Calculates WE needed to complete all the S2 missions per level, and ranks levels for each mission. (Doesn't take into account that the mob monsters in the boss level are fixed/known instead of 50/50 random. (I'm going to move this into the archived shit I don't use category sometime, but I referenced the sheet link in this cell somewhere and I don't want to bother finding that and breaking it just now) | ||||
81 | |||||||
82 | |||||||
83 | |||||||
84 | |||||||
85 | |||||||
86 | |||||||
87 | |||||||
88 | |||||||
89 | |||||||
90 | |||||||
91 | |||||||
92 | |||||||
93 | |||||||
94 | |||||||
95 | =importrange() sheet list. (Adding rows between this and row 10 will break all the sheets that use this list. Don't! If you move the rows around in this range, that's much safer... | Sheet URL if not master (Be smart, point it at the cell in the list of sheets way up top!) | Range | ||||
96 | Level metadata (Code, Name, WE, XP, Province, etc.) A bunch of stuff specific to missions and the BFF stuff is now depricated and might or might not be removed yet. | Levels!1:730 | |||||
97 | Province metadata sheet | Provinces!A:BA | Old Synth Data | Provinces!A:CA | |||
98 | Item metadata sheet | Items!1:35 | |||||
99 | Live Data sheet (Use if you are replicating/channging the counts sheets) | Data (Live)!A3:AS4000 | |||||
100 | Level counts: Data driven in Col C, Synthetic in Col E; See AR version a couple rows down | Level Counts!1:81 | Synthetic Counts!1:81 |