Formula One 7.0 Functions ReadMe
 
A number of F1J functions do not return exactly the same results as Excel functions. In many cases, there is inconsistency in the way that the Excel functions are implemented. The following are offered as examples of where and why F1J deviates from Excel results.
 
Note that all examples are from Excel 2000. Microsoft made many changes in Excel 2000 to fix problems/inconsistencies in Excel 97. F1J is consistent with Excel 2000 for all of these differences. If you find differences between F1J and Excel 97, please check Excel 2000 with the same values first. (For example, ACCRINTM in Excel 2000 differs from Excel 97 for calendar "basis" of 1 if a leap year is in the date range.)
 
General Error Reporting
 
Excel allows "Analysis add-in" functions to have more or fewer arguments than the documentation indicates (example—COMPLEX("j")). On normal functions, this is not allowed, but is caught at editing time. F1J does not allow these to be entered with incorrect numbers of arguments. When sheets that have incorrect number of arguments are imported from Excel to F1J, the incorrect argument count is caught and the function result is #N/A, whereas Excel will usually start evaluating arguments and may issue a different error. In the example case, Excel will give a #VALUE! error.
 
Specific Functions
  • ACCRINT, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PRICE, YIELD. These functions rely on the COUP… functions indicated above. In many cases, the calculations are complex and involve summing many interest payments. A difference in any coupon period for an interest payment may be propagated through to the answer.
     
  • AMORDEGRC. F1J and Excel 2000 differ on the way certain numbers are rounded. In most cases, this makes only a slight difference (usually by 1 in the currency amount). However, because of the accumulation of errors, there may be a major difference in final payment (vs. no payment).
     
  • CALL, GETPIVOTDATA, HYPERLINK, REGISTER.ID, and SQLREQUEST functions. When an Excel file containing one of these functions is exported for Formula One, the function returns #N/A. Future versions of Formula One may support GETPIVOTDATA, HYPERLINK, and SQLREQUEST.
     
  • CELL and INFO functions. Some of the arguments to these two functions are not supported in Formula One.
     
  • CHAR and CODE functions. These functions may return different results in Excel and Formula One because Formula One always uses the Unicode character set, while Excel uses the character set installed on the user’s computer. In locations outside of the United States, the difference will be noticeable for characters above 127.
     
  • CONVERT function. In some cases, Excel uses inaccurate measurements for the conversions. Since Formula One’s conversions are correct according to the latest international SI standards, formulas using CONVERT may return different results in Excel and Formula One.
     
  • COUPDAYS. For calendar "basis" 1, this frequently produces incorrect numbers in Excel for semi-annual and quarterly end-of-month dates. The following is a typical example. In Excel, the result is:

    • COUPDAYS("12/30/1994", "9/30/1997", 4, 1) = 90

    The correct answer is 92, which can be seen from the following series of formulas (all of which yield the same answers in both F1J and Excel 2000):

    COUPDAYBS("12/30/1994", "9/30/1997", 4, 1) = 91

    COUPDAYSNC("12/30/1994", "9/30/1997", 4, 1) = 1

    COUPPCD("12/30/1994", "9/30/1997", 4, 1) = 34607 (or 9/30/94)

    COUPNCD("12/30/1994", "9/30/1997", 4, 1) = 34699 (or 12/31/94)

  • COUPDAYSNC. This function, along with COUPDAYBS and COUPDAYS, should yield a set of answers that are always consistent for calendar "basis" 0 and 4. Specifically, COUPDAYBS + COUPDAYSNC = COUPDAYS. COUPDAYS is always 90, 180, and 360 for quarterly, semi-annual, and annual, respectively. However, consider the following examples from Excel:

    • COUPDAYBS("12/30/94", "11/29/97", 4, 0) = 31

    COUPDAYSNC("12/30/94", "11/29/97", 4, 0) = 60

    COUPDAYS("12/30/94", "11/29/97", 4, 0) = 90

    COUPDAYBS("12/30/94", "11/29/97", 4, 4) = 31

    COUPDAYSNC("12/30/94", "11/29/97", 4, 4) = 58

    COUPDAYS("12/30/94", "11/29/97", 4, 4) = 90

    As you can see, the first two lines of the first example add up to 91; for the second example they add up to 89. Since F1J agrees with Excel 2000 for COUPDAYBS and COUPDAYS (as well as COUPPCD and COUPNCD) for these cases, F1J adjusts COUPDAYSNC to provide a correct and consistent set of answers.

    NOTE: In Excel 97, COUPDAYBS returned 32 in both of these examples. F1J has chosen to be consistent with Excel 2000 in all cases that are not obviously in error.

  • COUPPCD. In Excel, this function apparently does not recognize 2000 as a leap year in certain cases. The following example shows the error.

    • COUPPCD("1/1/1999", "2/28/2000", 2, 0) = "8/31/1998"

    F1J returns "8/28/1998", which is consistent with normal leap year results (for both F1J and Excel):

    COUPPCD("1/1/1995", "2/28/1996", 2, 0) = "8/28/1994"

    For non-leap years, the result is the same in both F1J and Excel:

    COUPPCD("1/1/1994", "2/28/1995", 2, 0) = "8/31/1993"

    These results are consistent regardless of the calendar "basis" used. This leads to numerous errors in related functions. Specifically, COUPDAYBS incorrectly computes the number of days in all cases for "maturity" of 2/28/2000. Since many of the financial functions are built on the concept of coupon periods, these errors propagate through them. The following may be affected: ACCRINT, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PRICE, YIELD.
     

  • Database functions (DAVERAGE, DCOUNT, DCOUNTA, etc.). F1J follows the early Lotus 1-2-3 implementations of these database functions, which are significantly different than Excel’s current implementation. Use of numbers (including dates converted to serial numbers) in column headings is not supported. Simple wild-card characters (* and ?) are supported in text comparisons. Formulas may be entered in the criteria range, and will be applied to each row in turn.
     
  • DATEDIF. There are some discrepancies in results when using the YD option.
Known F1J 7.00 Bugs (to be fixed at first opportunity)
  • Certain complex array-entered formulas that use the aggregate function (like SUM) do not evaluate the same way Excel does.