Excel formula help: TCS fuel purification plants

[Gruffty]

Dunno if this is the right place (?should be in The Fleet?) but here goes anyway...

I want to get Excel to calculate percentage based fuel purification plants, as per pages 14 and 15 of Adventure 5: Trillion Credit Squadron, but (and here's the bit I can't figure out) Excel also needs to use the minimum plant sizes shown in the table on p. 15 of TCS.

Any help on this would be appreciated

 

[jfetters]

I think this would be the right place, Gruffty.

Don't have my CD handy at the moment - is p. 15 a separate table you need to reference?

 

[Gruffty]

I have TCS in dead tree and on the CT-CDROM, thanks Jim. What I need is advice on how to construct the formula so that Excel will use the minimum plant sizes (on p. 15, as your rightly point out) when the % of fuel calculation gives a result of less than the minimum plant size (at the ship construction TL).

Example:

Say the ship has a total of 11 tons of fuel, and is built at TL-15. I want to install a fuel purification plant.

Using the % of fuel column, the calculation is:

Fuel = 11/100 = 0.11 * 0.15 (the % of fuel at TL-15) = 0.02 tons of fuel purification plant.

But TCS says the *minimum* fuel purification plant at TL-15 is 3 dTons.

So what formula do I need to put into Excel to get it to take into account both aspects i.e. the % of fuel and the minimum plant size?

I can get Excel to give me one or the other using LOOKUP, but can't work out how to make it use the minimum plant sizes by TL, where the plant size is smaller than the minimum for the TL (as in the example above).

There. That's as clear as mud

 

[flykiller]

if(LOOKUP(tech15),if(shipTonnage/1000*LOOKUP(fuelPurifierSize)<3,3,shipTonnage/1000*LOOKUP(fuelPurifierSize)),<etc>)

 

[aramis]

simpler solutions exist, flykiller
=if(FPPRef >0,max(FuelTonsRef/lookup(TLLabel,FPPRateLabel),lookup(TLLabel,FPPMinLabel)),0)

Given that you label the cell with the tons of fuel FuelTonsRef, the FPP table has three columns labeled TLLabel, FPPRateLabel, and FPPMinLabel which contain the relevant data. FPPRef is a label for a cell which any positive number is an FPP installed.

 

[flykiller]

simpler solutions exist, flykiller

ah, my corrector. I didn't know max() existed, I'll have to remember that one. thanks.

 

[aramis]

max() and min() accept cells, ranges, or other formulae as their 2+ components.

Really good stuff to use. Deobfuscation is your friend!

 

[Gruffty]

simpler solutions exist, flykiller
=if(FPPRef >0,max(FuelTonsRef/lookup(TLLabel,FPPRateLabel),lookup(TLLabel,FPPMinLabel)),0)

Given that you label the cell with the tons of fuel FuelTonsRef, the FPP table has three columns labeled TLLabel, FPPRateLabel, and FPPMinLabel which contain the relevant data. FPPRef is a label for a cell which any positive number is an FPP installed.

........

OK, some clarification on my part. I forgot to mention I'm using OpenOfficeCalc, not MS Excel (they're very similiar though):

Worksheet "HG2ed":
cell B5 = construction TL (in the example, this is 15)
cell D18 = Jump fuel dTons
cell D18 = Power Plant dTons
cell D20 = where I want the Fuel Purification Plant dTons to appear (i.e. this is the cell into which the formula goes)

All the data for the % based purifications plants is on a worksheet called "T." (for "Tables"):

cells A9 to A16 are the TLs (hex from 10/A onwards, hence the DEC2HEX function)
cells G9 to G16 are the Minimum plant dTons
cells H9 to H16 are the %s
cells I9 to I16 are the costs in Cr

So, in my speadshite I entered:

=IF(FPPRef>0;MAX(D18+D19/LOOKUP(DEC2HEX(B5);T.A9:A16;T.H9:H16);LOOKUP(DEC2HEX(B5);T.A9:A16;T.G9:G16));0)

What do I need to do with the very first part - FPPRef>0 ?

 

[Gruffty]

I tried this, using the total fuel dTons (i.e. D18+D19) at the beginning of the formula:​

=IF(D18+D19>0;MAX(D18+D19/LOOKUP(DEC2HEX(B5);T.A9:A16;T.H9:H16);LOOKUP(DEC2HEX(B5);T.A9:A16;T.G9:G16));0)​

It gave me a result of 16.6667 dTons, when the result should have been 3 dTons.​

Clearly I'm doing something wrong here....... :nonono:

BTW, this is a TL-15, 100 dTon hull, J-1, M-1, PP-1 (just to keep the maths simple).​

 

[aramis]

I tried this, using the total fuel dTons (i.e. D18+D19) at the beginning of the formula:​

=IF(D18+D19>0;MAX(D18+D19/LOOKUP(DEC2HEX(B5);T.A9:A16;T.H9:H16);LOOKUP(DEC2HEX(B5);T.A9:A16;T.G9:G16));0)​

It gave me a result of 16.6667 dTons, when the result should have been 3 dTons.​

Clearly I'm doing something wrong here....... :nonono:

BTW, this is a TL-15, 100 dTon hull, J-1, M-1, PP-1 (just to keep the maths simple).​

A couple of parenths should solve the issue:

=IF((D18+D19)>0,MAX((D18+D19)/LOOKUP(DEC2HEX(B5),T.A9:A16,T.H9:H16),LOOKUP(DEC2HEX(B5),T.A9:A16,T.G9:G16)),0)​

It appears to be an order of operations issue: Parenthesis, Multiply, Divide, Add, Subtract

 

[Gruffty]

........uhm...........that gives a purification plant of 73.333 dTons for 11 tons of fuel...........

 

[aramis]

doublecheck the lookups and syntax there for.

 

[Gruffty]

okthnxbai!

 

[Gruffty]

Nope, no good. I'm going to have to try to look at this from another angle to get it to work.