These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

Science & Industry

 
  • Topic is locked indefinitely.
 

Spreadsheet calculations for Industry changes

First post
Author
Previous Topic Next Topic
Tash'k Omar
Indefinite Mass
#1 - 2014-05-26 14:56:38 UTC
My current industry spreadsheet allows me to input materials and it spits out: You need #### 425mm railguns, ### of these minerals and will have ### of these minerals left over. Obviously it's formatted but that's the general idea.

With the industry changes coming soonᵗᵐ, 425s will no longer be used for compression.

I would like to modify my spreadsheet to output: You need ### compressed ore 1,2,3 etc, and ### of these extra minerals and will have #### extra minerals, in a combination that leads to minimal volume, while also prioritizing minimal excess.

Is there an easy way to code this? I realize this is a rather complex problem. Is calculating every possible permutation the only way?
Rannasha Kore
Center for Advanced Studies
Gallente Federation
#2 - 2014-05-26 16:00:19 UTC
The problem your posing falls in the category of Linear Programming problems (sometimes also called Linear Optimization).

See Wikipedia for information: http://en.wikipedia.org/wiki/Linear_programming

But to get you on your way, the basic equations that drive this problem come about as follows. Name the tritanium contents per unit of volume from ore #1, #2, #3, etc... as T1, T2, T3, etc... If your looking to ship an amount Tx of tritanium, you get the constraint:
V1 T1 + V2 T2 + V3 T3 + ... >= Tx
with V1 being the quantity of ore #1, etc... Similar constraints follow from other minerals, but the T-values will be different. Finally, the goal is to minimize the expression
V1 + V2 + V3 + ....
which represents the total amount of ore.

You can then use techniques mentioned in the Wikipedia article (and/or references therein) to solve this system. I don't know if Excel or other spreadsheet software have the capabilities to do this or if you need to use a programming language for it.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2014-05-26 17:56:55 UTC  |  Edited by: Steve Ronuken
I'll be (once the figures are confirmed) updating the calculator on my site for this.

It'll just be talking about the basic ores, as they're a simple enough replacement with the better ones. (just get a little less of each Blink )

It's using lpsolve for it, as this is, in the end, just a variation on the diet problem, which is well documented for linear programming.



The other option is to (I'm told) use a genetic algorithm. It's a somewhat more elegant solution than brute force, but in the end, this is all still nasty math, and not well suited to a spreadsheet. (apparently the solver add-in can do it in excel. But I've not used that. http://excelcalculations.blogspot.co.uk/2011/05/diet-problem-linear-programming.html might be useful.)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Elena Thiesant
The Scope
Gallente Federation
#4 - 2014-05-26 20:57:52 UTC
I'm considering writing a genetic algorithm to optimise ore types after Cirus (time permitting). It's the type of problem a GA is excellent at, just not in Excel :-)
Dealth Striker
Perkone
Caldari State
#5 - 2014-05-30 02:49:42 UTC
I went to the store and bought pencils, pencil sharpener, lots of erasers, and a couple packs of notebook paper.

I am ready for the changes
Striker Out!!
Dyncha
The Creators
#6 - 2014-07-26 20:35:40 UTC
Steve Ronuken wrote:

The other option is to (I'm told) use a genetic algorithm. It's a somewhat more elegant solution than brute force, but in the end, this is all still nasty math, and not well suited to a spreadsheet. (apparently the solver add-in can do it in excel. But I've not used that. http://excelcalculations.blogspot.co.uk/2011/05/diet-problem-linear-programming.html might be useful.)


Quote:
In fact, read George Dantzig's 1990 article The Diet Problem, in which he describes his attempt to follow a diet derived from this method. He nearly overdosed on Bovril Bouillion cubes closely followed by two pounds of bran. He ended up following his wife's advice and lost over 20 pounds.


Not sure what to think about it... ;-)))
Qoi
Exert Force
#7 - 2014-07-26 21:22:48 UTC
It seemed natural to rewrite the problem as a least squares optimization problem (at least at the time Lol), so i'm using http://en.wikipedia.org/wiki/Non-negative_least_squares

I guess to a physicist every nail looks like a least squares nail Ugh

http://eve-industry.org
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#8 - 2014-07-26 21:28:12 UTC
https://www.fuzzwork.co.uk/compression/ is my updated version (yes, that is a lot of skills.I'm hoping to, at some point soon, allow for skill storage and autopopulation)

It's using linear programming. I suspect the results of Qoi's and mine will be somewhat similar.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter