TommyShelby Posted October 4, 2015 Share Posted October 4, 2015 As Plerrick mentioned in his thread earlier we (EDR and TDA) have talked about releasing our crafting spreadsheets. Plerrick released their version earlier and now we are releasing our's. We aim to make our spreadsheets easy to use and we know that alot of people dislikes spreadsheets. So with that in mind we have tried to setup our spreadsheet in a way so that's it's not confusing and only takes a very short introduction/tutorial to understand it. If you find the colours irritating or find the spreadsheet hard to use/confusing please describe to us what the problem is and maybe we can find a better solution. We want to improve upon our spreadsheets so if there is a feature you think our spreadsheet is missing please do tell. Anyways, here is a short tutorial on how to use our spreadsheet: Step 1. Making a Personal Copy of the Spreadsheet. - Click the link. You should be now redirected to the spreadsheet. - Click "File" in top left corner of the browser. - Select "Make a copy", this will create a copy of the spreadsheet in your own Google Drive. Step 2. Using the Spreadsheet. Now that you have your own copy you can start using the spreadsheet. I will go over the different features and how to use them in the following steps. When you open the spreadsheet it will look like this: (Without the red/blue text + boxes). NB: Important. There is only a few things you want to change in the spreadsheet. First you want to choose the ship you want to craft + Quality level, the amount and the type of wood you want to use. Second, if you have any resources or materials in your warehouse type in the amount you have. Third, unit prices, you can change these however you'd like, this way you can always get an estimate of how much money (Gold) you need to spend. Fourth, There is 3 subsheets for your usage. I have explained how the "Ship Search" subsheet works. The "Module Search" subsheet is basically the exact same thing. The "Market Prices Data" list's production values and consumption values in the different ports. That's basically it. If you change other things in the sheet i can't guarantee that it will work. If you do mess something up you can simply make a new copy of the original spreadsheet. Another thing, in the top left corner of the sheet we show the version number so that you can check if you have the latest sheet. The version number will be posted in the bottom of the OP. Stuff we want to add: - We want to add a feature so that you can choose which port you are currently in and the sheet will then tell you, not only how many resources/materials you need but also which nearby ports that produce the resources you need. Not sure how long this will take to get working properly. If you have stuff you want added or if you want to help us with the sheet/implementing new features please send me a message here on the forums. I check the forum pretty much every day and try to respond to everything. Now, here is the link for the original spreadsheet, enjoy!: LINK (Click to open the Original Spreadsheet). Thank you for reading! The sheet was made by: Tommy Shelby & Primus. (Thank you Primus for all your hard work, i wouldn't have been able to make this on my own). 9 Link to comment Share on other sites More sharing options...
_Masterviolin Posted October 5, 2015 Share Posted October 5, 2015 This is some pretty advanced Spreadsheet formatting! Good tool; I have a feeling that I will be using it in the near future. Link to comment Share on other sites More sharing options...
qw569😳 Posted October 5, 2015 Share Posted October 5, 2015 (edited) Good job, but for produce Frigate Q1 Fir need 18 Tar and 3 Furnishings. And total amount resources needed Iron Ore 200 Coal 212 Oak Log 88 Fir Log 222 Pine Log 8 Hemp 72 Lignum Vitae Log 2 Stone Block 57 Gold 6 Silver 6 Red Wood Log 15 http://i69.fastpic.ru/big/2015/1005/eb/80c1edc87f463b5f51e891f5c0aea7eb.jpg http://i70.fastpic.ru/big/2015/1005/43/59c486f991bffe2909f57f2f0ee30443.jpg Edited October 5, 2015 by qw569 Link to comment Share on other sites More sharing options...
TommyShelby Posted October 5, 2015 Author Share Posted October 5, 2015 Good job, but for produce Frigate Q1 Fir need 18 Tar and 3 Furnishings. And total amount resources needed Iron Ore 200 Coal 212 Oak Log 88 Fir Log 222 Pine Log 8 Hemp 72 Lignum Vitae Log 2 Stone Block 57 Gold 6 Silver 6 Red Wood Log 15 http://i69.fastpic.ru/big/2015/1005/eb/80c1edc87f463b5f51e891f5c0aea7eb.jpg http://i70.fastpic.ru/big/2015/1005/43/59c486f991bffe2909f57f2f0ee30443.jpg Corrected the Frigate recipe. Actually the total amount of resources listed in our sheet is correct. We take leftover, excess resources/materials into account as well. So the "Total amount resources needed" listed in our sheet is the actual amount of total resources needed. If you want to check out the "Breakdown Materials" as we call it, unhide row 29 in your copy of the spreadsheet. Link to comment Share on other sites More sharing options...
qw569😳 Posted October 5, 2015 Share Posted October 5, 2015 (edited) Corrected the Frigate recipe. Actually the total amount of resources listed in our sheet is correct. We take leftover, excess resources/materials into account as well. So the "Total amount resources needed" listed in our sheet is the actual amount of total resources needed. If you want to check out the "Breakdown Materials" as we call it, unhide row 29 in your copy of the spreadsheet. Ok. But where Red Wood Log, silver and gold http://i69.fastpic.ru/big/2015/1005/a3/2e257e9b6ebcd5d9fbf52c5bf15bb7a3.jpg Your Frigate recipe don't use Furnishings or i have bad copy your spreadsheet. I found error Ships Data!0205 = 0 (Silver for produce Furnishings). Ships Data!J205 = 6 (Iron Ore don't use for produce Furnishings). Ships Data!J215 must equal 156. Edited October 5, 2015 by qw569 Link to comment Share on other sites More sharing options...
TommyShelby Posted October 5, 2015 Author Share Posted October 5, 2015 Ok. But where Red Wood Log, silver and gold http://i69.fastpic.ru/big/2015/1005/a3/2e257e9b6ebcd5d9fbf52c5bf15bb7a3.jpg Your Frigate recipe don't use Furnishings or i have bad copy your spreadsheet. Make a new copy of the spreadsheet. It's version 1.04 now after correcting the frigate recipe. Link to comment Share on other sites More sharing options...
qw569😳 Posted October 5, 2015 Share Posted October 5, 2015 (edited) Make a new copy of the spreadsheet. It's version 1.04 now after correcting the frigate recipe. Silver = 0 Cell "Ships Data!O205" must have value 6 Cell "Ships Data!J205" must have value 0 Cell "Ships Data!J215" must have value 156 Edited October 5, 2015 by qw569 Link to comment Share on other sites More sharing options...
TommyShelby Posted October 5, 2015 Author Share Posted October 5, 2015 Okay so i went through the database and couldn't find anything wrong in the recipes. Ergo something else than the recipe's had to be wrong. After a little while i found that we had forgotten to add Roundup() in a number of cells. This was resulting in the "Ships Data!J215" being 150 instead of 156. I updated the sheet to V 1.05, the errors mentioned by qw569 (Thank you very much!) should be corrected now. Link to comment Share on other sites More sharing options...
Prater Posted October 5, 2015 Share Posted October 5, 2015 Okay so i went through the database and couldn't find anything wrong in the recipes. Ergo something else than the recipe's had to be wrong. After a little while i found that we had forgotten to add Roundup() in a number of cells. This was resulting in the "Ships Data!J215" being 150 instead of 156. I updated the sheet to V 1.05, the errors mentioned by qw569 (Thank you very much!) should be corrected now. I've been meaning to talk to you about this for awhile now, but how you do the hours isn't correct either (or at least wasn't last time I checked). When I was working on mine, I noticed I wasn't getting the same hours as you were. I noticed that we were both doing it wrong and fixed it on mine a week or two ago. It is related to the round issue. As an example, the Cerberus used to show as 869 on yours (I haven't checked if it is still this), but it is actually 839. Link to comment Share on other sites More sharing options...
TommyShelby Posted October 5, 2015 Author Share Posted October 5, 2015 I've been meaning to talk to you about this for awhile now, but how you do the hours isn't correct either (or at least wasn't last time I checked). When I was working on mine, I noticed I wasn't getting the same hours as you were. I noticed that we were both doing it wrong and fixed it on mine a week or two ago. It is related to the round issue. As an example, the Cerberus used to show as 869 on yours (I haven't checked if it is still this), but it is actually 839. I'll have to look into that. I didn't do that part so it'll take me a bit of time to figure out what's going on there. But i'll look into it. Link to comment Share on other sites More sharing options...
Prater Posted October 5, 2015 Share Posted October 5, 2015 I'll have to look into that. I didn't do that part so it'll take me a bit of time to figure out what's going on there. But i'll look into it. If I remember right, here is the logic: hours = roundup(material total amount needed / material quantity per job) * hours per job If I remember right, you don't roundup (material total amount needed / material quantity per job) and instead keep the decimal which causes problems. Link to comment Share on other sites More sharing options...
TommyShelby Posted October 6, 2015 Author Share Posted October 6, 2015 Spreadsheet updated to version 1.06. We corrected several recipe's which had minor mistakes in them. We made the "Total Labor Hour" count work properly. It now shows the correct numbers. Added Constitution and Pickle recipe. Link to comment Share on other sites More sharing options...
Pyzro Posted December 23, 2015 Share Posted December 23, 2015 It's easier to go file> Make a copy As long as your logged into google, it will place a copy under your own drive. Thanks for this Link to comment Share on other sites More sharing options...
Pyzro Posted December 23, 2015 Share Posted December 23, 2015 also- I think I may have found an issue, at least for crafting the brig. Currently, to craft one Brig, it says that you will require 4 tar. The ship itself requires 4 tar, but the cordage and oakum requires 6 hemp and 2 tar to make 8 cordage. It needs 12 cordage, so 2 runs will require 12 hemp and 4 tar, so 8 tar would be needed overall. unless you are just counting the materials to make the tar (fir logs), then it would make sense, but a tad misleading. Link to comment Share on other sites More sharing options...
TommyShelby Posted December 23, 2015 Author Share Posted December 23, 2015 Working as intended. The materials section shows how much of each material you need to build the ship itself. The section above shows the exact amount of resources needed to make all those materials. Does it make more sense now? If not, how can we improve it? Link to comment Share on other sites More sharing options...
Pyzro Posted December 24, 2015 Share Posted December 24, 2015 (edited) I feel like maybe everyone else understands it just fine, but before I found yours, I started my own, so I may have a different thought process on what needs to happen. When I was building completely from scratch, I would always come up short when it came to tar and iron ingots- so I had to craft them several times. It seems like you go straight from the raw materials to the final product but do not incorporate that there's a step between that (rudder parts, cordage and oakum, cables and howsers, and iron fittings are all two step processes). So, things like ingots (iron, gold, and silver) will never be populated on your spreadsheet. Also, I'm going to have to double check my calculations- I'm coming up with different totals than you when it comes to raw materials.. If I can abandon my own sheet I would (for reference) btw. I love your sheet. I wish I knew how to pull data from a different sheet like that. I'll keep looking to see if there's a better solution. I figured it out and consolidated everything. Also, for the Brig, it says that you need 29 blocks, but require 2 lignum logs... one lignum log will craft 50 blocks. Edited December 24, 2015 by Pyzro Link to comment Share on other sites More sharing options...
TommyShelby Posted December 24, 2015 Author Share Posted December 24, 2015 When I was building completely from scratch, I would always come up short when it came to tar and iron ingots- so I had to craft them several times. It seems like you go straight from the raw materials to the final product but do not incorporate that there's a step between that (rudder parts, cordage and oakum, cables and howsers, and iron fittings are all two step processes). So, things like ingots (iron, gold, and silver) will never be populated on your spreadsheet. You could say that we are skipping the step inbetween. Our database is made up of "Tiers"/"Techs". Tier 1 items are the very basic resources. Tier 2 items are crafted using Tier 1 items. Tier 3 items are crafted using Tier 1 and Tier 2 items. Tier 4 items are crafted using Tier 1, Tier 2 and Tier 3 items. Essentially, everything in Tier 2, 3 and 4 are made from Tier 1 Items. So we have calculated (Well, the sheet has ) the Tier 1 Equivalent of all the Tier 2, 3 and 4 Items. This could definitely make it seem like we are skipping certain steps even though we aren't. We most probably could remove things such as Ingots from the list of materials in the "Ship Search" sheet. Also, I'm going to have to double check my calculations- I'm coming up with different totals than you when it comes to raw materials.. If I can abandon my own sheet I would (for reference) What are you calculating and what are your results? Also, for the Brig, it says that you need 29 blocks, but require 2 lignum logs... one lignum log will craft 50 blocks. Hmm, i've found the problem but it might take a while for us to fix as it's actually a bigger issue than one might think! + It might be affecting other stuff than the calculations of Lignum Vitae Log. Oh, btw, we made the sheet before they made Crafting notes Craftable so they are listed as a "Tier 1" Item (Base resource). Thank you very much for the feedback Pyzro. Link to comment Share on other sites More sharing options...
Pyzro Posted December 25, 2015 Share Posted December 25, 2015 What are you calculating and what are your results? In my examples, I'll use the Constitution using Fir tier 1 (Since until recently I didn't realize the wood was a drop-down menu since it doesn't look like one.) I'll try to explain how I got my calculation, and if you notice a discrepancy, please let me know and I'll fix it on my end. I'm still trying to figure out your spreadsheets and databases, so figuring out your calculations has been tricky Coal: All coal is used to craft ingots, so I'll break down the parts to each ingot. You calculated 318, I calculated 332. Coal here is needed in 5 locations: Rudder Parts, Cables and Howsers, Iron Fittings, and two spots in Furnishings Rudder Parts (x3) require 1x oak log and 1x iron ingot. 46 rudder parts are needed, since we craft 3, it's rounded up to 48 with 2 left over. using =ROUNDUP(#RudderParts/3) will give us 16 Iron Ingots + 16 Oak Logs Cables and Howsers (x4) require 3x hemp and 1x iron ingot. 61 Cables are needed, since we craft 4, it's rounded up to 64 with 3 left over. using =ROUNDUP(#CaH/4)*3 will give us 48 hemp, then =ROUNDUP(#hemp/3) will give us 16 Iron Ingots Iron Fittings (x4) require 6x iron ingots. 69 Fittings are needed, since we craft 4, it's rounded up to 72 with 3 left over. using =ROUNDUP(#fittings/4)*6 will give us 108 Iron Ingots Furnishings (x1) require 5x Wood Log, 1x iron ingot, and 1x silver ingot 16 furnishing are needed, and since it's 1:1 ratio, we need 13 Gold Ingots and 13 Silver Ingots Ingots are 2 ore and 2 coal for 1 ingot. we have a total of 166 ingots x 2 = 332 ore Fir Log You calculated 157, I calculated 310 Fir is needed in 4 locations: Tar, Fir Frame Parts, Cordage and Oakum, and Wooden Fittings Tar (x1) requires 1x Fir Log 13 tar are needed, so 13 fir logs Fir Frame Parts (x5) require 3x Fir Log 333 Frame Parts are needed, rounded up to 335 with 2 left over. using =ROUNDUP(#FFP/5)*3 will give us 201 Fir Logs Cordage and Oakum (x8) require 6x hemp and 2x tar 41 CaO are needed, rounded up to the nearest 8 will be 48 with 7 left over. using =MROUND(#CoA+3,8)/8*6 will give us 36 hemp, then =SUM(#hemp/6)*2 gives us 12 Tar (12 Fir Logs) Wooden Fittings (x6) requires 3x Fir Log 165 WF are needed, rounded to 168 with 3 left over. Using =ROUNDUP(#WF/6)*3 gives us 84 Fir Logs For a total of 310 Logs Hemp You calculated 113, I calculated 105 Hemp is needed in 3 locations: Canvas Rolls, Cordage and Oakum, and Cables and Howsers Canvas Rolls (x3) require 3x hemp 21 CR are needed, rounded to 3, with 0 left over. Using =MROUND(#CR+1,3) gives us 21 Hemp Cordage and Oakum (x8) require 6x hemp and 2x tar Calculated previously required 36 Hemp Cables and Howsers (x4) require 3x hemp and 1x iron ingot. Calculated previously required 48 Hemp For a total of 105 Hemp Iron Ore: Required 2x ore for 1x Iron Ingot You calculated 292, I calculated 280 Iron Ore is needed in 3 locations: Rudder Parts, Cables and Howsers, and Iron Fittings Rudder Parts (x3) require 1x oak log and 1x iron ingot. Calculated previously gave us 16 Iron Ingots, so 32 Iron Ore Cables and Howsers (x4) require 3x hemp and 1x iron ingot. Calculated previously gave us 16 Iron Ingots, so 32 Iron Ore Iron Fittings (x4) require 6x iron ingots. Calculated previously gave us 108 Iron Ingots, so 216 Iron Ore For a total of 280 Iron Ore Oak Log You calculated 326, I calculated 122 Oak Logs are needed in 2 locations: Rudder Parts and Planks Rudder Parts (x3) require 1x oak log and 1x iron ingot. Calculated previously gave us 16 Oak Logs Planks (5x) require 1x oak log 529 are needed, rounded up to 530 with 1 left over. Using =ROUNDUP(#Planks/5) gives us 106 Oak Logs For a total of 122 Oak Logs Stone Block You calculated 80, I calculated 78. Stone Blocks are only needed for the Ballast Ballast (2x) requires 3x stone blocks 51 are needed rounding up to 52 with 1 left over. Using =ROUNDUP(#Ballast/2)*3 gives us 78 Stone Blocks Totaling 78 Stone Blocks Pine Log You calculated 12, I calculated 11 Pine Logs are only needed for Rigging Parts Rigging Parts (2x) requires 1x Oak Log 21 are needed, rounding to 22 with 1 left over. Using =ROUNDUP(#RP/2) gives us 11 Pine Log Totaling 11 Pine Logs Again, these are what I calculated and may have missed something major. If I have missed anything, or my calculations are off, please let me know. I made this chart to help verify all my calculations Link to comment Share on other sites More sharing options...
TommyShelby Posted December 25, 2015 Author Share Posted December 25, 2015 Your calculations are spot on Before we released the sheet we went double checked all the values and everything was correct. - I'm not sure when/what exactly happened but somehow nothing is correct atm. - - That said, i've found the problem in our sheet and i expect to have it fixed within a week, maybe two. So, revised version coming soon! 1 Link to comment Share on other sites More sharing options...
Mercenary Posted January 3, 2016 Share Posted January 3, 2016 Can you please do a excel version so I can download it,I don't have googles account and don't want to have one they just waist computer resources thank you. Link to comment Share on other sites More sharing options...
TommyShelby Posted January 3, 2016 Author Share Posted January 3, 2016 - For now you have the option to download an Excel version and hope that it works, or you can make a google account (It doesn't waste computer resources ) I'll keep your comment in mind though. Perhaps some time in the future you will see an Excel version ^.^ Link to comment Share on other sites More sharing options...
Kernkraft3000 Posted January 24, 2016 Share Posted January 24, 2016 (edited) In the original sheet i cant chose a ship? Edit: i copied to my google drive. Works. Edited January 24, 2016 by Kernkraft3000 Link to comment Share on other sites More sharing options...
Robaroo Posted January 27, 2016 Share Posted January 27, 2016 Good job, but for produce Frigate Q1 Fir need 18 Tar and 3 Furnishings. And total amount resources needed Iron Ore 200 Coal 212 Oak Log 88 Fir Log 222 Pine Log 8 Hemp 72 Lignum Vitae Log 2 Stone Block 57 Gold 6 Silver 6 Red Wood Log 15 http://i69.fastpic.ru/big/2015/1005/eb/80c1edc87f463b5f51e891f5c0aea7eb.jpg http://i70.fastpic.ru/big/2015/1005/43/59c486f991bffe2909f57f2f0ee30443.jpg Would love to see more version like this for each ship... I need the "For Dummies" versions so i can just copy the list..look on my map..sail and collect Link to comment Share on other sites More sharing options...
TsarTwig Posted February 1, 2016 Share Posted February 1, 2016 is there a fixed version of the spreadsheet anywhere for download? seems the raw materials numbers do not match the end results. Link to comment Share on other sites More sharing options...
TommyShelby Posted February 1, 2016 Author Share Posted February 1, 2016 is there a fixed version of the spreadsheet anywhere for download? seems the raw materials numbers do not match the end results. Not yet, i've been lazy. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now