INFORMATION FUNCTIONS
|
1. CELL:- In Microsoft Excel “CELL” function returns the information
about a given cell. This gives information related to the contents, formatting
or location of the cell.
Syntax of CELL function:-
=CELL(info_type,[reference])
Arguments:-
Info_type:- The type of information to return
about the cell.
Reference:- The reference from which to get
information and it is an optional part.
Example of CELL function:-
The following pictures show the example of the CELL function.
The
following info_type can be classified with the cell function,
|
Info_type |
Description |
|
address |
returns the address of the first cell
in the reference. |
|
col |
returns the column number of the first
cell. |
|
color |
returns the value of the first cell (1
= formatted color and 0 = not formatted color) |
|
contents |
returns the value of the cell
(formulas aren’t returned) |
|
file name |
returns the file name and full path as
text. |
|
format |
returns a code that corresponds to the
number format of the cell. (See the list below) |
|
parentheses |
1 = formatted with parentheses and 0 =
not formatted |
|
prefix |
returns a text value that corresponds
to the level prefix of the cell (see the below list). |
|
protect |
returns 1 = cell reference is locked
and 0 = not locked. |
|
Row |
returns the row number of the first
cell |
|
Type |
returns a text value that corresponds
to the type of data in the cell (“b” = when cell is blank, “i” = contains
text, “v” = cell contains anything else). |
|
Width |
returns the column width of the cell |
|
Prefix |
Description |
|
‘ |
The cell text is left alignment. |
|
“ |
The cell text is right alignment. |
|
^ |
If the cell text is centered. |
|
() |
If the cell text is fill alignment. |
|
FORMAT CODE RETURNED |
FORMAT CODE MEANING |
FORMAT CODE RETURNED |
FORMAT CODE MEANING |
|
G |
General |
P2 |
0.00% |
|
F0 |
Zero |
S2 |
0.00E+00 |
|
,0 |
#,##0 |
D1 |
d-mmm-yy or dd-mmm-yy |
|
F2 |
0 |
D2 |
d-mmm or dd-mmm |
|
,2 |
#,##0.00 |
D3 |
mmm-yy |
|
C0 |
$#,##0_);($#,##0) |
D4 |
m/d/yy or m/d/yy h:mm or mm/dd/yy |
|
C0- |
$#,##0_);[red]($#,##0) |
D5 |
mm/dd |
|
C2 |
$#,##0.00_);($#,##0.00) |
D6 |
h:mm:ss AM/PM |
|
C2- |
$#,##0.00_);[red]($#,##0.00) |
D7 |
h:mm AM/PM |
|
P0 |
0% |
D8 |
h:mm:ss |
2. ERROR.TYPE:- In Microsoft Excel “ERROR.TYPE” function returns an integer value by receiving an error value.
Syntax of ERROR.TYPE function:-
=ERROR.TYPE(error_val)
Argument:-
Error_val:- The error value which to get an error code as integer.
Example of ERROR.TYPE function:-
The following pictures show the example of the ERROR.TYPE function.
3. INFO:- In Microsoft Excel “INFO” function returns information about the current environment like directory, OS version, system, etc.
Syntax of INFO function:-
=INFO(type_text)
Argument:-
Type_text:- The information type to return as text. See the type text table below.
|
Type_text |
Description |
|
num file |
Number of active worksheet in active workbooks. |
|
Release |
Excel version |
|
System |
Operating system name |
|
Osversion |
Operating system version |
|
Origin |
First visible cell at top left corner of sheet (e.g. A:A1) |
|
Recalc |
Recalculation mode |
|
Directory |
Current directory or folder path |
Example of INFO function:-
The following pictures show the example of the INFO function.
4. ISBLANK:- When a cell contains is blank then in Microsoft Excel “ISBLANK” function returns TRUE otherwise FALSE.
Syntax of ISBLANK function:-
=ISBLANK(value)
Argument:-
value:- The value to verify.
Example of ISBLANK function:-
The following pictures show the example of the ISBLANK function.
5. ISERR:- In Microsoft Excel “ISERR” function returns TRUE for any error type except the #N/A error.
Syntax of ISERR function:-
=ISERR(value)
Argument:-
value:- The value to verify any error except #N/A.
Example of ISERR function:-
The following pictures show the example of the ISERR function.
6. ISERROR:- In Microsoft Excel “ISERROR” function returns TRUE for any excel generated error including #N/A .
Syntax of ISERROR function:-
=ISERROR(value)
Argument:-
value:- The value to verify any error.
Example of ISERROR function:-
The following pictures show the example of the ISERROR function.
7. ISEVEN:- In Microsoft Excel “ISEVEN” function returns TRUE if the cell contains even number otherwise returns FALSE for odd number.
Syntax of ISEVEN function:-
=ISEVEN(number)
Argument:-
number:- The number to be tested.
Example of ISEVEN function:-
The following pictures show the example of the ISEVEN function.
8. ISLOGICAL:- In Microsoft Excel “ISLOGICAL” function returns TRUE if the given cell contains logical value (TRUE or FALSE) otherwise returns TRUE for any value.
Syntax of ISLOGICAL function:-
=ISLOGICAL(value)
Argument:-
value:- The value to be tested.
Example of ISLOGICAL function:-
The following pictures show the example of the ISLOGICAL function.
9. ISNA:- In Microsoft Excel “ISNA” function returns TRUE for #N/A error otherwise gives FALSE.
Syntax of ISNA function:-
=ISNA(value)
Argument:-
value:- The value to be tested.
Example of ISNA function:-
The following pictures show the example of the ISNA function.
10. ISNONTEXT:- In Microsoft Excel “ISNONTEXT” function returns TRUE for non-text value otherwise gives FALSE.
Syntax of ISNONTEXT function:-
=ISNONTEXT(value)
Argument:-
value:- The value to be tested.
Example of ISNONTEXT function:-
The following pictures show the example of the ISNONTEXT function.
11. ISNUMBER:- In Microsoft Excel “ISNUMBER” function returns TRUE if the supplied value is a number otherwise FALSE.
Syntax of ISNUMBER function:-
=ISNUMBER(value)
Argument:-
Value:- The value to be tested.
Example of ISNUMBER function:-
The following picture shows the example of the ISNUMBER function.
12. ISODD:- In Microsoft Excel “ISODD” function returns TRUE if the number is odd otherwise FALSE.
Syntax of ISODD function:-
=ISODD(number)
Argument:-
number:- The number to be tested.
Example of ISODD function:-
The following picture shows the example of the ISODD function.
13. ISREF:- When the cell contains valid reference then Microsoft Excel “ISREF” function returns TRUE otherwise FALSE.
Syntax of ISREF function:-
=ISREF(value)
Argument:-
Value:- The value to be tested.
Example of ISREF function:-
The following picture shows the example of the ISREF function.
14. ISTEXT:- When the supplied cell contains text then Microsoft Excel “ISTEXT” function returns TRUE otherwise returns FALSE.
Syntax of ISTEXT function:-
=ISTEXT(value)
Argument:-
Value:- The value to be tested.
Example of ISTEXT function:-
The following picture shows the example of the ISTEXT function.
15. N:- In Microsoft Excel “N” function transforms a non number value to number, dates to serial numbers, TRUE to 1, anything else to “zero(0)”.
Syntax of N function:-
=N(value)
Argument:-
Value:- The value to convert to number.
Example of N function:-
The following picture shows the example of the N function.
16. NA:- In Microsoft Excel “NA” function returns the error value #N/A (means value not available).
Syntax of NA function:-
=NA()
Example of NA function:-
The following picture shows the example of the NA function.
17. TYPE:- In Microsoft Excel “TYPE” function returns an integer representing the data type of a value (1 = number, 2 = text, 4 = logical value, 16 = error value, 64 = array).
Syntax of TYPE function:-
=TYPE(value)
Argument:-
Value:- The value to know its type.
Example of TYPE function:-
The following picture shows the example of the TYPE function.
0 Comments:
Post a Comment