Bug 32971 - Complex Formula (IF and NORMSINV) giving #VALUE!
Summary: Complex Formula (IF and NORMSINV) giving #VALUE!
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 34973
  Show dependency tree
 
Reported: 2005-01-06 19:38 UTC by Will Stranathan
Modified: 2008-05-05 13:53 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Will Stranathan 2005-01-06 19:38:56 UTC
The following formula:
=IF(C2=B2,0,IF((C2=0),6,NORMSINV(1-(C2/B2))+1.5))

gives a #VALUE! in F2.

B2 =16923
C2 =6039
D2 =(B2-C2)/B2
E2 =C2/B2*(10^6) // I had to do (10^6) - 1000000 complains 'Value out of range.
Value:"1000000" Radix:10'
F2 =IF(C2=B2,0,IF((C2=0),6,NORMSINV(1-(C2/B2))+1.5))

If I open the newly-created sheet, those cells show that value, but if I go
through and hit F2 (edit) and just Enter in each of those types of fields, they
get re-parsed and corrected.
Comment 1 Will Stranathan 2005-01-06 19:44:52 UTC
Seems to do with the complexity of the formula:

=IF(C2=B2,0,1) works
=NORMSINV(1-(C2/B2))+1.5) works
=IF(C2=B2,0,NORMSINV(1-(C2/B2))+1.5)) fails

So IF() works, NORMSINV() works - IF with another formula seems to not work
properly.
Comment 2 Josh Micich 2008-05-05 13:53:05 UTC
Retested OK in 3.1-beta1

It was probably the nested-if that was causing this bug.  That problem was fixed in 3.0.1-alpha due to work on bug 24925 (2004-04-29, svn r353553)