I am learning google sheets and trying to put together a sheet for industrial stuff. My goal is to have my sheet be able to tell me the ME levels of different rigged citadels and use that data in my final production.
For instance I want to have it tell me final ME level (Minerals needed) for capital Armor Plate BPO at ME10/TE20 being built in a T1 rigged Tatara and then switch my sheet to calculate parts needed to build an Archon BPO ME8/TE20 in a T1 rigged sotiyo.
This is where I am stuck at:
Going by the Devblog made discussing engineering complexes and the rigs I am off on my math somewhere. Devblog says max ME bonus for T2 rigged complex in nullsec/WH is 5.99%, I am only coming up with 5.09% Not sure what I have missed. Adn the TE is probably off, but I don’t know the real numbers there. I am trying to have it apply all skills as well.
Trying to get basic bonuses for structure with rigs, skills, and implants included. After this I will look to figure out math on ME Blueprints themselves.
Sorry, it will probably help move if I put a copy of my whole sheet. That is only part of a page in my sheet. I was mainly looking to figure out what’s wrong with my math on calculating the Structure+rig+Sov ME bonus on that part of the sheet. I figured I would do the blueprint ME math in a different sheet using my (Structure+Rig+Sov)ME * blueprint ME = total ME at the end product.
Because currently with that math I have listed in that example sheet. The total structure ME bonus should be 5.99%; however, I am only getting 5.09%. I haven’t even started messing with TE because I have no ideal what my target TE is to make sure my math if correct.
My end with this is to be able to calculate total materials no matter which structure I am building the product in. So I can use this one sheet to build everyone I would ever want to by just adding the BPO ME0 materials to the sheet, changing the structure options, and update skills/implants to receive correct amount of materials needed for the build. And I assumed this was the easiest way to achieve that goal. If you have a better idea please do let me know. Again I am a noob with sheets and have only learned what I know so far over the past 4 weeks or so.
Are you looking for something like my Upwell Structure Spreadsheet?
which does what you want yours to do, where mine is specific to the production of Upwell Structures only.
In regards to how to split up your spreadsheet, you’d want 1 page for all the input/output, 1 page as database with all the necessary data in relation to BPOs (Base Material(s) cost at ME:0 and Job time at TE:0) and if you want to get some estimated cost in ISK for materials you will also need to pull that from an external source.
Some basic functions you’d need to familiarize yourself with:
Thank you for your response as well. I did look over your sheet and I am not sure I understand where it does what I’m looking for. I have been using some of those statements and will take a look at HLOOKUP() as I don’t think I know that one.
As for exactly what I’m after…let me try and explain in a different way. On my sheet example listed above.
I want:
B17 to show the calculated ME bonus % for the structure selected ME(B11) * Structure System Bonus(B10) * Rig Base ME Bonus(B8) = B17
I want to select which station and where I plan to build in B1 and B2 for ME bonus calculation which will later be multiplied to the blueprint ME on another page will turn out the materials needed for that job.
I have the base math set up; however, according to CCP’s devblog discussing engineering complexes they say a T2 rig complex in null/WH space should have a ME bonus % of 5.99%. I am only getting 5.09%.
So my question and the answer I am looking for is what am I missing to get 5.09% instead of 5.99%? Am I just dumb and their 5.99% comes from the structure and the blueprint together or am I understanding their devblog correctly and the structure alone provides 5.99% which is then multiplied to the blueprint ME level to come up with the final ME % bonus?
If I am understanding their devblog correctly then where am I missing the .9%? Because if I am wrong with a T2 rigged complex in Nullsec/WH then it all structures ME bonus shown in B17 are going to be wrong no matter which combination of options I select in B1 and B2.
And right now I am trying to figure out the math for everything regarding the structure side of it. After I have figured that out I will then figure out the math for the Blueprint part and at that point I can multiply them together when calculating the final materials needed for the build I am trying to do.
Taking a look at your formula you posted I am not sure I am understanding you correctly.
I broke it down and I come up with the same, 1.1669944. However; if I multiply that by the base Trit needed for 1 Capital Drone bay, 347,163, I come up with 405,138. Please point me to my ignorance.
@ISD_Sakimura
Even still I seem to be getting the wrong number.
Base Trit needed for 1 Cap Drone bay build = 347,163
Base trit needed for 1 Cap Drone Bay built in a T2 rigged Engineering Complex in Nullsec/WH with ME10 blueprint = 293,733
Your formula comes up with = 289,189
= Roundup(347163 * (2-(1.1 * 1.01 * (1 + (0.024 * 2.1)))))
Is this what you’re going for?
= Roundup(347163 * (0.9 * 0.99 * (1-(0.024 * 2.1))))
Or what am I missing? The formula I posted gives me the correct amount of 293,733
lol, no problem. It still helped me figure out what I was doing wrong. The math seems to have a problem if you add the structure bonus in with the rig. You have to do the rig and afterwards calculate in the structure bonus like the formula I listed. Makes things a little harder then I would like in my sheet, but I will overcome.
Yes, the Security Multiplier of the Rig only apply to the Rig Bonus, so that has to be calculated before it is multiplied to the rest of the formula.
this is where the IF() function comes in handy, while it may make the length of the entire Cell “Function” very long, it will make your Spreadsheet semi-automatic based on choices in your reference cell(s).
If you take a look at the Spreadsheet Evaldia shared with us, you have a few “Reference” cells:
Structure Type: 3 Citadels, 3 Engineering Complexes and 2 Refineries. (1 Cell providing a List of all the structures by Name, to choose from)
Structure BPC ME Level: 0-10. (also a list)
Structure Component BPC ME Level: 0-10. (also a list)
Lastly all other cells is basically a Function with reference to thoses “Reference” cells
If you set up your spreadsheet this way it limits the data you have to change each time, if there are changes to any of the variables of the bonuses. when you change your choices in the reference cells the sheet will update any cell that has it included in it’s function
