X3TC - Optimum Ore & Silicon Use Calculator (Excel)

[Taniniver]

Tanin'iver presents - the X3 Terran Conflict Optimum Ore & Silicon Use Calculator (1.4.1)!

What it is: A spreadsheet to help you decide how to most effectively use the Ore or Silicon output from a complex to turn it into credits.

What it's not: An all in one complex calculator. This tool is designed to be used alongside existing complex calculators.

Compatibility: It was produced in Excel 2007 but saved in Excel 2003 compatible format. It has been tested as compatible with OpenOffice.

Download here:
http://www.filefront.com/17259479/Optim ... 1.4.1.xls/

Version history:
1.0 - Initial release, office 2007 format
1.1 - Replacement food cost calculation function from Laden Swallow for OpenOffice compatibility, many thanks!
1.2 - Added support for calculating optimum Silicon use, previous versions were Ore only. Also some input validation.
1.3 - Added support for Terran Keris Drone Fabs and Crystal Fabs to the silicon tab, also some minor cleanup and conditional formatting to highlight the best options. Credit for this version goes to to Laden Swallow, thanks!
1.4 - Major update by Laden Swallow, now includes support for all silicon consuming factories too, the ability to use mobile mined minerals, automatic generation of URL's to plug in to em3e3's online complex calculator, and more!
1.4.1 - Minor bugfixes and tweaks

Background:
I decided to develop this when tool when planning a huge Ore producing complex to fulfil a certain plot in X3TC. I realised that the universe would be highly unlikely to be able to consume such amounts of Ore (10,213 per hour in this case) once I was done supplying it for the plot.

I didn't want to simply junk the complex when it could be used to make me some money instead, so I planned to add extra energy production to support some weapon factories, which would remain disabled until I was done supplying Ore. But which ones to use? I initially thought to just go for big expensive weapons like PPCs, surely they would make the most profit!

It turns out though that this isn't the best choice - those factories are very expensive and would take a long time to pay for themselves. Also, there are other things that can be produced with Ore that make more profit.

A bit of checking with complex calculators found that the most profitable factory that uses Ore is in fact the Energy Bolt Chaingun Ammunition Forge, which is also much cheaper and would pay for itself quickly! Sadly this has another problem though - it produces 163 products per hour, and my complex would support 68 of these factories - over 11000 cargo storage units per hour.

I certainly don't want to have to sell that many manually to equipment docks, and selling ships full of them at shipyards only gives half the value (or so I have read on this forum). I really wanted something that would be produced in small enough volumes that I could either sell manually at EQ docks, or do the trick with loading up ships for the "supply me a ship" missions with them, since this gives the full value. I didn't want to have to do this too often though.

How to find the right balance? What factory would give me an optimum combination of a respectable profit, not too much volume per hour, and not take forever to pay for itself? The Optimum Ore Use Calculator was born

Further development after forum suggestions includes the ability to determine the optimum use of Silicon too, making this now the Optimum Ore & Silicon Use Calculator!

How to use:

Simply fill in your spare Ore or Silicon production (second sheet) or the number of factories you wish to build in the highlighted fields at the top right. The sheet will then show a variety of useful information for all the possible Ore/Silicon consuming factories you might want to build, the most important ones being:

Profit/Hour - the total profit per hour from selling the products at the average price. If you want to modify the price, simply edit the Selling Price column for the ware you are interested in.

% of Max - This shows the profits expressed as a percentage of the maximum possible from any of the factories. The most profitable Ore consuming factory at average prices is an Energy Bolt Chaingun Ammunition Forge, so this gets 100%. A Flail Missile Production Facility gives 91.64% of the profits you would make if you used an Energy Bolt Chaingun Ammunition Forge.

Break Even + Break Even w/Food - the time for the factories to pay for themselves, first just with the cost of the factories alone, then also including the necessary food fabs. This figure assumes you are using a self sustaining complex and thus getting the energy for free.

