Excel Functions

A Guide to Excel Functions

The following Excel functions are broken out by category. The optional arguments are in brackets.

Date & Time Functions - Beginner

Function	Description	Arguments
DATE	Creates a date with day, month, and year.	Year, Month, Day
DATEDIF	Returns days, months, years between two dates.	Start_Date, End_Date, Unit
DATEVALUE	Converts a date in text format to an Excel date format.	Date_Text
DAY	Returns the day as a number (1 - 31)	Date
DAYS	Returns the days between two dates.	End_Date, Start_Date
DAYS360	Returns days between 2 dates in a 360-day year.	Start_Date, End_Date, [Method]
HOUR	Returns the hour as a number (0 - 23)	Serial_Number
MINUTE	Returns minutes as a number (0 - 59)	Serial_Number
MONTH	Returns the month as a number (1 - 12)	Date
NOW	Returns the current date and time.	No arguments required.
SECOND	Returns the second as a number (0 - 59)	Serial_Number
TODAY	Returns the current date.	No arguments required.
WEEKDAY	Returns the day of the week as a number.	Serial_Number, [Return_Type]
WORKDAY	Returns a date n working days in the past or future.	Start_Date, Days, [Holidays]
YEAR	Returns the year from a user provided date.	Date

Date & Time Functions - Advanced

Function	Description	Arguments
EDATE	Shifts date n months in the future or past	Start_Date, Months
EOMONTH	Returns last day of the month n months in the future or past.	Start_Date, Months
NETWORKDAYS	Returns the number of working days between two dates.	Start_Date, End_Date, [Holidays]
ISOWEEKNUM	Returns the ISO week number for a given date.	Date
NETWORKDAYS.INTL	Returns the number of working days between two dates.	Start_Date, End_Date, [Weekend], [Holidays]
TIME	Create a time with user provided hours, minutes, and seconds.	Hour, Minute, Second
TIMEVALUE	Returns the time from a text string.	Time_Text
WEEKNUM	Returns the week number for a given date.	Serial_Num, [Return_Type]
WORKDAY.INTL	Returns the date n working days in the future or past.	Start_Date, Days, [Weekend], [Holidays]
YEARFRAC	Returns the fraction of a year between two dates.	Start_Date, End_Date, [Basis]

Logical Functions - Beginner

Function	Description	Arguments
AND	Test multiple conditions with an AND statement	Logical1, [Logical2], [...]
FALSE	Returns the logical value False.	No arguments required.
IF	Tests for specific conditions.	Logical_Test, [Value_if_True], [Value_if_False]
IFNA	Identifies #N/A errors	Value, Value_if_NA
NOT	Reverses arguments and results	Logical
OR	Test multiple conditions with an OR statement	>Logical1, [Logical2], [...]
TRUE	Returns the logical value True.	No arguments required.

Logical Functions - Advanced

Function	Description	Arguments
IFERROR	Identifies and handles errors	Value, Value_if_Error
IFS	Tests multiple conditions, returning the first True	Test1, Value1, [Test2], [Value2], [...]
SWITCH	Matches values and returns the first match	Expression, Value1, Result1, [Value2], [Result2], [...]
XOR	Performs an exclusive OR	Logical1, [Logical2], [...]

Math Functions - Beginner

