Functions

What are Functions in Excel?

Functions in Excel are predefined formulas that perform specific calculations or operations on data. Here are the key points about Excel functions:

Basic Concept

Excel functions are preset formulas that help perform mathematical, statistical, and logical operations. They allow you to quickly and easily perform complex calculations that would be time-consuming to do manually.

Structure

• Functions start with an equal sign (=) followed by the function name and parentheses containing the arguments

What are types of Functions exist in Excel?

• Mathematical functions (e.g., SUM, PRODUCT)
• Statistical functions (e.g., AVERAGE, STDEV)
• Logical functions (e.g., IF, AND, OR)
• Text functions (e.g., CONCATENATE, LEFT, RIGHT)
• Date and time functions (e.g., NOW, DATE, YEAR)
• Lookup and reference functions (e.g., VLOOKUP, INDEX, MATCH)

Using Functions

To use a function:

1. Select the cell where you want the result
2. Type = followed by the function name
3. Enter the arguments in parentheses
4. Press Enter to calculate the result

Functions are a powerful feature of Excel that can significantly enhance your data analysis and calculation capabilities.

A Comprehensive List of Functions by Category

Math and Trigonometry Functions

Function	Description
SUM	Adds up a range of cells or numbers
AVERAGE	Calculates the average (arithmetic mean) of a range of numbers
COUNT	Counts the number of cells that contain numbers
MAX	Returns the largest value in a set of numbers
MIN	Returns the smallest value in a set of numbers
PRODUCT	Multiplies all the numbers given as arguments
POWER	Raises a number to a specified power
SQRT	Calculates the square root of a number
ABS	Returns the absolute value of a number
ROUND	Rounds a number to a specified number of digits
ROUNDUP	Rounds a number up to a specified number of digits
ROUNDDOWN	Rounds a number down to a specified number of digits
INT	Rounds a number down to the nearest integer
CEILING	Rounds a number up to the nearest multiple of significance
FLOOR	Rounds a number down to the nearest multiple of significance
MOD	Returns the remainder after a number is divided by a divisor
RAND	Generates a random number between 0 and 1
RANDBETWEEN	Generates a random integer between two specified numbers
PI	Returns the value of pi (3.14159…)
SIN	Returns the sine of an angle
COS	Returns the cosine of an angle
TAN	Returns the tangent of an angle
DEGREES	Converts radians to degrees
RADIANS	Converts degrees to radians

Statistical Functions

Function	Description
MEDIAN	Returns the median (middle value) of a set of numbers
MODE	Returns the most frequently occurring value in a range
STDEV	Calculates the standard deviation of a sample
VAR	Calculates the variance of a sample
LARGE	Returns the k-th largest value in a dataset
SMALL	Returns the k-th smallest value in a dataset
PERCENTILE	Returns the k-th percentile of a range of data
QUARTILE	Returns the specified quartile of a dataset
RANK	Returns the rank of a number in a list of numbers
CORREL	Calculates the correlation coefficient between two datasets
FORECAST	Predicts a future value based on existing values

Logical Functions

Function	Description
IF	Performs a logical test and returns one value for TRUE, another for FALSE
AND	Returns TRUE if all arguments are TRUE, FALSE otherwise
OR	Returns TRUE if any argument is TRUE, FALSE otherwise
NOT	Reverses the logical value of its argument
XOR	Returns TRUE if an odd number of arguments are TRUE, FALSE otherwise
IFERROR	Returns a specified value if a formula evaluates to an error, otherwise returns the formula’s result
IFS	Checks multiple conditions and returns a value corresponding to the first TRUE condition
SWITCH	Evaluates an expression against a list of values and returns the result corresponding to the first matching value

Text Functions

Function	Description
LEN	Returns the number of characters in a text string
LEFT	Returns a specified number of characters from the start of a text string
RIGHT	Returns a specified number of characters from the end of a text string
MID	Returns a specific number of characters from a text string, starting at a specified position
CONCATENATE	Joins two or more text strings into one string
TEXTJOIN	Combines text from multiple ranges with a specified delimiter
TRIM	Removes all spaces from text except for single spaces between words
UPPER	Converts text to uppercase
LOWER	Converts text to lowercase
PROPER	Capitalizes the first letter of each word in a text string
SUBSTITUTE	Replaces old text with new text in a string
FIND	Finds one text string within another (case-sensitive)
SEARCH	Finds one text string within another (not case-sensitive)

Date and Time Functions

Function	Description
TODAY	Returns the current date
NOW	Returns the current date and time
YEAR	Returns the year of a date value
MONTH	Returns the month of a date value
DAY	Returns the day of a date value
WEEKDAY	Returns the day of the week for a date value
WORKDAY	Returns a date that is a specified number of workdays before or after a given date
NETWORKDAYS	Returns the number of whole working days between two dates
DATEDIF	Calculates the number of days, months, or years between two dates
EDATE	Returns a date that is a specified number of months before or after a start date
EOMONTH	Returns the last day of the month, a specified number of months before or after a start date

Lookup and Reference Functions

