XL2000: Calculation Differences Between the Office Spreadsheet and Excel

ID: Q216578

The information in this article applies to:

Microsoft Excel 2000
Microsoft Office Spreadsheet Component 9.0, run time

SUMMARY

The Microsoft Office Spreadsheet Component provides the ability to incorporate a spreadsheet onto a Web page. The Office Spreadsheet Component offers much of the same basic functionality of Microsoft Excel. However, there are differences regarding some formulas and functions that are not available in the Office Spreadsheet Component or that may calculate differently. This article discusses these differences.

MORE INFORMATION

Worksheet Functions

The following is a list of Excel worksheet functions that are not available in the Office Spreadsheet Component:


   CALL                 ISPMT                MMULT
   DATEDIF              JIS                  PHONETIC
   FINDB                LEFTB                REGISTER.ID
   FORECAST             LENB                 REPLACEB
   FREQUENCY            LINEST               RIGHTB
   GETPIVOTDATA         LOGEST               SEARCHB
   MDETERM              LOOKUP(Array form)   SQL.REQUEST
   GROWTH               MDETERM              TRANSPOSE
   INDEX (Array form)   MIDB                 TREND
   INFO                 MINVERSE             YEN

Array Formulas

Formulas that you must enter as an array, using CTRL+SHIFT+ENTER, do not evaluate correctly in the Office Spreadsheet Component. You cannot enter array formulas in the Office Spreadsheet. Notice that when you enter the array formula, array brackets {} do not surround the formula. The following example illustrates this behavior:


   A1: Apple          B1: 11
   A2: Orange         B2: 12
   A3: Apple          B3: 13 
   A4: Banana         B4: 14
   A5: Grape          B5: 15
   A6: =SUM(IF(A1:A5="Apple",B1:B5,0))

Instead of returning the number 24 in cell A6, the Office Spreadsheet Component returns a #VALUE! error because it cannot evaluate the array references properly.

Nesting Functions

In formulas, you can use functions as arguments for other functions. When you use a function as an argument, or nest a function, it must return the same type of value that the argument requires. For example, the following formula uses a nested AVERAGE function and compares it with the value 50. The comparison must return TRUE or FALSE because this is the required type of value for the first argument in an IF function.


   =IF(AVERAGE(A1:A5)>50,SUM(B1:B5),0)

In Microsoft Excel, a single formula can contain up to seven levels of nested functions. However, the Office Spreadsheet Component does not have this limit. Actually, no limit exists for nesting functions other than the limit of 1,024 characters in a single formula. However, if you plan to export the Office Spreadsheet to Microsoft Excel, you should limit nesting functions to seven levels because you cannot exceed the limit in Excel. When you export an Office Spreadsheet to Excel, the cell that contains a formula exceeding this limit will only contain the value of that formula.

If you click Export to Excel, you receive the following error message:

HTML Import Errors
Problems came up in the following areas during load:
Cell Formula

Calculation and Precision Options

The Office Spreadsheet Component offers limited functionality to customize how it calculates the spreadsheet. The two calculation options available are Automatic and Manual. The following calculation options are not available in the Office Spreadsheet Component:

Iteration

Including the following iteration options:

Maximum iterations

Maximum change

Precision as displayed
1904 date system
Accept labels in formulas
Zero values (viewing zero values is on by default and cannot be turned off)

Natural Language Formulas and Named Ranges

Labels in formulas and named ranges are not available in the Office Spreadsheet Component. When using a formula that refers to a label, the Office Spreadsheet Component displays a #NAME? error.

Labels in formulas, or natural language formulas allow you to refer to a list of items by row or column heading (label). For example, you can refer to the following sales by region, using the labels for North, South, East, and West for ProductA and ProductB instead of explicitly stating the range of cells:


   A1:                B1: ProductA        C1: ProductB
   A2: North          B2: 100             C2: 190
   A3: South          B3: 120             C3: 170
   A4: East           B4: 125             C4: 160
   A5: West           B5: 115             C5: 175
   A6: =ProductA East
   A7: =ProductB West

Both formulas in cells A6 and A7 will result in a #NAME? error.

You cannot create range names, or defined names, in an Office Spreadsheet Component. If you copy cells from an Excel workbook into an Office Spreadsheet Component, any formulas referring to defined names evaluate to the cell range and the defined name is removed.

