Excel Functions and Formulas

 

 

Excel tips on functions and formulas

Excel Functions: Examples and Tutorial

Excel macros (VBA)

Excel and VBA Expert

Excel fonctions et formules

Excel Functions and Formulas

 

 

Lesson 1: Excel Spreadsheets  Information Functions (17)

Functions What it Does
ISERROR Returns TRUE if the value is any error value
CELL  Returns information about the formatting, location, or contents of a cell
ISNUMBER Returns TRUE if the value is a number
ISTEXT Returns TRUE if the value is text
ERROR.TYPE Returns a number corresponding to an error type
INFO Returns information about the current operating environment
ISBLANK  Returns TRUE if the value is BLANK
ISERR Returns TRUE if the value is any error value except #N/A
ISEVEN Returns TRUE if the number is even
ISLOGICAL Returns TRUE if the value is a logical value
ISNONTEXT Returns TRUE if the value is not text
ISNA Returns TRUE if the value is the #N/A error value
ISODD Returns TRUE if the value is odd
ISREF Returns TRUE if the value is a reference
N Returns a value converted to a number
NA  Returns the error value #N/A
TYPE Returns a number indicating the data type of a value

Examples of Basic Information Formulas

CELL, MID, FIND

If you want the name and path of the active spreadsheet to be entered automatically in a cell, use the formula: =CELL("filename" )
if you want only the filename use :
=MID(CELL("filename" ,A1),FIND("[" ,CELL("filename" ,A1))+1,FIND("]" ,CELL("filename" ,A1))-FIND("[" ,CELL("filename",A1))-1)

ISERROR/ISNA

When a formula refers to a cell in which you have another formula, always use the ISERROR function to avoid trashing the last formula with a " #DIV/0" or a " #VALUE" or a " #N/A" .
=IF(ISERROR(B1/A1),"",(B1/A1))
if the value of cell A1 is 0, the cell in which you have put the above formula will be empty and not carry a value of #DIV/0.
=IF(ISERROR(B1/A1),0,(B1/A1))
if the value of cell A1 is 0, the value of the cell in which you have put the above formula will be 0 and not #DIV/0.
I also use the ISERROR rather then the ISNA function when I work with
INDEX/MATCH.

IF, ISNUM, LEFT  and MID

In UK all postal codes start by a prefix of one or two letters. My correspondent wanted a formula to extract the prefixes so he could make a list of them. With the postal codes in column one the following formula in column 2 would do the job.
=IF(ISNUMBER(MID(A1,2,1)*1),LEFT(A1,1),LEFT(A1,2))
Depending on the number of characters in the prefix the formula should return the first  character from the left or the first 2  characters from the left: LEFT(A1,1) or LEFT(A1,2)
Before any of these solutions is applied we must check if there are one or two letters at the beginning of the postal code. To do so we will check if the second character
MID(A1,2,1)  is a number. The problem here is that any character from a text string is consider as a letter by Excel. Postal codes, serial numbers and others that include a letter or are formatted as text  are text by nature . So we multiply the second character by 1. If the character is a digit  to begin with it becomes a number but if it is a letter it doesn't: ISNUMBER(MID(A1,2,1)*1). 

Information Functions in Excel
Links to the Top of the Page


Excel Functions and Formulas Sitemap

Downloadable Tutorials, Examples and Templates

 

 

Excel tips on functions and formulas

Excel Functions: Examples and Tutorial

Excel macros (VBA)

Excel and VBA Expert

Excel fonctions et formules