Function	Description
VLOOKUP	Looks up a value in the first column of a table and returns a value in the same row from a specified column
HLOOKUP	Looks up a value in the top row of a table and returns a value in the same column from a specified row
INDEX	Returns a value or reference of the cell at the intersection of a particular row and column in a range
MATCH	Searches for a specified item in a range of cells and returns its relative position
OFFSET	Returns a reference to a range that is offset from a given cell or range by a specified number of rows and columns
INDIRECT	Returns the reference specified by a text string
ADDRESS	Creates a cell reference as text, given row and column numbers
ROW	Returns the row number of a reference
COLUMN	Returns the column number of a reference

Financial Functions

Function	Description
PV	Calculates the present value of an investment
FV	Calculates the future value of an investment
PMT	Calculates the payment for a loan based on constant payments and a constant interest rate
RATE	Returns the interest rate per period of an annuity
NPER	Returns the number of periods for an investment
NPV	Calculates the net present value of an investment using a discount rate and a series of future payments and income
IRR	Calculates the internal rate of return for a series of cash flows
XIRR	Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV	Returns the net present value for a schedule of cash flows that is not necessarily periodic
SLN	Returns the straight-line depreciation of an asset for one period
DB	Returns the depreciation of an asset for a specified period using the fixed-declining balance method

Information Functions

Function	Description
ISBLANK	Returns TRUE if the referenced cell is empty
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
ISODD	Returns TRUE if the number is odd
ISTEXT	Returns TRUE if the value is text
ISNUMBER	Returns TRUE if the value is a number
ISNA	Returns TRUE if the value is the #N/A error value
ISREF	Returns TRUE if the value is a reference
TYPE	Returns a number indicating the data type of a value

Web Functions

Function	Description
ENCODEURL	Returns a URL-encoded string
WEBSERVICE	Retrieves data from a web service
FILTERXML	Returns specific data from XML content using an XPath expression

Database Functions

Function	Description
DSUM	Adds the numbers in a field of records in a list or database that match conditions you specify
DCOUNT	Counts the cells containing numbers in a field of records in a list or database that match conditions you specify
DMAX	Returns the largest number in a field of records in a list or database that match conditions you specify
DMIN	Returns the smallest number in a field of records in a list or database that match conditions you specify
DAVERAGE	Averages the values in a field of records in a list or database that match conditions you specify

Engineering Functions

Function	Description
BIN2DEC	Converts a binary number to decimal
DEC2BIN	Converts a decimal number to binary
HEX2DEC	Converts a hexadecimal number to decimal
DEC2HEX	Converts a decimal number to hexadecimal
OCT2DEC	Converts an octal number to decimal
DEC2OCT	Converts a decimal number to octal

Cube Functions

Function	Description
CUBEKPIMEMBER	Returns a key performance indicator (KPI) property and displays the KPI name in the cell
CUBEMEMBER	Returns a member or tuple from the cube
CUBEMEMBERPROPERTY	Returns the value of a member property from the cube
CUBERANKEDMEMBER	Returns the nth ranked member in a set
CUBESET	Defines a calculated set of members or tuples by sending a set expression to the cube on the server

Array Functions

Function	Description
TRANSPOSE	Returns a vertical range of cells as a horizontal range, or vice versa
MMULT	Returns the matrix product of two arrays
MUNIT	Returns the unit matrix for the specified dimension

Compatibility Functions

Function	Description
BETADIST	Returns the cumulative beta probability density function
BETAINV	Returns the inverse of the cumulative beta probability density function
BINOMDIST	Returns the individual term binomial distribution probability
CHIDIST	Returns the one-tailed probability of the chi-squared distribution
CHIINV	Returns the inverse of the one-tailed probability of the chi-squared distribution
CHITEST	Returns the test for independence
FDIST	Returns the F probability distribution
FINV	Returns the inverse of the F probability distribution
FTEST	Returns the result of an F-test
GAMMADIST	Returns the gamma distribution
GAMMAINV	Returns the inverse of the gamma cumulative distribution
GAMMALN	Returns the natural logarithm of the gamma function
HYPGEOMDIST	Returns the hypergeometric distribution
LOGINV	Returns the inverse of the lognormal distribution
LOGNORMDIST	Returns the cumulative lognormal distribution
NEGBINOMDIST	Returns the negative binomial distribution
NORMDIST	Returns the normal cumulative distribution
NORMINV	Returns the inverse of the normal cumulative distribution
NORMSDIST	Returns the standard normal cumulative distribution
NORMSINV	Returns the inverse of the standard normal cumulative distribution
PERCENTRANK	Returns the percentage rank of a value in a dataset
POISSON	Returns the Poisson distribution
RANK	Returns the rank of a number in a list of numbers
STDEV	Estimates standard deviation based on a sample
STDEVP	Calculates standard deviation based on the entire population
TDIST	Returns the Student’s t-distribution
TINV	Returns the inverse of the Student’s t-distribution
TTEST	Returns the probability associated with a Student’s t-test
VAR	Estimates variance based on a sample
VARP	Calculates variance based on the entire population
WEIBULL	Returns the Weibull distribution
ZTEST	Returns the two-tailed P-value of a z-test