Cargo Fleet Optimization Spreadsheet

[db48x]

I'll check this when I get a chance.  If it's true, then I'll file a bug report.
Fair enough.  I usually don't go around hanging extra crew quarters on my commercial ships, so that wasn't a major design driver.  This wasn't intended to be a comprehensive system for designing commercial ships, which is why I also left out things like small fuel tanks. 

But... But... how can you build ships with an exact size of 79.8? You've got to put that small tank in there to get all that free extra range!

[bean (OP)]

But... But... how can you build ships with an exact size of 79.8? You've got to put that small tank in there to get all that free extra range!

I don't.  Like you, I do fill those spaces with trim fuel.  But it didn't seem worth worrying about, because it's a rounding error on the level of design the sheet is intended for.

[db48x]

I don't.  Like you, I do fill those spaces with trim fuel.  But it didn't seem worth worrying about, because it's a rounding error on the level of design the sheet is intended for.

I added support for fuel tankers by calculating the amount of fuel delivered divided by the fuel used. Still need to fix the crew quarters though.

[bean (OP)]

I checked.  My copy won't let you lock the design without an engineering space on your freighter.  It will throw an error in the lower right, instead of revoking commercial status.

[db48x]

I checked.  My copy won't let you lock the design without an engineering space on your freighter.  It will throw an error in the lower right, instead of revoking commercial status.

Ah, I see what you mean; it does indeed disable the lock button when I remove the engineering space. However in practice I always just go straight to the population window to retool to the class. Apparently it doesn't have the same restriction.

[db48x]

BTW, I just used an IPython notebook to interactively optimize a missile

[bean (OP)]

Ah, I see what you mean; it does indeed disable the lock button when I remove the engineering space. However in practice I always just go straight to the population window to retool to the class. Apparently it doesn't have the same restriction.

That shouldn't be allowed. 

BTW, I just used an IPython notebook to interactively optimize a missile

Sounds neat.  I'll see what I can make of it.  (I was the first to find the engine optimization stuff when 6.0 came out, but I'm an aerospace engineer, not a computer person.  I usually use MATLAB.)

[db48x]

I was playing around with the solver on this sheet to optimize a design. Optimizing for Colonists/BP/hr/L results in a ship with one cryo module, one engine, and one tiny fuel tank. Optimizing for (Colonists/BP/hr)*(Colonists/L) gets me a more reasonable design with 35 cryo modules, 2 advanced CHS, 23 engines, and one standard fuel tank.

Maybe I'm just too tired at this hour, but I really expected the former to be the right optimization target. As expected, both designs are worse in Colonists/BP/hr than a design optimized just for Colonists/BP/hr, and both are worse on Colonists/L than a design optimized for Colonists/L, but the first design is much worse. Can you think of a reason for this to be a better optimization target?

BTW, the solver in LibreOffice works once you set up the right constraints (setting a constraint so that every variable is >= 0, is an integer, and so that it doesn't put a million tiny fuel tanks, etc) but the UI is annoying. To solve again in a different column you have to edit all of the constraints individually so that they point to the correct cells. I also had to restrict it to solving with only one type of engine, as it could never find its way out of the trap of putting in multiple types (which makes the design invalid). How does Excel compare?

[bean (OP)]

I'm not sure that either of those make numerical sense, which is why you're getting weird results.  I think the proper way to do this is to establish a figure of merit for the design, some function of Colonist/BP/hr (I'll call that B below) and Colonist/L (L), and then optimize that.  This is technically what you did for the second, but you need to be more explicit about the weights you use. 
The simplest FOM would be F=X*B+Y*L where X and Y are weighting factors.  You can set X to 1, and then Y becomes how many colonists/L you're willing to trade for 1 Colonist/BP/hr.  I'd have to play with the exact numbers before I'd want to guess at reasonable weights.
You can of course make more complicated weighting functions, if you want one variable to stay relatively close to a specific value while the other one fluctuates a lot.  Say you want to cut L approximately in half because you're low on fuel, but don't want to make that a hard value.  So your function might be F=X*B-Y*(L-Lt)^2 where Lt is the target L.  (The minus is because you want the value to decrease as you depart from Lt, not increase.)
Excel's solver is pretty similar.  My first thought is to use a macro and set all the constraints in there, which makes them easier to edit and much easier to re-use.
Edit:
My second example doesn't make much sense, as there's no reason to want to penalize designs for being more fuel-efficient than the target.  I can't think of a single function which does what I want there, which is to give small bonuses for exceeding the fuel economy target, but large penalties for being below it.

[SenniTreborius]

Can a cargo ship load more than one automated mine?

[DIT_grue]

Edit:
My second example doesn't make much sense, as there's no reason to want to penalize designs for being more fuel-efficient than the target.  I can't think of a single function which does what I want there, which is to give small bonuses for exceeding the fuel economy target, but large penalties for being below it.

Start from something like (1.1 * ( L - Lt ) - abs( L - Lt ) ), perhaps?

Can a cargo ship load more than one automated mine?

Yes, provided it has the cargo space available.

[bean (OP)]

Start from something like (1.1 * ( L - Lt ) - abs( L - Lt ) ), perhaps?

That might work.  You'd have to play with the weights, but it does give more or less the desired result.