Excel formulas and subreports / subtotals

[kheaps]

I have a report in which each band is comprised of a handful of other subreports. Each subreport has a total at the bottom, in the same column as the numbers being totaled. I'm able to get a total at the bottom of the band through passing variables to/from the subreports, but I cannot figure how to write an excel formula that would count the correct number of cells and ingore the subtotals. (I'd like to write a formula in case the end user decides to change rows which cannot currently be populated from the database.)

The best I can think of would be to "name" the subtotal cells, and then reference them by name, but I also cannot figure out how name cells using the jrxml.

[kheaps]

An imperfect solution, for now - sum and divide by two:

<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1))/2"]]></propertyExpression>[/code]