Formula Help

[Salesman_McSalesguy]

Hello!

I am building a sheet to manage production of various items, but am having some trouble with the material efficiency formula.

I have been using this formula on Google Sheets:
=MAX(1,CEILING(ROUND(1*[Base Material Requirement]*[Material Efficiency],2)))
Which works for some but not all items.

For example, when building a Fortizar with ME4 in a citadel with an additional 5.04% ME (T2 rig) and the structure’s base 1% ME, which gives me a total ME of 0.902016, it reads as requiring 37 Structure Market Network (this is correct) but asks for 5 Structure Advertisement Nexus (this is not correct as it requires 4).

Oddly, in-game at this ME it requires 4 of all components that would normally require 5, but all components that require 10 are not reduced. This is reflected properly at https://eve-industry.org/ (seen here: https://i.imgur.com/ZhlWSWt.png) but the formula it provides does not.

What am I missing?

[ISD_Sakimura]

@Salesman_McSalesguy

I have been using this formula on Google Sheets:
=MAX(1,CEILING(ROUND(1*[Base Material Requirement]*[Material Efficiency],2)))
Which works for some but not all items.

You have 3 input in relation to ME efficiency

• BPO/BPC ME research
• Structure ME bonus
• Structure Rig ME bonus (this has it’s own multiplier based on where it’s anchored).

So with this info you have 4-5 cells with info affecting the Materials required, I would make the function as this:

=ROUNDUP( [Base Material Requirement] * ( ( 1 - [BPC ME] ) * ( 1 - [Structure ME] ) * ( 1 - ( [Structure Rig ME] * [Structure Rig SecMod] ) ) ) ) )

@Salesman_McSalesguy

Oddly, in-game at this ME it requires 4 of all components that would normally require 5, but all components that require 10 are not reduced.

Check the BPO again… Fortizar has always only needed (Base) 4, 10 or 40 of the components.

ME:4 is equal to 4%, which is 10 * 0.04 = 0.4, so 10 - 0.4 = 9.6, but the game can only take whole integers as input, so 9.6 becomes 10. any number between 9.0 and 10 will be rounded up, even 9.00001 would be counted as 10.

If you really wanted to make use of the ME of blueprint you would have to be making atleast 3 Fortizars in a single Manufacturing job, the math then becomes (3 * 10) * 0.04 = 1.2, so 30 - 1.2 = 28.8 which is rounded up to 29. By making 3 in one go you save 1 of each Component that originally needed 10 to produce one Structure.

[Salesman_McSalesguy]

@ISD_Sakimura

Check the BPO again… Fortizar has always only needed (Base) 4, 10 or 40 of the components.

I can’t believe I didn’t notice that! I feel pretty silly.

Everything seems to be working out now. Thank you so much!

[Marcus_Gideon]

Maybe I’m just not thinking about it hard enough… Or maybe you guys are thinking about it too hard.

Once upon a time, we had to calculate the “perfect” ME out of hundreds. But these days it’s just 10 points for 10%. So why bother with formulas and just get it to 10?

[Salesman_McSalesguy]

Research time. It’s not all that feasible to research a Fortizar BPO to 10/20 as it would take half a decade without a citadel rigged for it. Similar (but not quite as long, 2-3 years) for capital ship hull BPOs.

At a certain point, it’s more realistic to work with a ME 7-8 and eat the 5m loss on the 1 component it costs you.

The components should always have 10/X since you can usually get those up in a few months.