You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Chris McCann <te...@gmail.com> on 2012/11/05 04:52:07 UTC
Excel summation formula error
Hi all,
I've got a formula in a spreadsheet that simply sums the 21 cells in the
column above it, such as IY101 =SUM(IY80:IY100).
The values in the summed cells in the spreadsheet in Excel are all zero, as
is the formula value.
However, using POI 3.8 XSSF and the FormulaEvaluator, while all of the
summed cells (IY80 thru IY100, whose values are also from formulas) show
zero, the formula value for the sum in IY101 is not zero (82750.0, to be
exact).
I'm seeing this same wrong answer in several other cells that use the same
relative formula. There are a large number of formulas in the sheet since
it calculates financial data for each month of a lease up to 30 years.
Looking at the formula values for the cells that feed the value in IY80,
for example, shows similar behavior -- a range of cells all have the value
0 but the formula that sums them shows some odd, non-zero value.
Has anyone else seen this behavior? Any hints on what might be causing it
and how to fix it or work around it?
Thanks,
Chris
Re: Excel summation formula error
Posted by Yegor Kozlov <ye...@dinom.ru>.
The first thing to do is to check against the latest build from trunk.
There were many fixes since POI-3.8 and your problem can be fixed
already.
Do you use SUBTOTAL in your worksheet ? There were fixes in this
function, see Bugzilla 53434.
If the bug is still reproducible with the latest build from trunk, can
you create a new bug in Bugzilla and attach sample Java code and input
workbook that demonstrate the problem?
Yegor
On Mon, Nov 5, 2012 at 4:52 AM, Chris McCann <te...@gmail.com> wrote:
> Hi all,
>
> I've got a formula in a spreadsheet that simply sums the 21 cells in the
> column above it, such as IY101 =SUM(IY80:IY100).
>
> The values in the summed cells in the spreadsheet in Excel are all zero, as
> is the formula value.
>
> However, using POI 3.8 XSSF and the FormulaEvaluator, while all of the
> summed cells (IY80 thru IY100, whose values are also from formulas) show
> zero, the formula value for the sum in IY101 is not zero (82750.0, to be
> exact).
>
> I'm seeing this same wrong answer in several other cells that use the same
> relative formula. There are a large number of formulas in the sheet since
> it calculates financial data for each month of a lease up to 30 years.
>
> Looking at the formula values for the cells that feed the value in IY80,
> for example, shows similar behavior -- a range of cells all have the value
> 0 but the formula that sums them shows some odd, non-zero value.
>
> Has anyone else seen this behavior? Any hints on what might be causing it
> and how to fix it or work around it?
>
> Thanks,
>
> Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org