Function	Description	Arguments
ABS	Returns the absolute value of a number.	Number
CEILING	Returns number rounded up to nearest multiple.	Number, Multiple
CEILING.MATH	Returns number rounded up to nearest multiple.	Number, [Significance], [Mode]
CEILING.PRECISE	Returns number rounded up to nearest multiple.	Number, [Significance]
DECIMAL	Converts alpha numeric numbers to decimals.	Number, Radix
EVEN	Returns a number rounded up to the next even integer.	Number
GCD	Returns the greatest common divisor of two or more numbers.	Number1, [Number2], [...]
INT	Returns the integer portion of a decimal by rounding down.	Number
LCM	Returns the least common multiple of two or more numbers.	Number1, [Number2], [...]
FLOOR	Returns a number rounded down to the nearest specified multiple.	Number, Multiple
FLOOR.MATH	Returns a number rounded down to the nearest multiple.	Number, [Significance], [Mode]
FLOOR.PRECISE	Returns a number rounded down to the nearest multiple.	Number, [Significance]
PI	Returns the value of pi	No arguments required.
ODD	Returns a number rounded up to the next odd integer.	Number
LN	Returns the natural logarithm of a number.	Number
LOG	Returns the logarithm of a number.	Number, [Base]
MOD	Returns the remainder from division.	Number, Divisor
POWER	Raises a number to a power.	Number, Power
PRODUCT	Returns the product of supplied numbers.	Number1, [Number2], [...]
QUOTIENT	Returns the quotient without the remainder.	Numerator, Denominator
ROUND	Returns a number rounded to a given number of digits.	Number, Num_Digits
ROUNDDOWN	Returns a number rounded down to a given number of digits.	Number, Num_Digits
ROUNDUP	Returns a number rounded up to a given number of digits.	Number, Num_Digits
SQRT	Returns the positive square root of a number.	Number
SUBTOTAL	Returns the subtotal in a list or database.	Function_Num, Ref1, [Ref2], [...]
SUM	Returns the sum of numbers.	Number1, [Number2], [...]
SUMIF	Returns the sum of numbers in a range that meet specific criteria.	Range, Criteria, [Sum_Range]
SUMIFS	Returns the sum of numbers in a range that meet multiple criteria.	Sum_Range, Range1, Criteria1, [Range2], [Criteria2], [...]
SUMPRODUCT	Performs an exclusive OR	Logical1, [Logical2], [...]
TRUNC	Truncates a number to a given precision.	Number, [Num_Digits]

Math Functions - Advanced

Function	Description	Arguments
AGGREGATE	Returns the aggregate calculation.	Function_Num, Options, Ref1, Ref2
ARABIC	Convert Roman numerals to Arabic numerals.	Roman_Text
ASIN	Returns the angle of a triangle.	Number
COS	Returns the cosine of an angle.	Number
ROMAN	Convert numbers to Roman numerals.	Number, [Form]
DEGREES	Converts radians to degrees.	Angle
EXP	Returns the value of e raised to a number.	Number
FACT	Returns the factorial of a number.	Number
FACTDOUBLE	Returns the double factorial of a number.	Number
LOG10	Returns the base-10 logarithm of a number.	Number, [Base]
MROUND	Returns a number rounded to the nearest specified multiple.	Number, Multiple
MDETERM	Returns the matrix determinant of an array.	Array
MINVERSE	Returns the inverse matrix of an array.	Array
MUNIT	Returns the unit matrix for a given dimension.	Dimension
RADIANS	Converts degrees to radians.	Angle
RAND	Returns a random number between 0 (zero) and 1.	No argument required.
RANDBETWEEN	Returns a random number between two specified values.	Bottom, Top
SIGN	Returns the sign of a number.	Number
SIN	Returns the sine of an angle.	Number
TAN	Returns the tangent of an angle.	Number

Lookup & Reference - Beginner

Function	Description	Arguments
CHOOSE	Returns a value from a list based on position.	Index_Num, Value1, [Value2], [...]
COLUMN	Returns the column number of a reference.	[Reference]
COLUMNS	Returns the number of columns in an array or reference.	Array
FORMULATEXT	Returns the formula in a cell.	Reference
HYPERLINK	Creates a clickable link.	Link_Location, [Friendly_Name]
ROW	Returns the row number of a reference.	[Reference]
ROWS	Returns the number of rows in an array or reference.	Array
LOOKUP	Looks up a value in a one column range.	Lookup_Value, Lokoup_Vector, [Result_Vector]
TRANSPOSE	Flips the orientation of a range of cells.	Array
VLOOKUP	Lookup a value in a table by matching on the first column.	Value, Table, Col_Index, [Range_Lookup]
HLOOKUP	Lookup a value in a table by matching on the first row.	Value, Table, Row_Index, [Range_Lookup]

Lookup & Reference - Advanced

Function	Description	Arguments
ADDRESS	Creates a cell address from a given row and cell.	Rom_Num, Col_Num, [Abs_Num], [A1], [Sheet]
AREAS	Returns the number of areas in a reference.	Reference
INDEX	Return a value in a list or table based on location.	Array, Row_Num, [Col_Num], [Area_Num]
INDIRECT	Creates a reference from text.	Ref_Text, [A1]
MATCH	Returns the position of an item in an array.	Lookup_Value, Lookup_Array, [Match_Type]
MMULT	Performs matrix multiplication.	Array1, Array2
OFFSET	Creates a reference offset from a given starting point.	Reference, Rows, Cols, [Height], [Width]
GETPIVOTDATA	Returns data retrieved from a pivot table in a formula.	Data_Field, Pivot_Table, [Field1], [Item1], [...]

