Differences between CELF and Excel sheet specifications

This page describes the main differences in specifications for features and functions that exist in both CELF sheets and Excel sheets.

Attention

  • The contents of this page do not cover all the specification differences between CELF and Excel.
  • Depending on the version of Excel, the behavior may differ from the explanation on this page.

Absolute reference cells after sorting by filter button

In CELF, when sorting by a filter button, if there is an absolute reference (such as $A$1) in the cell that will be moved to a different row by the sorting, the referenced value will change as if it were a relative reference.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
../../../_images/img_01.en158.png
Excel sheet）
../../../_images/img_02.en144.png

Numeric (floating point) precision

CELF and Excel handle numbers as floating-point numbers according to IEEE754, which has limited precision.
For example, if you compare the calculation result of "11.3 - 11" with "0.3", the comparison result will be "FALSE" in both the CELF sheet and the Excel sheet.
However, in some cases, floating-point calculation results may differ between CELF and Excel due to differences in the correction of rounding errors between CELF and Excel.
For example, if you compare the calculation result of "0.1+0.1+0.1" with "0.3", the result will be different between CELF and Excel.
CELF sheet)
The comparison result of the formula "=0.1+0.1+0.1=0.3" is "FALSE".
../../../_images/img_33.en9.png
Excel sheet）
The comparison result of the formula "=0.1+0.1+0.1=0.3" is "TRUE".
../../../_images/img_34.en9.png

Handling of "February 29" (leap year) in 1900

Excel handles the year 1900 as a leap year, but CELF does not handles the year 1900 as a leap year. (The year 1900 is not a leap year, but Excel can handle "1900-02-29".)
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
If the formula "=DATE(1900,3,0)" is entered to find the end of the month "February 1900" with the DATE function, "1900-02-28" will be displayed.
../../../_images/img_03.en117.png
Excel sheet）
If the formula "=DATE(1900,3,0)" is entered to find the end of the month "February 1900" with the DATE function, "1900-02-29" will be displayed.
../../../_images/img_04.en105.png

See also

See the Microsoft Troubleshooting on why Excel evaluates the year 1900 as a leap year.


Behavior of numbers with more than 16 significant digits

In Excel, you cannot enter a number that exceeds 16 significant digits in a cell value or formula, but in CELF, you can specify a number that exceeds 16 significant digits.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
If "1234567890123456" is entered in the cell, "1234567890123456" will be displayed.

Attention

However, the following functions round the value to 15 significant digits and then evaluate the value or show the result.

  • ROUND
  • ROUNDDOWN
  • ROUNDUP
  • MROUND
  • FLOOR
  • CEILING
  • INT
Excel sheet）
If "1234567890123456" is entered in the cell, "1234567890123450" will be displayed.

Operation when comparing a "number" with a "numeric string" using the comparison operator

In CELF, when a comparison operator such as the equal operator is used to compare a "number" with a "numeric string", the "numeric string" is handled as a number and the expression is evaluated.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
If you enter '=123="123"' in the cell, the formula evaluates to TRUE.
Excel sheet）
If you enter '=123="123"' in the cell, the formula evaluates to FALSE.

Automatic update of cells that reference Cut ＆ Pasted cells

In CELF, when cells are cut and pasted, the references to the original cells are not automatically updated.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
../../../_images/img_05.en94.png

Tip

This behavior also applies to dragging and dropping cells.

Excel sheet）
../../../_images/img_06.en78.png

Behavior when the cell value specified as the function argument is blank

In CELF, if the value of a cell specified as a function argument is blank, the cell value is treated as an empty string ("").
Therefore, for functions that expect non-string values such as numbers or dates as arguments, the results may differ from those in Excel.

ex.) When a blank cell is specified as the argument (serial value) of the DAY function
CELF sheet)
Cells with blank value are handled as empty string (""), so they are evaluated as " =DAY("")" and the result is "#VALUE!".
../../../_images/img_35.en6.png
Excel sheet）
Cells with blank value are handled as "0", so they are evaluated as " =DAY(0)" and the result is "0".
../../../_images/img_36.en4.png

Attention

To get the same result as in Excel, make sure the cell value is not a blank or an empty string ("").

About spill and arrays

CELF does not support Excel’s spill and arrays. Therefore, specifying an array as an argument will not work in CELF.
Additionally, when the result of a function consists of multiple values, only the first one is displayed.

ex.) When "0" is specified for the 2nd argument (row number) of the INDEX function

CELF sheet)
../../../_images/img_37.en3.png
Excel sheet）
../../../_images/img_38.en2.png

AVERAGEA function behavior

Difference in behavior when there are cells with blank value

When the CELF AVERAGEA function contains a cell with a blank value in the argument, the blank cell is treated as "0" and is counted in the average.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
Cells with blank value are handled as "0", so they are evaluated as "(100 + 0 + 200) / 3" and the result is "100".
../../../_images/img_07.en74.png
Excel sheet）
Cells with blank value are excluded, so they evaluate to "(100 + 200) / 2" and the result is "150".
../../../_images/img_08.en70.png

