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.

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.

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.)

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.

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.

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!

And you're able to lock the design without one?  That's a bug.

Quite possibly!

It was to make sure that the cost of the crew quarters got into the system.  In some cases that was important.

Ah, I'd forgotten about that. Ships with more engines will lose a little bit of the penalty of the extra crew, because the BP will be lower than it should be.

I think the best way to do it would be to have two fields for each size of crew quarters, one which is calculated from the crew requirements and another for extra berths. I just don't like overwriting a formula with a number; it's too easy to forget that you've done so.