Text

Function	Description	Arguments
CHAR	Returns a character from a number.	Number
CLEAN	Removes non-printable characters from text.	Text
CODE	Returns the code for a character.	Text
CONCAT	Joins text values without a delimiter.	Text1, [Text2], [...]
CONCATENATE	Joins text together.	Text1, [Text2], [Text3], [...]
DOLLAR	Coverts a number to text using currency format.	Number, Decimals
EXACT	Compares two text strings.	Text1, Text2
FIND	Returns the location of text within a string.	Find_Text, Within_Text, [Start_Num]
FIXED	Formats a number as text with fixed decimals.	Number, [Decimals], [No_Commas]
LEFT	Extract text from the left of a string.	Text, [Num_Chars]
RIGHT	Extract text from the right of a string.	Text, [Num_Chars]
LEN	Returns the length of text.	Text
LOWER	Coverts text to lower case.	Text
MID	Extracts text from within a string.	Text, Start_Num, Num_Chars
PROPER	Capitalizes the first letter in each word.	Text
REPLACE	Replaces text based on location.	Old_Text, Start_Num, Num_Chars, New_Text
REPT	Repeats text as specified	Text, Number_Times
SEARCH	Returns the location of text in a string.	Find_Text, Within_Text, [Start_Num]
UPPER	Converts text to upper case.	Text
SUBSTITUTE	Replaces text based on content.	Text, Old_Text, New_Test, [Instance]
TEXT	Converts a number to text in number formatting.	Value, Format_Text
TRIM	Removes extra spaces from text.	Text
TEXTJOIN	Joins text values with a delimiter.	Delimiter, Ignore_Empty, Text1, [Text2], [...]
VALUE	Converts text to a number.	Text

Information

Function	Description	Arguments
TYPE	Returns the type of value in a cell.	Value
T	Filters for text values only.	Value
INFO	Returns information about the current environment.	Type_Text
ERROR.TYPE	Tests for a specific error value.	Error_Val
ISBLANK	Tests if a cell is empty.	Value
ISERR	Tests for any error except the #N/A error value.	Value
ISERROR	Tests for any error.	Value
ISEVEN	Tests if a value is even.	Value
ISFORMULA	Tests if a cell contains a formula.	Reference
ISLOGICAL	Tests if a value is a logical value.	Value
ISNA	Tests for the #N/A error.	Value
ISNONTEXT	Tests for a non-text value.	Value
ISNUMBER	Tests for numerical values.	Value
ISODD	Tests if a value is odd.	Value
ISREF	Tests for a reference.	Value
ISTEXT	Tests for a text value.	Value
N	Converts a value to a number.	Value
NA	Creates the #N/A error value.	No arguments required.
SHEET	Returns the sheet index number.	[Value]
SHEETS	Returns the number of sheets in a reference.	[Reference]
CELL	Returns information about a cell.	Info_Type, [Reference]

Financial - Beginner

Function	Description	Arguments
FV	Returns the future value of an investment.	Rate, Nper, Pmt, [PV], [Type]
IPMT	Returns the interest in a given period.	Rate, Per, Nper, PV, [FV], [Type]
PMT	Returns the periodic payment for a loan.	Rate, Nper, PV, [FV], [Type]
PPMT	Returns the principal payment for a given period.	Rate, Per, Nper, PV, [FV], [Type]
NPER	Returns the number of periods for a loan or investment.	Rate, Pmt, PV, [FV], [Type]
PV	Returns the present value of an investment.	Rate, Nper, Pmt, [FV], [Type]
RATE	Returns the interest rate per period of an annuity.	Nper, Pmt, PV, [FV], [Type], [Guess]
IRR	Returns the internal rate of return.	Values, [Guess]
NPV	Calculates the net present value.	Rate, Value1, [Value2], [...]
ISPMT	Returns the interest paid for a specific period.	Rate, Per, Nper, PV
NOMINAL	Returns the annual nominal interest rate.	Effect_Rate, Npery

Financial - Advanced