Space/Hour - the total storage space used by the products per hour, to work out how often you will need to send another Mistral SF to unload it all

The most effective way to use it is to use the sort/filter buttons at the top of each column to organise the output - you might want to exclude Terran factories if you can't buy those, or sort by Space/Hour whilst comparing the % of Max. The choice of what to build is ultimately down to you, but hopefully this tool can make it easier to quickly compare some possibilities.

I hope someone might find this useful, please post to let me know if you like it, or with suggestions for improvements, or if you find any errors

Tanin'iver

[TTD]

Cost w/Food = Err:508

This happens when loaded into Open Office.org

Not all of us can afford MS licenses beyond the cost of the OS.

[Taniniver]

Cost w/Food = Err:508

This happens when loaded into Open Office.org

Not all of us can afford MS licenses beyond the cost of the OS.

Sorry about that TTD, as I said I hadn't tested it in OO

The function used to calculate that field is pretty complex, since it has to work out the appropriate number of L and M sized food factories, and I didn't want to resort to macro use.

If anyone knows more about spreadsheet coding than I (this is by far the most complex thing I ever did with it) and knows how to implement that without the very deep nested IF function I used in the hidden "Y" column, I'd be happy to receive your help!

[TTD]

ok. When sorted,it may prove very useful.
Cheers.

[Taniniver]

Thanks for the feedback Laden!

The IF statement is so long and complex because it does in fact take into account how to best fulfil the food requirements, without producing waste.

Lets say you have 63 factories to provide food for. A more simple calculation method would say take 12 L food fabs to provide 60, then 2 M for 64 in total, with some waste. This is in fact what I did at first, but I wasn't happy with it.

What that long IF statement does is notices that you have a number of factories, greater than 10, and ending in a 3. It then says that the best way to fulfil that is with 11 L food fabs and 4 M food fabs, so there is no waste.

The only time the calculation produces any waste is with exactly 1 or 3 factories, since it isn't possible to fulfil those amounts exactly. The sheet indicates this by putting "Waste Food!" in column T in this circumstance to highlight the problem.

Columns O and P represent the cost of, for example, an L Argnu Beef factory plus and L Meatsteak Cahoona factory. They take the race from column A, then look up the appropriate values using the hidden data in columns AF, AG, and AH. These are then used by the long IF statement to add the appropriate costs once it works out how many factories are needed.

A summary of what the long IF actually does is as follows:

If 0 factories, return a cost of 0
If 1 factory, use a single M fab, the cost being looked up from column P. Highlight the waste using a function in column T.
If 2 factories, use a single M fab, cost as above.
If 3 factories, use 2 M fabs, highlight the waste.
If 4 factories, use 2 M fabs.
If 5 factories, use a single L fab, the cost coming from column O
If 6 factories, use 3 M fabs.
If 7 factories, use 1 L and 1 M fab.
If 8 factories, use 4 M fabs.
If 9 factories, use 1 L and 2 M fabs.

Now the trickier bit, more than 9...

The function then uses TRUNC($C2,-1) to look at the last digit of the number of factories to be produced - does it end in a 0, or a 1, or a 2, etc...

If it ends in a 0, simple - divide the number of factories by 5, use that many L fabs.
If it ends in a 1, subtract 6 from the number of factories, and produce the remaining number divided by 5 L fabs, plus 3 M fabs.
If it ends in a 2, subtract 2 and produce resulting number/5 L plus 1 M.
If it ends in a 3, subtract 8 and produce resulting number/5 L plus 4 M.
If it ends in a 4, subtract 4 and produce resulting number/5 L plus 2 M.
If it ends in a 5, just produce number/5 L.
If it ends in a 6, subtract 6 and produce resulting number/5 L plus 3 M.
If it ends in a 7, subtract 2 and produce resulting number/5 L plus 1 M.
If it ends in a 8, subtract 8 and produce resulting number/5 L plus 4 M.
If it ends in a 9, subtract 4 and produce resulting number/5 L plus 2 M.

