Bug 8757 - Formula results in "#VALUE!" in Excel
Summary: Formula results in "#VALUE!" in Excel
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2002-05-02 23:01 UTC by Matthew McClain
Modified: 2005-03-20 17:06 UTC (History)
0 users

patch file (5.47 KB, application/octet-stream)
2002-05-05 14:48 UTC, Matthew McClain

Note You need to log in before you can comment on or make changes to this bug.
Description Matthew McClain 2002-05-02 23:01:14 UTC
//  Create a worksheet.xls with a formula C1 == (A1+B1).
//  Copy worksheet.xls to worksheet2.xls.

//  Open worksheet2.xls in Excel.
//  Notice, "#VALUE!" is in C1.
//  Click C1.
//  Click Formula Editor (text input next to equal sign).
//  Click Spreadsheet.
//  C1 changes to 5.
//  Save.

//  Open worksheet.xls in Excel.
//  Save.
//  (I did this because the worksheet.xls created/edited by POI
//   is around 5k and the one created by Excel is 13k.)

//  Performed diff of BiffViewer output on worksheet.xls and worksheet2.xls
//  and only difference appears to be the value of the formula (NaN vs. 5).

//  Did a pure hex compare (in emacs) against worksheet.xls and worksheet2.xls 
//  and saw other differences which are not reflected in BiffViewer output; 
//  those differences are not shown in the bfd mode in BiffViewer either.

import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.poifs.filesystem.*;

public class FormulaProblem implements Serializable
    public static void
    main(String args[])
        throws Exception
        if (args[0].equals("create")) {
        if (args[0].equals("edit")) {
        System.out.println("pass in either 'create' or 'edit'");

    public static void
        throws Exception
        System.out.println("create workbook.xls");

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow((short) 0);
        HSSFCell cell = row.createCell((short) 0);

        cell = row.createCell((short) 1);

        cell = row.createCell((short) 2);

        FileOutputStream out = new FileOutputStream("workbook.xls");

    public static void
        throws Exception
        System.out.println("edit workbook.xls");

        POIFSFileSystem fileSystem =
            new POIFSFileSystem(new FileInputStream("workbook.xls"));

        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        HSSFCell cell = row.getCell((short) 2);

        FileOutputStream out = new FileOutputStream("workbook.xls");
Comment 1 Matthew McClain 2002-05-02 23:04:19 UTC
this is win 2000 (5.00.2195) Service Pack 2,
        office 2000 (Excel 9.0.2720), 
        latest POI code (05/02/2002)in jakarta-poi\src\java
Comment 2 Avik Sengupta 2002-05-04 15:48:31 UTC
Ok, A1+B1 results in a #value, while sum(A1,B1) has the value as 0, both when 
written from poi. Need to see why value gets saved to NaN. 
Comment 3 Andy Oliver 2002-05-04 15:50:10 UTC
this isn't being recursive is it?  meaning A1 referring to itself.
Comment 4 Matthew McClain 2002-05-04 18:04:19 UTC
in org/apache/poi/hssf/record/formula/ReferencePtg.java:
change sid to 0x44 from 0x24
    public final static byte sid  = 0x44;

I also "hacked" org/apache/poi/hssf/record/formula/Ptg.java
to compile and recognize both 0x24 and 0x64 as well (like before)
        final int valueRef = ReferencePtg.sid - 0x20;  
        final int arrayRef = ReferencePtg.sid + 0x20;  

I think this stems from using the NUMBER field where Excel
usually uses MULRK (at least in the handful of spreadsheets
I've looked at with the BIFFViewer).

Both sum(a1,b1) and a1+b1 work with this.
Comment 5 Andy Oliver 2002-05-04 18:53:15 UTC
Can you send a patch?


Comment 6 Matthew McClain 2002-05-05 14:48:08 UTC
Created attachment 1787 [details]
patch file
Comment 7 Avik Sengupta 2002-05-06 14:04:42 UTC
Thanks, I'll commit this.  But this probably means that excel does care 
(sometimes?) about the class of token. Add token expects a V class parameter, 
and we were giving it an R class param. Sum can work with anything, so is 
probably a bad testcase. I'll test this some more. (btw, there is no 
circularity problem here .. i was putting the above formulas in the third row :)
Comment 8 Andy Oliver 2002-05-06 14:25:05 UTC
yes..  I think we should divide it so that we have a 

|          |
Value     Reference

the SIDs should be in the latter two.  But the functionality in the top class.
Comment 9 Andy Oliver 2002-05-10 03:04:13 UTC
Avik did you apply this?
Comment 10 Avik Sengupta 2002-05-13 16:18:37 UTC
Patch from Mathew McClain committed, but keeping the bug open to fix #value 
errors in ALL formulas. Unfortunately, the dev kit does not have much detail on 
Ptg classes, so its back to formula viewer for me :-(
Comment 11 Avik Sengupta 2002-05-27 21:12:13 UTC
Fixed for all known cases.