Welcome to our Excel Functions Reference Page! Here, you’ll find a categorized list of all Excel functions for easy navigation. Whether you’re a beginner or an advanced user, this resource will help you unlock the power of Excel functions to streamline your tasks and enhance your data analysis capabilities. Use the table of contents to explore the classes and find the functions you need quickly.
Date and Time
| Function Name | Description |
|---|---|
| DATE | The DATE function in Excel allows you to create a date by specifying the year, month, and day as separate arguments. It is particularly useful for constructing dates for various calculations, such as determining payment schedules, project timelines, or aging analysis. For example, you can use DATE to find the date a certain number of days from today or to convert text dates into a recognizable date format. |
| DATEDIF | DATEDIF is a hidden gem that calculates the difference between two dates in various units, such as years, months, or days. It’s helpful for determining someone’s age or the duration between two events. With DATEDIF, you can precisely compute these intervals and incorporate them into your spreadsheets. |
| DATEVALUE | DATEVALUE is used to convert a text representation of a date into a proper date format that Excel can recognize. This function is handy when you import data or receive data from external sources where dates may be in text format. It allows you to work with these dates for various calculations and analyses within your worksheets. |
| DAY | The DAY function takes a date as input and returns the day of the month (an integer between 1 and 31) for that date. It’s useful when you need to extract specific information from a date, such as determining deadlines, due dates, or scheduling recurring tasks on a monthly basis. |
| EDATE | EDATE is used to calculate a future or past date based on a given start date and a specified number of months. It’s a valuable tool for financial planning, project scheduling, or any situation where you need to find a date that is a fixed number of months away from a reference date. |
| EOMONTH | EOMONTH stands for “End of Month,” and it’s used to find the last day of the month a specified number of months before or after a given date. This function is handy when you need to determine fiscal periods, payment due dates, or any date-related task where the month’s end is significant. |
| HOUR | HOUR is a date and time function that extracts the hour component from a given time or datetime. This can be useful in scenarios where you want to perform calculations or create alerts based on the time of day. Whether it’s tracking shifts, managing appointments, or monitoring event schedules, HOUR helps you extract the hour information for precise analysis. |
| MINUTE | MINUTE is another date and time function, but this one focuses on extracting the minutes from a time or datetime value. It’s essential when you need to delve into the fine details of time-based data, such as tracking meeting durations, calculating time elapsed, or managing schedules that require minute-level precision. |
| MONTH | The MONTH function allows you to extract the month from a given date. This can be particularly useful when dealing with data that needs to be grouped or analyzed on a monthly basis. You can use it to create reports, perform trend analysis, or track monthly performance. |
| NOW | NOW returns the current date and time. It’s a dynamic function that updates automatically when you open or recalculate the workbook. You can use NOW to create timestamps, track data entry or modification times, or simply display the current date and time in your spreadsheets. |
| SECOND | SECOND, as the name suggests, extracts the seconds component from a time or datetime value. It’s beneficial when you need to work with time-based data at a very granular level, such as measuring response times, analyzing performance metrics, or recording the exact time of events. |
| TIME | The TIME function constructs a time value based on provided hours, minutes, and seconds. It’s handy for creating custom time intervals or timestamps when working with data that requires precision at the time level. You can use TIME to calculate durations, plan schedules, or record event times in a consistent format. |
| TIMEVALUE | TIMEVALUE converts a text representation of time into a proper time format recognized by Excel. This function is useful when dealing with imported data or data from external sources that may present times as text. It allows you to work with time data efficiently in your spreadsheets. |
| TODAY | TODAY returns the current date as a static value that doesn’t change with workbook recalculation or reopening. It’s useful for tasks where you need to capture the current date as a reference point, such as logging events, tracking deadlines, or creating date-based calculations that shouldn’t change over time. |
| WEEKDAY | WEEKDAY returns a number representing the day of the week for a given date, where 1 is Sunday, 2 is Monday, and so on. This function is helpful when you want to categorize or analyze data based on the day of the week, such as scheduling tasks, planning events, or determining patterns in your data. |
| YEAR | YEAR extracts the year component from a date. It’s commonly used when you need to categorize or analyze data on an annual basis, for example, tracking yearly trends, calculating age, or managing fiscal year-related tasks. Whether for financial analysis or personal records, YEAR makes it easy to extract year information from dates. |
Info Functions
| Function Name | Description |
|---|---|
| INFO | The INFO function provides various information about the current environment of Excel, such as the version, available memory, and the current operating system. It’s typically used in more advanced Excel applications and macros where you need to customize behaviors or make decisions based on the software’s environment. |
| ISBLANK | ISBLANK is a logical function that checks whether a cell is empty. It returns TRUE if the cell contains no data, including text, numbers, or formulas, and FALSE if the cell has any content. This function is helpful for conditionally formatting cells, validating input, or filtering data based on emptiness. |
| ISERR | ISERR is used to identify if a cell contains any error value except #N/A. It returns TRUE when a cell contains an error value such as #DIV/0! or #VALUE!, and FALSE if the cell contains no error or #N/A. You can use this function to handle errors in your formulas or to categorize cells with non-#N/A errors. |
| ISERROR | ISERROR is a logical function that checks whether a cell contains any error value, including #N/A. It returns TRUE if the cell contains an error value like #N/A, #DIV/0!, or #VALUE!, and FALSE if the cell contains no error. This function is valuable for identifying and handling errors in your calculations and data. |
| ISEVEN | ISEVEN is a logical function that determines if a number is even. It returns TRUE if the number is even and FALSE if it’s odd. This function is handy for creating formulas that perform different actions based on whether a number is even or odd. It’s commonly used in various applications like financial modeling or scheduling tasks. |
| ISFORMULA | ISFORMULA checks whether a cell contains a formula. It returns TRUE if the cell contains a formula, and FALSE if it contains a constant value or text. This function is useful for dynamically processing cells with formulas, validating input, or auditing spreadsheets to ensure that formulas are used where necessary. |
| ISLOGICAL | ISLOGICAL checks whether a cell contains a logical (Boolean) value. It returns TRUE if the cell contains TRUE or FALSE, and FALSE if it contains any other value, including text, numbers, or errors. This function is beneficial for ensuring that cells contain the expected logical values for logical tests and decision-making. |
| ISNA | ISNA checks if a cell contains the #N/A error value. It returns TRUE if the cell contains #N/A, and FALSE if it contains any other value, including other errors or data. This function is valuable for handling cells with missing or unavailable data, ensuring that your formulas respond appropriately to these conditions. |
| ISNONTEXT | ISNONTEXT verifies if a cell does not contain text data. It returns TRUE if the cell contains anything other than text, such as numbers, dates, or errors, and FALSE if it contains text. This function is helpful for filtering or categorizing cells based on their data type or for ensuring data quality in your spreadsheets. |
| ISNUMBER | ISNUMBER checks whether a cell contains a number. It returns TRUE if the cell contains a number, including dates, and FALSE if it contains any other data type, such as text or errors. This function is essential for validating data integrity, creating numeric formulas, or categorizing cells based on their numeric content. |
| ISODD | ISODD is a logical function that determines if a number is odd. It returns TRUE if the number is odd and FALSE if it’s even. Similar to ISEVEN, ISODD is useful for creating formulas that perform different actions based on the parity of numbers, such as scheduling or categorizing data. |
| ISREF | ISREF checks whether a cell reference is valid. It returns TRUE if the reference is valid, meaning it points to an existing cell, and FALSE if the reference is invalid, such as a reference to a non-existent cell. This function is helpful for validating cell references and ensuring they work as intended in your formulas. |
| ISTEXT | ISTEXT checks if a cell contains text data. It returns TRUE if the cell contains text, including numbers formatted as text, and FALSE if it contains any other data type, such as numbers, dates, or errors. This function is essential for working with text-based data and for categorizing or analyzing text content in your spreadsheets. |
Statistical Functions
| Function Name | Description |
|---|---|
| AVEDEV | AVEDEV calculates the average of the absolute deviations from the mean for a dataset. It measures the average spread or dispersion of data points around the mean and is useful for analyzing variability in a dataset. |
| AVERAGE | AVERAGE calculates the arithmetic mean (average) of a range of numbers. It’s one of the most fundamental statistical functions and is used to find the central tendency of data. |
| AVERAGEA | AVERAGEA computes the average of a range of numbers, similar to AVERAGE, but treats logical values (TRUE/FALSE) and text as numbers when performing the calculation. |
| AVERAGEIF | AVERAGEIF calculates the average of numbers in a range based on a specified criterion or condition. It’s helpful for finding the average of values that meet specific criteria. |
| AVERAGEIFS | AVERAGEIFS calculates the average of numbers based on multiple criteria. It’s particularly useful when you need to find the average of values that meet more than one condition. |
| BETADIST | BETADIST returns the cumulative beta probability density function for a given x-value in a beta distribution. It’s commonly used in statistical analysis and modeling. |
| BETAINV | BETAINV calculates the inverse of the cumulative beta probability density function for a given probability in a beta distribution. It’s often used for statistical modeling and analysis. |
| BINOM.DIST | BINOM.DIST calculates the individual or cumulative probability of a specific number of successes in a fixed number of Bernoulli trials. It’s frequently applied in probability and statistics for analyzing binary outcomes. |
| BINOM.DIST.RANGE | BINOM.DIST.RANGE finds the probability of having between a specified range of successful outcomes in a fixed number of Bernoulli trials. It’s valuable for assessing the likelihood of a certain number of successes within a range. |
| BINOM.INV | BINOM.INV returns the smallest value for which the cumulative binomial probability is greater than or equal to a specified probability. It’s used in statistical analysis to find the threshold number of successes. |
| CONFIDENCE.NORM | CONFIDENCE.NORM calculates the confidence interval for a population mean using a normal distribution. It’s used to estimate the range within which the true population mean is likely to fall. |
| CONFIDENCE.T | CONFIDENCE.T computes the confidence interval for a population mean using a Student’s t-distribution. It’s used for estimating the likely range of the population mean with small sample sizes. |
| COUNT | COUNT counts the number of cells in a range that contain numbers. It’s useful for basic numeric counting. |
| COUNTA | COUNTA counts the number of non-empty cells in a range, including text, numbers, and errors. It’s useful for counting all types of data. |
| COUNTBLANK | COUNTA counts the number of non-empty cells in a range, including text, numbers, and errors. It’s useful for counting all types of data. |
| COUNTIF | COUNTIF counts the number of cells in a range that meet a specific condition. It’s useful for conditional counting. |
| COUNTIFS | COUNTIFS counts the number of cells in a range that meet multiple conditions. It’s useful for advanced conditional counting. |
| COUNTIN | COUNTIN counts the number of cells in a range that belong to a specific category or condition. It’s useful for categorizing data. |
| COUNTUNIQUE | COUNTUNIQUE counts the number of unique values in a range, excluding duplicates. It’s useful for identifying unique data points |
| CORREL | CORREL calculates the correlation coefficient between two datasets, indicating the strength and direction of their linear relationship. It’s an essential tool in statistics for understanding the association between variables. |
| COVAR.P | COVAR.P computes the population covariance between two datasets. It measures the degree to which two variables change together in the entire population. |
| COVAR.S | COVAR.S calculates the sample covariance between two datasets. It measures the degree to which two variables change together within a sample. |
| DEVSQ | DEVSQ calculates the sum of squares of deviations from the mean in a dataset. It quantifies the total variation in data points, which is essential for various statistical analyses, including calculating variance and standard deviation. |
| EXPON.DIST | EXPON.DIST returns the exponential distribution probability for a specified value in a dataset with an exponential distribution. It’s commonly used for modeling and analyzing time between events or decay rates. |
| F.DIST | F.DIST calculates the cumulative F probability distribution for a given F-value in the F-distribution. It’s widely used in statistical testing, such as ANOVA, to analyze variances between groups or samples. |
| F.DIST.RT | F.DIST.RT calculates the complementary cumulative F probability distribution for a given F-value. It’s particularly useful for right-tailed F-distribution tests and significance analysis in statistics. |
| F.INV | F.INV returns the inverse of the cumulative F probability distribution for a given probability. It’s commonly used in statistical analysis to find critical values for F-tests and significance testing. |
| F.INV.RT | F.INV.RT calculates the inverse of the complementary cumulative F probability distribution for a given probability. It’s crucial for finding critical values in right-tailed F-distribution tests and statistical analysis. |
| FISHER | FISHER transforms a correlation coefficient into a Fisher z-transformation value. It’s used when comparing correlations or conducting statistical tests on correlation data. |
| FISHERINV | FISHERINV reverses the Fisher z-transformation to obtain the original correlation coefficient. It’s an essential tool for statistical analyses that require converting Fisher z-values back to correlations. |
| FORECAST | FORECAST predicts a future value based on existing data points. It’s a valuable tool for time series forecasting and trend analysis in various fields, including finance and economics. |
| FORECAST.ETS | FORECAST.ETS uses exponential smoothing to forecast future values in a time series. It’s particularly effective for handling time series data with seasonality and trend components. |
| GAMMA | GAMMA calculates the gamma function for a given number. It’s widely used in mathematics and statistics, particularly for computing factorials of non-integer values. |
| GAMMA.DIST | GAMMA.DIST returns the cumulative gamma probability distribution for a specified value in a gamma distribution. It’s used in statistical analysis to model random variables with gamma distributions. |
| GAMMA.INV | GAMMA.INV calculates the inverse of the cumulative gamma probability distribution for a given probability in a gamma distribution. It’s commonly used for statistical modeling and analysis. |
| GAMMALN | GAMMALN computes the natural logarithm of the gamma function for a given number. It’s useful in various mathematical and statistical applications. |
| GAMMALN.PRECISE | GAMMALN.PRECISE is similar to GAMMALN but provides a more precise result. It calculates the natural logarithm of the gamma function with higher accuracy. |
| GEOMEAN | GEOMEAN calculates the geometric mean of a set of numbers. It’s used in various statistical analyses, especially when dealing with data that grows exponentially or has multiplicative relationships. |
| HARMEAN | HARMEAN computes the harmonic mean of a set of numbers. It’s valuable in statistical analysis when dealing with rates, averages, or other situations where the harmonic mean is more appropriate than the arithmetic mean. |
| INTERCEPT | INTERCEPT calculates the point where a trendline intersects the y-axis in a linear regression. It’s an essential part of linear regression analysis to determine the starting point of a linear relationship between variables. |
| KURT | KURT calculates the kurtosis of a dataset, which measures the “tailedness” or peakedness of the data’s distribution. It’s commonly used in statistical analysis to understand the shape of data distributions. |
| LARGE | LARGE returns the kth largest value from a dataset. It’s helpful for identifying the largest values within a dataset or for conducting statistical analysis involving extreme values. |
| LCM | LCM finds the least common multiple of multiple numbers. It’s valuable for various applications, including scheduling, time management, and solving problems involving fractions or ratios. |
| LINEST | LINEST performs linear regression analysis on a dataset to calculate the coefficients of the best-fit line. It’s a powerful tool for modeling and analyzing linear relationships between variables. |
| LOGEST | LOGEST conducts exponential regression analysis on a dataset to calculate the coefficients of the best-fit exponential curve. It’s frequently used in scientific and engineering fields for modeling exponential growth or decay. |
| MAX | MAX returns the largest value from a range of numbers. It’s commonly used in statistical analysis to find the maximum value in a dataset or to identify the peak value in a set of data. |
| MAXA | MAXA is similar to MAX but treats logical values (TRUE/FALSE) and text as numbers when performing the calculation. It calculates the maximum value in a range, including non-numeric data. |
| MAXIFS | MAXIFS calculates the maximum value in a range based on multiple criteria. It’s valuable for finding the maximum value that meets specified conditions within a dataset. |
| MEDIAN | MEDIAN returns the middle value in a set of numbers. It’s a robust measure of central tendency and is often used in statistics to find the middle value, especially in datasets with outliers. |
| MIN | MIN returns the smallest value from a range of numbers. It’s commonly used in statistical analysis to find the minimum value in a dataset or to identify the lowest point in a set of data. |
| MINA | MINA is similar to MIN but treats logical values (TRUE/FALSE) and text as numbers when performing the calculation. It calculates the minimum value in a range, including non-numeric data. |
| MINIFS | MINIFS calculates the minimum value in a range based on multiple criteria. It’s valuable for finding the minimum value that meets specified conditions within a dataset. |
| MODE.MULT | MODE.MULT calculates the mode for a set of numbers, returning multiple modes if they exist. The mode is the most frequently occurring value in a dataset, and this function is used in various statistical analyses. |
| MODE.SNGL | MODE.SNGL finds the mode, which is the most frequently occurring value in a dataset. Unlike MODE.MULT, it returns a single mode even if there are multiple modes in the data. |
| NEGBINOM.DIST | NEGBINOM.DIST returns the probability mass function for a negative binomial distribution. It’s used in probability and statistics to model the number of trials required for a certain number of successes. |
| NORM.DIST | NORM.DIST calculates the cumulative normal distribution for a given x-value in a normal distribution. It’s frequently used in statistical analysis and hypothesis testing to assess the likelihood of specific outcomes. |
| NORM.INV | NORM.INV computes the inverse of the cumulative normal distribution for a given probability in a normal distribution. It’s commonly used to find critical values in hypothesis testing and statistical analysis. |
| NORM.S.DIST | NORM.S.DIST calculates the standard normal distribution for a given x-value. It’s often used in statistical analysis and hypothesis testing to assess the likelihood of specific outcomes in standard units. |
| NORM.S.INV | NORM.S.INV computes the inverse of the standard normal distribution for a given probability. It’s commonly used in statistical analysis and hypothesis testing to find critical values in standard units. |
| PERCENTILE.EXC | PERCENTILE.EXC calculates the exclusive kth percentile of a dataset, excluding the percentile value itself. It’s useful for identifying values below which a certain percentage of data falls. |
| PERCENTILE.INC | PERCENTILE.INC computes the inclusive kth percentile of a dataset, including the percentile value itself. It’s commonly used for analyzing data distribution and identifying critical values. |
| PERCENTRANK.EXC | PERCENTRANK.EXC returns the exclusive rank of a value within a dataset, providing the percentage of values below the given value. It’s helpful for assessing the position of a specific value within a data distribution. |
| PERCENTRANK.INC | PERCENTRANK.INC calculates the inclusive rank of a value within a dataset, including the value itself, as a percentage of values below it. It’s useful for evaluating data distribution and identifying the position of a value within that distribution. |
| PERMUT | PERMUT calculates the number of permutations of a set of items. It’s useful for combinatorial problems, such as arranging items in different orders or solving problems related to permutations. |
| PERMUTATIONA | PERMUTATIONA, like PERMUT, computes the number of permutations of a set of items. However, it treats items as unique, even if they are identical, and is used in scenarios where items are distinguishable from one another. |
| PHI | PHI calculates the standard normal cumulative distribution function for a given z-value. It’s commonly used in statistical analysis and hypothesis testing to find the probability of a z-score in a standard normal distribution. |
| POISSON.DIST | POISSON.DIST calculates the probability mass function for a Poisson distribution, which models the number of events occurring in a fixed interval of time or space. It’s often used in probability and statistics for analyzing event counts. |
| RANK.AVG | RANK.AVG returns the rank of a value within a dataset, allowing for duplicate values and providing an average rank for identical values. It’s helpful for assessing the position of data points within a dataset while accounting for ties. |
| RANK.EQ | RANK.EQ calculates the rank of a value in a dataset, considering duplicate values and providing a unique rank for each value. It’s useful for ranking data points within a dataset while distinguishing between identical values. |
| RSQ | RSQ computes the coefficient of determination (R-squared) for a dataset, indicating the proportion of variance in the dependent variable that can be explained by the independent variable in a linear regression. It’s crucial for understanding the goodness of fit in regression analysis. |
| SKEW | SKEW calculates the skewness of a dataset, which measures the asymmetry in the distribution of data. It’s commonly used in statistical analysis to understand the shape of data distributions. |
| SKEW.P | SKEW.P computes the population skewness of a dataset, similar to SKEW, but considering the data as a full population rather than a sample. It’s used in statistical analysis to assess the skewness of population data. |
| SMALL | SMALL returns the kth smallest value from a dataset. It’s valuable for identifying the smallest values within a dataset or for conducting statistical analysis involving extreme values. |
| STDEVP | STDEVP calculates the population standard deviation of a dataset. It measures the amount of variation or dispersion in the entire population. |
| STDEV.S | STDEV.S computes the sample standard deviation of a dataset. It measures the amount of variation or dispersion in a sample of data. |
| STDEVA | STDEVA is similar to STDEV.P but treats logical values (TRUE/FALSE) and text as numbers when performing the calculation. It calculates the population standard deviation, including non-numeric data. |
| STDEVPA | STDEVPA is similar to STDEV.S but treats logical values (TRUE/FALSE) and text as numbers when performing the calculation. It calculates the sample standard deviation, including non-numeric data. |
| STEYX | STEYX calculates the standard error of the estimated y-values for each x-value in a linear regression. It’s useful for understanding the accuracy of predictions made by the regression model. |
| T.DIST | T.DIST calculates the cumulative Student’s t-distribution for a given x-value in a t-distribution. It’s commonly used in hypothesis testing, confidence intervals, and statistical analysis. |
| T.DIST.2T | T.DIST.2T calculates the two-tailed cumulative Student’s t-distribution for a given x-value. It’s used in hypothesis testing to assess the likelihood of a certain t-statistic in both tails of the distribution. |
| T.DIST.RT | T.DIST.RT calculates the complementary cumulative Student’s t-distribution for a given x-value. It’s particularly useful for right-tailed t-distribution tests and significance analysis in statistics. |
| T.INV | T.INV returns the inverse of the Student’s t-distribution for a given probability. It’s commonly used in hypothesis testing and statistical analysis to find critical values for t-tests. |
| T.INV.2T | T.INV.2T calculates the inverse of the two-tailed Student’s t-distribution for a given probability. It’s essential for finding critical values in two-tailed t-distribution tests and statistical analysis. |
| T.INV.RT | T.INV.RT computes the inverse of the complementary cumulative Student’s t-distribution for a given probability. It’s used in right-tailed t-distribution tests and significance analysis in statistics. |
| TRIMMEAN | TRIMMEAN calculates the mean (average) of a dataset after removing a specified percentage of data points from both ends. It’s useful for finding the trimmed mean to reduce the impact of outliers in statistical analysis. |
| VAR.P | VAR.P calculates the population variance for a dataset. It measures the spread or dispersion of data in the entire population. |
| VAR.S | VAR.S computes the sample variance for a dataset. It measures the spread or dispersion of data in a sample. |
| VARA | VARA is similar to VAR.P but treats logical values (TRUE/FALSE) and text as numbers when performing the calculation. It calculates the population variance, including non-numeric data. |
| VARPA | VARPA is similar to VAR.S but treats logical values (TRUE/FALSE) and text as numbers when performing the calculation. It calculates the sample variance, including non-numeric data. |
Logical Functions
| Function Name | Description |
|---|---|
| AND | AND returns TRUE if all of its arguments are TRUE; otherwise, it returns FALSE. It’s used to evaluate multiple conditions, and it returns TRUE only if all the conditions are met. |
| FALSE | FALSE returns the logical value FALSE. It’s a basic function used to represent a false condition or result. |
| IF | IF returns one value if a specified condition is TRUE and another value if it’s FALSE. It’s fundamental for making decisions in Excel based on the outcome of a logical test. |
| IFERROR | IFERROR returns a value you specify if a formula evaluates to an error and another value if it’s not an error. It’s useful for handling errors and displaying custom messages. |
| IFNA | IFNA returns a value you specify if a formula results in the #N/A error, and another value if it’s not an #N/A error. It’s helpful for custom error handling. |
| NOT | NOT returns TRUE if its argument is FALSE, and it returns FALSE if its argument is TRUE. It’s used to reverse the logical value of a condition or expression. |
| OR | OR returns TRUE if at least one of its arguments is TRUE; otherwise, it returns FALSE. It’s used to evaluate multiple conditions, and it returns TRUE if any of the conditions are met. |
| SWITCH | SWITCH evaluates an expression against a list of values and returns the corresponding result for the first matching value. It’s helpful for conditional branching and simplifying complex IF statements. |
| TRUE | TRUE returns the logical value TRUE. It’s a basic function used to represent a true condition or result. |
Math Functions
| Function Name | Description |
|---|---|
| ABS | ABS returns the absolute value of a number, making negative numbers positive and leaving positive numbers unchanged. |
| ACOS | ACOS calculates the arccosine of a number, returning an angle in radians between 0 and π (pi). |
| ACOSH | ACOSH calculates the hyperbolic arccosine of a number. It returns the inverse hyperbolic cosine in radians. |
| ACOT | ACOT calculates the arccotangent of a number, returning an angle in radians. |
| ACOTH | ACOTH calculates the hyperbolic arccotangent of a number, returning the inverse hyperbolic cotangent in radians. |
| AGGREGATE | AGGREGATE performs various aggregate calculations, such as finding the sum, average, maximum, or minimum, while allowing for options to ignore errors or hidden rows. |
| CEILING.MATH | CEILING.MATH rounds a number up to the nearest multiple of a specified significance, and it can use various rounding modes. |
| CEILING.PRECISE | CEILING.PRECISE rounds a number up to the nearest multiple of a specified significance, without using rounding modes. |
| COMBIN | COMBIN calculates the number of combinations for a given number of items taken from a set. It’s useful for solving combinatorial problems. |
| COS | COS returns the cosine of an angle in radians. It’s used for various trigonometric calculations. |
| COSH | COSH calculates the hyperbolic cosine of a number, providing the value of the hyperbolic trigonometric function. |
| COT | COT calculates the cotangent of an angle in radians, providing the value of the trigonometric function. |
| COTH | COTH calculates the hyperbolic cotangent of a number, providing the value of the hyperbolic trigonometric function. |
| DEGREES | DEGREES converts an angle from radians to degrees. It’s useful for converting angular measurements between different units. |
| DIVIDE | DIVIDE performs division between two numbers or arrays and handles division by zero, returning either the result or an optional alternate value. |
| EVEN | EVEN rounds a number up to the nearest even integer. It’s used for various applications, including rounding for even amounts. |
| EXP | EXP calculates the exponential value of a number, raising the mathematical constant ‘e’ to the power of the specified number. |
| FACT | FACT returns the factorial of a non-negative integer, which is the product of all positive integers up to that number. |
| FLOOR.MATH | FLOOR.MATH rounds a number down to the nearest multiple of a specified significance and supports various rounding modes. |
| FLOOR.PRECISE | FLOOR.PRECISE rounds a number down to the nearest multiple of a specified significance without using rounding modes. |
| GCD | GCD calculates the greatest common divisor of two or more integers, which is the largest positive integer that divides them without leaving a remainder. |
| INT | INT returns the integer portion of a number, effectively removing the decimal part. It’s used for truncating numbers toward zero. |
| LCM | LCM calculates the least common multiple of two or more integers, which is the smallest multiple that is evenly divisible by all of them. |
| LN | LN returns the natural logarithm of a number, using the base ‘e’ (Euler’s number). It’s useful for various mathematical and scientific calculations. |
| LOG | LOG calculates the logarithm of a number with a specified base. It’s used for changing the base of logarithmic calculations. |
| LOG10 | LOG10 calculates the base-10 logarithm of a number. It’s commonly used for logarithmic calculations in base 10. |
| MDETERM | MDETERM calculates the matrix determinant of an array. It’s used for linear algebra and solving systems of linear equations. |
| MINVERSE | MINVERSE calculates the inverse of a matrix, provided the matrix is square and non-singular. It’s essential for solving systems of linear equations. |
| MMULT | MMULT performs matrix multiplication of two matrices, returning the product matrix. It’s valuable for various mathematical and engineering applications. |
| MOD | MOD returns the remainder when one number is divided by another. It’s useful for calculating periodicity or cycles in data. |
| MULTINOMIAL | MULTINOMIAL calculates the multinomial coefficient for a set of numbers. It’s used in combinatorics and statistics to count the number of ways items can be divided into categories. |
| ODD | ODD rounds a number up to the nearest odd integer. It’s used for various applications, including rounding for odd amounts. |
| PI | PI returns the mathematical constant π (pi), which represents the ratio of a circle’s circumference to its diameter. It’s used in various mathematical and scientific calculations. |
| POWER | POWER raises a number to a specified power. It’s used for exponential calculations and is equivalent to the “^” operator. |
| PRODUCT | PRODUCT multiplies numbers together, producing the product of all the numbers in a given range. It’s a fundamental function in mathematical and financial calculations. |
| QUOTIENT | QUOTIENT performs integer division between two numbers and returns the quotient without the remainder. It’s useful for dividing numbers and discarding the decimal part. |
| RADIANS | RADIANS converts an angle from degrees to radians. It’s valuable for converting angular measurements between different units. |
| RAND | RAND generates a random decimal number between 0 and 1. It’s often used for simulating randomness and creating random data. |
| RANDBETWEEN | RANDBETWEEN generates a random integer number within a specified range. It’s useful for simulating random data or scenarios. |
| ROUND | ROUND rounds a number to a specified number of decimal places. It’s used for controlling the precision of numeric values. |
| ROUNDDOWN | ROUNDDOWN rounds a number down to a specified number of decimal places. It’s useful for truncating numbers towards zero. |
| ROUNDUP | ROUNDUP rounds a number up to a specified number of decimal places. It’s valuable for increasing the precision of numeric values. |
| SIGN | SIGN returns the sign of a number as 1 (positive), -1 (negative), or 0 (zero). It’s useful for classifying numbers based on their sign. |
| SIN | SIN returns the sine of an angle in radians. It’s used for various trigonometric calculations. |
| SINH | SINH calculates the hyperbolic sine of a number, providing the value of the hyperbolic trigonometric function. |
| SQRT | SQRT calculates the square root of a number. It’s useful for finding the positive square root of a value. |
| SQRTPI | SQRTPI calculates the square root of the product of a number and π (pi). It’s used in various mathematical and scientific calculations. |
| SUM | SUM adds up all the numbers in a range or array. It’s a fundamental function for calculating the total of values. |
| SUMPRODUCT | SUMPRODUCT multiplies corresponding elements in multiple arrays and then sums the results. It’s used for various calculations involving multiple arrays. |
| SUMSQ | SUMSQ returns the sum of the squares of numbers in a range or array. It’s valuable for calculating the sum of squared values. |
| TRUNC | TRUNC truncates a number to a specified number of decimal places, effectively removing the decimal part. It’s used for controlling the precision of numeric values. |
Text Functions
| Function Name | Description |
|---|---|
| CHAR | CHAR returns the character specified by the ASCII code. It’s useful for converting ASCII codes to characters. |
| CLEAN | CLEAN removes non-printable characters from text, ensuring that the text is clean and doesn’t contain characters that may cause issues. |
| CODE | CODE returns the ASCII code of the first character in a text string. It’s used to find the numeric code for a character. |
| CONCATENATE | CONCATENATE combines multiple text strings into one. It’s an older text function, and the “&” operator is commonly used for the same purpose in Excel. |
| EXACT | EXACT compares two text strings and returns TRUE if they are identical, including their case. It’s used for precise text comparisons. |
| FIND | FIND returns the position of one text string within another. It’s helpful for locating the starting position of a substring within a larger text. |
| LEFT, LEFTB | LEFT returns a specified number of characters from the beginning of a text string. LEFTB is used for byte-oriented text, such as in double-byte character set (DBCS) languages. |
| LEN, LENB | LEN returns the number of characters in a text string. LENB is used for byte-oriented text, such as in double-byte character set (DBCS) languages. |
| LOWER | LOWER converts text to all lowercase letters. It’s used for changing the case of text to lowercase. |
| MID, MIDB | MID returns a specified number of characters from the middle of a text string. MIDB is used for byte-oriented text, such as in double-byte character set (DBCS) languages. |
| PROPER | PROPER capitalizes the first letter of each word in a text string. It’s useful for converting text to title case. |
| REPLACE, REPLACEB | REPLACE replaces a specified number of characters in a text string with new text. REPLACEB is used for byte-oriented text, such as in double-byte character set (DBCS) languages. |
| REPT | REPT repeats a text string a specified number of times. It’s used for creating repetitive text patterns. |
| RIGHT, RIGHTB | RIGHT returns a specified number of characters from the end of a text string. RIGHTB is used for byte-oriented text, such as in double-byte character set (DBCS) languages. |
| SEARCH, SEARCHB | SEARCH returns the position of one text string within another, but it’s not case-sensitive. SEARCHB is used for byte-oriented text, such as in double-byte character set (DBCS) languages. |
| SUBSTITUTE | SUBSTITUTE replaces occurrences of a specified substring within a text string with new text. It’s used for text replacement. |
| TEXT | TEXT converts a value to text with a specified number format. It’s commonly used for formatting numbers as text. |
| TRIM | TRIM removes extra spaces from a text string, leaving only single spaces between words. It’s used to clean up text. |
| UPPER | UPPER converts text to all uppercase letters. It’s used for changing the case of text to uppercase. |
| VALUE | VALUE converts text that represents a number to an actual numeric value. It’s useful for converting text to numbers for mathematical operations. |
Financial Functions
| Function Name | Description |
|---|---|
| ACCINT | ACCRINT calculates the accrued interest for a security that pays periodic interest. It’s commonly used in financial analysis and investments. |
| ACCINTM | ACCRINTM calculates the accrued interest for a security that pays interest at maturity. It’s used for financial analysis and investments. |
| AMORDEGRC | AMORDEGRC calculates the depreciation of an asset using a variable declining balance method. It’s helpful for accounting and financial analysis. |
| AMORLINC | AMORLINC calculates the depreciation of an asset using a linear depreciation method. It’s used for accounting and financial analysis. |
| COUPDAYBS | COUPDAYBS returns the number of days from the beginning of a coupon period to the settlement date. It’s used in bond and financial analysis. |
| COUPDAYS | COUPDAYS calculates the number of days in the coupon period that contains the settlement date. It’s used in bond and financial analysis. |
| COUPDAYSNC | COUPDAYSNC calculates the number of days from the settlement date to the next coupon date. It’s used in bond and financial analysis. |
| COUPNCD | COUPNCD returns the next coupon date after the settlement date. It’s useful in bond and financial analysis. |
| COUPNUM | COUPNUM returns the number of coupons between the settlement date and the next coupon date. It’s used in bond and financial analysis. |
| COUPPCD | COUPPCD returns the previous coupon date before the settlement date. It’s valuable in bond and financial analysis. |
| DB | DB calculates the depreciation of an asset for a specified period using the fixed-declining balance method. It’s commonly used for asset accounting. |
| DDB | DDB calculates the depreciation of an asset for a specified period using the double-declining balance method. It’s commonly used for asset accounting. |
| DISC | DISC calculates the discount rate of a security. It’s used in financial analysis, particularly for discount securities. |
| DOLLARDE | DOLLARDE converts a dollar price into a decimal number. It’s used for converting dollar prices into decimal representation. |
| DOLLARFR | DOLLARFR converts a decimal number into a dollar price. It’s used for converting decimal values into dollar representation. |
| DURATION | DURATION calculates the Macaulay duration of a security, representing the weighted average time until its cash flows are received. It’s used in financial analysis. |
| FV | FV calculates the future value of an investment based on a series of periodic payments, interest rate, and the number of periods. It’s used in financial planning and investment analysis. |
| FVSCHEDULE | FVSCHEDULE calculates the future value of an investment based on a variable schedule of payments and interest rates. It’s used in financial planning and investment analysis. |
| INTRATE | INTRATE calculates the interest rate for a fully invested security. It’s used in financial analysis, especially for bonds and investments. |
| IRR | IRR calculates the internal rate of return for a series of cash flows. It’s used to assess the profitability of an investment. |
| MDURATION | MDURATION calculates the modified Macaulay duration of a security, which is a measure of its sensitivity to changes in interest rates. It’s used in financial analysis. |
| MIRR | MIRR calculates the modified internal rate of return for a series of cash flows, addressing the reinvestment rate issue in traditional IRR. It’s used in financial analysis. |
| NPER | NPER calculates the number of periods required to reach a specified future value with a fixed interest rate and periodic payments. It’s used in financial planning and investment analysis. |
| NPV | NPV calculates the net present value of a series of cash flows, helping to determine the profitability of an investment or project. |
| ODDLYIELD | ODDLYIELD calculates the yield of a security with an odd first period. It’s used in financial analysis. |
| ODDFPRICE | ODDFPRICE calculates the price per $100 face value of a security with an odd first period. It’s used in financial analysis. |
| ODDLPRICE | ODDLPRICE calculates the price per $100 face value of a security with an odd last period. It’s used in financial analysis. |
| PDURATION | PDURATION calculates the price per $100 face value of a security with an odd last period. It’s used in financial analysis. |
| PMT | PMT calculates the periodic payment for a loan or investment based on a fixed interest rate, number of periods, and present value. It’s used in financial planning and loan calculations. |
| PPMT | PPMT calculates the principal payment for a specific period of a loan or investment. It’s used in financial planning and loan calculations. |
| PRICE | PRICE calculates the price per $100 face value of a security. It’s used in financial analysis, particularly for bonds and investments. |
| PRICEDISC | PRICEDISC calculates the price per $100 face value of a discounted security. It’s used in financial analysis. |
| PRICEMAT | PRICEMAT calculates the price per $100 face value of a security that pays interest at maturity. It’s used in financial analysis. |
| PV | PV calculates the present value of an investment or future cash flows, discounting them to their current value. It’s used in financial analysis and investment valuation. |
| RATE | RATE calculates the interest rate for an investment or loan, given the number of periods, periodic payment, and present value. It’s used in financial analysis and loan calculations. |
| RECEIVED | RECEIVED calculates the amount received at the maturity of a security, including both principal and interest. It’s used in financial analysis. |
| SLN | SLN calculates the straight-line depreciation of an asset for a specified period. It’s used for asset accounting. |
| SYD | SYD calculates the sum-of-the-years-digits depreciation of an asset for a specified period. It’s used for asset accounting. |
| TBILLEQ | TBILLEQ calculates the equivalent yield of a Treasury bill. It’s used in financial analysis. |
| TBILLPRICE | TBILLPRICE calculates the price per $100 face value of a Treasury bill. It’s used in financial analysis. |
| TBILLYIELD | TBILLYIELD calculates the yield of a Treasury bill. It’s used in financial analysis. |
| VDB | VDB calculates the depreciation of an asset for a specified period using the double-declining balance method with optional switching to straight-line. It’s used for asset accounting. |
| XIRR | XIRR calculates the internal rate of return for a series of cash flows that may not occur at regular intervals. It’s used in financial analysis and investment evaluation. |
| XNPV | XNPV calculates the net present value for a series of cash flows that may not occur at regular intervals. It’s used in financial analysis. |
| YIELD | YIELD calculates the yield of a security. It’s used in financial analysis, particularly for bonds and investments. |
| YIELDDISC | YIELDDISC calculates the yield of a discounted security. It’s used in financial analysis. |
| YIELDMAT | YIELDMAT calculates the yield of a security that pays interest at maturity. It’s used in financial analysis. |
Engineering Functions
| Function Name | Description |
|---|---|
| BESSELI | BESSELI calculates the modified Bessel function I(x) for a specified order and value of x. It’s used in engineering and mathematical analysis. |
| BESSELJ | BESSELJ calculates the Bessel function J(x) for a specified order and value of x. It’s used in engineering and mathematical analysis. |
| BESSELK | BESSELK calculates the modified Bessel function K(x) for a specified order and value of x. It’s used in engineering and mathematical analysis. |
| BESSELY | BESSELY calculates the Bessel function Y(x) for a specified order and value of x. It’s used in engineering and mathematical analysis. |
| BIN2DEC | BIN2DEC converts a binary number to its decimal equivalent. It’s used for binary-to-decimal conversion in digital systems. |
| BIN2HEX | BIN2HEX converts a binary number to its hexadecimal equivalent. It’s used for binary-to-hexadecimal conversion in digital systems. |
| BIN2OCT | BIN2OCT converts a binary number to its octal equivalent. It’s used for binary-to-octal conversion in digital systems. |
| DEC2BIN | DEC2BIN converts a decimal number to its binary equivalent. It’s used for decimal-to-binary conversion in digital systems. |
| DEC2HEX | DEC2HEX converts a decimal number to its hexadecimal equivalent. It’s used for decimal-to-hexadecimal conversion in digital systems. |
| DEC2OCT | DEC2OCT converts a decimal number to its octal equivalent. It’s used for decimal-to-octal conversion in digital systems. |
| DELTA | DELTA returns 1 if two specified values are equal and 0 if they’re not. It’s used for checking equality between values. |
| ERF | ERF calculates the error function for a specified value. It’s used in engineering and statistics for error analysis. |
| ERFC | ERFC calculates the complementary error function for a specified value. It’s used in engineering and statistics for error analysis. |
| FACTDOUBLE | FACTDOUBLE returns the double factorial of a number, which is the product of every other integer up to that number. It’s used in mathematics and engineering. |
| GCD | GCD calculates the greatest common divisor of two or more integers, which is the largest positive integer that divides them without leaving a remainder. It’s used in number theory and engineering. |
| IMABS | IMABS calculates the absolute value (magnitude) of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMAGINARY | IMAGINARY returns the imaginary coefficient of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMARGUMENT | IMARGUMENT returns the argument (angle) of a complex number in radians. It’s used in electrical engineering, control systems, and signal processing. |
| IMCONJUGATE | IMCONJUGATE calculates the complex conjugate of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMCOS | IMCOS calculates the cosine of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMCOSH | IMCOSH calculates the hyperbolic cosine of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMDIV | IMDIV divides one complex number by another. It’s used in electrical engineering, control systems, and signal processing. |
| IMEXP | IMEXP calculates the exponential value of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMLN | IMLN calculates the natural logarithm of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMLOG10 | IMLOG10 calculates the base-10 logarithm of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMLOG2 | IMLOG2 calculates the base-2 logarithm of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMPOWER | IMPOWER raises a complex number to a specified power. It’s used in electrical engineering, control systems, and signal processing. |
| IMPRODUCT | IMPRODUCT multiplies complex numbers together. It’s used in electrical engineering, control systems, and signal processing. |
| IMREAL | IMREAL returns the real part of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMSEC | IMSEC calculates the secant of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMSECH | IMSECH calculates the hyperbolic secant of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMSIN | IMSIN calculates the sine of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMSINH | IMSINH calculates the hyperbolic sine of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMSQRT | IMSQRT calculates the square root of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| IMSUB | IMSUB subtracts one complex number from another. It’s used in electrical engineering, control systems, and signal processing. |
| IMSUM | IMSUM adds complex numbers together. It’s used in electrical engineering, control systems, and signal processing. |
| IMTAN | IMTAN calculates the tangent of a complex number. It’s used in electrical engineering, control systems, and signal processing. |
| MOD | MOD returns the remainder when one number is divided by another. It’s used in various engineering and mathematical calculations. |
| MROUND | MROUND rounds a number to the nearest multiple of another number. It’s used in engineering and financial calculations. |
| MULTINOMIAL | MULTINOMIAL calculates the multinomial coefficient of a set of numbers, which is used in combinatorial mathematics and engineering. |
| QUOTIENT | QUOTIENT returns the integer portion of one number divided by another. It’s used in engineering and mathematical calculations. |
Array Functions
| Function Name | Description |
|---|---|
| BYCOL | BYCOL performs a calculation on each column of a range and returns the results as an array. It’s useful for applying a function to each column in a range. |
| BYROW | BYROW performs a calculation on each row of a range and returns the results as an array. It’s useful for applying a function to each row in a range. |
| CHOOSECOLS | CHOOSECOLS selects specific columns from a range and returns them as a new range. It’s used for extracting specific columns from a larger dataset. |
| CHOOSEROWS | CHOOSEROWS selects specific rows from a range and returns them as a new range. It’s used for extracting specific rows from a larger dataset. |
| CUBEKPIMEMBER | CUBEKPIMEMBER returns a key performance indicator (KPI) name and its value from a cube. It’s used in OLAP (Online Analytical Processing) scenarios. |
| CUBEMEMBER | CUBEMEMBER returns a member or tuple from a cube. It’s used in OLAP scenarios for extracting specific data from multidimensional databases. |
| CUBEMEMBERPROPERTY | CUBEMEMBERPROPERTY returns the value of a member property from a cube. It’s used in OLAP scenarios for accessing additional information about cube members. |
| CUBERANKEDMEMBER | CUBERANKEDMEMBER returns the nth-ranked member from a set in a cube. It’s used in OLAP scenarios for ranking and sorting data. |
| CUBESET | CUBESET creates a set of cube members from a cube. It’s used in OLAP scenarios for defining custom sets of data. |
| CUBESETCOUNT | CUBESETCOUNT returns the number of items in a set from a cube. It’s used in OLAP scenarios for determining the size of a set. |
| CUBEVALUE | CUBEVALUE retrieves a value from a cube based on specified dimensions. It’s used in OLAP scenarios for extracting specific data from multidimensional databases. |
| EXPAND | EXPAND returns a one-dimensional array that is the concatenation of two or more arrays. It’s used for combining multiple arrays into a single array. |
| FILTER | FILTER returns an array that contains only the values from a range that meet specified criteria. It’s used for extracting specific data from a larger dataset. |
| FILTERXML | FILTERXML returns specific data from an XML string or document. It’s used for extracting information from XML documents. |
| HSTACK | HSTACK combines multiple ranges horizontally into a single range. It’s used for merging data from different sources or sheets. |
| MAP | MAP applies a function to each element of an array and returns the results as a new array. It’s used for performing operations on array elements. |
| SEQUENCE | SEQUENCE generates a sequence of numbers in an array. It’s useful for creating number series or vectors. |
| SLICE | SLICE returns a one-dimensional array that is a specified row from a two-dimensional array. It’s used for extracting specific rows from a larger dataset. |
| SORT | SORT sorts the contents of an array in ascending or descending order. It’s used for arranging data in a specific order. |
| SORTBY | SORTBY sorts the contents of an array based on the values in another array or range. It’s used for custom sorting based on specific criteria. |
| TRANSPOSE | TRANSPOSE switches the rows and columns of an array. It’s used for converting rows into columns and vice versa. |
| UNIQUE | UNIQUE returns a list of unique values from a range or array. It’s used for extracting distinct values from a dataset. |
| VSTACK | VSTACK combines multiple ranges vertically into a single range. It’s used for merging data from different sources or sheets. |
| XLOOKUP | XLOOKUP searches a range or an array, and returns an item corresponding to the first match it finds. It’s an improved replacement for older lookup functions like VLOOKUP. |
| XMATCH | XMATCH searches a range or an array, and returns the relative position of an item within that range or array. It’s used for finding the position of a specific value. |
VBA Functions
| Function Name | Description |
|---|---|
| MsgBox | Displays a dialog box with a message and buttons for user interaction. |
| InputBox | Prompts the user for input in a dialog box. |
| Len | Returns the number of characters in a string. |
| Left / Right | Returns a specified number of characters from the start or end of a string. |
| Mid | Returns a specific number of characters from a string, starting at a specified position. |
| Date / Time | Returns the current date or time. |
| Now | Returns the current date and time. |
| Format | Converts a value to a specific format. |
| CInt / CDbl | Converts a value to an integer or double data type. |
| If…Then…Else | Executes a set of statements based on a condition. |
| For…Next | Repeats a set of statements a specified number of times. |
| Do…Loop | Repeats a set of statements as long as a condition is true. |
| Function | Defines a custom function. |
| Sub | Defines a subroutine (a group of code that can be executed). |
| WorksheetFunction | Allows you to use Excel worksheet functions in VBA code. |