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.
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 360-day year
DB Returns the depreciation of an asset for a specified period using the fixed-declining 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 (case-sensitive)
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 k-th 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 straight-line depreciation of an asset for one period
SMALL Returns the k-th 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_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 sum-of-years' 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