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

 

www.hoffits.com

 

 

 

free html hit counter