Bug 26610 - nested IF's in formulas produce #VALUE! error in excel
Summary: nested IF's in formulas produce #VALUE! error in excel
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-pre3
Hardware: All All
: P3 normal with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 34973
  Show dependency tree
 
Reported: 2004-02-02 18:18 UTC by Sasha Borodin
Modified: 2008-05-05 13:51 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Sasha Borodin 2004-02-02 18:18:05 UTC
The formula document on the HSSF website instructed to report all occurrences of #VALUE! when 
attempting to programmatically assign a formula to a cell, so here goes:

IF(A1 > 5, "big number", "small number")

This formula works, and displays correctly when opened in Excel.

#VALUE! is only displayed if the outer IF branches to an inner IF.  For example:

> IF(A1 > 5, "big number", IF(A1 < 2, "really small number", "small number"))

#VALUE! is displayed if "A1 > 5" evaluates to false, and branches to the "else" argument, where 
there's a nested IF.  If "A1 > 5" evaluates to true, the formula displays correctly, because there's no 
nested if in the "true" argument.

#VALUE! is always displayed if both the true and false arguments contain nested IF's.

-The cell displays #VALUE!
-but if I type it in directly in Excel, the formula works
-also, clicking in the formula bar of POI-generated formula, then hitting Enter, fixes the formula

Thanks,

-Sasha Borodin
Comment 1 Tushar Kapila 2005-09-19 13:13:27 UTC
Having AND in the eval part of a If in a formula results in #Value err too.

If user goes to the output cell and presses F2 in Excel then presses enter ( 
makes Excel re evaluate it) the formula shows up ok.
Using Calc now in Excel menu does not work

Automated work around - placing a loop macro that touches all formula .
'In workbook

Private Sub Workbook_Open()
If Sheets(1).Range("iv1001").Value = "0" Then
    Sheets(1).Range("iv1001").Value = "1"
    Sheets(1).touchFormlas
End If
End Sub

'in sheet
Sub touchFormlas()
On Local Error GoTo errH
Dim wkBk As Workbook
Dim sht As Worksheet
Dim i As Long
Dim colFrom As Long
Dim colTo As Long
Dim rwFrm As Long
Dim rwTo As Long

Set wkBk = Workbooks(ActiveSheet.Cells(1, 2).Value)

Set sht = wkBk.Sheets(ActiveSheet.Cells(2, 2).Value)

rwFrm = ActiveSheet.Cells(3, 2).Value

rwTo = ActiveSheet.Cells(4, 2).Value
colFrom = ActiveSheet.Cells(5, 2).Value

colTo = ActiveSheet.Cells(6, 2).Value

If rwTo < 0 Then
    rwTo = rwFrm
    Do While Len(sht.Cells(rwTo, colFrom).Value) > 0
        rwTo = rwTo + 1
    Loop
    
End If

'touch all cells formulas - workaround for #value POI bug
'run this macro in workbook open ( keep a flag in cell BI5000 so it does not 
run everytime u open - only first time)
For i = rwFrm To rwTo
    For j = colFrom To colTo
        If sht.Cells(i, j).Formula <> "" Then
            z = sht.Cells(i, j).Formula
            sht.Cells(i, j).Formula = ""
            sht.Cells(i, j).Value = "=3"
            sht.Cells(i, j).Formula = z 'sht.Cells(i, j).Formula
        End If
        'sht.Cells(i, j).Refresh
    Next
    
Next
Err.Clear
If Err.Number <> 0 Then
errH:
    Debug.Print Err.Number, Err.Description
    Resume Next
End If
End Sub
Comment 2 Tushar Kapila 2005-09-19 13:18:05 UTC
egample of formula that has AND
=IF(AND(BH3<>0,BH3<>"",BK3<>""),BH3*BK3,0)

workaround tried - nested if - that does not work

in this case had another work around :
=IF((BH4>BX4),BH4,0)*IF(BK4>BX4,BK4,0)
Comment 3 Josh Micich 2008-05-05 13:51:13 UTC
Retested OK in 3.1-beta1

Probably fixed in 3.0.1-alpha due to work on bug 24925 (2004-04-29, svn r353553)