COUNTA function behavior

Difference in behavior when a formula exists in the specified range

The COUNTA function in CELF count cells by excluding a cell when the result of a formula in it is an empty string.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
If the result of the formula is an empty string, the result will be displayed without counting that cell.
../../../_images/img_09.en62.png
Excel sheet）
Always count cells and display the result, regardless of whether result of the forumla is an empty string or not.
../../../_images/img_10.en54.png

ISBLANK function behavior

Difference in behavior when the referenced cell is a formula

The ISBLANK function in CELF will result in "true" if a cell is specified with a formula that returns an empty string.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
If the result of the formula is an empty string, the result is "true".
../../../_images/img_11.en52.png
Excel sheet）
Regardless of whether the result of the formula is an empty string, the result will be "false".
../../../_images/img_12.en48.png

SUBTOTAL function behavior

Difference in behavior of aggregation method （1st argument）

If you specify "1 to 11" as the aggregation method (first argument) of the SUBTOTAL function of CELF, hidden rows will not be included in the calculation.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
If you specify the aggregation method "9" (SUM), the total will be calculated excluding hidden rows.
../../../_images/img_13.en44.png
Excel sheet）
If you specify the aggregation method "9" (SUM), the total will include hidden rows.
../../../_images/img_14.en39.png

SUMIF function behavior

Difference in behavior when an empty cell( blank, or "") is specified for the search condition (2nd argument)

The SUMIF function in CELF will match cells with empty value(blank or "") if the search condition specifies a cell with empty value.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
Matches cells in the range with empty value(blank or "") and the result is the sum of cells with empty values.
../../../_images/img_15.en36.png
Excel sheet）
It will not match any cell with empty value in the range and the result will be "0".
../../../_images/img_16.en34.png

COUNTIF function behavior

Difference in behavior when an empty cell( blank, or "") is specified for the search condition (2nd argument)

The COUNTIF function in CELF will match a cell with empty value(blank or "") if the search condition specifies a cell with empty value.
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
Matches cells with empty value in the range and the result is the number of cells with empty value.
../../../_images/img_17.en30.png
Excel sheet）
It will not match any cell with empty value in the range and the result will be "0".
../../../_images/img_18.en28.png

MROUND function behavior

Difference in behavior when "0" is specified for the multiplier (2nd argument)

The MROUND function of CELF prints a "#DIV/0!" error if "0" is specified for the multiple (2nd argument).
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
The result of the formula "=MROUND(1,0)" is "#DIV/0!".
Excel sheet）
The result of the formula "=MROUND(1,0)" is "0".

CEILING function behavior

Difference in behavior when "0" is specified for the significance (2nd argument)

The CEILING function of CELF becomes "#DIV/0!" when "0" is specified as the significance (2nd argument).
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
The result of the formula "=CEILING(1,0)" is "#DIV/0!".
Excel sheet）
The result of the formula "=CEILING(1,0)" is "0".

FLOOR function behavior

Difference in behavior when "0" is specified for the significance (2nd argument)

The FLOOR function of CELF becomes "#DIV/0!" when "0" is specified for the number (1st argument) and "0" for the significance (2nd argument).
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
The result of the formula "=FLOOR(0,0)" is "#DIV/0!".
Excel sheet）
The result of the formula "=FLOOR(0,0)" is "0".

TRIM function behavior

Different whitespace characters to be deleted

The TRIM function in CELF removes Unicode whitespace characters (e.g., horizontal tabs) and non-ASCII whitespace characters (e.g., full-width spaces) in addition to half-width spaces.
ex.) When the string "*□ABC□*" is specified in the TRIM function
Here, half-width spaces are represented by "" and full-width spaces are represented by "□".
CELF sheet)
The result is "ABC".
Excel sheet）
The result is "□ABC□".

Difference in behavior when there is a half-width space between characters

The TRIM function in CELF does not remove spaces between characters.
ex.) If the string "ABC***DEF" is specified in the TRIM function
Here, half-width spaces are represented by "".
CELF sheet)
The result will be "ABC***DEF" (no change).
Excel sheet）
The result will be "ABC*DEF" (the consecutive spaces between the characters will be deleted to one).

VLOOKUP function behavior

Difference in behavior when "" or a cell with "empty value" is specified as the lookup value (1st argument)

The VLOOKUP function in CELF will match cells with empty value ("" or blank) if the lookup value (1st argument) is "" or a cell with blank value.
CELF sheet)
If you specify a blank cell as the lookup value, it will match cells with empty value (blank or empty string "").
../../../_images/img_19.en26.png
Excel sheet）
If you specify a cell with an empty value ("" or blank) as the lookup value, it will not match a cell with an empty value and the result will be "#N/A".
../../../_images/img_20.en24.png

Differences in behavior when the values to be compared are "numbers" and "numeric strings"

