Jul 13, 2010 at 9:32 PM
Edited Jul 13, 2010 at 9:34 PM
To improve performance when Formulae reference other calculated cells, the PHPExcel calculation engine caches the results of all calculations (by default).
A10 Formula =SUM(A1:A9)
A11 Formula =A10 / COUNT(A1:A9)
So if I request the calculated values of A10 and then of A11, the calculation engine will not recalculate the value of A10 a second time, because it has already performed that calculation once and stored the result.
Most of the time, this is the preferred behaviour because it improves performance when repeatedly referencing calculated cells in different formulae.
This cache is indexed by Worksheet name and cell reference.
In your case, you have two workbooks, each with the same worksheet names and cells containing formulae, but which result in different values; but the second workbook will reference the same entry in the cache as the first workbook.
What you'll need to do is clear the cache between completing your calculations for the first workbook, and starting the calculations for the second workbook. You can do this using:
You would also need to call this if you were getting the calculated value for B1; then modifying the values in B2, B3 or B4; then recalculating B1 to get the new sum.
Note that the calculation cache also has an expiry, so it is sometimes possible that a previously calculated value wouldn't remain in cache, and might be recalculated subsequently.