Article ID: 893352
Article Last Modified on 4/26/2005
APPLIES TO
- Microsoft Office Excel 2003
SYMPTOMS
Consider the following scenario. You use the ERF error function and the ERFC complementary error function in Microsoft Office Excel 2003 to perform calculations that require a high-level of precision. For example, you perform calculations that involve numbers that are up to one part per million. In this scenario, you may experience limited accuracy. You may experience one or more of the following symptoms:
- When the ERF error function calculates return values that correspond to input values that are between 0.7 and 1.3, only the first 6 or 7 digits that appear after a leading 0 (zero) may be correct.
- When the ERF error function calculates return values that correspond to input values that are between 0.0 and 0.7, only the first 8 or 9 digits that appear after a leading 0 (zero) may be correct.
- When the ERF error function calculates return values that correspond to input values that are more than 1.3, the number of correct digits that appear after a leading 0 (zero) increases from 7 until the input value is more than 3.6. For input values that are more than 3.6, the first 14 or 15 digits that appear after a leading 0 (zero) may be correct.
- When the ERFC complementary error function calculates return values that correspond to input values that are between 0.7 and 1.3, only the first 6 or 7 digits that appear after a leading 0 (zero) may be correct.
- When the ERFC complementary error function calculates return values that correspond to input values that are between 0.0 and 0.7, the number of correct digits that appear after a leading 0 (zero) decreases from 11 digits to 8 digits.
- When the ERFC complementary error function calculates return values that correspond to input values that are between 1.3 and 4.0, the number of correct digits that appear after a leading 0 (zero) increases from 7 digits to 9 digits.
- When the ERFC complementary error function calculates return values for an input value that is more than 4.0, the number of correct digits that appear after a leading 0 (zero) decreases until the input value is 6 or more. For example, the ERFC complementary function incorrectly returns a value of 0.0 for input values that are more than 5.9. For input values that are 6.0 or more, no digits that appear after a leading 0 (zero) are correct.
CAUSE
This problem occurs because these functions are calculated by approximations of limited accuracy in Excel 2003.
WORKAROUND
To work around this problem, follow these steps:
- Use the NORMSDIST function to retrieve 10 or more correct digits after a leading 0 (zero) from the ERF error function for any input value. To do this, replace the ERF(
x) element with the 2*NORMSDIST(x*SQRT(2))-1 element.
By doing this, correct values will also be returned for negative input values. That is, instead of an incorrect value of #NUM!, the ERF error function will return the correct value.
- Use the NORMSDIST function to retrieve 10 or more correct digits after a leading 0 (zero) from the ERFC complementary error function for any input value, except for input values between 2.5 and 3.5. To do this, replace the ERFC(
x) element with the 2*NORMSDIST(x*SQRT(2)) element. If the input value is between 2.5 to 3.5, the number of correct digits that appear after a leading 0 (zero) decreases from 10 digits to 6 digits.
By doing this, correct values will also be returned for negative input values. That is, instead of an incorrect value of #NUM!, the ERFC complementary error function will return the correct value.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
For more information about the NORMSDIST function in Excel 2003, click the following article number to view the article in the Microsoft Knowledge Base:
827369 Description of the NORMSDIST function in Excel 2003
For more information about the Analysis ToolPak in Excel 2003, click the following article number to view the article in the Microsoft Knowledge Base:
82908 Description of the effects of the improved statistical functions for the Analysis ToolPak in Excel 2003 and in Excel 2004 for Mac
Additional query words: ERF ERFC
Keywords: kbtshoot kbprb KB893352