The VLOOKUP function in CELF compares lookup values and range(table array in Excel) by handling "numeric strings" as number values.
So, if the lookup value is the string "123", it is considered a match if there is a number "123" in the range (table array in Excel).
CELF sheet)
If the lookup value is the string "2", it will match the cell with number 2 and the result will be "Apple".
../../../_images/img_21.en22.png

Tip

If the lookup value is the number 2, it will match not only the number 2 but also cells that contain the strings "2" and "002".

Excel sheet）
If the lookup value is the string "2", it will not match the number 2 in table array and the result will be "#N/A".
../../../_images/img_22.en19.png

HLOOKUP function behavior

Difference in behavior when "" or a cell with "empty value" is specified as the lookup value (1st argument)

The HLOOKUP function in CELF will match cells with "empty value" if the lookup value (1st argument) is "" (empty string) or a cell with "empty value".
CELF sheet)
If you specify a blank cell as the lookup value, it will match cells with empty value (blank or empty string "").
../../../_images/img_23.en18.png
Excel sheet）
If you specify a cell with an empty value ("" or blank) as the lookup value, it will not match a cell with an empty value and the result will be "#N/A".
../../../_images/img_24.en18.png

Differences in behavior when the values to be compared are "numbers" and "numeric strings"

The HLOOKUP function in CELF compares lookup values and range (table array in Excel) by handling "numeric strings" as number values.
So, if the lookup value is the string "123", it is considered a match if there is a number "123" in the range (table array in Excel).
CELF sheet)
If the lookup value is the string "2", it will match the cell with number 2 and the result will be "Apple".
../../../_images/img_25.en16.png
Excel sheet）
If the lookup value is the string "2", it will not match the number 2 in table array and the result will be "#N/A".
../../../_images/img_26.en10.png

XLOOKUP function behavior

Differences in behavior when the values to be compared are "numbers" and "numeric strings"

The XLOOKUP function in CELF compares lookup values and range (array or range to search in Excel) by handling "numeric strings" as number values.
So, if the lookup value is the string "123", it is considered a match if there is a number "123" in the range (table array in Excel).
CELF sheet)
If the lookup value is the string "2", it will match the cell with number 2 and the result will be "Apple".
../../../_images/img_27.en13.png
Excel sheet）
If the lookup value is the string "2", it will not match the number 2 in table array and the result will be "#N/A".
../../../_images/img_28.en13.png

Difference in the value returned by the return array (3rd argument)

The XLOOKUP function in CELF returns cell values in the return array (3rd argument), not cell references.
Therefore, you cannot nest the XLOOKUP function in functions that require a cell reference as an argument, such as the ROW function.
CELF sheet)
If an XLOOKUP function is specified as the reference for the ROW function, the ROW function will not be evaluated even if there is a value matching the lookup value.
../../../_images/img_29.en11.png
Excel sheet）
If an XLOOKUP function is specified as the reference for the ROW function, the ROW function will evaluate the matched value if it matches the lookup value.
../../../_images/img_30.en11.png

Difference in behavior when "2" is specified for the match mode (5th argument)

The XLOOKUP function in CELF does not support the "2" (wildcard match) in the match mode (5th argument).
If "2" is specified for the match mode, the result will be "#VALUE!".
CELF sheet)
If "2" is specified for the match mode (5th argument), the result will be "#VALUE!".
../../../_images/img_31.en8.png

Tip

If you want to perform a fuzzy search such as a partial match, please consider methods like using the "Get one record from table" action to search.

Excel sheet）
If "2" is specified for the match mode (5th argument), fuzzy search will be performed.
../../../_images/img_32.en8.png

MATCH function behavior

Differences in behavior when specifying "0" for the match type (third argument)

In Excel, the MATCH function allows the use of wildcards for the lookup value when "0" is specified for the match type (third argument). However, the MATCH function in CELF does not support this.Therefore, even when using wildcards, the search is performed with the entered string as it is, resulting in "#N/A".

CELF sheet)
When a string that uses wildcards is specified as the lookup value, it searches for the exact input string, resulting in "#N/A".
../../../_images/img_39.en3.png

Tip

If you want to perform a fuzzy search such as a partial match, please consider methods like using the "Get one record from table" action to search.

Excel sheet）
When a string that uses wildcards is specified as the lookup value, a fuzzy search is performed.
../../../_images/img_40.en3.png

ADDRESS function behavior

Differences in behavior when the sheet name (fifth argument) is specified, but the reference type (third argument) or reference style (fourth argument) is omitted.

In CELF’s ADDRESS function if the sheet name (fifth argument) is specified but either the reference type (third argument) or the reference style (fourth argument) is omitted, the result will be "#VALUE!".
The following is an example of the behavior of CELF and Excel with different results.
CELF sheet)
If the reference type (third argument) is omitted, the result will be "#VALUE!".
../../../_images/img_411.png

Tip

If the sheet name (fifth argument) is specified, providing both the reference type (third argument) andthe reference style (fourth argument) ensures that the result matches that of Excel.

Excel sheet）
When the reference type (third argument) is omitted, the result will be displayed as "SHEET!$B$1".
../../../_images/img_421.png