Bug 51710

Does your file use shared formulas that don't start on the first cell by any chance? (There's a current discussion on the dev list about those)

You ment this discussion ?

http://apache-poi.1045710.n5.nabble.com/Excel-formula-oddness-td4680643.html 

Yes, this seems to relate to the same issue. First of all, as far as I can recall, I created the example file with simple routine; first made one formula with "semi-static" references, then copied it from left to right for a total row, then copy for row, paste for area downwards. So the use case is "shared formula" case.

I looked into sheet1.xml, and there is this weird syntax in the exact cell where the problem starts to show, E60. Until that, the row 60 is OK with si="2" but ref-field in E60 is weird no matter how you think, and then for every cell with si="3" POI returns incorrect formula.

---8<---------

<row r="60" spans="1:13">
<c r="A60" s="1">
<v>58</v>
</c>
<c r="B60">
<f t="shared" si="2"/>
<v>580</v>
</c>
<c r="C60">
<f t="shared" si="2"/>
<v>580</v>
</c>
<c r="D60">
<f t="shared" si="2"/>
<v>580</v>
</c>
<c r="E60">
<f t="shared" ref="C60:M83" si="3">$A60*E$2</f>
<v>580</v>
</c>
<c r="F60">
<f t="shared" si="3"/>
<v>580</v>
</c>
<c r="G60">
<f t="shared" si="3"/>
<v>580</v>
</c>

I stared the xml for a while and then noticed, that only thing that doesn't make sense is "ref" attribute of formula. The declared shared formula is always correct relative to the containing cell. The ref-attribute on the other hand is almost random, and does not in any way reflect to the formula copying process. What it seems to define is boundaries where the shared instance is in use.

When I count the references, it appears that there's familiar magic numbers involved :=)

 176 si="0"
 255 si="1"
 255 si="2"
 255 si="3"
  64 si="4"
 255 si="5"
 255 si="6"
  64 si="7"
..

So, it seems to me that 
A) one formula "instance" is shared maximum 255 times
B) distribution of these shared instances is somewhat random (mystical optimization?)
C) ref-attribute really does only define the "boundaries", nothing else

I also peeked the POI code, and found out that there's calculations on ss-level. It raised a question if HSSF-heritage is misleading the XSSF-implementation here ?

P.S. document that made me understand this shared-thing:  http://www.codeproject.com/KB/XML/ooxml_is_defective.aspx (see chapter 3)

Created attachment 27427 [details]
sheet1.xml in readable form

Attached the sheet1.xml (from example xlsx) in readable form (with linefeeds etc)

Created attachment 28391 [details]
graphical explanation of the problem

Created attachment 28392 [details]
graphical explanation of the problem

Should be fixed in r1294127, your test file included in the POI test collection.

It appeares that if a shared formula range preceeds its master cell then the preceding part is discarded.
For example, if the cell is E60 and the shared formula range is C60:M85 then the effective range is E60:M85 and the part C60:M85 is ignored. 

See the graphical explanation of the issue.

Regards,
Yegor