Compressed Material Requirements

Industry Gameplay Center Science & Industry
1

I am looking to calculate in Excel the amount of compressed ore needed to fulfill X minerals.

It can be either null or high sec materials.

I know there is a method using solver but it might be beyond my knowledge to make it work on this scale.

I also know there are websites that can calculate this but want to add it to my spreadsheet for manufacturing,

Can any one help with this one?

2

lulwath ?

You want to know the optimum quantity of compressed ore to reprocess into X minerals, with lowest possible isk cost including hauling.
In a spreadsheet ?
Without a solver ?

Is that so ?

3

That will require very complex math with many variables, there are 16 types of Ore, 14 if you ignore Mercoxit and Veldspar, which each has 1-4 types of minerals, so 16 * 4 = 64 (max) variables from the Ore alone, then you have the variables connected to your reprocessing yield which adds another 5 or so.

It would be fairly simple if you were only looking for 1 type of Mineral, but since you probably looking for it to be for all 7-8 types of minerals… well even with my high understanding of math, I would still not attempt this.

4

Is this what you need? https://www.fuzzwork.co.uk/compression/

Why create your own when it is already out there

1 Like
5

It may be usable, but it does not try to minimize the excess minerals you will get, if you put in 25 million for all but Morphite, you will end up with more than 1 billion excess Tritanium, not to mention the rest that are in excess.

OP wanted to know the amount that would generate the least amount of excess minerals, or am I wrong @Gonzo_Zirud?

6

Thanks, I want it in my spreadsheet so when calculating the build and buy requirements it gives me a total to purchase ideally with minimum wastage.

There are a few online websites but I would like to create it in excel to minimise the using / reliance of other tools

7

It is possible but you will probably have to use a separate “sheet” for that (I’m not meaning a new document/file), you can use the “Problem Solver”, it’s not activated by default so you’ll have to activate it.

How to activate Problem Solver in MS Excel
To activate Problem Solver go to Options > Add ins > on right-hand side in the bottom you will see Administrate: Excel add-ins, click the “execute” (or what ever it says) > in the dialog box that comes up tick in “Add-in Problem Solver” and press “Ok”

:grey_exclamation: WARNING :grey_exclamation: - The Problem Solver may take a long time to find a solution.

you will need:

  • A cell with your Reprocessing Yield %.
  • A column with list of quantity of compressed ore you will need to achieve your requirements.
  • A column with list of compressed ore types.
  • A column with list of volume per unit.
  • A cell calculating the total volume. (will be needed for the Problem Solver)
  • A row with list of mineral types.
  • A matrix with all the yields
  • A row with list of required minerals.
  • A row with list of excess minerals
  • A cell that show total quantity of excess mineral (will be needed for Problem Solver)

Here is an example:

058150c1feadabc0e334a4c0a5e67948
058150c1feadabc0e334a4c0a5e679481324Ă—561 34.7 KB

Now for the Problem Solver:

  • Set Objective: A cell in the sheet.
  • Set your goal (for objective), Max, Min or Specific Value.
  • Set Adjustable Variable(s): can be one or more cells.
  • Add your constraints.

Like here (sorry about the language, if you can’t read it):

69b78aea36d6dbb8a194e21150f4038c_WithEnglishText
69b78aea36d6dbb8a194e21150f4038c_WithEnglishText571Ă—600 17.8 KB

1 Like
8

With a little playing it worked perfectly, thanks for the guide it was really helpful.

No way would I have figured that out any time soon.

9

Happy it worked out for you, as you probably have guessed you can optimize it for:

  • Minimizing the Excess Minerals. - This is what I’ve use in the example.
  • Minimizing the Compressed Ore Volume
  • Minimizing the Quantity of Compressed Ore.
  • Minimizing the total Value of excess minerals - This will need some additional row(s) for input and calculations.
10
  • minimize the total cost, including freight

that’s what he actually wants :wink:

total_cost = buycost*(1+freighttax)+freightvolcost
buycost = sum[ore] (quantity(ore) * price(ore) )
freightvolcost = sum[ore] (quantity(ore) * vol(ore)) * volprice

volprice is the price your freighter charges to move 1 mÂł
freighttax is the collateral tax your freighter charges (eg 2%)

eg if you use karkinos from jita to o-v, the freighttax is 0.015 and the volumic price is 420

basically you need two more columns, one for the price of the compressed ore, one for the volume, and a third cell for total price.

11

Do you know of a Google sheet way of running a solver?

12

Haven’t found a usefull way, yet.

13

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.