Complex Calculator V3.0!!

Litcube · Post by **Litcube** » Sun, 21. Jan 07, 20:33

You are a gentleman and a scholar.

Gannon · Post by **Gannon** » Sat, 3. Feb 07, 01:34

I think I just noticed a bug. If I try to build a Space Jewellery factory it doesn't figure out the required resources properly. Instead of adding factories to provide it with Energy Cells and Maja Snails it provides it with Energy Cells and Soja Husks. The factories for Soja Husks do not go into the final output calculations, though, so it just shows the production of Majaglit, Energy Cells, and Maja Snails.

Alienmoe · Post by **Alienmoe** » Sat, 3. Feb 07, 07:27

Just a quick question:

I used one of the standalone versions before I got excel and it had a rather useful, although it turned out to give the wrong answer, bit that told you what the complex needed in terms of resources to start it looping. Been searching through the 2.3 sheet and cant seem to find it anywhere.

So is it there, and if so where?

chilipalmer · Post by **chilipalmer** » Tue, 27. Feb 07, 08:45

i cannot get this to work with office 2007 any ideas why ?

Merroc · Post by **Merroc** » Tue, 27. Feb 07, 11:50

Gannon wrote:I think I just noticed a bug. If I try to build a Space Jewellery factory it doesn't figure out the required resources properly. Instead of adding factories to provide it with Energy Cells and Maja Snails it provides it with Energy Cells and Soja Husks. The factories for Soja Husks do not go into the final output calculations, though, so it just shows the production of Majaglit, Energy Cells, and Maja Snails.

Its not really a bug, it just isnt programmed to add them. It only adds Bio/Food for a crystal fab and an SPP. In the same way it doesnt add mines.
I might add it though...

Just a quick question:

I used one of the standalone versions before I got excel and it had a rather useful, although it turned out to give the wrong answer, bit that told you what the complex needed in terms of resources to start it looping. Been searching through the 2.3 sheet and cant seem to find it anywhere.

So is it there, and if so where?

Thats a function only in the stand alone version.

i cannot get this to work with office 2007 any ideas why ?

I have no idea, but in 2 weeks i can check what happens at my parents... Unless you can provide me with more information, you'll have to wait and hope i dont forget.

Merroc · Post by **Merroc** » Fri, 4. May 07, 02:42

chilipalmer wrote:i cannot get this to work with office 2007 any ideas why ?

Right, due to a fortunate chain of events i was reminded of this now i have office 2007 myself. I managed to run the macros after a search of at least 10 minutes how to activate them! I still have to get used to the UI

. After i figured that out, i hadnt got any problems.

So if you still have problems and accidently read this, feel free to give more information on what doesnt work for you.

Merroc · Post by **Merroc** » Wed, 9. May 07, 11:41

If you;re having problems running the macro in Excel, it might help to download and install .net and the SP 1. Since i had no problems myself (maybe because i already have it) i cant see if this actually helps, but according to TomasArafel who pointed this out to me, it does...

He also might have an sollution for OO...

jumbled · Post by **jumbled** » Wed, 23. May 07, 05:49

My mind is boggling again, and it's not the first time...

I restarted my game a while back to clear up issues while upgrading to X3 2.0. In the process, I'm reexamining the notes I made from my previous game on the complexes I was building (fortunately, I made copies of your spreadsheet with my details in them). I was using v1.15 of this sheet at the time. Now I'm playing with your v2.3 version.

