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 







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 







