Bug 11827 - [PATCH] Problem generating formulas containing SUMIF()
Summary: [PATCH] Problem generating formulas containing SUMIF()
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-dev
Hardware: PC All
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2002-08-19 17:22 UTC by Gary Robertson
Modified: 2005-03-20 17:06 UTC (History)
0 users



Attachments
Generation from POI (13.50 KB, application/vnd.ms-excel)
2002-08-19 17:25 UTC, Gary Robertson
Details
Generation from POI (5.50 KB, application/vnd.ms-excel)
2002-08-19 17:25 UTC, Gary Robertson
Details
Ater saving unchanged in Excel 97 (13.50 KB, application/vnd.ms-excel)
2002-08-19 17:26 UTC, Gary Robertson
Details
After double clicking on formula in hand1.xls and saving. (13.50 KB, application/vnd.ms-excel)
2002-08-19 17:27 UTC, Gary Robertson
Details
BiffView of poi.xls (42.09 KB, text/plain)
2002-08-19 17:27 UTC, Gary Robertson
Details
BiffView of hand1.xls (42.45 KB, text/plain)
2002-08-19 17:28 UTC, Gary Robertson
Details
BiffView of hand2.xls (42.45 KB, text/plain)
2002-08-19 17:28 UTC, Gary Robertson
Details
Patch: Changed VALUE/REF token on SUMIF() (8.62 KB, patch)
2002-11-04 22:59 UTC, Gary Robertson
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Gary Robertson 2002-08-19 17:22:19 UTC
I'm having some problems using the SUMIF() formula - if I generate a cell
using it and then open the workbook in Excel 97 (or 2000)
the displayed cell value is #VALUE!. However, if I look at the contents of
the cell, the formula appears to be set correctly, and if I double-click
on the cell, or hit the function button followed by OK, the cell then
recalculates correctly.

I should have said that if I do a manual recalculation  within Excel (
tools | options | calculation | manual, F9) the cell is still not
updated - I have to double click on it as described below.

I'm using 1.7-dev and JDK 1.3.1 with Excel 97. 

Attachments are:
poi.xls, poi.out - xls and BiffViewer from POI
hand1.xls, hand1.out - xls and BiffViewer after loading into Excel 97 and 
saving unchanged
hand2.xls, hand2.out - xls and BiffViewer after loading hand1.xls, double-
clicking on formula and resaving.

diff between hand1.out and hand2.out:

$ diff hand1.out hand2.out
1687c1687
<     .value           = NaN
---
>     .value           = 0.0
1702c1702
< 00000000 45 00 00 01 00 00 C0 00 C0
---
> 00000000 25 00 00 01 00 00 C0 00 C0
1717c1717
< 00000000 45 00 00 01 00 01 C0 01 C0
---
> 00000000 25 00 00 01 00 01 C0 01 C0

In context:

============================================
Offset 0xa41 (2625)
recordid = 0x6, size =47
[FORMULA]
    .row       = 2
    .column    = 3
    .xf              = f
    .value           = 0.0
    .options         = 0
    .zero            = -52428800
    .expressionlength= 25
    .numptgsinarray  = 4
Formula 0=AreaPtg
firstRow = 0
lastRow  = 1
firstCol = 0
lastCol  = 0
firstColRowRel= true
lastColRowRel = true
firstColRel   = true
lastColRel    = true

00000000 25 00 00 01 00 00 C0 00 C0                      %........

Formula 1=org.apache.poi.hssf.record.formula.IntPtg@60be38
00000000 1E B8 0B                                        ...

Formula 2=AreaPtg
firstRow = 0
lastRow  = 1
firstCol = 1
lastCol  = 1
firstColRowRel= true
lastColRowRel = true
firstColRel   = true
lastColRel    = true

00000000 25 00 00 01 00 01 C0 01 C0                      %........

Formula 3=<FunctionPtg>
   field_1_num_args=3
      name         =SUMIF
   field_2_fnc_index=345
</FunctionPtg>
00000000 42 03 59 01                                     B.Y.

[/FORMULA]

============================================
Comment 1 Gary Robertson 2002-08-19 17:25:21 UTC
Created attachment 2759 [details]
Generation from POI
Comment 2 Gary Robertson 2002-08-19 17:25:47 UTC
Created attachment 2760 [details]
Generation from POI
Comment 3 Gary Robertson 2002-08-19 17:26:49 UTC
Created attachment 2761 [details]
Ater saving unchanged in Excel 97
Comment 4 Gary Robertson 2002-08-19 17:27:18 UTC
Created attachment 2762 [details]
After double clicking on formula in hand1.xls and saving.
Comment 5 Gary Robertson 2002-08-19 17:27:56 UTC
Created attachment 2763 [details]
BiffView of poi.xls
Comment 6 Gary Robertson 2002-08-19 17:28:15 UTC
Created attachment 2764 [details]
BiffView of hand1.xls
Comment 7 Gary Robertson 2002-08-19 17:28:35 UTC
Created attachment 2765 [details]
BiffView of hand2.xls
Comment 8 Gary Robertson 2002-08-19 17:30:40 UTC
attach_id 2759 added in error - ignore!
Comment 9 Gary Robertson 2002-11-04 22:59:25 UTC
Created attachment 3718 [details]
Patch: Changed VALUE/REF token on SUMIF()