But the thing is the numbers in some cases are coming up differently between the 2 versions, and neither really matches with my "understanding" of resources used/produced (namely, energy). For instance, in a shield building complex, I have all 6 shield facts (one of each, all S size), plus support facts, plus a couple extras to handle ore surplus from the mines. In all, totalling up to the equivalent of 48 S-sized factories (adding up L's, M's and S's). I have two SPPs, an L and an M, and 4 mines: a silicon L on a 43 rock, a silicon M on a 26 rock, an ore L on a 29 rock and an ore M on a 13 rock.

According to my estimates, I should have a slight surplus on ore and silicon, which is fine. But the thing that eats at me all the time is energy. My understanding is a single S-size SPP should make enough for 9 facts. I got this idea from somewhere a while back. Of course, there are no S sizes in X3, but multiples count the same (M=2x, L=5x, XL=10x). With my SPPs here, I should make enough for 63 S-size facts.

In your yield calc, my mines equal about 10.88 silicon and 6.83 ore (if to fill S sized facts). In my mind, this should also equate to a similar amount of energy used by the mines.

But if all these facts/mines count up as 48 + 10.88 + 6.83 = 65.71 S sized facts consumption of energy, why then did your v1.15 tell me I have a "surplus" of 100.63% energy production and v2.3 now tells me I have 117.45% production overflow? Shouldn't I be overRUNning not overFLOWing?

But then, I don't recall my previous complex going out for energy due to shortages, but I dismissed it for slow sales and overstock/shutdown of production on some items.

So how does the energy thing work, especially for mines (if we consider a base level S mine on a 25 rock, no multiples)?

jumbled · Post by **jumbled** » Thu, 24. May 07, 08:21

Ok, I did a little more number crunching and I *think* I got an idea of how you're figuring these things in here, but it takes a leap of faith or two to understand due to the uneven division of energy-out per crystal-in on the SPP's (someone needs to refresh their math -- everything else works out evenly on inputs/outputs).

My mines produce 10.88 silicon and 6.83 ore (if thinking in terms of 100% S-level factories worth of use), so theoretically, I could run that many S facts at full capacity. But I'm not. *I* work with WHOLE numbers, not fractions, so I have facts sucking 10 silicon and 6 ore (6 shield fabs, a crystal L, crystal M and 3 Qtubes). I guess the calculator is interpreting this as the mines NOT working at 100%, thus returning the surplus ore and credting back the energy I would otherwise spend making it.

If you add up the number of facts in my complex, it's now counting as 48 facts, plus the equivalent of 16 for the mines (again, thinking S-level). That's now 64 total. The SPPs make multiples of 9 per, so an L and M make up 63. However, due to an error in SOMEONE'S math (*cough* Egosoft *cough*), there is a discrepancy in the inputs/outputs of SPPs vs crystal fabs. An M fab make 120 crystals/hr, whereas an M SPP uses 122 and a fraction per hour. This screws things up a bit for the math, but running the SPP output numbers for an L + M, the energy produced is actually 65 and a fraction, enough for my whole complex (parts of which are not running at 100% anyway) plus a bit of surplus.

Is this correct?

jumbled · Post by **jumbled** » Fri, 25. May 07, 13:08

Ok, I *think* I have good news and bad news... The good news is I feel one step closer to sanity again. The bad news is I lost most of my hair along the way.

I was running another sheet, this one for a shield complex I had running in my previous game, plus a few changes to experiment with numbers.

Essentially is went a little like this. 5 shield facts and some chip fabs (to fill in for surplus silicon), plus supporting M class bio, one SPP L and support, and some roids: 43 silicon, 6 silicon, 21 ore and 18 ore, all L size. It looked ok so far and my numbers seemed to jive with the sheet.

But since I had extra ore and the low level shields tend to go fast, I added one more 1MJ, 5MJ and 25MJ, totalling 8 shield facts now. Reworking the figures and the arrangement a bit, I now had a complex with an additional SPP M and support.

The thing is the sheet wouldn't ever tell me I'm running out of energy. By the time I finished playing with it, I had something like 72 S-level fabs sucking energy out of an SPP L and M, which only gives around 65-66 (rounding fractions) AND I still had surplus showing! This was not right, so I figured there had to be a calculation error.

I counted my factories time and again, I ran my numbers on a handheld calculator, even went so far as to plop L mines on the two roids in the game (43 and 6 silicon) to see the times and yields they make, and it all came up correct with your yield calculator and the numbers in the "hidden" columns on the spreadsheet.

So I then I opened the VB editor...it had to be in there....

Now I feel a bit vindicated. I found not one, but two actual errors, one of which explained why the numbers *seemed* to be excluding my silicon mines from the energy count altogether. You made a typo in an IF statement using the wrong column reference. Instead of taking "surplus" minerals (column AR) and adding it back for an EC credit, you took the entire volume of product (column AO) multiplied by the EC per unit and subtracted for a credit. It was as though my silicon mines didn't exist.

You also missed the mark on the ore mine section looking for the "Energy Cells" entry in the output to add back the numbers for surplus there. You're looking in a column full of numbers (AK), not the names of resources (AN). This would explain why it's showing ore for sale when it's not supposed to be selling any. You may want to take a look at this.

Now I can go to sleep....I only spent the entire night pulling my hair out on this problem.

BTW, one last Q... The output shows how many "NPC S type factories needed to fill supply/demand". The number under there doesn't tell me anything about how many of my factories I need to fill the gap, because it might say 1 or 2 (S types) but I can put several M's and a couple of L's in my figures and the number barely budges.

Merroc · Post by **Merroc** » Fri, 25. May 07, 13:45

You want to know what ill do with this long story?

Let me tell you, ill write down for myself telling myself to read this when... lets call it awake, cos im not really that right now. And when im at my own computer, then as i have told myself by then re read everything, re-examine everything, etc, etc. Fix what needs fixing. Stuff like that, you know

.

Also typo's or more commonly known as bugs in such a sheet/macro are possible, its people like you, mostly bald people i guess... that i rely mostly on, on getting them out.
A shame i wasnt around at all last week, i might have been able to save you from your baldness

jumbled · Post by **jumbled** » Sat, 26. May 07, 01:28

Merroc wrote:Also typo's or more commonly known as bugs in such a sheet/macro are possible, its people like you, mostly bald people i guess... that i rely mostly on, on getting them out.

I'm not bald, I'm "Follicle Deficient"!

No problem. At least now things seem to work a little better and I feel more confident to start planning my financial enterprise. In so doing, it's likely I might lose the rest of my hair, but then at least I won't have to worry about buying new combs all the time.

I'm a former programmer, myself, so I know the perils and pitfalls of making good software. I really enjoy the work you and others put in to make the "X" experience so much fun, and hope to see more of it in the future!

Merroc · Post by **Merroc** » Mon, 28. May 07, 20:54

Uhh right, i read it again.

Im in the VBA editor now, but for fear of making it worse in my quest to correct it, would be usefull if you provided line numbers where the errors are.

I know i made it myself, but its a loooong time ago

.
Also your "You made a typo in an IF statement" is very helpfull, you should realise why im sarcastic

.
The error probably sneaked in from the V1 to V2 change, when i edited loads of the columns and tried to fix the macro accordingly (well some parts at least, others i rewritten).

And about your NPC S type factories needed question. Not entirely sure what you mean. Its suppose to work that e.g. you have a Space Fuel loop, closed one. You can use this number to see how many buyer stations (in this case probably Pirate Bases) you need to sell your complete production.
Or if you have an EC shortage, you can see how many NPC SPPs you need to buy enough EC. It basically works by taking the "For Sale / Hour" or "To Buy / Hour" and divide it by a (fixed) number in the raw data sheet. If your the first doesnt change, then that wont either...

jumbled · Post by **jumbled** » Tue, 29. May 07, 02:05

Merroc wrote:Im in the VBA editor now, but for fear of making it worse in my quest to correct it, would be usefull if you provided line numbers where the errors are.

Ok, in sheet 4, Complex Calculator, starting at line 141, this is in my copy now. You'll need to cross-reference with yours to compare:

Code: Select all

        If wks.Cells(nRow1, "AN") = "Ore" Then 'we're looking for Ore to change
            If Not wks.Cells(nRow1, "AR") = "" Then 'For that Ore should be produced of course
                AmountExcess = wks.Cells(nRow1, "AR") 'How much excess Ore is produced
                If AmountExcess > 0 Then 'Only if there is an excess
                    For nRow2 = 2 To 79
                        If wks.Cells(nRow2, "AN") = "Energy Cells" Then 'look for the Energy Cell entry
                            EnergyCells = wks.Cells(nRow2, "AR") 'Excess Energy Production
                                ExcessAdd = AmountExcess * 6 'The amount of Energy which doesnt get used for Energy Cells
                                Temp = wks.Cells(nRow2, "AP")
                                newEnergycells = Temp - ExcessAdd 'How much we have in the end
                                Temp = wks.Cells(nRow1, "AP")
                                newAmountExcess = Temp 'How much Ore we have in the end (usage)
                            wks.Cells(nRow2, "AP") = newEnergycells 'Set EC
                            wks.Cells(nRow1, "AO") = newAmountExcess 'Set Ore
                        End If
                    Next
                End If
            End If
        ElseIf wks.Cells(nRow1, "AN") = "Silicon Wafers" Then 'And Silicon
            If Not wks.Cells(nRow1, "AR") = "" Then 'Silicon should be produced
                AmountExcess = wks.Cells(nRow1, "AR") 'And have an excess (how much)
                If AmountExcess > 0 Then 'Greater than 0
                    For nRow2 = 2 To 79
                        If wks.Cells(nRow2, "AN") = "Energy Cells" Then 'Look for EC entry
                            EnergyCells = wks.Cells(nRow2, "AR") 'What is the excess EC production?
                                ExcessAdd = AmountExcess * 24 'excess Silicon * EC per Silicon
                                Temp = wks.Cells(nRow2, "AP")
                                newEnergycells = Temp - ExcessAdd
                                Temp = wks.Cells(nRow1, "AP")
                                newAmountExcess = Temp
                            wks.Cells(nRow2, "AP") = newEnergycells
                            wks.Cells(nRow1, "AO") = newAmountExcess
                        End If
                    Next
                End If
            End If

On line 146, you used column AK, not AN, so you were looking for the text "Energy Cells" in a column of numbers, not names. This resulted in Ore excess not being taken back for EC, and I would always show ore for sale. And on line 161 (I think), you were looking for the excess amount of silicon (presumably in the For Sale column AR), but taking the number from the output column AO instead. So instead of returning the excess for a EC credit, you took the full output and credited it back. I had to make some assumptions here on your intent, cross-referencing between these two sections, but it seems to work better now.

And again, as I think I mentioned earlier, in your Factories page, and in the Raw Data page, you use an incorrect cycle tme for Hornet missiles. Should be 16 minutes (960 seconds). I found this when I was getting uneven bio usage from the food facts divided into the tech output.

I also noticed you are now using constants in the Factories page, whereas you used formulas in v1.15. Just my personal opinion, but I would think keeping one set of constants (maybe on your Raw Data page) and using formulas based on that everywhere else might make for fewer chances of errors (and easier corrections).

Merroc wrote:And about your NPC S type factories needed question. Not entirely sure what you mean. Its suppose to work that e.g. you have a Space Fuel loop, closed one. You can use this number to see how many buyer stations (in this case probably Pirate Bases) you need to sell your complete production.
Or if you have an EC shortage, you can see how many NPC SPPs you need to buy enough EC. It basically works by taking the "For Sale / Hour" or "To Buy / Hour" and divide it by a (fixed) number in the raw data sheet. If your the first doesnt change, then that wont either...

Maybe... I tend to make completely self-contained loops and the only thing coming out is an end product. What this part might tend to show me is if my loop is incomplete and I need to see where I have "extras" to use up by adding more high-level factories, or if I'm over-doing it and need to take something out, or use a larger size on something to fill in.

The section that confuses me the most is for the EC. It could be because I get credits back for the excess mining, and it gets used first before I "run out" and need more from outside. Therefore, if I add more fabs into my complex, I see odd figures for EC because more of the excess return from the mines is used. I should probably just ignore it until I actually run out of EC internally. Bio and minerals work more to my expectations.

Merroc · Post by **Merroc** » Wed, 30. May 07, 01:58

First of all, thanks. Ill look into it when i have the time... hope its anytime soon...

jumbled wrote:I also noticed you are now using constants in the Factories page, whereas you used formulas in v1.15. Just my personal opinion, but I would think keeping one set of constants (maybe on your Raw Data page) and using formulas based on that everywhere else might make for fewer chances of errors (and easier corrections).

You honestly must be kidding me!

Trust me, the way i make it is by formula's, however if i keep that sheet in the customer version in full formula's. You can press the "calculate now" button, get some coffee, come back and see its still not done. It might not be now, on my Core 2 Duo, but it was on my old Sempron. In light of users without up to date processors i decided to do it this way. Dont worry, its only full of errors if the formula's are

.
Also having the raw data in that factory sheet isnt possible either, cos lookups wouldnt work. Basically the factories sheet isnt full of constants, like hell i want to enter everything twice!

jlehtone · Post by **jlehtone** » Wed, 30. May 07, 08:25

@jumbled: You could go through X-Freak Cartman's Komplexprüfer too, just for comparison.

What happens in a complex is simple. Really. The problem is that one should iterate and consider the current stock amounts of every ware and their effect on every step, just like the game does. Dave Toome's standalone has such simulation. Excel has "Solver", doesn't it? No, I do not suggest using it. The assumptions and approximation made in spreadsheet together with user brain are quite sufficient, aren't they? Winnie the Pooh does not play X3, does he?

There are two types of self-sufficient complexes: the one that produces what it consumes, and the one that consumes only what it produces. The latter works only if at least one ware stock is continuously full. Every factory requires a float. Some just need more float than others.

Simple Goner math.

John McG · Post by **John McG** » Wed, 5. Dec 07, 12:02

It appears that silicon mines are not included in the complex calculations to see what factories are needed in a complex, is that correct?
We really do need them included as crystal fabs etc. are affected.
Thanks

John McG · Post by **John McG** » Thu, 6. Dec 07, 11:41

You don't appear to have Beta Kyon Emitter factories in your excellent 'complex calculator'.
Are you thinking of adding them?
Thanks

pjknibbs · Post by **pjknibbs** » Thu, 6. Dec 07, 13:02

Eh? Beta Kyon Emitter factories don't exist...unless you're using a mod that adds them, and the Complex Calculator can hardly be expected to include every single station that has been added by all the mods ever created for X3!

exogenesis · Post by **exogenesis** » Thu, 6. Dec 07, 14:44

Is Merroc still posting ?

pjknibbs:
Merroc's Excel sheets do include support for Ashley's Fab's, ie XL wepaons fabs,
but Armagedon added Kyons & EMP forges later than Merroc's latest version.

John McG:
Mines are included, why do you not think so?
If you're thinking of 'autofill', Merroc didn't support this cos "don't know what asteroid size you want to use".

egosoft.com

egosoft.com

Complex Calculator V3.0!!

complex calculations - silicon mines

Beta Kyon Emitter