Spreadsheets again

[ffutures]

I'm getting on fairly well so far, but I'm now stuck on something that ought to be fairly simple:

In the text I've given the weight and price of a ship's galley as follows:

$2500 / 0.6 tons buys a minimum galley suitable for e.g. a crew of four or five. To calculate the costs for larger galleys divide the total number of people to be served in a single sitting by five (round up) and multiply this number by $500 to add the extra cost, by 0.1 tons to add the extra tonnage.

Small short range craft don't necessarily have galleys at all, the crew might eat packed meals.

If the ship is equipped to land horizontally the weight goes up by 25% and the price by 50%, reflecting much more complicated plumbing and gimbal-mounted stoves etc. - this is entered as input C5 with permitted values of 0 (vertical landing) or 1 (horizontal landing)

Input C18 is the number of passengers to be served


For this one I don't want smooth variation - I'm assuming that this goes up in steps. from e.g. a stove with four heating elements to one with 6, bigger cooking pots, etc.

Can anyone give me Excel formulas to produce the total price and the total weight of the galley?

Comments

[nelc.livejournal.com]

I'm not going to try the exact Excel formula because I'm not experienced with that application, but something like:

2000 + (Round(no_of_crew/5)*500) and

0.5 + (Round(no_of_crew/5)*0.1)

The exact form of the round function will depend on how Excel does it. If it has a ROUNDUP function, use that; if it rounds down by default you'll have to stick a (... +1) in before the multiplier (or rather around the expression to be multiplied by, if you see what I mean).

[errolwi.livejournal.com]

Yes, xl has ROUNDUP, set no of decimals to 0 ( before multiplying by 500).
Try 2000 + max(0,roundup(num_crew_in_sitting/5-5,0))*500. I suggest allowing no more than 3 sittings max, and preferably 2. The MAX function is needed to deal with how xl rounds up negatives (as I understand the help notes).

[ffutures.livejournal.com]

Thanks - I'll try both tonight (though I'm beginning to think cutting it down to "small galley" and "large galley" and leaving it at that might be easier for anyone who isn't using a spreadsheet.)

[idylll.livejournal.com]

Weight: =IF(C5=0,0.6+ROUNDUP(C18/5,0)*0.1,0.6*1.5+ROUNDUP(C18/5,0)*0.1)
Price: =IF(C5=0,2500+ROUNDUP(C18/5,0)*500,(2500+ROUNDUP(C18/5,0)*500)*1.5)

[ffutures.livejournal.com]

Thanks - I'll try it tonight (though I'm beginning to think cutting it down to "small galley" and "large galley" and leaving it at that might be easier for anyone who isn't using a spreadsheet.)