Bug 24925 - Nested IF Formula results in "#VALUE!" in Excel
Summary: Nested IF Formula results in "#VALUE!" in Excel
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-pre3
Hardware: PC All
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2003-11-23 08:03 UTC by Alec Beaton
Modified: 2005-03-20 17:06 UTC (History)
0 users

the test file in java (2.85 KB, text/plain)
2003-11-23 08:04 UTC, Alec Beaton
Resulted Excel File (Using Excel 97) the result xls with #VALUE! (4.00 KB, application/octet-stream)
2003-11-23 08:05 UTC, Alec Beaton
Proposed fix for nested if statements #VALUE (11.16 KB, patch)
2004-03-24 20:40 UTC, Peter M. Murray
Details | Diff
The REAL proposed fix for nested if #VALUE problem (940 bytes, patch)
2004-03-24 20:41 UTC, Peter M. Murray
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Alec Beaton 2003-11-23 08:03:49 UTC

I'm using POI HSSF version poi-bin-2.0-RC1-20031102, excel 97/XP

I'm trying to use a nested if formula in excel which is used for writing a 
string based on a cell value. this cell value is also a formula of devision 
("A2/A3"). When I'm using simple IF it works but when I use a nested IF then a 
#VALUE! comes up. When I enter the sheet using excel, go to the cell and just 
press enter on the formula line then I get the proper value. 
What am I doing wrong ?
I have seen that sometimes it does not necessary connected to the fact that the 
devision cell is also a formula but to the fact that the cell is a float and 
not integer and sometimes when I use integer in the IF formula then it works.
Moreover when using % in a formula parser fails.

Source Code is:

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;

 * A Writer which writes to XLS file wit the #VALUE! problem
public class TestXLSWriter {

    public static final int COLUMN_A    = 0;
    public static final int COLUMN_B    = 1;
    public static final int COLUMN_C    = 2;
    public static final int COLUMN_D    = 3;

     * Creates a new demo.
    public TestXLSWriter() {

    public void write() 
    throws Exception {
        HSSFWorkbook wb = createTestWorkbook();
        FileOutputStream out = new FileOutputStream("test.xls");

    private HSSFWorkbook createTestWorkbook()
    throws Exception {

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Test Sheet");

        HSSFRow row;
        HSSFCell cell;

        // Create a row and put some cells in it. Rows are 0 based.
        row = sheet.createRow((short)0);

        // Create a cell
        cell = row.createCell((short)COLUMN_A);

        cell = row.createCell((short)COLUMN_B);
        cell = row.createCell((short)COLUMN_C);
        // Although problem occurs with or without representing
        // fraction using precent style I use it in my 
        // program and that's why I put it in the test.
        HSSFCellStyle style = wb.createCellStyle();
        cell = row.createCell((short)COLUMN_D);
        // Here is the problem :
        // basically I want 0 if C1 < 0.3, 2 if C1 > 0.8 and 1 if in between.
        // In real life I will turn 0,1,2 to "Failed", "OK", "GOOD"
        // why does this line produce #VALUE! ???
        // However when I enter excel 97/XP and click inside this value
        // it works.
        // Moreover, I know that for sure the nested IF is the problem.
        cell.setCellFormula("IF(C1<0.3, 0, IF(C1>0.8, 2, 1))");

        // Other setCellFormulas that work are :
        // without nested IF it works
        //cell.setCellFormula("IF(C1<0.3, 0, 1)");

        // if I try with 30% the parser fails.
        // cell.setCellFormula("IF(C1<30%, 0, IF(C1>80%, 2, 1))");

        return wb;

    public static void main (String[] args) throws Exception {
        System.out.println("DEBUG: hello");
        TestXLSWriter w = new TestXLSWriter();

Comment 1 Alec Beaton 2003-11-23 08:04:47 UTC
Created attachment 9248 [details]
the test file in java
Comment 2 Alec Beaton 2003-11-23 08:05:58 UTC
Created attachment 9249 [details]
Resulted Excel File (Using Excel 97) the result xls with #VALUE!
Comment 3 Peter M. Murray 2004-03-24 20:40:44 UTC
Created attachment 10961 [details]
Proposed fix for nested if statements #VALUE
Comment 4 Peter M. Murray 2004-03-24 20:41:56 UTC
Created attachment 10962 [details]
The REAL proposed fix for nested if #VALUE problem
Comment 5 Peter M. Murray 2004-03-24 20:44:42 UTC
The problem is that cell references in the nested ifs were not getting their 
Token classes set properly because the recursion would bail out early when it 
reached nodes that were not AbstractFunctionPtgs.  In fact, we want to recurse 
into the entire tree to push out this token munging stuff.  NOTE: The second 
patch file i submitted is the one i am proposing.  The other one is just 
cruft.  Sorry.
Comment 6 Danny Mui 2004-03-24 20:51:53 UTC
hmm sounds and looks good enough :).  gonna apply and check out unit tests.
Comment 7 Glen Stampoultzis 2004-04-29 07:05:33 UTC
Patch applied