Peter
Noneley 






Excel Function
Dictionary 

Updated Oct 2009  no macros or VBA code  smaller file
size 

The Excel Function
Dictionary contains over 150 examples of functions. 



Its free. 

Click to Download 



Any problems with
the file, let me know and I will fix it. 



Contact 

mail@xlfdic.com 



Cardiff. Wales. UK. 



Help on the following
functions. 


Age Calculation 
Using DATEDIF() 

AutoSum shortcut key 
Using Alt and = 

Brackets in formula 
Sample 

FileName formula 
Using MID() CELL() and FIND() 

Instant Charts 
Using F11 

Ordering Stock 
Stock Ordering 

Percentages 
How to calculate various percentages 

Project Dates 
Example using date calculation. 

Show all formula 
Using Ctrl and ` 

Split ForenameSurname 
Using LEFT() RIGHT() FIND() SUBSTITUTE() 

Time Calculation 
How to calculate time. 

TimeSheet For Flexi 
Example flexi time sheet. 

ABS 
Returns the absolute value of a number 

AND 
Returns TRUE if all its arguments are TRUE 

AVERAGE 
Returns the average of its arguments 

BIN2DEC 
Converts a binary number to decimal 

CEILING 
Rounds a number to the nearest integer or to the nearest multiple of
significance 

CELL 
Returns information about the formatting, location, or contents of a cell 

CHAR 
Returns the character specified by the code number 

CHOOSE 
Chooses a value from a list of values 

CLEAN 
Removes all nonprintable characters from text 

CODE 
Returns a numeric code for the first character in a text string 

COMBIN 
Returns the number of combinations for a given number of objects 

CONCATENATE 
Joins several text items into one text item 

CONVERT 
Converts a number from one measurement system to another 

CORREL 
Returns the correlation coefficient between two data sets 

COUNT 
Counts how many numbers are in the list of arguments 

COUNTA 
Counts how many values are in the list of arguments 

COUNTBLANK 
Counts the number of blank cells within a range 

COUNTIF 
Counts the number of nonblank cells within a range that meet the given
criteria 

DATE 
Returns the serial number of a particular date 

DATEDIF 
Calculates the difference between two dates. Undocumented in v5/7/97 

DATEVALUE 
Converts a date in the form of text to a serial number 

DAVERAGE 
Returns the average of selected database entries 

DAY 
Converts a serial number to a day of the month 

DAYS360 
Calculates the number of days between two dates based on a 360day year 

DB 
Returns the depreciation of an asset
for a specified period using the fixeddeclining balance method 

DCOUNT 
Counts the cells that contain numbers in a database 

DCOUNTA 
Counts nonblank cells in a database 

DEC2BIN 
Converts a decimal number to binary 

DEC2HEX 
Converts a decimal number to hexadecimal 

DELTA 
Tests whether two values are equal 

DGET 
Extracts from a database a single record that matches the specified
criteria 

DMAX 
Returns the maximum value from selected database entries 

DMIN 
Returns the minimum value from selected database entries 

DOLLAR 
Converts a number to text, using currency format 

DSUM 
Adds the numbers in the field column
of records in the database that match the criteria 

EDATE 
Returns the serial number of the
date that is the indicated number of months before or after the start date 

EOMONTH 
Returns the serial number of the
last day of the month before or after a specified number of months 

ERROR.TYPE 
Returns a number corresponding to an error type 

EVEN 
Rounds a number up to the nearest even integer 

EXACT 
Checks to see if two text values are identical 

FACT 
Returns the factorial of a number 

FIND 
Finds one text value within another (casesensitive) 

FIXED 
Formats a number as text with a fixed number of decimals 

FLOOR 
Rounds a number down, toward zero 

FORECAST 
Returns a value along a linear trend 

FREQUENCY 
Returns a frequency distribution as a vertical array 

GCD 
Returns the greatest common divisor 

GESTEP 
Tests whether a number is greater than a threshold value 

GROWTH 
Returns values along an exponential trend 

HEX2DEC 
Converts a hexadecimal number to decimal 

HLOOKUP 
Looks in the top row of an array and returns the value of the indicated
cell 

HOUR 
Converts a serial number to an hour 

IF 
Specifies a logical test to perform 

INDEX 
Uses an index to choose a value from a reference or array 

INDIRECT 
Returns a reference indicated by a text value 

INFO 
Returns information about the current operating environment 

INT 
Rounds a number down to the nearest integer 

ISBLANK 
Returns TRUE if the value is blank 

ISERR 
Returns TRUE if the value is any error value except #N/A 

ISERROR 
Returns TRUE if the value is any error value 

ISEVEN 
Returns TRUE if the number is even 

ISLOGICAL 
Returns TRUE if the value is a logical value 

ISNA 
Returns TRUE if the value is the #N/A error value 

ISNONTEXT 
Returns TRUE if the value is not text 

ISNUMBER 
Returns TRUE if the value is a number 

ISODD 
Returns TRUE if the number is odd 

ISREF 
Returns TRUE if the value is a reference 

ISTEXT 
Returns TRUE if the value is text 

LARGE 
Returns the kth largest value in a data set 

LCM 
Returns the least common multiple 

LEFT 
Returns the leftmost characters from a text value 

LEN 
Returns the number of characters in a text string 

LOOKUP (vector) 
Looks up values in a vector or array 

LOWER 
Converts text to lowercase 

MATCH 
Looks up values in a reference or array 

MAX 
Returns the maximum value in a list of arguments 

MEDIAN 
Returns the median of the given numbers 

MID 
Returns a specific number of
characters from a text string starting at the position you specify 

MIN 
Returns the minimum value in a list of arguments 

MINUTE 
Converts a serial number to a minute 

MINVERSE 
Returns the matrix inverse of an array 

MMULT 
Returns the matrix product of two arrays 

MOD 
Returns the remainder from division 

MODE 
Returns the most common value in a data set 

MONTH 
Converts a serial number to a month 

MROUND 
Returns a number rounded to the desired multiple 

N 
Returns a value converted to a number 

NA 
Returns the error value #N/A 

NETWORKDAYS 
Returns the number of whole workdays between two dates 

NOT 
Reverses the logic of its argument 

NOW 
Returns the serial number of the current date and time 

ODD 
Rounds a number up to the nearest odd integer 

OR 
Returns TRUE if any argument is TRUE 

PERMUT 
Returns the number of permutations for a given number of objects 

PI 
Returns the value of Pi 

POWER 
Returns the result of a number raised to a power 

PRODUCT 
Multiplies its arguments 

PROPER 
Capitalises the first letter in each word of a text value 

QUARTILE 
Returns the quartile of a data set 

QUOTIENT 
Returns the integer portion of a division 

RAND 
Returns a random number between 0 and 1 

RANDBETWEEN 
Returns a random number between the numbers you specify 

RANK 
Returns the rank of a number in a list of numbers 

REPLACE 
Replaces characters within text 

REPT 
Repeats text a given number of times 

RIGHT 
Returns the rightmost characters from a text value 

ROMAN 
Converts an arabic numeral to roman, as text 

ROUND 
Rounds a number to a specified number of digits 

ROUNDDOWN 
Rounds a number down, toward zero 

ROUNDUP 
Rounds a number up, away from zero 

SECOND 
Converts a serial number to a second 

SIGN 
Returns the sign of a number 

SLN 
Returns the straightline depreciation of an asset for one period 

SMALL 
Returns the kth smallest value in a data set 

STDEV 
Estimates standard deviation based on a sample 

STDEVP 
Calculates standard deviation based on the entire population 

SUBSTITUTE 
Substitutes new text for old text in a text string 

SUBTOTAL 
Returns a subtotal in a list or database 

SUM 
Adds its arguments 

SUM_as_Running_Total 
Sample 

SUM_using_names 
Using SUM(jan) 

SUM_with_OFFSET 
Sample 

SUMIF 
Adds the cells specified by a given criteria 

SUMPRODUCT 
Returns the sum of the products of corresponding array components 

SYD 
Returns the sumofyears' digits depreciation of an asset for a specified
period 

T 
Converts its arguments to text 

TEXT 
Formats a number and converts it to text 

TIME 
Returns the serial number of a particular time 

Timesheet 
Sample 

TIMEVALUE 
Converts a time in the form of text to a serial number 

TODAY 
Returns the serial number of today's date 

TRANSPOSE 
Returns the transpose of an array 

TREND 
Returns values along a linear trend 

TRIM 
Removes spaces from text 

TRUNC 
Truncates a number to an integer 

TYPE 
Returns a number indicating the data type of a value 

UPPER 
Converts text to uppercase 

VALUE 
Converts a text argument to a number 

VAR 
Estimates variance based on a sample 

VARP 
Calculates variance based on the entire population 

VLOOKUP 
Looks in the first column of an
array and moves across the row to return the value of a cell 

WEEKDAY 
Converts a serial number to a day of the week 

WORKDAY 
Returns the serial number of the
date before or after a specified number of workdays 

YEAR 
Converts a serial number to a year 

YEARFRAC 
Returns the year fraction
representing the number of whole days between start_date and end_date 

www.hoffits.com 













