Excel information functions

This article about the introduction of Excel information functions

Information functions are mainly used to display some of the information inside Excel, such as data error information, operating environment parameters, data types, and so on. There are 9 articles.

1.CELL

Purpose: Returns information such as the format, position, or content of a cell in the upper-left corner of a reference area, which is used primarily to maintain compatibility with other spreadsheet programs.

Syntax: CELL (info_type,reference)

Parameters: Info_type is a text value indicating the type of cell information you want. Reference represents the cell for which you want to get information about. If omitted, the information specified in Info_type is returned to the last changed cell.

Instance: the formula "=cell" ("Row", A20) returns 20, and if the A3 cell contains total, the cell ("contents", A3) returns "total."

2.ERROR. TYPE

Purpose: Returns a number that corresponds to an error type, or returns #n/a if there are no errors. You can use Error.type to detect error values in the IF function and return text strings (such as "error occurred") to replace the error values.

Syntax: Error.type (Error_val)

Parameter: Error_val is an error type that needs to get its numeric code. Although Error_val can be the actual error value, it is usually a cell reference, and the cell contains the formula you want to detect.

Note: ERROR. The error code returned by the type function is: #NULL! Returns 1, #DIV/0! returns 2, #value! Returns 3, #REF! Returns 4, #NAME, returns 5, #NUM! Returns 6, #N/A returns 7, and other errors return #n/a.

Instance: If a5=36/0, then the formula "=error." TYPE (A5) "returns 2.

3.INFO

Purpose: Returns information about the current operating environment.

Syntax: INFO (type_text)

Parameter: Type_text is a text that indicates the type of information to be returned. Where "directory" returns the path to the current directory or folder, "Memavail" returns the available memory space (in bytes), "memused" returns the memory space occupied by the data, and "Numfile" returns the number of all currently open worksheets. OSVersion "returns the version number (text) of the current operating system," Recalc "returns the current recalculation (" Automatic "or" manual ")," release "returns the version number (text) of Excel, and" system "returns the operating system name (macintosh=" Mac ", windows=" PCDOs ")," Totmem "returns all the memory space, including the memory space already occupied (in bytes).

Note: The text parameters in the info function must be enclosed in Latin quotation marks.

Instance: Formula "=info" ("OSVersion") "returns" Windows (32-bit) 4.90, =info ("Memavail") returns 1048576.

4.IS class function

Purpose: Includes nine worksheet functions that are used to validate values or reference types. They can verify the type of the numeric value and return TRUE or false based on the value of the parameter. For example, when a value is a blank cell reference, the Isblank function returns the logical value TRUE, or false.

Syntax: ISBLANK (value), Iserr (value), ISERROR (value), islogical (value), ISNA (value), Isnontext (value), Isnumber (value), Isref (value), Istext (value).

Parameter: value is the parameter that needs to be checked. is blank (blank cell), error value, logical value, text, number, reference value, or reference to the name of any of the above parameters.

If the argument in the function is the following, the argument that returns True:isblank is a blank cell, the Iserr argument is any error value (except #n/a), and the IsError parameter is any error value (#N/A, #value!, #REF!, #DIV/0!, # num!, #NAME, or #null!), islogical parameter is a logical value, ISNA parameter is error value #n/a,isnontext parameter is any text content (this function returns True when the value is a blank cell), isnumber parameter is a number , the Isref argument is a reference, and the Istext argument is text.

Note: The parameter value of the IS class function is not convertible. In most other functions that require numbers, the text "19" is converted to the number 19. However, in the formula Isnumber ("19"), "19" is converted from a literal value to another type of value, but returns false.

The IS class function is useful for testing the results of formula calculations, and it can be combined with a function if to provide a way to isolate error values in a formula. (www.3lian.com)

Instance: the formula "=isblank (") "returns FALSE,=ISREF (A5) returns True (where A5 is a blank cell). If you need to calculate the average of the A1:A4 region, but you cannot determine whether the cell contains a number, the formula average (A1:A4) returns the error value #div/0!. To cope with this situation, you can use the formula "=if" (ISERROR (AVERAGE (A1:A4)), the reference contains blank cells, AVERAGE (A1:A4)) to detect possible errors.