|
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 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 |
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 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 |
|
||||||
|
|
|
|
||||||
|
|
|||||||