For example, when you copy the formula =SUM(myRange) from an Excel worksheet to an Office Spreadsheet, where myRange refers to cells $A$1:$A$10, it evaluates to the formula =SUM($A$1:$A$10).

Additionally, if you copy a formula that refers to a workbook or worksheet other than the active worksheet, the formula evaluates to the resulting value, and the formula is thus removed. For example, if you copy the following formula from Excel to the Office Spreadsheet, it will contain just the value of the formula, because the formula is not converted.


   =SUM(Sheet1!B1+Sheet2!B2)

Database and List Management Functions

When using the database and list management functions, you may encounter some operations where the database functions return different results from Microsoft Excel or return an error value. The following functions are affected:


   DAVERAGE
   DCOUNT
   DCOUNTA
   DGET
   DMAX
   DMIN
   DPRODUCT
   DSTDEV
   DSTDEVP
   DSUM
   DVAR
   DVARP

Database Functions Return Incorrect Result

The database functions listed above may all return incorrect results when used in the Office Spreadsheet. This problem occurs when the criteria field does not match any field in the database range and your criteria includes a comparison operator. Instead of returning the expected value of 0 (which Excel does), the Office Spreadsheet returns an incorrect result. The following example illustrates this behavior:


   A1: Name         B1: ID       C1: Name     D1: Product
   A2: Bill         B2: 1        C2: Bill     D2: >1
   A3: Bill         B3: 2        C3:          D3:
   A4: =DCOUNT(A1:B3,"ID",C1:D2)

The DCOUNT formula in A4 returns the value 1 instead of 0 as expected. This behavior is also true for other database functions. For example, you could substitute DSUM in this example as follows: =DSUM(A1:B3,"ID",C1:D2). Instead of returning the correct value of 0 (which Excel does), in the Office Spreadsheet, DSUM returns the value 3.

Criteria Range Cannot Include a Formula

When using one of the functions listed above that includes an argument for criteria, the criteria range cannot contain a formula, also known as a computed criteria. The following types of criteria are considered invalid and will cause the database function to return a #VALUE! error value:


   =$A$1=1

   ="department"

   =Min($A$1)

