Menu
Excel Functions
Logical Functions
| Function Name | Description | Function Variables |
|---|---|---|
| AND | Test multiple conditions with AND | logical1, logical2,… |
| FALSE | Generate the logical value FALSE | NA |
| IF | Test for a specific condition | logical_test, value_if_true, value_if_false |
| IFERROR | Trap and handle errors | value, value_if_error |
| IFNA | Trap and handle #N/A errors | value, value_if_na |
| IFS | Test multiple conditions, return first true | test1,value1,test2, value2… |
| NOT | Reverse arguments or results | logical |
| OR | Test multiple conditions with OR | logical1,logical2… |
| SWITCH | Match multiple values, return first match | expression,val1/result1,val2/result2,…,default |
| TRUE | Generate the logical value TRUE | NA |
| XOR | Perform exclusive OR | logical1,logical2,… |
Date and time Function
| Function Name | Description | Function Variables |
|---|---|---|
| DATE | Create a date with year, month, and day | year, month, day |
| DATEDIF | Get days, months, or years between two dates | start_date, end_date, unit |
| DATEVALUE | Convert a date in text format to a valid date | date_text |
| DAY | Get the day as a number (1-31) from a date | date |
| DAYS | Get days between dates | end_date, start_date |
| DAYS360 | Get days between 2 dates in a 360-day year | start_date, end_date, method |
| EDATE | Shift date n months in future or past | start_date, months |
| EOMONTH | Get last day of month n months in future or past | start_date, months |
| HOUR | Get the hour as a number (0-23) from a Time | serial_number |
| ISOWEEKNUM | Get ISO week number for a given date | date |
| MINUTE | Get minute as a number (0-59) from time | serial_number |
| MONTH | Get month as a number (1-12) from a date | serial_number |
| NETWORKDAYS | Get the number of working days between two dates | start_date, end_date, holidays |
| NETWORKDAYS.INTL | start_date, end_date, holidays | start_date, end_date, holidays |
| NOW | Get the current date and time | NA |
| SECOND | Get the Second as a number (0-59) from a Time | serial_number |
| TIME | Create a time with hours, minutes, and seconds | hour, minute, second |
| TIMEVALUE | Get a valid time from a text string | time_text |
| TODAY | Get the current date | NA |
| WEEKDAY | Get the day of the week as a number | serial_number, return_type |
| WEEKNUM | Get the week number for a given date | serial_num, return_type |
| WORKDAYS | Get a date n working days in the future or past | start_date, days, holidays |
| WORKDAY.INTL | Get date n working days in future or past | start_date, days. weekend, holidays |
| YEAR | Get the year from a date | date |
| YEARFRAC | Get the fraction of a year between two dates | start_date, end_date, basis |
Information Function
| Function Name | Description | Function Variables |
|---|---|---|
| ASC | Changes full-width English letters or katakana within a character string | text_value |
| CELL | Get information about a cell | info_type, reference |
| ERROR.TYPE | Test for a specific error value | error_val |
| INFO | Get information about current environment | type_text |
| ISBLANK | Test if a cell is empty | value |
| ISERR | Test for any error but #N/A | value |
| ISERROR | Test for any error | value |
| ISEVEN | Test if a value is even | value |
| ISFORMULA | Test if cell contains a formula | reference |
| ISLOGICAL | Test if a value is logical | value |
| ISNA | Test for the #N/A error | value |
| ISNONTEXT | Test for a non-text value | value |
| ISNUMBER | Test for numeric value | value |
| ISODD | Test if a value is odd | value |
| ISREF | Test for a reference | value |
| N | Returns a value converted to a number | value |
| NA | Returns the error value #N/A | NA |
| ISTEXT | Test for a text value | value |
| SHEET | Get sheet index number | value |
| SHEETS | Get number of sheets in a reference | reference |
| TYPE | Get the type of value in a cell | value |
Lookup and reference Function
| Function Name | Description | Function Variables |
|---|---|---|
| ADDRESS | Create a cell address from a row and column number | row_num, col_num, abs_num, a1, sheet |
| AREAS | Get the number of areas in a reference. | reference |
| CHOOSE | Get a value from a list based on position | index_num, value1, value2… |
| COLUMN | Get the column number of a reference | reference |
| COLUMNS | Get the number of columns in an array or reference | array |
| FORMULATEXT | Get the formula in a cell | reference |
| GETPIVOTDATA | Retrieve data from a pivot table in a formula | data_field, pivot_table, field1, item1… |
| HLOOKUP | Look up a value in a table arranged horizontally | lookup_value, table_array, row_index. range_lookup |
| HYPERLINK | Create a clickable link. | link_location, friendly_name |
| INDEX | Get a value in a list or table based on location | array, row_num, col_num, area_num |
| INDIRECT | Create a reference from text | ref_texta1 |
| LOOKUP | Look up a value in a one-column range | lookup_value, lookup_vector, result_vector |
| MATCH | Get the position of an item in an array | lookup_value, lookup_array, match_type |
| OFFSET | Create a reference offset from given starting point | reference, rows, cols, height, width |
| ROW | Get the row number of a reference | reference |
| ROWS | Get the number of rows in an array or reference | array |
| RTD | Retrieves real-time data from a program that supports COM automation | RealTimeServerProgID,ServerName,Topic1,[Topic2], … |
| TRANSPOSE | Flip the orientation of a range of cells | array |
| VLOOKUP | Lookup a value in a table by matching on the first column | lookup_value, table_array, column_index_num, range_lookup |
Text Function
| Function Name | Description | Function Variables |
|---|---|---|
| CHAR | Get a character from a number | number |
| CLEAN | Strip non-printable characters from text | text |
| CODE | Get the code for a character | text |
| CONCAT | Join text values without delimiter | text1, text2,… |
| CONCATENATE | Join text together | text1,text2 text3,… |
| DOLLAR | Convert a number to text in currency format | number, decimals |
| EXACT | Compare two text strings | text1,text2 |
| FIND | Get the location of text in a string | find_text, within_text, start_num |
| FIXED | Format number as text with fixed decimals | number, decimals, no_commas |
| LEFT | Extract text from the left of a string | text, num_chars |
| LEN | Get the length of text. | text |
| LOWER | Convert text to lower case | text |
| MID | Extract text from inside a string | text, start_num, num_chars |
| NUMBERVALUE | Convert text to number with custom separators | text, decimal_separator, group_separator |
| PROPER | Capitalize the first letter in each word | text |
| REPLACE | Replace text based on location | old_text, start_num, num_chars, new_text |
| REPT | Repeat text as specified | text, number_times |
| RIGHT | Extract text from the right of a string | text, num_chars |
| SEARCH | Get the location of text in a string | find_text, within_text, start_num |
| SUBSTITUTE | Replace text based on content | text, old_text, new_text, instance |
| T | Converts its arguments to text | value |
| TEXT | Convert a number to text in a number format | value, format_text |
| TEXTJOIN | Join text values with a delimiter | delimiter, ignore_empty, text1, text2, … |
| TRIM | Remove extra spaces from text | text |
| UNICHAR | Get Unicode character by number | number |
| UNICODE | Get number from Unicode character | text |
| UPPER | Convert text to upper case | text |
| VALUE | Convert text to a number | text |
| BAHTTEXT | Converts a number to text, using the ß (baht) currency format | number |
Statistical Function
| Function Name | Description | Function Variables |
|---|---|---|
| AVEDEV | Get sum of squared deviations | number1, number2,… |
| AVERAGE | Get the average of a group of numbers | number1, number2… |
| AVERAGEA | Get the average of a group of numbers and text | value1, value2,… |
| AVERAGEIF | Get the average of numbers that meet criteria. | range, criteria, average_range |
| AVERAGEIFS | Average cells that match multiple criteria | avg_rng, range1, criteria1, range2, criteria2, … |
| BINOM.DIST | Get binomial distribution probability | number_s, trials, probability_s, cumulative |
| BINOM.DIST.RANGE | Returns the probability of a trial result using a binomial distribution | trials, probability_s, number_s, [number_s2] |
| BINOMDIST | Get binomial distribution probability | number_s, trials, probability_s, cumulative |
| BETADIST | Returns the beta cumulative distribution function | x, alpha, beta, [Optional1], [Optional2] |
| BETA.DIST | Returns the beta cumulative distribution function | x, alpha, beta, cumulative, [Optional1], [Optional2] |
| BETAINV | Returns the inverse of the cumulative distribution function for a specified beta distribution | probability, alpha, beta, [Optional1], [Optional2] |
| BETA.INV | Returns the inverse of the cumulative distribution function for a specified beta distribution | probability, alpha, beta, [Optional1], [Optional2] |
| BINOM.INV | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value | trials, probability_s, alpha |
| COUNT | Count numbers | value1, value2, … |
| COUNTA | Count the number of non-blank cells | value1, value2, … |
| COUNTBLANK | Count cells that are blank | range |
| COUNTIF | Count cells that match criteria | range, criteria |
| COUNTIFS | Count cells that match multiple criteria | range1, criteria1, range2, criteria2, … |
| CONFIDENCE | Returns the confidence interval for a population mean | alpha, standard_dev, size |
| CONFIDENCE.NORM | Returns the confidence interval for a population mean | alpha, standard_dev, size |
| CONFIDENCE.T | Returns the confidence interval for a population mean, using a Student’s t distribution | alpha, standard_dev, size |
| CORREL | Returns the correlation coefficient between two data sets | array1, array2 |
| COVAR | Returns covariance, the average of the products of paired deviations | array1, array2 |
| COVARIANCE.P | Returns covariance, the average of the products of paired deviations | array1, array2 |
| COVARIANCE.S | Returns the sample covariance, the average of the products deviations for each data point pair in two data sets | array1, array2 |
| CRITBINOM | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value | trials, probability_s, alpha |
| DEVSQ | Get sum of squared deviations | number1, number2,… |
| EXPON.DIST | Returns the exponential distribution | x, lambda, cumulative |
| F.INV | Returns the inverse of the F probability distribution | probability, deg_freedom1, deg_freedom2 |
| F.INV.RT | Returns the inverse of the F probability distribution | probability, deg_freedom1, deg_freedom2 |
| FINV | Returns the inverse of the F probability distribution | probability, deg_freedom1, deg_freedom2 |
| F.DIST | Returns the F probability distribution | x, deg_freedom1, deg_freedom2, cumulative |
| FDIST | Returns the F probability distribution | x, deg_freedom1, deg_freedom2 |
| F.DIST.RT | Returns the F probability distribution | x, deg_freedom1, deg_freedom2 |
| FISHER | Returns the Fisher transformation | value |
| FISHERINV | Returns the inverse of the Fisher transformation | value |
| F.TEST | Returns the result of an F-test | array1, array2 |
| FTEST | Returns the result of an F-test | array1, array2 |
| FORECAST | Predict value along a linear trend | x, known_ys, kown_xs |
| FORECAST.ETS | Predict value with a seasonal trend | target_date, values, timeline, seasonality, data_completion, aggregation |
| FORECAST.ETS.CONFINT | Get confidence interval for forecast value at given date | target_date, values, timeline, confidence_level, seasonality, data_completion, aggregation |
| FORECAST.ETS.SEASONALITY | Get length of the seasonal pattern | values, timeline, data_completion, aggregation |
| FORECAST.ETS.STAT | Get statistical value related to forecasting | values, timeline, statistic_type, seasonality, data_completion, aggregation |
| FORECAST.LINEAR | Predict value along a linear trend | x, known_ys, kown_xs |
| FREQUENCY | Get the frequency of values in a data set | data_array, bins_array |
| GAMMA | Returns the Gamma function value | number |
| GAMMA.DIST | Returns the gamma distribution | x, alpha, beta, cumulative |
| GAMMADIST | Returns the gamma distribution | x, alpha, beta, cumulative |
| GAMMA.INV | Returns the inverse of the gamma cumulative distribution | probability, alpha, beta |
| GAMMAINV | Returns the inverse of the gamma cumulative distribution | probability, alpha, beta |
| GEOMEAN | Calculate geometric mean | number1, number2, … |
| GAMMALN | Returns the natural logarithm of the gamma function, Γ(x) | value |
| GAMMALN.PRECISE | Returns the natural logarithm of the gamma function, Γ(x) | value |
| GAUSS | Returns 0.5 less than the standard normal cumulative distribution | value |
| GROWTH | Returns values along an exponential trend | known_y’s, [known_x’s], [new_x’s], [const] |
| HARMEAN | Calculate harmonic mean | number1, number2, … |
| HYPGEOM.DIST | Returns the hypergeometric distribution | sample_s, number_sample, population_s, number_pop, cumulative |
| HYPGEOMDIST | Returns the hypergeometric distribution | sample_s, number_sample, population_s, number_pop |
| INTERCEPT | Get intercept of linear regression line | known_ys, known_xs |
| KURT function | Returns the kurtosis of a data set | number1, [number2], … |
| LARGE | Get nth largest value | array, k |
| LINEST | Get parameters of linear trend | known_ys, known_xs, conststats |
| LOGEST | Returns the parameters of an exponential trend | known_y’s, [known_x’s], [const], [stats] |
| LOGINV | Returns the inverse of the lognormal cumulative distribution | probability, mean, standard_dev |
| LOGNORM.DIST | Returns the cumulative lognormal distribution | x, mean, standard_dev, cumulative |
| LOGNORMDIST | Returns the cumulative lognormal distribution | x, mean, standard_dev |
| LOGNORM.INV | Returns the inverse of the lognormal cumulative distribution | probability, mean, standard_dev |
| MAX | Get the largest value | number1, number2… |
| MAXA | Return largest value | value1, value2, … |
| MAXIFS | Get maximum value with criteria | max_range, range1, criteria1, range2, criteria2… |
| MEDIAN | Get the median of a group of numbers | number1, number2… |
| MIN | Get the smallest value. | number1, number2… |
| MINA | Return smallest value | value1, value2, … |
| MINIFS | Get minimum value with criteria | min_range, range1, criteria1, range2, criteria2… |
| MODE | Get most frequently occurring number | number1, number2, … |
| MODE.MULT | Get most frequently occurring numbers | number1, number2, … |
| MODE.SNGL | Get most frequently occurring number | number1, number2, … |
| NORM.DIST | Get values and areas for the normal distribution | x, mean, standard_dev, cumulative |
| NORM.INV | Get the inverse of normal cumulative distribution | probability, mean, standard_dev |
| NORM.S.DIST | Get the standard normal CDF and PDF | z, cumulative |
| NORMSDIST | Returns the standard normal cumulative distribution | X |
| NORM.S.INV | Get inverse of the standard normal cumulative distribution | probability |
| NORMINV | Returns the inverse of the normal cumulative distribution | probability, mean, standard_dev |
| NEGBINOMDIST | Returns the negative binomial distribution | number_f, number_s, probability_s |
| PEARSON | Returns the Pearson product moment correlation coefficient | array1, array2 |
| PERCENTILE | Get kth percentile | array, k |
| PERCENTILE.EXC | Get kth percentile | array, k |
| PERCENTILE.INC | Get kth percentile | array, k |
| PERCENTRANK | Get percentile rank, inclusive | array, x, significance |
| PERCENTRANK.EXC | Get percentile rank, exclusive | array, x, significance |
| PERCENTRANK.INC | Get percentile rank, inclusive | array, x, significance |
| PERMUT | Get number of permutations without repetitions | number, number_chosen |
| PERMUTATIONA | Get number of permutations with repetitions | number, number_chosen |
| POISSON.DIST | Returns the Poisson distribution | x, mean, cumulative |
| POISSON | Returns the Poisson distribution | x, mean, cumulative |
| PHI | Returns the value of the density function for a standard normal distribution | number |
| PROB function | Returns the probability that values in a range are between two limits | x_range, prob_range, [lower_limit], [upper_limit] |
| QUARTILE | Get the quartile in a data set | array, quart |
| QUARTILE.EXC | Get the quartile in a data set | array, quart |
| QUARTILE.INC | Get the quartile in a data set | array, quart |
| RANK | Rank a number against a range of numbers | number, ref, order |
| RANK.AVG | Rank a number against a range of numbers | number, ref, order |
| RANK.EQ | Rank a number against a range of numbers | number ref, order |
| RSQ | Returns the square of the Pearson product moment correlation coefficient | known_y’s, known_x’s |
| SKEW | Get skewness of a distribution | number1, number2… |
| SKEW.P | Get skewness of a distribution based on population | number1, number2… |
| SLOPE | Get slope of linear regression line | known_ys, known_xs |
| SMALL | Get nth smallest value | array, k |
| STANDARDIZE | Calculate a normalized value (z-score) | x, mean, standard_dev |
| STEYX | Returns the standard error of the predicted y-value for each x in the regression | known_y’s, known_x’s |
| STDEV | Get the standard deviation in a sample | number1, number2,… |
| STDEV.P | Get standard deviation of population | number1, number2,… |
| STDEV.S | Get the standard deviation in a sample | number1, number2,… |
| STDEVA | Get standard deviation in a sample | number1, number2,… |
| STDEVP | Get standard deviation of population | number1, number2,… |
| STDEVPA | Get standard deviation for a population | number1, number2,… |
| T.TEST | Returns the probability associated with a Student’s t-test | array1, array2, tails, type |
| TTEST | Returns the probability associated with a Student’s t-test | array1, array2, tails, type |
| TINV | Returns the inverse of the Student’s t-distribution | probability, degrees_freedom |
| T.INV | Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom | probability, degrees_freedom |
| T.INV.2T | Returns the inverse of the Student’s t-distribution | probability, degrees_freedom |
| TDIST | Returns the Student’s t-distribution | x, degrees_freedom, tails |
| T.DIST | Returns the Percentage Points (probability) for the Student t-distribution | x, degrees_freedom, cumulative |
| T.DIST.2T | Returns the Percentage Points (probability) for the Student t-distribution | x, degrees_freedom |
| T.DIST.RT | Returns the Student’s t-distribution | x, degrees_freedom |
| TRIMMEAN | Calculate mean excluding outliers | array, percent |
| VAR | Get variation of a sample | number1, number2,… |
| VAR.P | Get variation of population | number1, number2,… |
| VAR.S | Get variation of a sample | number1, number2,… |
| VARA | Get variation of a sample | number1, number2,… |
| VARP | Get variation of a population | number1, number2,… |
| VARPA | Get variation of a population | number1, number2,… |
| WEIBULL | Calculates variance based on the entire population, including numbers, text, and logical values | x, alpha, beta, cumulative |
| WEIBULL.DIST | Returns the Weibull distribution | x, alpha, beta, cumulative |
| Z.TEST | Returns the one-tailed probability-value of a z-test | array, x, [sigma] |
| ZTEST | Returns the one-tailed probability-value of a z-test | array, x, [sigma] |
Engineering Function
| Function Name | Description | Function Variables |
|---|---|---|
| BIN2DEC | Converts a binary number to decimal | number |
| BIN2HEX | Converts a binary number to hexadecimal | number, places |
| BIN2OCT | Converts a binary number to octal | number, places |
| BITAND | Returns a ‘Bitwise And’ of two numbers | number1, number2 |
| BITLSHIFT | Returns a number shifted left by some number of bits | number, shift_amount |
| BITOR | Returns a ‘Bitwise Or’ of two numbers | number1, number2 |
| BITRSHIFT | Returns a number shifted right by some number of bits | number, shift_amount |
| BITXOR | Returns a ‘Bitwise Xor’ of two numbers | number1, number2 |
| BESSELI | Returns the modified Bessel function In(x) | x, n |
| BESSELK | Returns the modified Bessel function Kn(x) | x, n |
| BESSELY | Returns the Bessel function Yn(x) | x, n |
| CONVERT | Convert 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 number to octal | number, places |
| DELTA | Test two values are equal | number1, number2 |
| ERF | Returns the error function | lower_limit, [upper_limit] |
| ERF.PRECISE | Returns the error function | lower_limit, [upper_limit] |
| ERFC | Returns the complementary error function | number |
| ERFC.PRECISE | Returns the complementary ERF function integrated between x and infinity | number |
| GESTEP | Tests whether a number is greater than a threshold value | number, [step] |
| HEX2BIN | Converts a hexadecimal number to binary | number, places |
| HEX2DEC | Converts a hexadecimal number to decimal | number |
| HEX2OCT | Converts a hexadecimal number to octal | number, places |
| IMABS | Get absolute value of complex number | inumber |
| IMAGINARY | Get imaginary coefficient of complex number | inumber |
| IMPOWER | Raise complex number to given power | inumber, number |
| IMPRODUCT | Get product of complex numbers | inumber1, inumber2,… |
| IMREAL | Get real coefficient of complex number | inumber |
| IMSUB | Get difference between two complex numbers | inumber1, inumber2 |
| IMSUM | Get sum of complex numbers | inumber1, inumber2… |
| IMARGUMENT | Returns the argument theta, an angle expressed in radians | inumber |
| IMCONJUGATE | Returns the complex conjugate of a complex number | inumber |
| IMCOS | Returns the cosine of a complex number | inumber |
| IMCOSH | Returns the hyperbolic cosine of a complex number | inumber |
| IMCOT | Returns the cotangent of a complex number | inumber |
| IMCSC | Returns the cosecant of a complex number | inumber |
| IMDIV | Returns the quotient of two complex numbers | inumber1, inumber2 |
| IMEXP | Returns the exponential of a complex number | inumber |
| IMLN | Returns the natural logarithm of a complex number | inumber |
| IMLOG10 | Returns the base-10 logarithm of a complex number | inumber |
| IMLOG2 | Returns the base-2 logarithm of a complex number | inumber |
| IMSEC | Returns the secant of a complex number | inumber |
| IMSIN | Returns the sine of a complex number | inumber |
| IMSQRT | Returns the square root of a complex number | inumber |
| IMTAN | Returns the tangent of a complex number | inumber |
| OCT2BIN | Converts an octal number to binary | number, [places] |
| OCT2DEC | Converts an octal number to decimal | number |
| OCT2HEX | Converts an octal number to hexadecimal | number, [places] |
Financial Function
| Function Name | Description | Function Variables |
|---|---|---|
| ACCRINT | Get accrued interest periodic | id, fd, sd, rate, par, freq, basis, calc |
| ACCRINTM | Get accrued interest at maturity | id, sd, rate, par, basis |
| AMORDEGRC | Depreciation for accounting period coefficient | cost, purchase, first, salvage, period, rate, basis |
| AMORLINC | Depreciation for accounting period | cost, purchase, first, salvage, period, rate, basis |
| COUPDAYBS | Get days from coupon period to settlement date | settlement, maturity, frequency, basis |
| COUPDAYS | Get days in coupon period incl settlement date | settlement, maturity, frequency, basis |
| COUPDAYSNC | Get days from settlement date to next coupon date | settlement, maturity, frequency, basis |
| COUPNCD | Get next coupon date after settlement date | settlement, maturity, frequency, basis |
| COUPNUM | Get number of coupons payable | settlement, maturity, frequency, basis |
| COUPPCD | Get previous coupon date before settlement date | settlement, maturity, frequency, basis |
| CUMIPMT | Get cumulative interest paid on a loan | rate, nper, pv, start_period, end_period, type |
| CUMPRINC | Get cumulative principal paid on a loan | raten, per, pv, start_period, end_period, type |
| DB | Depreciation – fixed-declining balance | cost, salvage, life, period, month |
| DDB | Depreciation – double-declining | cost, salvage, life, period, factor |
| DISC | Get discount rate for a security | settlement, maturity, pr, redemption, basis |
| DOLLARDE | Convert dollar price as fraction to decimal | fractional_dollar, fraction |
| DOLLARFR | Convert price to fractional notation | decimal_dollar, fraction |
| DURATION | Get annual duration with periodic interest | settlement, maturity, coupon, yld, freq, basis |
| EFFECT | Get effective annual interest rate | nominal_rate, npery |
| EUROCONVERT | converts a number from euros to a euro member currency | number, source, target, full_precision, triangulation_precision |
| FV | Get the future value of an investment | rate, nper, pmt, pv, type |
| FVSCHEDULE | Get future value of principal compound interest | principal, schedule |
| INTRATE | Get interest rate for fully invested security | settlement, maturity, investment, redemption, basis |
| IPMT | Get interest in given period | rate, per, nper, pv, fv, type |
| IRR | Calculate internal rate of return | values, guess |
| MDURATION | Get Macauley modified duration par value of $100 | settlement, maturity, coupon, yld, freq, basis |
| MIRR | Calculate modified internal rate of return | values, finance_rate, reinvest_rate |
| NOMINAL | Get annual nominal interest rate | effect_rate, npery |
| NPER | Get number of periods for loan or investment | rate, pmt, pv, fv, type |
| NPV | Calculate net present value | Calculate net present value |
| ODDFPRICE | Get price per $100 odd first period | sd, md, id, fd, rate, yld, redem, freq, basis |
| ODDFYIELD | Get yield security with odd first period | sd, md, id, fd, rate, pr, redem, freq, basis |
| ODDLPRICE | Get price per $100 face value with odd last period | sd, md, id, rate, yld, redem, freq, basis |
| ODDLYIELD | Get yield of security with odd last period | sd, md, ldr, ate, pr, redem, freq, basis |
| PDURATION | Get periods required to reach given value | rate, pv, fv |
| PMT | Get the periodic payment for a loan | rate, nper, pv, fv, type |
| PPMT | Get principal payment in given period | rate, per, nper, pv, fv, type |
| PRICE | Get price per $100 face value – periodic interest | sd, md, rate, yld, redemption, frequency, basis |
| PRICEDISC | Get price per $100 discounted security | sd, md, discount, redemption, basis |
| PRICEMAT | Get price per $100 interest at maturity | sd, md, id, rate, yld, basis |
| PV | Get the present value of an investment | rate, nper, pmt, fv, type |
| RATE | Get the interest rate per period of an annuity | nper, pmt, pv, fv, type, guess |
| RECEIVED | Get amount received at maturity | settlement, maturity, investment, discount, basis |
| RRI | Get equivalent interest rate for growth | nper, pv, fv |
| SLN | Depreciation – straight-line | cost, salvage, life |
| SYD | Depreciation – sum-of-years | cost, salvage, life, period |
| TBILLEQ | Get bond-equivalent yield for a Treasury bill | settlement, maturity, discount |
| TBILLPRICE | Get price per $100 Treasury bill | settlement, maturity, discount |
| TBILLYIELD | Get yield for a Treasury bill | settlement, maturity, price |
| VDB | Depreciation – double-declining variable | cost, salvage, life, start, end, factor, no_switch |
| XIRR | Calculate internal rate of return for irregular cash flows | values, dates, guess |
| XNPV | Calculate net present value for irregular cash flows | rate, values, dates |
| YIELD | Get yield for security that pays periodic interest | sd, md, rate, pr, redemption, frequency, basis |
| YIELDDISC | Get annual yield for discounted security | sd, md, pr, redemption, basis |
| YIELDMAT | Get annual yield of security interest at maturity | sd, md, id, rate, pr, basis |
Math Function
| Function Name | Description | Function Variables |
|---|---|---|
| AGGREGATE | Return aggregate calculation | function_num, options, ref1, ref2 |
| ARABIC | Converts a Roman numerals to an Arabic numerals | roman_text |
| BASE | Convert number to another base | number, radix, min_length |
| CEILING | Round a number up to nearest multiple | number, significance |
| CEILING.MATH | Round a number up to nearest multiple | number, significance, mode |
| CEILING.PRECISE | Round a number up to nearest multiple | number, significance |
| COMBIN | Get number of combinations without repetitions | number, number_chosen |
| COMBINA | Get number of combinations with repetitions | number, number_chosen |
| CUBEKPIMEMBER | Returns a key performance indicator (KPI) name, property, and measure, and displays the name and property in the cell. | connection, kpi_name, kpi_property, [Optional caption] |
| CUBEMEMBER | Returns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube. | connection, member_expression, [Optional caption] |
| CUBEMEMBERPROPERTY | Returns the value of a member property in the cube | connection, member_expression, property |
| CUBERANKEDMEMBER | Returns the nth, or ranked, member in a set | connection, set_expression, rank, [Optional caption] |
| CUBESET | Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set | conn, set expression, , [sort order], [sort by] |
| CUBESETCOUNT | Returns the number of items in a set | set |
| CUBEVALUE | Returns an aggregated value from a cube. | connection, [member expression1], [member expression2],… |
| DECIMAL | Convert a number in a different base to a decimal number | number, radix |
| EVEN | Round a number up to the next even integer | number |
| EXP | Find the value of e raised to the power of a number | number |
| FACT | Find the factorial of a number | number |
| FACTDOUBLE | Get double factorial of a number | number |
| FLOOR | Round a number down to the nearest specified multiple | number, significance |
| FLOOR.MATH | Round number down to nearest multiple | number, significance, mode |
| FLOOR.PRECISE | Round number down to nearest multiple | number, significance |
| GCD | Get the greatest common divisor of numbers | number1, number2,… |
| INT | Get the integer part of a number by rounding down | number |
| LCM | Get the least common multiple of numbers | number1, number2,… |
| LN | Get the natural logarithm of a number | number |
| LOG | Get the logarithm of a number | number, base |
| LOG10 | Get the base-10 logarithm of a number | number |
| MDETERM | Get matrix determinant of given array | array |
| MINVERSE | Get inverse matrix of array | array |
| MMULT | Perform matrix multiplication | array1, array2 |
| MOD | Get the remainder from division | number, divisor |
| MROUND | Round a number to the nearest specified multiple | number, significance |
| MUNIT | Return unit matrix for a given dimension | dimension |
| MULTINOMIAL | Returns the multinomial of a set of numbers | number1, [number2], … |
| ODD | Round a number up to the next odd integer | number |
| PI | Get the value of π | NA |
| POWER | Raise a number to a power | number, power |
| PRODUCT | Get the product of supplied numbers | number1, number2, … |
| QUOTIENT | Returns the quotient without a remainder | numerator, denominator |
| RAND | Get a random number between 0 and 1 | NA |
| RANDBETWEEN | Get a random integer between two values | bottom, top |
| ROMAN | Converts numbers to Roman numerals | number, form |
| ROUND | Round a number to a given number of digits | number, num_digits |
| ROUNDDOWN | Round down to given number of digits | number, num_digits |
| ROUNDUP | Round a number up to a given number of digits | number, num_digits |
| SEC | Returns the secant of an angle | number |
| SECH | Returns the hyperbolic secant of an angle | number |
| SIGN | Get the sign of a number | number |
| SQRT | Find the positive square root of a number | number |
| SQRTPI | Returns the square root of (number * pi) | number |
| SUBTOTAL | Get a subtotal in a list or database | function_num, ref1, ref2,… |
| SUM | Add numbers together | number1, number2, number3,… |
| SUMIF | Sum numbers in a range that meet supplied criteria | range, criteria, sum_range |
| SUMIFS | Sum cells that match multiple criteria | sum_range, range1, criteria1, range2, criteria2,… |
| SUMPRODUCT | Multiply, then sum arrays | array1, array2,… |
| SUMSQ | Get sum of squares of supplied values | number1, number2,… |
| SUMX2MY2 | Sum of difference of squares in two arrays | array_x, array_y |
| SUMX2PY2 | Get sum of squares in two arrays | array_x, array_y |
| SUMXMY2 | Sum of squares of differences in two arrays | array_x, array_y |
| TRUNC | Truncate a number to a given precision | number, num_digits |
Dynamic array Function
| Function Name | Description | Function Variables |
|---|---|---|
| FILTER | Filters range with given criteria | array, include, if_empty |
| LAMBDA | Create custom function | parameter,…,calculation |
| LET | Assign variables inside formula | name1, value1, name2/value2, …, result |
| MAKEARRAY | Create array with calculated values | rows, columns, lambda |
| MAP | Map array to custom function | array1, array2,…,lambda |
| RANDARRAY | Get array of random numbers | rows, columns, min, max, integer |
| REDUCE | Reduce an array | initial_value, array, lambda |
| SCAN | Scan array and return intermediate results | initial_value, array, lambda |
| SEQUENCE | Get array of list of sequential numbers | rows, columns, start, step |
| SERIESSUM | Returns the sum of a power series based on the formula | x, n, m, coefficients |
| SORT | Sorts range or array | array, sort_index, sort_order, by_col |
| SORTBY | Sorts range or array by column | array, by_array, sort_order, array/order,… |
| UNIQUE | Extract unique values from range | array, by_col, exactly_once |
| XMATCH | Get the position of an item in a list or table | lookup_value, lookup_array, match_mode, search_mode |
Trigonometry Function
| Function Name | Description | Function Variables |
|---|---|---|
| ABS | Returns the absolute value of a number | number |
| ACOS | Get the inverse cosine of a value, in radians. | number |
| ACOSH | Returns the inverse hyperbolic cosine of a number | number |
| ACOT | Returns the arc cotangent of a number | number |
| ACOTH | Returns the hyperbolic arc cotangent of a number | number |
| ASIN | Return the inverse sine of a value in radians | number |
| ASINH | Returns the inverse hyperbolic sine of a number | number |
| ATAN | Count matching records in a database | number |
| ATAN2 | Get arctangent from x- and y-coordinates | x_num, y_num |
| ATANH | Returns the inverse hyperbolic tangent of a number | number |
| COS | Get the cosine of an angle provided in radians. | number |
| COSH | Get hyperbolic cosine of a number | number |
| COT | Get the cotangent of an angle. | number |
| COTH | Returns the cotangent of an angle | number |
| CSC | Get cosecant of an angle | number |
| CSCH | Returns the hyperbolic cosecant of an angle | number |
| DEGREES | Converts radians to degrees | angle |
| SEC | Get secant of an angle | number |
| SIN | Get the sine of an angle provided in radians. | number |
| SINH | Get hyperbolic sine of a number. | number |
| TAN | Get the tangent of an angle | number |
| TANH | Returns the hyperbolic tangent of a number | number |
Database Function
| Function Name | Description | Function Variables |
|---|---|---|
| DAVERAGE | Get average from matching records | database, field, criteria |
| DCOUNT | Count matching records in a database | database, field, criteria |
| DCOUNTA | Count matching records in a database | database, field, criteria |
| DGET | Get value from matching record | database, field, criteria |
| DMAX | Get max from matching records | database, field, criteria |
| DMIN | Get min from matching records | database, field, criteria |
| DPRODUCT | Get product from matching records | database, field, criteria |
| DSTDEV | Get standard deviation of sample in matching records | database, field, criteria |
| DSTDEVP | Get standard deviation of population in matching records | database, field, criteria |
| DSUM | Get sum from matching records | database, field, criteria |
| DVAR | Get sample variance for matching records | database, field, criteria |
| DVARP | Get population variance for matching records | database, field, criteria |