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