Bug 32971 - Complex Formula (IF and NORMSINV) giving #VALUE!
Complex Formula (IF and NORMSINV) giving #VALUE!
 Status: RESOLVED FIXED None POI Unclassified HSSF (show other bugs) 2.0-FINAL PC Linux P2 normal (vote) --- POI Developers List 34973 Show dependency tree

 Reported: 2005-01-06 19:38 UTC by Will Stranathan 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.
 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.``` 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.``` 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)```