Instead, the criteria range should only include the following operators and values in creating a comparison criteria:


   String
   Value
   Comparison Operators:
      >  (For example, >100)
      <  (For example, <100

DGET Fails with Duplicate Records

The DGET function in the Office Spreadsheet may return #NUM! although a match is found. This is different than what DGET returns on the Excel worksheet.

This problem occurs when DGET finds a duplicate record in the list that matches the search criteria. DGET should allow duplicate records if you are returning corresponding values in a field that does not contain values next to each duplicate record. The following example illustrates this problem:


   A1: Name         B1: ID       C1: Name
   A2: Bill         B2: 1        C2: Bill
   A3: Bill         B3: 
   A4: Steve        B4: 2
   A5: =DGET(A1:B4,2,C1:C2)

The result in the Office Spreadsheet will be #NUM! although Excel will return the value 1. Note that cell B3 is empty. Only one cell in the ID field contains a value corresponding to the duplicate entry in for the Name field.

DCOUNTA Does Not Count Empty Strings

You can use the DCOUNTA function to count all of the nonblank cells in a field that match the conditions you specify. If you enter an empty string using ="" or a single apostrophe ('), unlike Excel, the Office Spreadsheet Component does not count the empty string. Instead it treats an empty string as a blank cell. The following example illustrates this behavior:


   A1: Name         B1: ID       C1: Name
   A2: Bill         B2: '        C2: Bill
   A3: =DCOUNTA(A1:A2,"ID",C1:C2)

The result in cell A3 will be zero, although using this same example in an Excel worksheet will return the value 1.

To return results consistent with Microsoft Excel, do not enter an empty string ("") or ('). If cells in your lookup range contain an empty string, delete the contents of these cells.

COUNTIF and SUMIF Criteria

You can use the functions COUNTIF and SUMIF to count or add cells specified by a given criteria.

Greater Than or Less Than Criteria

When using the COUNTIF or SUMIF function, a criteria can be an expression that includes a greater than (>) or less than (<) sign. For example, the following formula returns a 0 in Excel, but a 1 in an Office Spreadsheet:


   A1: a
   A2: =COUNTIF($A$1,">")

When using the > or < symbols in a criteria, include text or values after the symbol, for compatibility. In the example above, for instance, instead of the criteria of ">" use ">0".

Wildcard in Criteria

The criteria of a COUNTIF OR SUMIF can include a wildcard character such as a question mark (?) to represent a single character or an asterisk (*) to represent multiple characters. However, when you precede a wildcard character with an operator such as an equal sign (=) or greater than and less than symbols (<>) the results may not match the results returned in an Excel worksheet.

For example, the following formula returns a 1 in Excel and a 0 in the Office spreadsheet:


   A1: abc
   A2: =COUNTIF($A$1,"=*")

To correct this problem, when using a wildcard character, do not include an operator in the criteria. For example, you would change the previous example to:


   A1: abc
   A2: =COUNTIF($A$1,"*")

Criteria Begins with an Equal Sign

In an Excel worksheet, a criteria is an expression that may include an equal sign and a wildcard character. For example, the following formula is valid in Excel but invalid in an Office Spreadsheet:


   =COUNTIF($A$1:$A$5,"=a*")

Because the criteria above begins with an equal sign, it will always return a count of 0 in the Office Spreadsheet. A criterion can include a wildcard character and an equal sign, but cannot begin with an equal sign. The following formulas contain valid criteria for the Office Spreadsheet:

This formula searches the range $A$1:$A$5 for any string beginning with the letter "b" (not case sensitive) and adds the corresponding value in the range $B$1:$B$5:
   =SUMIF($A$1:$A$5,"b*",$B$1:$B$5) 
This formula returns a count of strings in which the second character is an equal sign:
   =COUNTIF($A$1:$A$5,"?=*") 
This formula returns a count of strings containing the letter "a" anywhere in the string:
   =COUNTIF($A$1:$A$5,"*a*) 

Using Tilde Character with Lookup Functions

You can search for a value in a list of values and return a value from a column or row you specify using a lookup function. In an Excel worksheet, if you search for a special character such as ?, *, or ~ you must precede it with the tilde character. For example, to search for an asterisk (*) you would specify the lookup function to find ~*. Likewise to search for a tilde character you would precede it with another tilde, ~~.

However, when you search for a tilde character in an Office Spreadsheet, you enter just a single tilde character. To search for other special characters you do need to precede them with a tilde as you would in Excel. The following example illustrates this behavior:


   A1: Character      B1: Value
   A2: ?              B2: 5
   A3: ~              B3: 3
   A4: search for ?   B4: =VLOOKUP("~?",A1:B4,2,0)
   A5: search for ~   B5: =VLOOKUP("~",A1:B4,2,0)

The VLOOKUP in cell B4 would return the value 5 from column B. The result of cell B5 would be a 3 from the second column.

If you export the Office Spreadsheet to Microsoft Excel, the lookup function will not find a match when the search character is a tilde. The lookup function will return the #N/A error value indicating no match is found. In the example above, after you export to Excel you would need to change the formula in cell B5 to include an additional tilde character


   =VLOOKUP("~~",A1:B4,2,0)

in order to work correctly in Excel.

Using Intersecting Ranges in Formulas

In Microsoft Excel, a space acts as the intersection operator. When you enter a space between two ranges, the intersection of those ranges is returned. The Office Spreadsheet also supports using intersecting ranges inside formulas.

Intersecting Ranges Convert to a Single Reference

When you enter a valid intersecting range, the references resolve to a single address. The following example illustrates this behavior:


   A1: Bob        B1: Sue        C1: Pat
   A2: 1          B2: 2          C2: 3
   A3: 10         B3: 12         C3: 14
   A4: =A2:C2 B1:B3

The result of A4 will be the value 2, however the equation changes to the following:

=B2

This automatic formula conversion does not affect the immediate formula; however, in some cases you may not be able to copy and paste the intersection formula to other cells. This behavior occurs if you are using a mix of relative and absolute references. For example, although the following formula uses absolute references, it resolves to a single address:


   A1: Bob        B1: Sue        C1: Pat
   A2: 1          B2: 2          C2: 3
   A3: 10         B3: 12         C3: 14
   A4: =A2:C2 $B$1:$B$3

The formula in A4 changes to =B2. If you copy and paste A4 to cell B4, the formula will then refer to C2. To ensure correct results, do not copy and paste formulas that return an intersection of two ranges in the Office Spreadsheet.

Intersecting Address Does Not Follow Cell

Whenever you enter an intersecting address, if the intersection cannot be found, the result is a #NULL! error. In Microsoft Excel, you can cut and paste a cell from the intersecting range and the formula follows the cut cell. However, the Office Spreadsheet does not follow the cell and does not update the formula. The following example illustrates this problem:


   A1: Bob        B1: Sue        C1: Pat
   A2: 1          B2: 2          C2: 3
   A3: 10         B3: 12         C3: 14
   A4: =A3:C3 B2

The result in A4 is #NULL! because the intersection is not valid. However, if you cut cell B2 and paste it to cell B3, the result of cell A4 is still #NULL!. Notice the formula in A4 is unchanged; it did not follow the cut and paste operation and change the intersecting reference from B2 to B3.

Automatic Date Formatting

When you enter numbers or create a formula, the results may be automatically formatted as a date. For example if you enter the following, the results will appear as a date:

1 2 3 (Notice the spaces between each number.)
This will appear as 1/2/2003 instead of remaining a text string.
2 4 /6 (Notice the space between each number and before the slash.)
This will appear as 2/4/2006 instead of remaining a text string.
=DAY(A1) where A1 contained the date 12/25/1999
This will appear as 1/8/1900 instead of returning the value 25.

NOTE: The functions MONTH and YEAR also return a date format instead of the respective values 12 and 99 in this example.

To correct this problem, format the cells using the General Number format. To change the number format, follow these steps:

Select the cells to change to the General Number format.

Click the Property Toolbox on the toolbar. If the toolbar is not visible, right-click the worksheet and click Property Toolbox.

In the Spreadsheet Property Toolbox, click Format to expand the formatting options. In the Number Format list, click General Number.

Also, when you concatenate (join) a date function to a text string, the date will appear formatted as a date instead of its date value. This calculation behavior is different from Excel.

For example, the following formula


   ="Hello" & DATE(99,5,1)

appears on the Office Spreadsheet as


   Hello5/1/99

and on the Excel worksheet as:


   Hello36281

Precision and Rounding

There are differences in calculating precision with the Office Spreadsheet Component controls, which affect several areas. You will notice variances in rounding and data precision in calculating data, when accuracy extends to many decimal places. Typically, you can observe the differences in precision after five or more decimal places.

ZTEST Function

The ZTEST function returns a different value in the Office Spreadsheet and Microsoft Excel. The result also varies from the example in Excel Help. Although Excel can calculate to 15 decimal places, the degree of precision with ZTEST is limited to fewer than five or six decimal places, which is not significant (1.00E-06).

The following example illustrates this behavior:


   A1: 3
   A2: 6
   A3: 7
   A4: 8
   A5: 6
   A6: 5
   A7: 4
   A8: 2
   A9: 1
  A10: 9
  A11: =ZTEST(A1:A10,4)

The result in the Office Spreadsheet returns 0.090574203 while the same example in the Excel worksheet returns 0.090574259, a difference of 5.56747E-08.

FACT Function

The FACT worksheet function may return a very different value in the Office Spreadsheet than in Excel. This difference is due to a difference in how numbers are rounded up to a corresponding integer, which you can observe after the eighth decimal place in the Excel worksheet. The following example illustrates this behavior:


   =FACT(5.9999998)

The result in the Office Spreadsheet is 120, while the result in the Excel worksheet is 720, because Excel rounds the value 5.9999998 up to the number 6 before evaluating it with the FACT function. The Office Spreadsheet does not round up, and evaluates the number 5 with the FACT function.

ERF and ERFC Functions

The ERF or ERFC worksheet functions may return different values in the Office Spreadsheet, and Excel. The following examples illustrate this behavior:


   =ERF(1)

   This returns the number 0.84270079 in the Office Spreadsheet and 
   0.842700735 in the Excel worksheet.


   =ERFC(1)

   This returns the number 0.15729921 in the Office Spreadsheet and 
   0.157299265 in the Excel worksheet.

REFERENCES

For more information about the Office Spreadsheet Component, click Help on the Microsoft Office Spreadsheet toolbar, type About calculation in a spreadsheet in the Search box of Microsoft Office Spreadsheet Help, and then click List Topics to view the topic.

Additional query words: XL2000

Keywords : kbweb xlui xlformat xlformula xllist
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto