The following Excel functions are broken out by category. The optional arguments are in brackets.
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 |
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] |
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] |
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 |
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] |
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], [...] |
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 |
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 |
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] |
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 |
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], [...] |