It's a pretty inefficient way of calculating it, but it was the only method I could think of to get the proper number of L and M fabs without waste. I did look into using the LOOKUP function, but the array of values cannot have cell references in it to calculate the result. I also looked into HLOOKUP and VLOOKUP, but again hit problems with needing to reference cells in the same row as where the lookup was called from, to know which race factories to use.

Hope this all makes sense, if you (or anyone else for that matter!) have a method of calculating this that will work with Open Office too, I would certainly welcome it Feel free to make modifications to the sheet and post altered versions, I'm not going to enforce copyright or anything

EDIT: The post this was replying to seems to have vanished during the time I was typing the reply.

[Laden Swallow]

Oops sorry for messing around, a quick double check of my formula revealed I had misread something and needed to reassess what was wrong (by double checking values 1-10 on both spreadsheets)... As there had been no responses and to try and avoid confusion I deleted the post until I could track down my error.

Here is the true formula to go into Y2 and drag down through all the cells (excel):

=IF(AND(OR(MOD($C2,5)=1,MOD($C2,5)=3),$C2>5),((TRUNC($C2/5,0))-1)*$O2+(CEILING(MOD($C2,5),2)+4)*$P2/2,(TRUNC($C2/5,0))*$O2+CEILING(MOD($C2,5),2)*$P2/2)

Which should be able to be converted by openoffice when it opens and so run normally (when I tested my save)... If it doesn't, just swap all the commas for semicolons.