Function	Description	Arguments
CUMIPMT	Calculates the cumulative interest paid on a loan.	Rate, Nper, PV, Start_Period, End_Period, Type
CUMPRINC	Calculates the cumulative principal paid on a loan.	Rate, Nper, PV, Start_Period, End_Period, Type
MIRR	Calculates the modified internal rate of return.	Values, Finance_Rate, Reinvest_Rate
XIRR	Calculates the internal rate of return for irregular cash flows.	Values, Dates, [Guess]
XNPV	Calculates the net present value for irregular cash flows.	Rate, Values, Dates
DB	Depreciation with a fixed declining balance.	Cost, Salvage, Life, Period, [Month]
DDB	Depreciation with a double declining balance.	Cost, Salvage, Life, Period, [Factor]
DOLLARDE	Converts dollar price as fraction to decimal.	Fractional_Dollar, Fraction
DOLLARFR	Converts a price to a fractional notation.	Decimal_Dollar, Fraction
EFFECT	Returns an effective annual interest rate.	Nominal_Rate, Npery
RRI	Returns the equivalent interest rate for growth.	Nper, PV, FV
SLN	Straight line depreciation	Cost, Salvage, Life
SYD	Sum-of-years depreciation.	Cost, Salvage, Life, Period
VDB	Double declining variable depreciation	Cost, Salvage, Life, Start, End, [Factor], [No_Switch]

Database

Function	Description	Arguments
DAVERAGE	Returns the average from matching records.	Database, Field, Criteria
DCOUNT	Counts matching records in a database.	Database, [Field], Criteria
DCOUNTA	Counts matching records in a database.	Database, [Field], Criteria
DGET	Returns a value from a matching record.	Database, Field, Criteria
DMAX	Returns the max value from a matching record.	Database, Field, Criteria
DMIN	Returns the min value from a matching record.	Database, Field, Criteria
DPRODUCT	Returns product from a matching record.	Database, Field, Criteria
DSUM	Returns the sum from matching records.	Database, Field, Criteria
DSTDEV	Returns the standard deviation of a sample from matching records.	Database, Field, Criteria
DSTDEVP	Returns the standard deviation of a population from matching records.	Database, Field, Criteria
DVAR	Returns the variance of a sample from matching records.	Database, Field, Criteria
DVARP	Returns the variance of a population from matching records.	Database, Field, Criteria

Engineering - Beginner

Function	Description	Arguments
BIN2DEC	Converts a binary value to a decimal.	Number
BIN2HEX	Converts a binary value to a hexadecimal.	Number, [Places]
BIN2OCT	Calculates a binary number to octal.	Number, [Places]
BITAND	Returns a bitwise AND of two numbers	Number1, Number2
BITOR	Returns a bitwise OR of two numbers.	Number1, Number2
CONVERT	Converts measurement units.	Number, From_Unit, To_Unit
DEC2BIN	Converts a decimal number to binary.	Number, [Places]
DEC2HEX	Converts a decimal number to hexadecimal.	Number, [Places]
DEC2OCT	Converts a decimal to octal number.	Number, [Places]
HEX2BIN	Converts a hexadecimal to binary number.	Number, [Places]
HEX2DEC	Converts a hexadecimal to decimal number.	Number
HEX2OCT	Converts a hexadecimal to octal number.	Number, [Places]

Engineering - Advanced

Function	Description	Arguments
BITLSHIFT	Returns a number shifted left by a specified number of bits.	Number, Shift_Amount
BITRSHIFT	Returns a number shifted right by a specified number of bits.	Number, Shift_Amount
BITXOR	Returns a bitwise XOR of two numbers.	Number1, Number2
COMPLEX	Converts coefficients to complex numbers.	Real_Num, I_Num, [Suffix]
DELTA	Test if two value are equal.	Number1, [Number2]
IMABS	Returns the absolute value of complex numbers.	Inumber
IMAGINARY	Returns the imaginary coefficient of complex number.	Inumber
IMPOWER	Raises complex numbers to a given power.	Inumber, Number
IMPRODUCT	Returns the product of complex numbers.	Inumber1, [Inumber2], [...]
IMREAL	Returns the real coefficient of complex a number.	Inumber
IMSUB	Returns the difference between two complex numbers.	Number1, Number2
IMSUM	Returns the sum of complex numbers.	Inumber1, [Inumber2], [...]

Statistical - Beginner

