Microsoft KB Archive/260893

= Works: Spreadsheet Functions Imported from Excel 2000 to Works =

Article ID: 260893

Article Last Modified on 1/27/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Works 2000 Standard Edition
 * Microsoft Works 6.0
 * Microsoft Works Suite 2000
 * Microsoft Works Suite 2001

-



This article was previously published under Q260893



SUMMARY
There are 65 functions in Microsoft Excel that you can import and use in Microsoft Works Spreadsheet. If Works Spreadsheet encounters any other Excel function, you may receive the following error message

Problem with formula.

The formula is too long or too complex, or contains a function that is not supported. Click Yes to see more errors, click NO to ignore other errors, or click Cancel to stop loading the file.

where  is the cell in the spreadsheet in which the formula was ignored.

The cells imported with the ignored formulas have their currently returned value converted to decimal or text format.

NOTE: The total number of formula errors is reported to you.

Functions that return a text string have their last return value imported as text.

Functions that return decimal numbers have their last return value imported as decimal.

Any functions returning binary, octal, hexadecimal, or complex numbers have their last return value imported as text.



MORE INFORMATION
The following table is the list of the Excel functions that can be imported in some form to Works Spreadsheet. The notes explain any changes that are made to the name of the function or the behavior of the function when it is imported. If there is not a note accompanying a function, there is no difference between the behavior of the function in Excel and Works. FUNCTIONS               NOTES -               -

ABS

ACOS

AND

ASIN

ATAN

ATAN2

AVERAGE                 Function changes to AVG.

CHOOSE                  &quot;Choice&quot; parameter is reduced by 1 to affect change from 1-based to 0-based referencing (the value returned is the same).

COLUMNS                 Function changes to COLS.

COS

COUNTA                  Function changes to COUNT.

DATE

DAY                     Returns correct day if the argument is a serial number, or a cell reference, otherwise returns ERR.

DDB                     If the &quot;factor&quot; parameter is anything other than 2, the function produces the error described in the &quot;Summary&quot; section of this article.

EXACT

EXP

FALSE

FIND                    &quot;Offset&quot; parameter is reduced by 1 to affect change from 1-based to 0-based referencing and 1 is added to the return value so that it                         returns the same value Excel does.

FIXED                   Function changes to STRING and ignores the &quot;no_commas&quot; parameter so that it always displays with no commas. If the decimals argument is a negative number, ERR is returned.

HLOOKUP                 &quot;LookupValue&quot; parameter is reduced by 1 to                          affect change from 1-based to 0-based referencing (the value returned is the same). Works does not import the formula if the &quot;range_lookup&quot; argument is included in the formula.

HOUR

IF                      Excel allows the IF statement to have only one comma (after the condition). Excel assumes that the True condition is a zero and the False statement is FALSE. Works 2000 actually puts a                         &quot;0&quot; (without the quotation marks) in the True condition and FALSE in the False condition.

ISNA                    This Excel function always returns &quot;False&quot;, without the quotation marks, when it is imported by Works Spreadsheet.

LEFT

LEN                     Function changes to LENGTH.

LN

LOG10                   Function changes to LOG (which is base 10).

LOWER

MAX

MID                     &quot;Offset&quot; parameter is reduced by 1 to affect change from 1-based to 0-based referencing (the value returned is the same). Works Spreadsheet returns ERR if Excel returns empty text.

MIN

MINUTE

MOD

MONTH

N

NA                      Returned value changes from #N/A to N/A.

NOT

NOW                     The default Excel cell format for this function is Custom m/d/yyyy h:mm, and the default Works cell format for this function is General.

OR

PI

PROPER

PV                      The parameters are rearranged (because they are                          in a different order in Works) and the &quot;Payment&quot; parameter is subtracted to make the function work as it did in Excel. If the &quot;fv&quot; or &quot;type&quot; parameters are anything but 0, the formula produces the error described in                         the &quot;Summary&quot; section of this article.

RAND                    Note that the number reported automatically changes to a different random number when the worksheet is imported.

REPLACE                 &quot;Offset&quot; parameter is reduced by 1 to affect change from 1-based to 0-based referencing (the value returned is the same). Note that Works does not treat a number as a text string unless it is preceded with quotation marks.

REPT                    Function changes to REPEAT.

RIGHT

ROUND

ROWS

SEARCH                  Function changes to FIND and the &quot;Offset&quot; parameter is reduced by 1 to affect change from 1-based to 0-based referencing. Also, 1 is added to the return value in an attempt to                         return the same value as Excel. Importing a                          Search function from Excel may cause Works to return an ERR value when Excel returned a                             positive value because FIND is case-sensitive and SEARCH is not.

SECOND

SIN

SLN

SQRT                    Works displays ERR instead of #NUM! when you attempt to take the square root of a negative number.

SUM

SYD

T                       Function changes to S.

TAN

TIME

TRIM

TRUE

UPPER

VALUE

VARP                    Function changes to VAR.

VLOOKUP                 Works does not import the formula if the &quot;range_lookup&quot; argument is included in the formula.

YEAR                    1900 is added to the return value so the value shown represents the actual year as                         Excel displays. Works returns ERR if the argument is less than 1.

Additional query words: w_works works2k w2001 xl2000

Keywords: kbaddin kbinfo KB260893

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.