Daryk,
I've had a very close look at your spreadsheet again and we're also having a problem with semantics, which I might've been too loose with in the beginning of the thread. Let's clarify a few things. (skip to the bold near the bottom to see where the real problem is in the sheet).
Base XP: The value of the XP Pool before you start (pre-stage 0). Bonus in the form of field rebates and aging may be added to this; XP gained from negative traits *isn't*, because these XP are canceled out by taking the negative trait in the first place.
XP Pool: The current balance remaining in your XP pool at any stage of the build process. Pre stage 0, this will be Base XP. Post build, this will be 0.
Ending Build Cost: At the end of the build process, the is the summed value of ending TP converted back into XP. This will equal the Base XP plus any bonuses (i.e., field rebates and aging bonuses).
TOTAL - in your spreadsheet, you have a TOTAL cell in the balance sheet. I think this is what you think of when I say 'ending build cost.' But looking closely at the calculations, these aren't the same thing. Your TOTAL is just a sum of the cells above it. And, yes, because of the way you have your calculations (in particular the IF), you MUST add that very specific 'non-module negative traits' cell into your TOTAL to get things to balance at the end. But don't confuse this to think it means your Base XP (or ending build cost) is being increased, because it isn't. My spreadsheet uses that 'non-module negative traits' cell purely to confirm you haven't picked up too much XP by taking negative traits post-optimization.
--
Now, in your spreadsheet you have a cell called 'non-module negative trait', which only recently I've understood you take quite literally, and which you use differently than I realised.
I originally assumed you meant this to be 'XP spent in negative traits post-optimization', which isn't easy to word. There isn't any rules reason to track XP spent in *just* new negative traits, but there is a rules reason to track XP spent in neg traits post-optimization: so that you don't recover more than 10% of your Base XP at this stage, which is why I thought you had it in your spreadsheet (and why I left it in, but use it differently). But even in your sheet, it does not increase Base XP; you simply need it to help balance out because of your other calculations, the IF in particular.
I provided a very short sample build in a table in an earlier post, asking you to point out whether you agree/disagree. You didn't respond to that, so I'm not really sure what you're saying is wrong with my adjustments to the spreadsheet. However, I can point you to what's still wrong with your updated copy if we plug in the same build.
First I'll define what I think the columns in your sheet do:
XP: this is the ending XP in various stats after buying modules, but before optimization and spend phases.
Spent: this attempts to increase/decrease the XP column based on how much you input, which makes it a post-optimization step.
Remainder: this is what happens to the XP Pool - but not Base XP! This one's a bit tricky, as it attempts to optimize the sum of XP + Spent, but doesn't optimize module-acquired negative traits correctly, as I'll explain.
So, let's plug my sample build into your last updated sheet. Clear all stats/values (including attributes), plug 100 into the base XP, and the three traits/XP bought in the 100 XP module into columns A/B. Then plug the traits into column E to get the auto calcs started. This is what you're left with (and where the problem first appears):
Traits XP Spent Value Remainder
------------------------------------------
Trait A -75 -1 +75
Trait B +75 0 +75
Trait C +100 1 0
Now, the spreadsheet clearly attempts to optimize if you leave SPENT blank: Trait B correctly shows a VALUE of 0 and REMAINDER of 75, which reflects that the XP that was lost by buying the module (which decreased the XP pool) has now been returned to the XP pool.
But Trait A is a problem. It's apparently optimized, since the value is -1, but REMAINDER is incorrect: it should be +25, not +75, because you gain +25 XP back into the pool by lowering Trait A to -100, not +75xp. By putting +75 XP back into the pool, it's putting 50 XP more than it should, which means you end up spending more than you should.
This is confirmed if you look at your balance sheet at the bottom: it shows a remained of 150, when it should just be 100 at this point. So if you carried on spending to lower your TOTAL REMAINDER to 0, you will have spent 50 XP more than you should have!
Even if you try to manually optimise Trait A by putting -25 into SPENT, the REMAINDER is only adds 25 to the already incorrect +75. So the Build XP is still incorrectly increased by +50 XP at this stage.
Let's continue to the SPEND stage, adding Trait D and spending the 200 XP that *should* be left at this stage:
Traits XP Spent Value Remainder
------------------------------------------
Trait A -75 -1 +75
Trait B +75 0 +75
Trait C +100 1 0
Trait D -100 -1 +100
At first glance, this is a-ok. You put -100 XP into D (which means your character now has something bad he didn't have before), which got you +100 XP into your pool to spend elsewhere. The balance sheet isn't ok yet, though, since it hasn't yet 'picked up' that you've gone -100 XP into a negative trait, because none of you balance sheet looks at SENT.
You compensate for this with the 'non-module negative traits' cell, which you use to adjust TOTAL. But here TOTAL *isn't* the 'ending build cost' (in other words, the base xp after adjusted with bonuses)! It's simply a subtotal of the above cells, and doesn't directly relate to XP Pool or Base XP.*
So let's continue with the build and spend the remaining +200 XP (from optimisation and from adding Trait D):
Traits XP Spent Value Remainder
------------------------------------------
Trait A -75 -1 +75
Trait B +75 0 +75
Trait C +100 +200 3 -200
Trait D -100 -1 +100
We're back to the ending values in my earlier post that were balanced, but your sheet still says I have +50 remaining to spend. That's the same +50 from the earlier incorrect optimization of Trait A.
So here's where the real problem is in your spreadsheet, then: not the 'non module negative traits' cell or the totals you derive from it, which do correctly balance out in your sheet, but with any negative traits you picked up from modules that weren't already optimised at the end of buying modules are adding on too much XP back to the pool, which is incorrectly increasing the Base XP of the build!
You can see how big a problem this is by changing the original module to this: Trait A -175, Trait B +175, Trait C +100. This is still a +100 XP module, but plug it in and you'll see that the incorrect extra +50 XP has now jumped to an incorrect extra +150 XP!
This is happening because of that IF in column I. Take it out, and this now calculates properly. Which also means you can stop adding the 'non module negative traits' cell into your TOTAL, which no longer actually effects the balance sheet (it cancels itself out) which will then make that TOTAL = Base Build + bonuses. This is essentially what I've done, in addition to adding additional columns to make each step a bit more clear. That's why I removed the IF in the first place, and why I didn't understand why the 'non mod neg trait' cell was being used in the balance sheet, since it was cancelling itself out once I removed the IF.
---
*You can see this now by converting the ending TP to XP and adding it up: -1 + 3, -1 = -100 + 300 - 100 = 100, which correctly equals the Base XP! So in this build the ending build cost does correctly equal the base XP of 100 XP. Your TOTAL field says 200, but again, that's not the ending build cost - that was 100XP.
So the we had a bit of a problem with semantics. When you said the non mod neg traits increase the total build cost and I said they didn't, we've actually been talking about different things. You mean they increase the TOTAL cell in your sheet, which it must because of the way the your calculations work. While I meant they don't increase what the build is actually worth at the end.