(since you used IF and OR in your sheet, and AND is functionally similar I won't explain these for you, likewise you used TRUNC)

MOD(number, 'modulo' aka divisor): Remainder when the first number is divided by the second number.

Think of a clock at 1pm (or 1300h) it will show '1', at 2pm (1400h) '2' etc. this is an example of MOD(number,12) - here I use 5 to work out the remainder after using the L fabs. (so the logic test at the beginning is to see if there is more than 5 fabs and if they leave remainder 1 or 3 when divided by 5, then to use formula 1, else use formula 2.)

CEILING(number,2): If the number isn't divisible by 2 then go to the next number that is.

Just a quick lesson in number theory with the modulo.

[Taniniver]

No worries Laden, I was just a bit puzzled

I just tested with the new function and it seems to return exactly the same results as the old horrible nested IF one, so it's in!

Version 1.1 is available, now in .xls instead of .xlsx format, since it should (hopefully!) be compatible with Office 2003 too.

Huge thanks to the spreadsheet wizard Laden Swallow for the updated function, it looks a lot nicer than mine!

[Laden Swallow]

Just a minor observation:

It is possible to do a 'stupid thing' and put negative numbers, decimal numbers or text for the number of factories to build. Maybe replace Cx with:

=IF(ISNUMBER($V$6),IF(AND($V$6=TRUNC($V$6,0),$V$6>=0),$V$6,0),0)

so that all the fabs columns are zeroed,

And insert a formula in X6 with:

=IF(ISNUMBER($V$6),IF(AND($V$6=TRUNC($V$6,0),$V$6>=0),"","INVALID INPUT"),"INVALID INPUT")

Or your choice of wording.

Also just went back and notice for some reason I had a double OR in the formula (probably a lazy cut and paste job on my part hehe). A slight tidy up yields the same result (previous post updated)

Can also confirm compatibility with open office 3.2 Calc. Useful as my trial version of MSOffice has finally died after 13months.

Or this was me just directly changing the green square without realising there was already a formula in there and the input is meant to go in the yellow boxes (but you can still put silly input into the yellow boxes and get errors)

Next step is to generate a similar sheet for silicon for us hehe just bear in mind that not all the 'food' will be conventional food, e.g. massom powder or majaglit etc.

[TTD]

downloaded latest version. works well.

It is an unusual approach to the problem of designing a complex.
It will certainly help those who do not use Xadrian's calculator,which gives hourly yields and enables you to have surplus goods for sale or see where productions fall short,etc

Where it might help Xadrian users is in the style of chart and seeing break-even points.

Job well done ,I say.

Now... about those silicon 'roids....

[Taniniver]

Glad it works in Open Office now TTD, thanks again for the new function Laden.

Silicon too eh? Some people are never satisfied

Seriously though, that does sound like a good idea - I presume you mean one just for factories that directly consume silicon, and not including Crystal Fabs - things like chip plants, computer plants.

I'm not sure how many of those types of factory there are off the top of my head - I'll look into it and see if it's worthwhile. It shouldn't be too hard to adapt the current sheet though, apart from those factories that need unconventional "food" such as Majaglit, as Laden Swallow noted.

I hadn't bothered with input validation for invalid factory numbers on the original sheet since it was initially just intended for my own use, I only decided to release it later. I could certainly add it, and thanks for the functions there Laden.

Do you know if there is a way I can lock the green "Actual number built" cell to prevent people typing into it directly? I did look into the Lock Cell type things briefly, but couldn't seem to get it working without it also preventing typing into the yellow cells intended for the normal input.

I actually only included that cell in the first place to try to deal with the possibility of people typing both into the Spare Ore Production and Number to Build cells at the same time and it then being unclear how many factories would be built.

[Laden Swallow]

Using a trial version of office 2010 (so can't say for certain that it will be 100% the same in 2007):

1. Enable view headings and click the select all box (or use ctrl+A (possibly more than 1 press required)) to select all cells.
2. Right click and format cells.
3. Go to the protection tab and tick the 'locked' box.
4. Deselect all cells, then individually repeat the above for the two yellow boxes but removing the tick from the locked box.
5. In the 'review' tab click protect worksheet - contemplate adding a password or not (or maybe add a password, but with the password being freely visible).
6. Ta da.

[Taniniver]

Version 1.2 is up.

I included those input validation functions, thanks Laden Swallow!

I also added Silicon support as suggested. It wasn't too much trouble, apart from two things - the Terran Keris Drone factory is M sized, unlike all other similiar factories which are S, so it wouldn't plug into the calculator nicely. I added a note about it at one side, giving the figures it would produce if you were somehow able to build an S version of it. It's not a good choice anyway, the profit isn't that big, and the products use a fair amount of space too.

Likewise I didn't include Crystal Fabs, since they only come in M and L sizes, and people are likely to only want them for producing energy anyway. Crystals as an end product to actually sell are not very profitable and quite bulky.

The clear winner for using excess Silicon seems to be Chip Plants - they make the most profit, and also don't use much storage space for their output.

[Laden Swallow]

The M keris drone facility has me thinking; you can actually have 'decimal' production with factories - in the form of resource starved factories, so they only produce for instance 50% of the time (obviously not the most efficient method of producing something - but it could be interesting).

Possible alternate sheet for resource starved complexes:
(formatting is atrocious mind - artistic flair isn't one of my strong points hehe)

Link removed due to maths error in function which has been corrected...

[Taniniver]

Version 1.3 is up!

Credit for this version goes to Laden Swallow since he did pretty much all the work, thanks Laden

The main change is the addition of M Keris Drone and M and L Crystal Fabs to the Silicon side of things, along with the possibility of partial production from resource starved factories.

There is also a little more validation, and some conditional formatting that will probably only work in Office 2007 or newer. The formatting isn't actually required as such, it just helps to highlight which are the best results - most profitable, quickest return on investment etc... at a glance.

[Taniniver]

Version 1.4 is up!

Credit for this version goes to Laden Swallow since he did absolutely all of the work this time, thanks Laden

Big changes are present:

The calculator now includes the ability to use mobile mined silicon and ore

All the remaining Silicon using factories have been added

Automatic generation of URLs to plug into em3e3's online complex calculator, optionally including not just the factories (+ food) but also the other support - SPPs, Crystal Fabs, Complex Kits, etc!

Some other more minor changes that I probably forgot, including more useful conditional formatting