Function	Description	Arguments
AVEDEV	Returns the sum of squared deviations.	Number1, [Number2], [...]
AVERAGE	Returns the average of a group of numbers.	Number1, [Number2], [...]
AVERAGEA	Returns the average of a group of numbers and text.	Value1, [Value2], [...]
AVERAGEIFS	Returns the average of cells that match multiple criteria.	Avg_Rng, Range1, Criteria1, [Range2], [Criteria2], [...]
COMBIN	Returns the total number of combinations.	Number, Number_Chosen
COUNT	Counts numbers.	Value1, [Value2], [...]
COUNTA	Counts the number of non-blank cells.	Value1, [Value2], [...]
COUNTIFS	Counts cells that match multiple criteria.	Range1, Criteria1, [Range2], [Criteria2], [...]
COUNTIF	Counts cells that match criteria.	Range, Criteria
COUNTBLANK	Counts cells that are blank.	Range
FREQUENCY	Returns the frequency of values in a data set.	Data_Array, Bins_Array
GEOMEAN	Returns the geometric mean.	Number1, [Number2], [...]
HARMEAN	Returns the harmonic mean.	Number1, [Number2], [...]
LARGE	Returns the nth largest number.	Array, N
SMALL	Returns the nth smallest number.	Array, N
MAX	Returns the largest value.	Number1, [Number2], [...]
MAXA	Returns the largest value.	Value1, [Value2], [...]
MEDIAN	Returns the median of a group of numbers.	Number1, [Number2], [...]
MIN	Returns the smallest value.	Number1, [Number2], [...]
MINA	Returns the smallest value.	Value1, [Value2], [...]
MODE	Returns the most frequently occurring number.	Number1, [Number2], [...]
MODE.MULT	Returns the most frequently occurring number.	Number1, [Number2], [...]
MODE.SNGL	Returns the most frequently occurring number.	Number1, [Number2], [...]
PERCENTILE	Returns the kth percentile.	Array, K
PERCENTILE.EXC	Returns the kth percentile, exclusive.	Array, K
PERCENTILE.INC	Returns the kth percentile, inclusive.	Array, K
PERMUT	Returns the total number of permutations.	Number, Number_Chosen
QUARTILE	Returns the quartile in a data set.	Array, Quart
QUARTILE.EXC	Returns the quartile in a data set, exclusive.	Array, Quart
QUARTILE.INC	Returns the quartile in a data set, inclusive.	Array, Quart
RANK	Ranks a number against a range of number.	Number, Array, [Order]
RANK.AVG	Ranks a number against a range of number.	Number, Ref, [Order]
RANK.EQ	Ranks a number against a range of number.	Number, Ref, [Order]
STANDARDIZE	Calculates a normalized value (z-score).	X, Mean, Standard_Dev
STDEV	Returns the standard deviation of a sample.	Number1, [Number2], [...]
STDEV.S	Returns the standard deviation of a sample.	Number1, [Number2], [...]
STDEV.P	Returns the standard deviation of the population.	Number1, [Number2], [...]
STDEVP	Returns the standard deviation of the population.	Number1, [Number2], [...]
STDEVA	Returns the standard deviation of a sample.	Number1, [Number2], [...]
STDEVPA	Returns the standard deviation of the population.	Number1, [Number2], [...]
TRIMMEAN	Calculates the mean while excluding outliers.	Array, Percent
VAR	Returns the variation of a sample.	Number1, [Number2], [...]
VAR.P	Returns the variation of a population.	Number1, [Number2], [...]
VAR.S	Returns the variation of a sample.	Number1, [Number2], [...]
VARP	Returns the variation of a population.	Number1, [Number2], [...]
VARA	Returns the variation of a sample.	Number1, [Number2], [...]
VARPA	Returns the variation of a population.	Number1, [Number2], [...]

Statistical - Advanced

Function	Description	Arguments
DEVSQ	Returns the sum of squared deviations.	Number1, [Number2], [...]
MAXIFS	Returns the maximum value with criteria.	Max_Range, Range1, Criteria1, [Range2], [Criteria2], [...]
MINIFS	Returns the minimum value with criteria.	Min_Range, Range1, Criteria1, [Range2], [Criteria2], [...]
PERCENTRANK	Returns the percentile rank, inclusive.	Array, X, [Significance]
PERCENTRANK.EXC	Returns the percentile rank, exclusive.	Array, X, [Significance]
PERCENTRANK.INC	Returns the percentile rank, inclusive.	Array, X, [Significance]