数式
説明
詳細
SUM
Returns the sum of a range of numbers or cells.
SUM(number1, [number2], ...)
•number1: The first number or range to add.
•number2: Optional.Additional numbers or ranges to add.
SUM(number1, [number2], ...)
Example: SUM(5, 10, 15) returns 30 SUM(A2:A6) returns the sum of the values in cells A2 through A6 SUM(B2:B6, D2:D6) returns the sum of the values in both ranges B2 through B6 and D2 through D6.
AVERAGE
Returns the average (arithmetic mean) of the arguments.
AVERAGE(number1, [number2], ...)
•number1: The first number or range of numbers for which to calculate the average.
•numberN: Optional.Additional numbers or ranges of numbers for which to calculate the average.
Example: AVERAGE(1, 2, 3, 4, 5) returns 3 AVERAGE([2,4,6,8], [1,3,5,7]) returns 4.5 AVERAGE(-2, -1, 0, 1, 2) returns 0.
MIN
Returns the smallest number in a set of values, including numbers, arrays, and references.
MIN(number1,[number2],...)
•number1: The first numeric value, array, or reference for which you want to find the minimum value.
•[numberN]: Optional.Additional numeric values, arrays, or references for which you want to find the minimum value.You can have up to 255 arguments.
Example: MIN(3, 5, 1) returns 1 MIN(A1:C10) returns the smallest value in the range A1 through C10 MIN([1, 2, 3, 4, 5]) returns 1.
MAX
Returns the largest value in a range of cells.
MAX(number1, [number2], ...)
•number1: The first number or range of numbers that you want to find the maximum value for.
•numberN: Optional.Additional numbers or ranges of numbers that you want to find the maximum value for.You can specify up to 255 arguments.
Example: MAX(A1:A10) returns the largest value in the range A1:A10 MAX(10, 20, 30) returns the number 30, which is the largest value among the arguments MAX(B1:B5, C1:C5) returns the largest value between the two ranges B1:B5 and C1:C5.
COUNT
Counts the number of cells in a range that contain numbers.
COUNT(value1, [value2], ...)
•value1: The first value or range of cells to count.
•valueN: Optional.Additional values or ranges of cells to count.You can enter up to 255 arguments.
Example: COUNT(A1:A10) returns the number of cells in A1 through A10 that contain numbers COUNT(A1, B1, C1, D1) returns the number of cells with numbers in cells A1, B1, C1, and D1 COUNT(1, "text", TRUE, "", 5) returns 3.
SUMIF
Returns the sum of a range of cells that meet a specified criteria.
SUMIF(range, criteria, [sum_range])
•range: The range of cells to evaluate.
•criteria: The criteria used to determine which cells to add.
•sum_range: Optional.The range of cells to add together.If omitted, the cells in 'range' are used.
Example: SUMIF(A2:A6, ">10") returns the sum of all values in cells A2 through A6 that are greater than 10 SUMIF(A2:A6, "apples", B2:B6) returns the sum of all values in cells B2 through B6 where the corresponding cell in A2 through A6 contains the text "apples" SUMIF(D2:D6, "=green", E2:E6) returns the sum of all values in cells E2 through E6 where the corresponding cell in D2 through D6 contains the text "green".
COUNTIF
Counts the number of cells in a range that meet a specified criterion.
COUNTIF(range, criteria)
•range: The range of cells to be evaluated by the criteria.
•criteria: The criteria used to determine which cells to count.
Example: COUNTIF(A1:A10,">5") returns the number of cells in A1 through A10 that are greater than 5 COUNTIF(B2:D5,"=Bob") returns the number of cells in range B2 through D5 that equal "Bob" COUNTIF(H1:H100,"<>0") returns the number of cells in column H from rows 1 through 100 that are not equal to 0.
IF
The IF function is used to perform a logical test and return one value if the condition is true, and another value if the condition is false.
IF(logical_test, [value_if_true], [value_if_false])
•logical_test: The logical test to perform.
•value_if_true: Optional.The value to return if the logical_test evaluates to TRUE.If omitted, returns TRUE.
•value_if_false: Optional.The value to return if the logical_test evaluates to FALSE.If omitted, returns FALSE.
Example: IF(A1 > 10, "Greater than 10", "Less than or equal to 10") returns 'Greater than 10' if the value in cell A1 is greater than 10, and 'Less than or equal to 10' if it is less than or equal to 10.IF(B2 = "Yes", C2*0.1, C2) returns 10% of the value in cell C2 if the value in cell B2 is 'Yes', and the value in cell C2 if it is not.IF(D4 <> "", D4, E4) returns the value in cell D4 if it is not blank, and the value in cell E4 if it is.
MINUS
Returns the difference between two numbers or cells.
MINUS(number1, number2)
•number1: The first number or cell.
•number2: The second number or cell.
Example: MINUS(10, 5) returns 5 MINUS(A2, B2) returns the difference between the values in cells A2 and B2.
RSQ
Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
RSQ(known_y's, known_x's)
•known_y's: An array or range of dependent data points.
•known_x's: An array or range of independent data points.
Example: RSQ([2,3,9,1,8], [6,5,11,7,5])
DVARP
The DVARP function calculates the variance of a population.
DVARP(database, field, criteria)
•database: The range of cells that makes up the database.
•field: The column label that contains the numbers for which you want the variance.
•criteria: The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
Example: DVARP(A2:C10, 'Sales', A1:C1)
QUARTILE.INC
Calculates the inclusive quartile of a dataset, which is a value that separates the lowest 25% from the highest 75% of values.This function uses a slightly different calculation than the QUARTILE function.
QUARTILE.INC(array, quart)
•array: The array or range of data for which to determine the quartile.
•quart: The quartile to return.1 returns the minimum value, 2 returns the value at the first quartile, 3 returns the value at the median (second quartile), and 4 returns the value at the third quartile.
Example: QUARTILE.INC(A1:A10,2) returns the value at the first quartile (the value separating the lowest 25% of values from the highest 75%) for the range A1:A10 using the inclusive method QUARTILE.INC(B2:B20,3) returns the value at the median (the second quartile) for the range B2:B20 using the inclusive method
DAYS360
Calculates the number of days between two dates using a 360-day year.
DAYS360(start_date, end_date, [method])
•@param start_date: The start date of the time period.
•@param end_date: The end date of the time period.
•@param [method]: Optional.A flag that specifies the method to use for calculating the number of days.If omitted or zero, the US method is used.If non-zero, the European method is used.
Example: DAYS360("2012-02-02","2012-03-30") returns 58
OR
Returns true if any argument is true, and false otherwise.
OR(logical1, [logical2], ...)
•logical: The first condition to be evaluated.
•logicalN: Optional.An optional condition to be evaluated.
Example: OR(1=1,2=3) returns true OR(1=2,2=3,3=4) returns false OR(A1="Yes",A2="Yes",A3="Yes") returns true if at least one of the specified cells contains the value "Yes"
COVARIANCE.S
Calculates the sample covariance, which is an estimate of how much two random variables change together, between two specified sets of data.
COVARIANCE.S(array1, array2)
•array1: The first array or range of data.
•array2: The second array or range of data.Must be equal in length to array1.
Example: COVARIANCE.S([1,2,3,4],[5,6,7,8])
ARRAYTOTEXT
Converts an array into a delimited text string.
ARRAYTOTEXT(array, [format])
•array: The array to convert to text.
•[format]: Optional.The delimiter to use between values.Defaults to ',' if not specified.
Example: ARRAYTOTEXT([1, 2, 3, 4], 0) returns '1,2,3,4' ARRAYTOTEXT(["apples","oranges","bananas"], "1 ") returns '["apples", "oranges", "bananas"]'
SORT
Returns a sorted range or array.
SORT(array, [sort_index], [sort_order], [by_col])
•array: The range or array of values to sort.
•[sort_index]: Optional.The index of the column or row by which to sort.Default is 1.
•[sort_order]: Optional.The order in which to sort.1 for ascending order, -1 for descending order.Default is 1.
•[by_col]: Optional.TRUE to sort by column, FALSE to sort by row.Default is TRUE.
Example: SORT(A1:A10) returns the values in range A1:A10 sorted in ascending order SORT(B2:E6, 3, -1, TRUE) sorts the range B2:E6 by the values in the third column, in descending order, and by column SORT([5,4,3,2,1]) returns [1,2,3,4,5]
DDB
Calculates the depreciation of an asset for a specified period using the double-declining balance method.
DDB(cost, salvage, life, period, [factor])
•cost: The initial cost of the asset.
•salvage: The value of the asset at the end of its useful life.
•life: The number of periods over which the asset will be depreciated.
•period: The period for which you want to calculate the depreciation.Must be greater than or equal to 1 and less than or equal to the life of the asset.
•[factor]: Optional.The rate at which the balance declines during the first year of depreciation.If omitted or zero, defaults to 2 (double-declining balance).
Example: DDB(10000, 2000, 10, 2) returns $1600 (depreciation for second year) DDB(50000, 5000, 5, 3, 1.5) returns $7350 (depreciation for third year with factor of 1.5)
LOGNORM.DIST
Returns the cumulative log-normal distribution for a specified value, mean, and standard deviation.
LOGNORM.DIST(x, mean, standard_dev, [cumulative])
•x: The value for which you want to find the log-normal distribution.
•mean: The mean of ln(x), where x is normally distributed.Must be positive.
•standard_dev: The standard deviation of ln(x), where x is normally distributed.Must be positive.
•cumulative: Optional.A logical value that specifies the type of distribution to return.If TRUE, returns the cumulative distribution function; if FALSE or omitted, returns the probability mass function.
Example: LOGNORM.DIST(4, 3.5, 1.2) returns approximately 0.0176176 LOGNORM.DIST(4, 3.5, 1.2) returns approximately 0.0390836
BETA.DIST
Returns the cumulative beta probability density function.
BETA.DIST(x, alpha, beta, [cumulative], [A], [B])
•x: The value at which to evaluate the function.
•alpha: The first parameter of the beta distribution.Must be greater than 0.
•beta: The second parameter of the beta distribution.Must be greater than 0.
•[cumulative]: Optional.A logical value that specifies whether to return the cumulative distribution or the probability density function.If omitted, cumulative defaults to FALSE (cumulative distribution).
•[A]: Optional.The lower bound of the interval of x.If omitted, A defaults to 0.
•[B]: Optional.The upper bound of the interval of x.If omitted, B defaults to 1.
Example: BETA.DIST(4, 3.5, 1.2, true) returns 0.0390836 BETA.DIST(4, 3.5, 1.2, false) returns 0.0176176
COTH
Returns the hyperbolic cotangent of a number.
COTH(x)
x: The number for which you want to calculate the hyperbolic cotangent.
Example: COTH(0) returns #DIV/0! COTH(1) returns 1.313 COTH(-1) returns -1.313
HEX2DEC
Converts a hexadecimal number to decimal.
HEX2DEC(x)
x: The hexadecimal number you want to convert to decimal.
Example: HEX2DEC("A") returns 10 HEX2DEC("1D") returns 29 HEX2DEC("FFFF") returns 65535
RIGHT
Returns a specified number of characters from the end of a text string.
RIGHT(text, number_of_characters)
•text: The text string containing the characters to return.
•number_of_characters: The number of characters to return from the end of the text string.
Example: RIGHT("Hello World", 5) returns "World" RIGHT("Excel Functions", 9) returns "Functions" RIGHT("12345", 2) returns "45"
PV
Calculates the present value of an investment or loan based on a constant interest rate and future payments or receipts.
PV(rate, nper, pmt, [fv], [type])
•rate: The interest rate per period.
•@param nper: The total number of payment periods in the investment or loan.
•pmt: The payment made each period; it cannot change over the life of the investment or loan.
•[fv]: Optional.The future value of the investment or loan, payable after the final payment is made.If omitted, defaults to 0.
•[type]: Optional.The timing of the payment.If omitted, defaults to 0 (payments due at the end of the period).Use 1 for payments due at the beginning of the period.
Example: PV(0.1/12, 24, 1000, 10000, 0) returns approximately -29864.950264779152
COLUMN
Returns the column number of a reference.
COLUMN([reference])
[reference]: Optional.The cell or range of cells for which you want to return the column number.If omitted, returns the column number of the cell in which the formula appears.
Example: COLUMN(A1) returns 1 COLUMN(B4:C7) returns [[2, 3]] COLUMN() returns the column number of the cell containing this formula
MDETERM
Returns the matrix determinant of a square matrix.
MDETERM(matrix)
matrix: The array or range containing the square matrix for which you want to calculate the determinant.The matrix must have the same number of rows and columns.
Example: MDETERM(A1:C3) returns the determinant of the 3x3 matrix located in the range A1:C3 MDETERM(A1:D4) returns the determinant of the 4x4 matrix located in the range A1:D4.
IRR
Calculates the internal rate of return for a series of cash flows that occur at regular intervals.
IRR(values, [guess])
•values: An array or range of cash flows for which you want to calculate the internal rate of return.
•[guess]: Optional.An initial estimate of the internal rate of return.If omitted, it defaults to 0.1 (10%).
Example: IRR([-75000, 12000, 15000, 18000, 21000, 24000]) returns 0.05715142887178467 IRR([-75000, 12000, 15000, 18000, 21000, 24000], 0.075) returns 0.05715142887178447
FIXED
Rounds a number to the specified number of digits and formats the result with a fixed number of decimal places.
FIXED(number, [decimals], [no_commas])
•number: The number to be rounded and formatted.
•[decimals]: Optional.The number of digits to the right of the decimal point.Default is 2.
•[no_commas]: Optional.A logical value that specifies whether to use a comma as the thousands separator.Default is FALSE (comma is displayed).
Example: FIXED(1234.5678) returns 1234.57 FIXED(9876.54321, 3) returns 9876.543 FIXED(12345.6789, 1, TRUE) returns 12,345.7
STEYX
Calculates the standard error of the predicted y-value for each x in a regression.
STEYX(known_y's, known_x's)
•known_y's: An array or range of y-values that correspond to the x-values.
•known_x's: An array or range of x-values that correspond to the y-values.
Example: STEYX(B2:B6, A2:A6) returns the standard error of the predicted y-value for each x in a linear regression of the values in cells A2 through A6 and B2 through B6 STEYX(C2:C7, A2:A7) returns the standard error of the predicted y-value for each x in a linear regression of the values in cells A2 through A7 and C2 through C7 STEYX(E2:E9, D2:D9) returns the standard error of the predicted y-value for each x in a linear regression of the values in cells D2 through D9 and E2 through E9
FORECAST.ETS.STAT
Calculates statistical values for the Exponential Smoothing (ETS) algorithm applied to a time series data set.
FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])
•values: An array of numerical values representing the historical data points.
•timeline: An array of dates or numerical values representing the timeline corresponding to the historical data points.It should have the same number of elements as the 'values' parameter.
•statistic_type: A string specifying the desired statistical measure.Available options include 'alpha', 'beta', 'gamma', 'phi', and 'sigma'.
•[seasonality]: An optional parameter that specifies the number of data points per season for seasonal data.
•[data_completion]: An optional parameter that indicates whether the data should be considered complete (TRUE) or incomplete (FALSE).
•[aggregation]: An optional parameter that specifies the aggregation type to use for the data.Default is 'AVERAGE'.
Example: FORECAST.ETS.STAT(B2:B8, A2:A8, "alpha") returns the "alpha" smoothing parameter for the given data.
FORECAST.ETS.CONFINT
Calculates the lower and upper bounds of the confidence interval for a predicted value by using the Exponential Smoothing (ETS) algorithm.
FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])
•target_date: The date for which you want to predict a value.
•values: An array of numbers representing the historical data.
•timeline: An array of dates or numbers representing the timeline of the historical data.Should have the same number of elements as the 'values' parameter.
•[confidence_level]: Optional.The confidence level as a decimal value between 0 and 1.Default is 0.95, which corresponds to a 95% confidence level.
•[seasonality]: Optional.An integer specifying the number of periods in a complete season.Default is automatically detected.
•[data_completion]: Optional.A logical value that specifies whether to fill in missing data points.Default is FALSE (missing data points are not filled).
•[aggregation]: Optional.A string that specifies how to aggregate data for each period.Default is "AVERAGE".Other options include "SUM", "COUNT", and "MIN".
Example: FORECAST.ETS.CONFINT(B2:B6, A2:A6, "6/1/2019", 0.95) returns a confidence interval for the predicted sales value for June 1, 2019
PRICEDISC
Returns the price per $100 face value of a discounted security.
PRICEDISC(settlement, maturity, discount, redemption, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•discount: The discount rate of the security.
•redemption: The redemption value per $100 face value of the security.
•[basis]: Optional.The day count basis to use for calculating bond interest.If omitted, defaults to 0 (US (NASD) 30/360).
Example: PRICEDISC("2023-01-05","2023-01-31", 0.038, 100, 0) returns 99.72555556
CHISQ.TEST
Returns the test for independence.
CHISQ.TEST(actual_range, expected_range)
•actual_range: A range of cells containing the observed frequency counts.Each cell must contain a non-negative number.
•expected_range: A range of cells containing the expected frequency counts.Each cell must contain a non-negative number.
Example: CHISQ.TEST(A2:B4, C2:D4)
LOWER
Returns the test for independence.
LOWER(text)
text: The text string that you want to convert to lowercase.You can enter the text string directly into the function or reference a cell that contains the text string.
Example: LOWER("HELLO") returns the text string "hello" LOWER(A1) returns the text string in cell A1 with all uppercase letters converted to lowercase LOWER("Hello World") returns the text string "hello world".
PRICE
Returns the price per $100 face value of a security with an annual coupon rate.
PRICE(settlement, maturity, rate, yld, redemption, [frequency], [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•rate: The annual coupon rate of the security.
•yld: The annual yield of the security.
•redemption: The redemption value per $100 face value of the security.
•[frequency]: Optional.The number of coupon payments per year.If omitted, defaults to 2 (semi-annual).
•[basis]: Optional.The day count basis to use for calculating bond interest.If omitted, defaults to 0 (US (NASD) 30/360).
Example: PRICE("1/1/2023","1/1/2043",6%,8%,100)
NOT
Returns the opposite of a logical value, i.e., returns true if the value is false, and false if the value is true.
NOT(logical)
logical: The logical value to negate.
Example: NOT(true) returns false NOT(false) returns true NOT(5>10) returns true
COUNTBLANK
Counts the number of empty cells in a range.
COUNTBLANK(range)
range: The range of cells to count the empty cells.
Example: COUNTBLANK(A1:A10) returns the number of empty cells in A1 through A10
NORMDIST
Returns the normal distribution for the specified mean and standard deviation.
NORMDIST(x, mean, standard_dev, cumulative)
•x: The value at which to evaluate the function.
•mean: The arithmetic mean of the distribution.
•standard_dev: The standard deviation of the distribution.
•cumulative: Logical value that specifies the form of the function.If cumulative is true, NORMDIST returns the cumulative distribution function; if false, it returns the probability density function.If omitted, cumulative defaults to true.
Example: NORMDIST(1.96,0,1,FALSE)
OFFSET
Returns a reference to a range that is offset from a starting cell or range.
OFFSET(reference, rows, cols, [height], [width])
•reference: The starting point from which you want to base the offset.
•rows: The number of rows, up (negative value) or down (positive value), by which the resulting range should be offset.
•cols: The number of columns, to the left (negative value) or right (positive value), by which the resulting range should be offset.
•[height]: Optional.Argument that specifies the height, in number of rows, that you want the resulting range to be.
•[width]: Optional.Argument that specifies the width, in number of columns, that you want the resulting range to be.
Example: OFFSET(A1,2,2) returns a reference to the cell in the second row and second column relative to cell A1 OFFSET(A1:B5,1,1,1,1) returns a reference to the cell in the second row and second column of the range A2:B3 OFFSET(A1,0,0,3,3) returns a reference to a 3x3 range starting with cell A1
POWER
Returns the result of a number raised to a power.
POWER(number, power)
•number: The base number.
•power: The exponent by which to raise the base.
Example: POWER(2,3) returns 8, which is 2 raised to the power of 3 POWER(A1,B1) returns the value of cell A1 raised to the power of the value in cell B1 POWER(10,-2) returns 0.01, which is 10 raised to the power of -2
IMCONJUGATE
The IMCONJUGATE function is used to return the complex conjugate of a complex number.
IMCONJUGATE(inumber)
inumber: The complex number for which to find the complex conjugate.
Example: IMCONJUGATE("3+4i") returns 3-4i IMCONJUGATE("-2-5i") returns -2+5i IMCONJUGATE("0+8i") returns 0-8i
COUNTA
Counts the number of cells in a range that are not empty.
COUNTA(value1, [value2], ...)
•value: The first value or range of cells to count.
•valueN: Optional.Additional values or ranges of cells to count.You can enter up to 255 arguments.
Example: COUNTA(A1:A10) returns the number of non-empty cells in A1 through A10 COUNTA(A1, B1, C1, D1) returns the number of non-empty cells in cells A1, B1, C1, and D1 COUNTA(1, "text", TRUE, "", 5) returns 4
F.INV
Returns the inverse of the F probability distribution.
F.INV(probability, degrees_freedom1, degrees_freedom2)
•probability: A probability value between 0 and 1.
•degrees_freedom1: The numerator degrees of freedom for the distribution.
•degrees_freedom2: The denominator degrees of freedom for the distribution.
Example: F.INV(0.05, 3, 5)
BITOR
Returns a bitwise 'OR' of two numbers.
BITOR(number1, number2)
•number1: The first number to be used in the bitwise operation.
•number2: The second number to be used in the bitwise operation.
Example: BITOR(5, 3) returns 7 BITOR(12, 8) returns 12 BITOR(25, 18) returns 27
TRUNC
Truncates a number to a specified number of decimal places by removing the fractional part of the number.
TRUNC(number, [num_digits])
•number: The decimal number you want to convert to octal.
•[places]: Optional.The minimum number of characters to use for the octal number.If omitted or zero, the function will use as many characters as necessary.
Example: DEC2OCT(255) returns 377 DEC2OCT(42,4) returns 0052 DEC2OCT(1000) returns 1750
DEC2OCT
Converts a decimal number to octal format.
DEC2OCT(number, [places])
•number: The decimal number you want to convert to octal.
•[places]: Optional.The minimum number of characters to use for the octal number.If omitted or zero, the function will use as many characters as necessary.
Example: DEC2OCT(255) returns 377 DEC2OCT(42,4) returns 0052 DEC2OCT(1000) returns 1750
LOGEST
Calculates an exponential curve that fits a set of data and returns an array of values that describes the curve.
LOGEST(known_y's, [known_x's], [const], [stats])
•known_y's: The array or range containing the dependent variable values.
•[known_x's]: Optional.The array or range containing the independent variable values.If omitted, Excel uses [1,2,3,...] as the default independent variable array.
•[const]: Optional.A logical value that specifies whether to force the exponential curve to pass through the origin (0,0).If TRUE, the y-intercept is set to 0 and the curve is forced to pass through the origin.If FALSE or omitted, the y-intercept is calculated normally.
•[stats]: Optional.A logical value that specifies whether to return additional regression statistics.If TRUE, LOGEST returns an array of additional statistics, including the standard error and R-squared value.If FALSE or omitted, LOGEST returns only the coefficients of the exponential curve.
Example: LOGEST(A1:A10, B1:B10) returns an array of coefficients for the exponential curve that best fits the points (A1,B1), (A2,B2), ..., (A10,B10) LOGEST(A1:A10, B1:B10, TRUE) returns an array of coefficients for the exponential curve that passes through the origin and best fits the same points LOGEST(A1:A10, B1:B10, FALSE, TRUE) returns an array of regression statistics alongside the coefficients for the exponential curve that best fits the same points.
FALSE
Returns the logical value 'FALSE'.
FALSE()
Example: FALSE()
DISC
Calculates the discount rate for a security based on its price, face value, and coupon rate.
DISC(settlement, maturity, price, redemption, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•price: The price per $100 face value of the security.
•redemption: The redemption value per $100 face value of the security at maturity.
•[basis]: Optional.The day count basis to use for calculating fractional periods.If omitted, assumes a default of US (NASD) 30/360.
DISC(settlement, maturity, price, redemption, [basis])
Example: DISC("2023-01-04", "2023-01-31", 99.5, 100)
CALL
Calls a macro from a module or run a function.
CALL(macro_name, param1, param2,...)
•macro_name: The name of the macro to be called.
•paramN: Optional.One or more parameters to be passed to the macro.
Example: CALL("MyMacro", A1:B3) CALL("MyFunction", 10, "test") CALL("AnotherMacro")
BETADIST
Returns the cumulative beta probability density function.
BETADIST(x, alpha, beta, [A], [B])
•x: The value at which to evaluate the function.
•alpha: The first parameter of the beta distribution.Must be greater than 0.
•beta: The second parameter of the beta distribution.Must be greater than 0.
•[A]: Optional.The lower bound of the interval of x.If omitted, A defaults to 0.
•[B]: Optional.The upper bound of the interval of x.If omitted, B defaults to 1.
Example: BETADIST(0.2, 2, 3) BETADIST(0.8, 4, 2, 0.5, 1) BETADIST(0.5, 1, 1)
IMSECH
Returns the hyperbolic secant of a complex number.
IMSECH(inumber)
inumber: The complex number for which you want to calculate the hyperbolic secant.
Example: IMSECH("1+i")
TEXTAFTER
Returns the substring of text after a specified character or string.
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
•text: The text from which to extract the substring.
•delimiter: The character or string after which to start extracting the substring.
•[instance_num]: Optional.The instance number of the delimiter to use if there are multiple occurrences.Default is 1.
•[match_mode]: Optional.Specifies the match mode.Options include 'exact' (default) or 'approximate'.
•[match_end]: Optional.Specifies whether to match at the end of the text.Options include 'start' (default) or 'end'.
•[if_not_found]: Optional.The value to return if the delimiter is not found in the text.Default is an empty string.
Example: TEXTAFTER("apple,banana,cherry", ",") returns "banana,cherry" TEXTAFTER("Hello World", "World") returns "" TEXTAFTER("John Smith", " ") = "Smith".
TAN
Returns the tangent of an angle specified in radians.
TAN(x)
x: The angle in radians for which you want the tangent.The angle can be expressed in radians or degrees.Excel treats angles in radians as if they were measured in radians; angles in degrees are converted to radians (pi/180) before being evaluated.
Example: TAN(0.7854) TAN(1.0472) TAN(-1.5708)
TANH
Returns the hyperbolic tangent of a number.
TANH(x)
x: The real number for which you want the hyperbolic tangent.
Example: TANH(0) TANH(1) TANH(-1)
DPRODUCT
Returns the product of values selected from a database table-like array based on specified criteria.
DPRODUCT(database, field, criteria)
•database: The database range to use for the lookup.Should include headers for each column.
•field: The column header of the field you want to multiply.
•criteria: Criteria range or array to search for records that meet all of the specified conditions.
Example: DPRODUCT(A1:C10,"Price",[A1:A10,"Oranges",C1:C10,">=5"]) returns the product of all prices for oranges sold in quantities of 5 or more DPRODUCT(A1:C10,"Sales",[B1:B10,"Apples",C1:C10,"2018"]) returns the product of all sales for apples in the year 2018 DPRODUCT(A1:E10,"Units",[A1:A10,"Bananas",B1:B10,"North",E1:E10,"Yes"]) returns the product of all units sold for bananas in the North region with a 'Yes' value in the 'Discount' column
MULTINOMIAL
Returns the multivariate factorial, which is the product of factorials of a series of numbers.
MULTINOMIAL(number1, [number2],...)
•number1: The first number.
•numberN: The nth number.
Example: MULTINOMIAL(3,4,5) MULTINOMIAL(A2:A6)
FTEST
Returns the result of an F-test, which returns the two-tailed probability that the variances in two data sets are equal.
FTEST(array1, array2)
•array1: The first array or range of data.
•array2: The second array or range of data.
Example: FTEST(A2:A10,B2:B10)
QUOTIENT
Returns the integer portion of a division operation.It discards the remainder and returns only the whole number that divides evenly into the dividend.
QUOTIENT(numerator, denominator)
•numerator: The number to be divided.
•denominator: The number by which to divide the numerator.
Example: QUOTIENT(10,3) returns 3 because 3 is the largest whole number that divides evenly into 10 when divided by 3 QUOTIENT(1000,7) returns 142 because 7 goes into 1000 142 times with a remainder of 6 QUOTIENT(A1,B1) returns the integer portion of the result of dividing the value in cell A1 by the value in cell B1
ADDRESS
Returns a cell reference as a string, given row and column numbers, and an optional flag indicating whether the reference should be absolute or relative.
ADDRESS(row_num, col_num, [abs_num], [a1], [sheet_text])
•row_num: The row number for the cell reference.
•col_num: The column number for the cell reference.
•[abs_num]: Optional.A number specifying the type of reference to return.May be one of: 1 (default), 2, 3, or 4.
•[a1]: Optional.A value indicating whether the returned reference should use the A1-style or R1C1-style notation.Defaults to FALSE if not specified.
•[sheet_text]: Optional.The name of the worksheet to which the cell reference belongs.
Example: ADDRESS(1, 1) ADDRESS(2, 3, 4) ADDRESS(4, 4, 2, TRUE)
LEFTB
Returns a specified number of bytes from the beginning (left side) of a text string.
LEFTB(text, num_bytes)
•text: The text string that contains the characters you want to extract.
•num_bytes: Specifies how many bytes you want LEFTB to return.
Example: LEFTB('apple', 3) returns 'app' LEFTB('banana', 2) returns 'ba' LEFTB('cherry', 5) returns 'cher'
AVERAGEIFS
Returns the average (arithmetic mean) of all cells in a range that meet multiple specified criteria.
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
•average_range: The range of cells to be averaged.
•criteria_range1: The first range of cells to be evaluated by the first criteria.
•criteria1: The criteria used to determine which cells to include in the average for the first criteria range.Can be a number, expression, cell reference, or text string.
•criteria_rangeN: Optional.The nth range of cells to be evaluated by the second criteria.
•criteriaN: Optional.The nth criteria used to determine which cells to include in the average for the nth criteria range.Can be a number, expression, cell reference, or text string.
Example: AVERAGEIFS(A1:A5, B1:B5, '>3', C1:C5, '<10') AVERAGEIFS(D2:D7, E2:E7, 'red', F2:F7, '>10') AVERAGEIFS(H2:H9, I2:I9, '>=3', J2:J9, '<=12', K2:K9, '<>5').
ISOMITTED
Checks if a parameter in a formula is omitted and returns TRUE if the parameter is omitted, and FALSE otherwise.
ISOMITTED(reference)
reference: The reference to the parameter that you want to test.
Example: ISOMITTED(A1) returns TRUE if the parameter is omitted from the formula ISOMITTED(B2) returns FALSE if the parameter is not omitted from the formula ISOMITTED(C3:D4) returns an array of TRUE or FALSE values indicating whether each corresponding parameter in the formula is omitted.
IMABS
The IMABS function is used to return the absolute value (magnitude) of a complex number.
IMABS(inumber)
inumber: The complex number for which to find the absolute value.
Example: IMABS("2+3i") returns approximately 3.6056 IMABS("-4-3i") returns approximately 5 IMABS("0+10i") returns exactly 10
MAXIFS
Returns the largest number in a range of cells that meets multiple criteria.
MAXIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
•range: The range of cells from which you want to find the largest value that meets the specified criteria.
•criteria_range1: The first range of cells that you want to apply a criterion to.This can be the same as the 'range' argument.
•criteria1: The criterion that you want to apply to the 'criteria_range1'.
•criteria_rangeN: Optional.Additional ranges of cells that you want to apply criteria to.
•criteriaN: Optional.The criterion that you want to apply to the nth.You can specify up to 127 pairs of criteria_range and criteria arguments.
Example: MAXIFS(A1:A10, B1:B10, ">5") returns the largest number in the range A1:A10 that is greater than 5 in the corresponding cell in the range B1:B10 MAXIFS(B1:B10, A1:A10, "apples", B1:B10, ">5") returns the largest number in the range B1:B10 that corresponds to cells in the range A1:A10 containing the text string "apples" and the corresponding cell in the range B1:B10 being greater than 5 MAXIFS(A1:A10, A1:A10, "<>", B1:B10, "red") returns the largest number in the range A1:A10 that corresponds to cells in the range A1:A10 that are not blank and the corresponding cell in the range B1:B10 containing the text string "red".
GAMMA.DIST
Calculates the gamma distribution, which is a continuous probability distribution that describes the shape of data that has positive skewness and a long right tail.
GAMMA.DIST(x, alpha, beta, cumulative)
•x: The value at which you want to evaluate the distribution.
•alpha: A parameter that specifies the shape of the distribution.Alpha must be greater than 0.
•beta: A parameter that specifies the scale of the distribution.Beta must be greater than 0.
•cumulative: A parameter that specifies whether to return the cumulative distribution function (CDF) or the probability density function (PDF).Enter TRUE to return the CDF, or FALSE to return the PDF.
Example: GAMMA.DIST(2,3,2,FALSE) GAMMA.DIST(10,5,1.5,TRUE) GAMMA.DIST(20,7,4,FALSE)
EXP
The EXP function returns the value of e raised to a given power.
EXP(number)
number: The exponent to raise e to.
Example: EXP(2) returns approximately 7.389056 EXP(0.5) returns approximately 1.648721 EXP(A2) returns e raised to the power of the value in cell A2.
IFS
The IFS function is used to evaluate multiple conditions and return a value that corresponds to the first true condition.
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
•logical_test1: The first logical test to perform.
•value_if_true1: The value to return if the first logical_test evaluates to TRUE.
•logical_testN: Optional.The nth logical test to perform.
•value_if_trueN: Optional.The value to return if the nth logical_test evaluates to TRUE.
Example: IFS(A1 > 10, "Greater than 10", A1 > 5, "Between 6 and 10", A1 > 0, "Between 1 and 5") returns 'Greater than 10' if the value in cell A1 is greater than 10, 'Between 6 and 10' if it is between 6 and 10, and 'Between 1 and 5' if it is between 1 and 5.IFS(B2 = "Yes", C2*0.1, B2 = "No", C2*0.05) returns 10% of the value in cell C2 if the value in cell B2 is 'Yes', and 5% if it is 'No'.IFS(D4 = "", "Blank", D4 < 0, "Negative", D4 > 0, "Positive") returns 'Blank' if cell D4 is empty, 'Negative' if it is negative, and 'Positive' if it is positive.
LOGINV
Returns the inverse of the lognormal cumulative distribution function with a specified mean and standard deviation.
LOGINV(probability, mean, standard_dev)
•probability: The probability for which you want to find the corresponding inverse of the lognormal cumulative distribution function.
•mean: The mean of ln(x), where x is normally distributed.Must be positive.
•standard_dev: The standard deviation of ln(x), where x is normally distributed.Must be positive.
Example: LOGINV(0.5, 1, 1)
SECH
Returns the hyperbolic secant of a number.
SECH(number)
number: The numeric value for which to calculate the hyperbolic secant.
Example: SECH(0.5) returns 0.886818883970074 SECH(1) returns 0.648054273663885.
AMORDEGRC
Returns the depreciation for an accounting period, based on the fixed-rate declining balance method.
AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])
•cost: The initial cost of the asset.
•date_purchased: The date the asset was purchased.
•first_period: The date of the end of the first period.
•salvage: The value of the asset at the end of its useful life.
•period: The period for which to calculate depreciation.
•rate: The rate at which the asset is to be depreciated per period.
•[basis]]: Optional.The day count basis to use.Defaults to 0 if not specified.
Example: AMORDEGRC(10000, '2023-01-15', '2023-05-01', 2000, 6, 0.2) AMORDEGRC(100000, '2000-01-01', '2000-06-30', 20000, 2, 0.25, 1).
PPMT
Calculates the payment on the principal for a given investment based on constant-amount periodic payments and a constant interest rate.
PPMT(rate, per, nper, pv, [fv], [type])
•rate: The interest rate for the loan.
•per: The payment period in the range from 1 to nper.
•nper: The total number of payments (the loan term) for the loan.
•pv: The present value or principal of the loan.
•[fv]: Optional.The future value or cash balance you want to attain after the last payment is made.If omitted, defaults to 0.
•[type]: Optional.When payments are due.0 or omitted means at the end of the period, 1 means at the beginning of the period.
Example: PPMT(0.05/12, 1, 12*30, -100000,0).
CUBESETCOUNT
Returns the number of items in a set.
CUBESETCOUNT(connection, set_expression)
•connection: Required.A text string of a Microsoft Excel expression that evaluates to a set defined by the CUBESET function.Set can also be the CUBESET function, or a reference to a cell that contains the CUBESET function.
•set_expression: Required.Expression.
Example: =CUBESETCOUNT(CUBESET("Sales","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]"))
PMT
Calculates the payment for a loan based on constant payments and a constant interest rate.
PMT(rate, nper, pv, [fv], [type])
•rate: The interest rate for the loan.
•nper: The total number of payments (the loan term) for the loan.
•pv: The present value or principal of the loan.
•[fv]: Optional.The future value or cash balance you want to attain after the last payment is made.If omitted, defaults to 0.
•[type]: Optional.When payments are due.0 or omitted means at the end of the period, 1 means at the beginning of the period.
Example: PMT(0.05/12,12*30,-100000) PMT(0.1/12,24,5000,1000,1).
IMSUB
Returns the difference between two complex numbers.
IMSUB(inumber1, inumber2)
•inumber1: The complex number from which you want to subtract another complex number.
•inumber2: The complex number that you want to subtract from the first complex number.
Example: IMSUB("1+i", "2+3i") returns "-1-2i" IMSUB("3+4i", "-1+2i") returns "4+2i"
T.DIST.2T
Returns the two-tailed Student's t-distribution.
T.DIST.2T(x, degrees_freedom)
•x: The numeric value at which to evaluate the distribution.
•degrees_freedom: The number of degrees of freedom.
Example: T.DIST.2T(1.5, 10) T.DIST.2T(2, 8) T.DIST.2T(1, 5)
AVEDEV
Returns the average of the absolute deviations of data points from their mean.
AVEDEV(number1, [number2], ...)
•number1: The first number or range of numbers for which to calculate the average of the absolute deviations.
•numberN: Optional.Additional numbers or ranges of numbers for which to calculate the average of the absolute deviations.
Example: AVEDEV(1, 2, 3, 4, 5) returns 1.2 AVEDEV([2,4,6,8], [1,3,5,7]) AVEDEV(-2, -1, 0, 1, 2)
WEEKNUM
Returns the week number of a given date.
WEEKNUM(serial_number, [return_type])
•serial_number: The serial number that represents the date whose week number you want to find.
•[return_type]: Optional.A number that determines the type of return value: 1 (or omitted): Week starts on Sunday and week 1 is the week that includes January 1 2: Week starts on Monday and week 1 is the week that includes January 4 (the week that includes January 1 is the previous year's last week) 11: Week starts on Monday and week 1 is the week that includes January 1 12: Week starts on Tuesday and week 1 is the week that includes January 1 13: Week starts on Wednesday and week 1 is the week that includes January 1 14: Week starts on Thursday and week 1 is the week that includes January 1 15: Week starts on Friday and week 1 is the week that includes January 1 16: Week starts on Saturday and week 1 is the week that includes January 1
Example: WEEKNUM("2015-01-04") returns: 11 WEEKNUM("1900-01-02") returns 1 WEEKNUM(42008) returns 2.
FILTERXML
Returns specific data from XML content by using an XPath string.
FILTERXML(xml, xpath)
•xml: A string containing the XML content to filter.
•xpath: A string containing the XPath expression used to extract the data.
Example: FILTERXML("<xml><employee><name>John Doe</name><title>Manager</title></employee></xml>", "//name") returns "John Doe" FILTERXML("<catalog><book genre='novel' ISBN='1-861003-74-9'><title>The Handmaid's Tale</title><price>19.95</price></book><book genre='novel' ISBN='0-553-21311-3'><title>The Hobbit</title><price>10.99</price></book></catalog>", "sum(catalog/book/price)") returns "30.94"
SCAN
The SCAN function is a custom function in Microsoft Excel that allows you to apply a calculation to each element in an array and return an accumulated result based on the values in the array.
SCAN(initial_value, array, lambda(accumulator, value))
•initial_value: Sets the starting value for the accumulator.
•array: An array to be scanned.
•lambda(accumulator, value)): A LAMBDA that is called to scan the array.The LAMBDA takes two parameters: the accumulator and the current value in the array.
Example: SCAN(0, A1:A5, LAMBDA(a, b, IF(MOD(b,2)=0, a+b, a))) returns the accumulated sum of all the even numbers in the array A1:A5 SCAN('', A1:A5, LAMBDA(a, b, a & b)) returns a string concatenation of all the values in the array A1:A5 SCAN(1, A1:A5, LAMBDA(a, b, a * b)) returns the accumulated product of all the values in the array A1:A5 starting from 1.
IMARGUMENT
The IMARGUMENT function is used to return the argument (phase angle) of a complex number in radians.
IMARGUMENT(inumber)
inumber: The complex number for which to find the argument.
Example: IMARGUMENT(2+2i) returns approximately 0.7854 IMARGUMENT(-3-3i) returns approximately -2.3562.
PROPER
Capitalizes the first letter of each word in a text string and sets all other letters to lowercase.
PROPER(text)
text: The text string to capitalize.
Example: PROPER("this is a test") returns "This Is A Test" PROPER("ALL CAPS HERE") returns "All Caps Here" PROPER(A1) capitalizes the first letter of each word in the text contained in cell A1.
NORM.S.DIST
Returns the standard normal cumulative distribution function.
NORM.S.DIST(z, [cumulative])
•z: The value for which you want the distribution.
•[cumulative]: An optional logical value that specifies the form of the function.If cumulative is TRUE, NORM.S.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.If omitted, cumulative defaults to TRUE.
Example: NORM.S.DIST(-1.96,TRUE) returns approximately 0.025 NORM.S.DIST(2,TRUE) returns approximately 0.97725 NORM.S.DIST(0,FALSE) returns approximately 0.398942.
ISNONTEXT
Checks if a given value is not text (any non-textual value) and returns TRUE if the value is not text, and FALSE otherwise.
ISNONTEXT(value)
value: The value that you want to test.
Example: ISNONTEXT(123) returns TRUE because 123 is not a text value ISNONTEXT("banana") returns FALSE because "banana" is a text value ISNONTEXT(A1) returns TRUE if cell A1 contains any non-textual value, and FALSE otherwise.
F.TEST
Returns the result of an F-test, which returns the one-tailed probability that the variances in two data sets are not significantly different.
F.TEST(array1, array2)
•array1: The first array or range of data.
•array2: The second array or range of data.
Example: F.TEST(A2:A10,B2:B10) F.TEST(A2:A6,B2:B6) F.TEST(A2:A12,B2:B12).
HYPGEOMDIST
The HYPGEOMDIST function is used to calculate the probability of a specified number of successes in a population sample, given the population size, number of successes in the population, and sample size.
HYPGEOMDIST(sample_s, number_sample, population_s, number_pop)
•sample_s: The number of successes in the sample.
•number_sample: The size of the sample.
•population_s: The number of successes in the population.
•number_pop: The size of the population.
Example: HYPGEOMDIST(1, 4, 8, 20) returns approximately 0.3633.
DAY
Returns the day of the month as a number (1-31) for a given date.
DAY(date)
date: The date from which to extract the day of the month.
Example: DAY(2958465) returns 31 DAY("9999-12-31") returns 31
IMCOT
Returns the cotangent of a complex number in x + yi or x + yj text format.
IMCOT(inumber)
inumber: The complex number for which you want to calculate the cotangent.
Example: IMCOT("3+4i") IMCOT(1) IMCOT("0+1i")
ACOTH
Returns the inverse hyperbolic cotangent of a number.
ACOTH(number)
number: The value for which to return the inverse hyperbolic cotangent.Must not be 0.
Example: ACOTH(2) returns 0.5493061443
ISPMT
Calculates the interest payment for a given period of an investment based on a constant interest rate.
ISPMT(rate, period, periods, principal)
rate: The interest rate per period.
period: The period for which you want to calculate the interest payment.
periods: The total number of payment periods in the investment.
principal: The amount of the investment.
Example: ISPMT(0.08/12, 4, 36, 10000) returns the interest payment for month 4 of a 3-year loan with a 8% annual interest rate and a principal of $10,000 ISPMT(B2/12, C3, D4, A1) returns the interest payment for a specific period in an investment based on variable inputs ISPMT(0.06/12, 1, 60, 200000) returns the interest payment for the first month of a 5-year loan with a 6% annual interest rate and a principal of $200,000.
GAMMA
Calculates the gamma function, which is a generalization of the factorial function to real and complex numbers.
GAMMA(number)
number: The number for which you want to calculate the gamma function.
Example: GAMMA(0.5) returns 1.77245385091 GAMMA(2.5) returns 1.32934038818 GAMMA(4) returns 6.
AVERAGEA
Returns the average (arithmetic mean) of the arguments, including numbers, text, and logical values.
AVERAGEA(value1, [value2], ...)
•value: The first value or range of values for which to calculate the average.
•valueN: Optional.Additional value for which to calculate the average.
Example: AVERAGEA(1, 2, 3, '4', TRUE, FALSE) AVERAGEA(-2, -1, 0, '1', '2').
TBILLYIELD
Calculates the yield of a Treasury bill based on its price.
TBILLYIELD(settlement, maturity, price)
•settlement: The Treasury bill's settlement date.
•maturity: The Treasury bill's maturity date.
•price: The Treasury bill's price per $100 face value.
Example: TBILLYIELD('2008-03-31', '2008-06-01', 98.45) returns approximately 0.0914.
CONCAT
Joins two or more text strings into one string.
CONCAT(text1, [text2], ...)
•text: The first text string to concatenate.
•textN: Optional.Additional text string to concatenate.
Example: CONCAT("Hello", " ", "world") returns "Hello world" CONCAT("Mary", " had a little lamb.") returns "Mary had a little lamb."CONCAT("Today is ", TEXT(TODAY(), "mm/dd/yyyy")) returns a string that includes today's date.
XMATCH
Searches for a specified item in a range of cells, and returns the relative position of that item within the range.
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
•lookup_value: The value to search for.
•lookup_array: The range to search in.
•[match_mode]: Optional.Specifies how the function matches lookup_value with values in lookup_array.Can be either "0" (exact match) or "-1" (exact match or next smallest value).If omitted, it defaults to exact match.
•[search_mode]: Optional.Specifies the search mode.Can be either "1" (search from the beginning of the range) or "-1" (search from the end of the range).If omitted, it defaults to searching from the beginning.
Example: XMATCH("cherry", ["apple","banana","cherry"]) returns: 3 XMATCH(2, [1,2,3]) returns: 2 XMATCH("green", ["red","yellow","green","blue"], -1) returns: 3.
IMSIN
Returns the sine of a complex number.
IMSIN(inumber)
inumber: The complex number for which you want to calculate the sine.
Example: IMSIN("1+i") returns "1.2985+0.63496i" IMSIN("2+2i") returns "3.42095-1.5093i".
SUMIFS
Returns the sum of a range of cells that meet multiple specified criteria.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
•sum_range: The range of cells to add together.
•criteria_range1: The first range to evaluate.
•criteria1: The criteria used to determine which cells to add.
•criteria_rangeN: Optional.Additional ranges to evaluate.
•criteria2: Optional.Additional criteria used to determine which cells to add.
Example: SUMIFS(B2:B6, A2:A6, ">10") returns the sum of all values in cells B2 through B6 where the corresponding cell in A2 through A6 is greater than 10 SUMIFS(B2:B6, A2:A6, "apples", C2:C6, "green") returns the sum of all values in cells B2 through B6 where the corresponding cell in A2 through A6 contains the text "apples" and the corresponding cell in C2 through C6 contains the text "green" SUMIFS(D2:D6, D2:D6, "=green", E2:E6, "<10") returns the sum of all values in cells D2 through D6 where the corresponding cell in D2 through D6 contains the text "green" and the corresponding cell in E2 through E6 is less than 10.
GCD
Returns the greatest common divisor of two or more integers.
GCD(number1, number2,...)
•number1: The first integer for which you want to find the greatest common divisor.
•numberN: The nth integer for which you want to find the greatest common divisor.
Example: GCD(12,18) returns 6 GCD(50,75,100) returns 25 GCD(7,13) returns 1.
BESSELJ
Returns the Bessel function of the first kind, J(x), for a given real or complex number x.
BESSELJ(x, n)
•x: The real or complex number for which to calculate the Bessel function.
•n: The order of the Bessel function.Must be a nonnegative integer or a real number greater than -1.
Example: BESSELJ(5, 2) BESSELJ(1+2i, 0) BESSELJ(-3.5, 4.2).
TREND
Returns values along a linear trend.
TREND(known_y's, [known_x's], [new_x's], [const])
•known_y's: An array or range of dependent data points
•[known_x's]: Optional.Array or range of independent data points.If omitted, the array [1,2,3,...] is used.
•[new_x's]: Optional.Array or range of new x-values for which you want to predict corresponding y-values.If omitted, the same x-values as [known_x's] are used.
•[const]: Optional.Logical value specifying whether to force the constant b to equal 0.If omitted or FALSE, b is calculated normally.If TRUE, b is set equal to 0.
Example: TREND([1,2,3,4],[1,2,3,4]) returns [[1],[2],[3],[4]].
BETA.INV
Returns the inverse of the cumulative beta probability density function.
BETA.INV(probability, alpha, beta, [A], [B])
•probability: The probability at which to evaluate the function.Must be a number between 0 and 1 (inclusive).
•alpha: The first parameter of the beta distribution.Must be greater than 0.
•beta: The second parameter of the beta distribution.Must be greater than 0.
•[A]: Optional.The lower bound of the interval of x.If omitted, A defaults to 0.
•[B]: Optional.The upper bound of the interval of x.If omitted, B defaults to 1.
Example: BETA.INV(0.488, 2, 3) BETA.INV(0.9, 4, 2, 0.5, 1) BETA.INV(0.625, 1, 1, 0, 0.8).
DEC2HEX
Converts a decimal number to hexadecimal format.
DEC2HEX(number, [places])
•number: The decimal number you want to convert to hexadecimal.
•[places]: Optional.The minimum number of characters to use for the hexadecimal number.If omitted or zero, the function will use as many characters as necessary.
Example: DEC2HEX(255) returns FF DEC2HEX(42,4) returns 002A DEC2HEX(1000) returns 3E8.
ERROR.TYPE
The ERROR.TYPE function returns a number that corresponds to the error value in a cell.
ERROR.TYPE(error_val)
error_val: The cell containing the error value for which to return the corresponding number.
Example: ERROR.TYPE(#N/A) returns 7 ERROR.TYPE(#VALUE!) returns 3 ERROR.TYPE(A2) returns the corresponding number for the error value in cell A2.
FORECAST.LINEAR
Calculates or predicts a future value based on linear regression of a data set.
FORECAST.LINEAR(x, known_y's, known_x's)
•x: The x-value used to predict the y-value.
•known_y's: An array of y-values representing known data points that you want to use in the regression analysis.
•known_x's: An array of x-values representing known data points that you want to use in the regression analysis.The number of elements in known_x's should be equal to the number of elements in known_y's.
Example: FORECAST.LINEAR(10,A2:A8,B2:B8) FORECAST.LINEAR(20,A2:A8,B2:B8) FORECAST.LINEAR(30,A2:A8,B2:B8).
NORM.DIST
Returns the probability density function or the cumulative distribution function for a specified normal distribution.
NORM.DIST(x, mean, standard_dev, cumulative)
•x: The value at which to evaluate the function.
•mean: The arithmetic mean of the distribution.
•standard_dev: The standard deviation of the distribution.
•cumulative: A logical value that specifies the form of the function.If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.
Example: NORM.DIST(1.96,0,1,FALSE) NORM.DIST(-2,10,3.5,TRUE) NORM.DIST(60,70,10,TRUE).
MODE
Returns the most frequently occurring value in a range of data.
MODE(number1, [number2],...)
•number: The first number or range of numbers for which you want to find the mode.You can also enter up to 255 additional numbers or ranges.
•numberN: The nth number or range of numbers for which you want to find the mode.You can also enter up to 255 additional numbers or ranges.
Example: MODE(1,2,2,3,4,4,4,5) returns 4 MODE(A1:A10) returns the most frequently occurring value in the range A1 through A10 MODE(B1:B12,C1:C12) returns the most frequently occurring value in the ranges B1:B12 and C1:C12.
T.INV
Returns the inverse of the one-tailed probability of a Student's t-distribution.
T.INV(probability, degrees_freedom)
•probability: The probability associated with the two-tailed t-distribution.
•degrees_freedom: The number of degrees of freedom for the t-distribution.Must be a positive integer.
Example: T.INV(0.05, 10) T.INV(0.1, 5) T.INV(0.025, 20).
COS
Returns the cosine of an angle provided in radians.
COS(number)
number: The angle for which you want to calculate the cosine, in radians.
Example: COS(0) returns 1 COS(PI()) returns -1 COS(PI()/4) returns 0.707.
LCM
Returns the least common multiple of one or more integers.The least common multiple is the smallest positive integer that is a multiple of each integer in a given set.
LCM(number1, [number2], ...)
•number: The first number for which you want to find the least common multiple.
•numberN: Optional.Additional numbers for which you want to find the least common multiple.
Example: LCM(12, 18) returns 36 because 36 is the smallest number that is a multiple of both 12 and 18 LCM(8, 12, 14) returns 168 because 168 is the smallest number that is a multiple of 8, 12, and 14 LCM(A1:A10) returns the least common multiple of the values in cells A1 through A10.
TIME
Returns a decimal value representing a specific time.
TIME(hour, minute, second)
•hour: The hour of the time that you want to represent.
•minute: The minute of the time that you want to represent.
•second: The second of the time that you want to represent.If omitted, defaults to 0.
Example: TIME(12, 30, 15) returns 0.5201388889 TIME(8, 0, 0) returns 0.3333333333 TIME(16, 45) returns 0.6979166667.
ISERROR
Returns TRUE if a value is any error value, and FALSE otherwise.
ISERROR(value)
value: The value or reference to the cell that you want to test.
Example: ISERROR(A1) returns TRUE if cell A1 contains any error value, including #N/A ISERROR(B2) returns TRUE if cell B2 contains a #VALUE! error ISERROR(C3+D3) returns TRUE if the formula in C3+D3 evaluates to an error value.
HEX2BIN
Converts a hexadecimal number to binary.
HEX2BIN(number,[places])
•number: The hexadecimal number you want to convert to binary.The most significant bit of number is the left-most bit.
•[places]: Optional.The number of characters to use.Places must be a multiple of 4.If places is omitted, HEX2BIN uses the minimum number of characters necessary.
Example: HEX2BIN("F",4) returns 1111 HEX2BIN("3A",8) returns 00111010 HEX2BIN("B7") returns 10110111.
MINVERSE
Returns the matrix inverse of an array.
MINVERSE(array)
array: The array for which you want to calculate the matrix inverse.The array must be square (that is, it must have the same number of rows as columns).
Example: MINVERSE([[1,2],[3,4]]) returns [[-2,1],[1.5,-0.5]] MINVERSE(A1:C3) returns the matrix inverse of the range A1:C3 MINVERSE(B1:D4) returns the matrix inverse of the range B1:D4.
CUBEKPIMEMBER
Returns the value of a key performance indicator (KPI) and the name of the KPI in the cube.
CUBEKPIMEMBER(connection, kpi_name, [caption])
•connection: The name of the Excel data source connection.
•kpi_name: The name of the KPI to retrieve from the cube.
•[caption]: Optional.The caption of the KPI to retrieve from the cube.If omitted, the name parameter is used as the caption.
Example: CUBEKPIMEMBER("Sales Data","Revenue") returns the revenue KPI for the Sales Data cube along with its name CUBEKPIMEMBER("Budget Data","Profit", "Net Profit") returns the net profit KPI for the Budget Data cube along with its name CUBEKPIMEMBER("Inventory Data","Turnover Rate") returns the turnover rate KPI for the Inventory Data cube along with its name.
VLOOKUP
Searches for a value in the first column of a table or range of cells, and then returns a value in the same row from a column you specify.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
•lookup_value: The value that you want to search for.The lookup_value argument can be a value (number, text, or logical value), or a cell reference to a number, text, or logical value.
•table_array: The table of information in which data is looked up.Use a reference to a range or a range name.
•col_index_num: The column number (starting with 1 for the left-most column of table_array) that contains the return value.In other words, the column number that VLOOKUP uses to find the return value.If Col_index_num is less than 1, a #VALUE! error is returned.If Col_index_num is greater than the number of columns in table_array, a #REF! error is returned.
•[range_lookup]: Optional.A logical argument that specifies whether you want VLOOKUP to find an exact or approximate match.If range_lookup is either TRUE or is omitted, an approximate match is returned.If an exact match is not found, the next largest value that is less than lookup_value is returned.If range_lookup is FALSE, VLOOKUP will only find an exact match.If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used.If an exact match is not found, the error value #N/A is returned.
Example: VLOOKUP("apples", A1:B10, 2, FALSE) VLOOKUP(5, A1:B10, 2, TRUE).
ROUNDUP
Rounds a number up to a specified number of digits.
ROUNDUP(number, num_digits)
•number: The number you want to round up.
•num_digits: The number of digits to which you want to round up the number.
Example: ROUNDUP(3.14159, 2) returns 3.15 ROUNDUP(99.45, 0) returns 100 ROUNDUP(1234.56789, -2) returns 1300.
VALUETOTEXT
Converts a value to its textual representation in the number format of the current language and optionally with a specified format.
VALUETOTEXT(value, [format])
•value: The numeric value that you want to convert to text.
•[format]: Optional.A text string that specifies the number format to use for the conversion.The format must be enclosed in double quotation marks.If omitted, the function uses the default number format for the current language.
Example: VALUETOTEXT(1234.5678) returns "1,234.57" (assuming the current language uses comma as the decimal separator) VALUETOTEXT(0.75, "0%") returns "75%" VALUETOTEXT(123456.789, "#,##0.00 $") returns "123,456.79 $".
SUMX2MY2
Returns the sum of the difference of squares of corresponding values in two arrays or ranges.
SUMX2MY2(array_x, array_y)
•array_x: An array or range that contains the first set of values.
•array_y: An array or range that contains the second set of values.
Example: SUMX2MY2(A2:A6, B2:B6) returns the sum of the difference of squares of each pair of values in cells A2 through A6 and B2 through B6 SUMX2MY2(C2:C6, D2:D6) returns the sum of the difference of squares of each pair of values in cells C2 through C6 and D2 through D6.
IMEXP
Returns the exponential of a complex number.
IMEXP(inumber)
inumber: The complex number for which you want to calculate the exponential value.
Example: IMEXP("3+4i") returns "-13.12878+15.20078i" IMEXP("1+i") returns "1.4687+2.2874i" IMEXP("0+1i") returns "0.5403+0.8415i".
BYCOL
Applies a lambda function to each column of a range and returns an array of the results.
BYCOL(reference, lambda)
•reference: The range to process column by column.
•lambda: A lambda function to apply to each column.
Example: BYCOL(A1:B3, 2) returns C1:D3 BYCOL(C4:F8, -3) returns A4:B8 BYCOL(G2:H5, 0) returns G2:H5
NORMSDIST
Returns the standard normal cumulative distribution function.
NORMSDIST(z)
z: The value for which you want the distribution.
Example: NORMSDIST(-1.96) returns approximately 0.025 NORMSDIST(2) returns approximately 0.97725.
NETWORKDAYS.INTL
Returns the number of whole workdays between start_date and end_date using parameters to indicate which and how many days are weekend days, and a given set of holidays.
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
•start_date: The start date of the period.
•end_date: The end date of the period.
•[weekend]: Optional.An argument that indicates which days of the week are weekend days.Use a seven-character string with a 1 or 0 in each position representing Monday through Sunday, respectively.A 1 indicates a non-workday and 0 indicates a workday.
•[holidays]: Optional.A range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
Example: NETWORKDAYS.INTL("2023-01-01", "2023-03-15", "0111110") returns the number of whole workdays between Jan 1, 2023 and Mar 15, 2023 excluding weekends (Saturday and Sunday).
ODDLYIELD
Calculates the yield of a security with an odd last period.
ODDLYIELD(settlement, maturity, issue, last_interest, rate, pr, redemption, frequency, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•issue: The issue date of the security.
•last_interest: The date of the last interest payment, which is an odd period.
•rate: The annual coupon rate of the security.
•pr: The price per $100 face value of the security.
•redemption: The redemption value of the security per $100 face value.
•frequency: The number of coupon payments per year.
•[basis]: Optional.An argument that specifies the day count basis to use.If omitted, Excel uses the US (NASD) 30/360 basis.
Example: ODDLYIELD('2022-01-01', '20220-06-30', '2021-12-31', '2022-03-31', 0.08, 98, 100, 2) returns approximately 10.17%.
TTEST
Returns the probability associated with a Student's t-test.
TTEST(array1, array2, tails, type)
•array1: array1 - the first array or range of data.
•array2: array2 - the second array or range of data.If omitted, TTEST will test for the difference in means between Array1 and a hypothetical mean of zero.
•tails: tails - specifies the number of distribution tails to test.If omitted, the default value is 2 (which tests for a two-tailed distribution).
•type: type - specifies the type of t-test to perform.If omitted, the default value is 2 (which performs a paired t-test).
Example: TTEST(A1:A10, B1:B10, 2, 2) returns the probability associated with a two-sample paired t-test of the ranges A1:A10 and B1:B10 TTEST(A1:A10, 0, 1, 1) returns the probability associated with a one-sample t-test that the mean of A1:A10 is equal to 0 TTEST(A1:A10, B1:B10, 1) returns the probability associated with a two-sample equal variance t-test of the ranges A1:A10 and B1:B10.
FLOOR.MATH
Rounds a number down to the nearest integer or to the nearest multiple of a specified factor.
FLOOR.MATH(number, [significance], [mode])
•number: The number that you want to round down.
•[significance]: Optional.The multiple to which you want to round.Default is 1.
•[mode]: Optional.A value that determines how to round the number.Default is 0 (round towards negative infinity).1 = Round towards positive infinity.2 = Round towards zero.3 = Round away from zero.
Example: FLOOR.MATH(24.3) = 24 FLOOR.MATH(6.8, 2) = 6.
SORTBY
Sorts a range or array based on the values in another range or array.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)
•array: The range or array of values to sort.
•by_array1: The range or array of values by which to sort.
•sort_order1: Optional.The order in which to sort the values in by_array1.1 for ascending order, -1 for descending order.Default is 1.
•[by_arrayN, sort_orderN]: Optional.Additional ranges or arrays of values by which to sort, followed by their corresponding sort orders.
Example: SORTBY(A2:B6, B2:B6) sorts the range A2:B6 based on the values in column B, in ascending order SORTBY(D2:F7, E2:E7, -1, F2:F7, 1) sorts the range D2:F7 first by the values in column E in descending order, and then by the values in column F in ascending order.
PERCENTILE.EXC
Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.
PERCENTILE.EXC(array, k)
•array: The array or range of data that defines relative standing.
•k: The percentile to return, expressed as a decimal between 0 and 1, exclusive.
Example: PERCENTILE.EXC(A1:A10,0.9) returns the value at the 90th percentile in the range A1 through A10 PERCENTILE.EXC(B1:B5,0.8) returns the value at the 80th percentile in the range B1 through B5 PERCENTILE.EXC(C1:C100,0.99) returns the value at the 99th percentile in the range C1 through C100.
GAMMALN
Calculates the natural logarithm of the gamma function, which is a generalization of the factorial function to real and complex numbers.
GAMMALN(number)
number: The number for which you want to calculate the natural logarithm of the gamma function.
Example: GAMMALN(0.5) returns 0.5723649429 GAMMALN(2.5) returns 0.2846828705 GAMMALN(4) returns 1.791759469
FISHERINV
Returns the inverse of the Fisher transformation on a specified value.
FISHERINV(y)
y: The numeric value for which to find the inverse Fisher transformation.
Example: FISHERINV(0.549306144334055) returns 0.5 FISHERINV(1.09861228866811) returns 0.8 FISHERINV(-0.20273255405408) returns -0.2.
ERFC
The ERFC function returns the complementary error function of a number, which is equal to 1 minus the error function of the same number.
ERFC(x)
x: The number for which to calculate the complementary error function.
Example: ERFC(0) returns 1 ERFC(1.5) returns 0.033894853524654 ERFC(A2) returns the complementary error function of the value in cell A2.
XOR
Returns a logical exclusive OR of all arguments.
XOR(logical1, [logical2], ...)
•logical: A logical value or expression that can be evaluated as TRUE or FALSE.
•logicalN: Optional.Additional logical values or expressions to evaluate.
Example: XOR(TRUE, FALSE) returns TRUE XOR(FALSE, FALSE, FALSE, TRUE) returns TRUE XOR(1>2, "a"="a", 3<4) returns FALSE.
XLOOKUP
Searches a range or an array for a specified value and returns a corresponding value in the same position from another range or array.
XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
•lookup_value: The value to search for.
•lookup_array: The range or array to search.
•return_array: The range or array to return a value from.
•match_mode: Optional.Specifies how the function matches lookup_value with values in lookup_array.Can be either "0" (exact match) or "-1" (exact match or next smallest value).If omitted, it defaults to exact match.
•search_mode: Optional.Specifies whether the function searches for an exact or approximate match.Can be either 0 (exact match) or 1 (approximate match).If omitted, it defaults to exact match.
Example: XLOOKUP("apple", ["apple","banana","cherry"], [5, 10, 15]) returns 5 XLOOKUP(2, [1,2,3], ["one","two","three"], -1) returns "two" XLOOKUP("green", ["red","yellow","green","blue"], ["roses","sunflowers","grass","ocean"], ,1) returns "grass".
IMDIV
Returns the quotient of two complex numbers.
IMDIV(inumber1, inumber2)
•inumber1: The complex number that you want to divide.
•inumber2: The complex number that you want to divide by.
Example: IMDIV("4+3i", "1+i") returns "2.5-0.5i" IMDIV("6+2i", "2+2i") returns "2-i".
IMCOS
The IMCOS function is used to return the cosine of a complex number.
IMCOS(inumber)
inumber: The complex number for which to find the cosine.
Example: IMCOS(2+3i) returns approximately -4.1896-9.1092i IMCOS(-4-3i) returns approximately -27.0348+3.8512i IMCOS(0+10i) returns approximately 11013.2329.
REPT
Repeats a text string a specified number of times.
REPT(text, number_times)
text: The text string to repeat.
number_times: The number of times to repeat the text.
Example: REPT("Hello ", 3) returns "Hello Hello Hello " REPT("Excel ", 5) returns "Excel Excel Excel Excel Excel " REPT("123", 2) returns "123123"
F.DIST
Returns the cumulative distribution function of the F-distribution.
F.DIST(x, degrees_freedom1, degrees_freedom2, cumulative)
•x: The value at which to evaluate the function.
•degrees_freedom1: The numerator degrees of freedom for the distribution.
•degrees_freedom2: The denominator degrees of freedom for the distribution.
•cumulative: A logical value that indicates whether to return the cumulative distribution function or the probability density function.
Example: F.DIST(15.206,3,5,FALSE) F.DIST(15.206,3,5,TRUE) F.DIST(20,5,10,FALSE).
FDIST
Returns the cumulative distribution function of the F-distribution.
FDIST(x, degrees_freedom1, degrees_freedom2)
•x: The value at which to evaluate the function.
•degrees_freedom1: The numerator degrees of freedom for the distribution.
•degrees_freedom2: The denominator degrees of freedom for the distribution.
Example: FDIST(15.206,3,5) FDIST(20,5,10) FDIST(8.15,2,10).
LINEST
Calculates the statistics for a line by fitting a straight line to a set of known data points using the least squares method.
LINEST(known_y's, [known_x's], [const], [stats])
•known_y's: The array or range containing the dependent variable values.
•known_x's: Optional.The array or range containing the independent variable values.If omitted, Excel uses [1,2,3,...] as the default independent variable array.
•const: Optional.A logical value that specifies whether to force the y-intercept to be 0.If TRUE, the y-intercept is set to 0 and the slope is calculated using only the x variable data.If FALSE or omitted, the y-intercept is calculated normally.
•stats: Optional.A logical value that specifies whether to return additional regression statistics.If TRUE, LINEST returns an array of additional statistics, including the standard error and R-squared value.If FALSE or omitted, LINEST returns only the slope and y-intercept values.
Example: LINEST(A1:A10, B1:B10) returns the slope and y-intercept of the best fit line through the points (A1,B1), (A2,B2), ..., (A10,B10) LINEST(A1:A10, B1:B10, TRUE) returns the slope of the best fit line with y-intercept set to zero and going through the points (A1,B1), (A2,B2), ..., (A10,B10) LINEST(A1:A10, B1:B10, FALSE, TRUE) returns an array of regression statistics alongside the slope and y-intercept values.
SQRTPI
Returns the positive square root of a number multiplied by pi (π).
SQRTPI(x)
x: The number for which to calculate the square root, which will then be multiplied by pi (π).Must be non-negative.
Example: SQRTPI(9) SQRTPI(2) SQRTPI(A1) returns the square root of the value in cell A1, multiplied by pi (π).
NOMINAL
Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year.
NOMINAL(effective_rate, npery)
•effective_rate: The effective annual interest rate.
•npery: The number of compounding periods per year.
Example: NOMINAL(0.1,12) NOMINAL(0.083,4).
MDURATION
Returns the Macauley duration of a security with an assumed par value of $100.
MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
•settlement: The security's settlement date, which is the date when the security is traded to the buyer.
•maturity: The security's maturity date, which is the date when the security expires and the issuer must pay the principal to the bondholder.
•coupon: The security's annual coupon rate.
•yld: The security's annual yield.
•frequency: The number of coupon payments per year.Common values are 1 for annually, 2 for semiannually, and 4 for quarterly.
•basis: Optional.The type of day count basis to use.0 or omitted = US (NASD) 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.
Example: MDURATION("2022-01-01", "2032-12-31", 5.75, 0.06, 2, 0) returns the Macauley duration for a security that pays a 5.75% annual coupon, has a settlement date of January 1, 2022, a maturity date of December 31, 2032, an annual yield of 6%, and pays coupons twice a year (semiannually) MDURATION("2022-01-01", "2032-12-31", 5.75, 0.06, 4, 1) returns the Macauley duration for a security that pays a 5.75% annual coupon, has a settlement date of January 1, 2022, a maturity date of December 31, 2032, an annual yield of 6%, and pays coupons four times a year (quarterly) MDURATION("2022-01-01", "12/31/2032-12-31", 5.75, 0.06, 1) returns the Macauley duration for a security that pays a 5.75% annual coupon, has a settlement date of January 1, 2022, a maturity date of December 31, 2032, an annual yield of 6%, and pays coupons once a year.
ASINH
Returns the inverse hyperbolic sine of a number.
ASINH(x)
x: The number for which to calculate the inverse hyperbolic sine.
Example: ASINH(0) returns 0 ASINH(1) returns 0.881373587 ASINH(2) returns 1.443635475.
T.INV.2T
Returns the inverse of the two-tailed probability of a Student's t-distribution.
T.INV.2T(probability, degrees_freedom)
•probability: The probability associated with the two-tailed t-distribution.
•degrees_freedom: The number of degrees of freedom for the t-distribution.Must be a positive integer.
Example: T.INV.2T(0.05, 10) returns 2.228138852.
DVAR
The DVAR function estimates variance based on a sample from selected database entries.
DVAR(database, field, criteria)
•database: The range of cells that makes up the database.
•field: The column label that contains the numbers for which you want the variance.
•criteria: The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
Example: DVAR(A2:C10,'Sales',A1:C1) returns the variance of sales where the corresponding value in row 1 equals any value in A1:C1 DVAR(A2:C10,'Quantity Sold',B1:B1) returns the variance of quantity sold where the corresponding value in B1 equals the value in B1 DVAR(A2:C10,'Price',[Product]='Fruits')returns the variance of prices for fruit products.
NETWORKDAYS
Returns the number of whole workdays between start_date and end_date using parameters to indicate which and how many days are weekend days.
NETWORKDAYS(start_date, end_date, [holidays])
•start_date: The start date of the period.
•end_date: The end date of the period.
•holidays: Optional.A range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
Example: NETWORKDAYS("2023-01-01","2023-03-15") returns the number of whole workdays between Jan 1, 2023 and Mar 15, 2023 excluding weekends NETWORKDAYS("4/1/2023","4/30/2023",B1:B10) returns the number of whole workdays in April 2023 excluding weekends and holidays listed in range B1:B10 NETWORKDAYS(A2,B2,["12/25/2023","1/1/2024"]) returns the number of whole workdays between the dates in cells A2 and B2, excluding weekends and holidays Dec 25, 2023 and Jan 1, 2024.
PROB
Returns the probability of an outcome occurring given a range of values and associated probabilities.
PROB(range, probability, [lower], [upper])
•range: The range of numeric values to consider for the probability calculation.
•probability: The probabilities associated with each value in the range argument.
•lower: Optional.The lower bound of the range of values to consider for the probability calculation.If omitted, defaults to the minimum value in the range argument.
•upper: Optional.The upper bound of the range of values to consider for the probability calculation.If omitted, defaults to the maximum value in the range argument.
Example: PROB(A1:A5,B1:B5) returns the probability of any value from A1 to A5 occurring, based on the corresponding probabilities in B1 to B5 PROB(A1:A5,B1:B5,10,20) returns the probability of any value from A1 to A5 between 10 and 20, based on the corresponding probabilities in B1 to B5.
ISFORMULA
Checks if a given cell contains a formula and returns TRUE if the cell contains a formula, and FALSE otherwise.
ISFORMULA(reference)
reference: The reference to the cell that you want to test.
Example: ISFORMULA(A1) returns TRUE if cell A1 contains a formula ISFORMULA(B2) returns FALSE if cell B2 does not contain a formula ISFORMULA(C3:D4) returns an array of TRUE or FALSE values indicating whether each corresponding cell in the range contains a formula.
YEARFRAC
Returns the fraction of a year between two dates, represented as a decimal value.
YEARFRAC(start_date, end_date, [basis])
•start_date: The start date of the period.
•end_date: The end date of the period.
•basis: Optional.The day count basis to use in the calculation.Can be 0, 1, 2, 3, or 4.If omitted, it defaults to 0 (US (NASD) 30/360).
Example: YEARFRAC("2022-01-01", "2022-03-15") returns: 0.197222 YEARFRAC("1995-12-31", "2000-01-01", 1) returns: 4.008219 YEARFRAC("2000-01-01", "2000-02-29", 4) returns: 0.164383.
SKEW
Returns the skewness of a distribution, which is a measure of asymmetry.
SKEW(number1, [number2], ...)
•number1: The first number or range of numbers for which to calculate the skewness.
•number2: Optional.Additional numbers or ranges of numbers for which to calculate the skewness.Up to 255 arguments can be provided.
Example: SKEW(A1:A100) returns the skewness of the values in cells A1 through A100.
REPLAC
Replaces a sequence of characters in a string with another set of characters.
REPLACE(old_text, start_num, num_chars, new_text)
•old_text: The text string that contains the characters you want to replace.
•start_num: The position of the first character you want to replace in old_text.
•num_chars: The number of characters you want to replace in old_text.
•new_text: The replacement set of characters.
Example: REPLACE("Hello World", 7, 5, "Universe") returns "Hello Universe" REPLACE("John Doe", 2, 4, "Dane") returns "JDone" REPLACE("123456789", 3, 3, "ABC") returns "12ABC789".
PEARSON
Returns the Pearson product moment correlation coefficient between two data sets.
PEARSON(array1, array2)
•array1: The first array or range of values.
•array2: The second array or range of values.
Example: PEARSON(A1:A10,B1:B10) returns the correlation coefficient between the values in cells A1 through A10 and B1 through B10.
SEC
Returns the secant of an angle specified in radians.
SEC(x)
x: The angle in radians for which to calculate the secant.
Example: SEC(0.5) returns 1.13949392732455 SEC(1) returns 1.85081571768093 SEC(2) returns -1.03278929966568.
MAP
Applies a formula or function to every cell in a range and returns the results as a new array.
MAP(array, formula)
•array: The range of cells that you want to apply the formula to.
•formula: The formula or function that you want to apply to each cell in the array.The formula can reference the current cell with a dot (.) or the entire array with square brackets ([]).
Example: MAP(A1:A5, LAMBDA(x, x*2)) applies the formula x*2 to each cell in the range A1:A5 and returns the resulting array.
BESSELI
Returns the modified Bessel function of the first kind, I(x), for a given number x.
BESSELI(x, n)
•x: The number for which to calculate the modified Bessel function.
•n: The order of the Bessel function.Must be a nonnegative integer.
Example: BESSELI(1.5, 1) returns 0,981666428.
DROP
The DROP function removes a specified number of rows and columns from a given array, starting from the top-left corner.
DROP(array, rows, [columns])
•array: The array from which you want to remove rows and columns.
•rows: The number of rows to remove from the array.
•columns: The number of columns to remove from the array.This parameter is optional.
Example: DROP(A1:D5, 2, 1) removes 2 rows and 1 column from the array A1:D5.
IMCSC
Calculates the cosecant of a complex number.
IMCSC(x)
x: The angle in radians for which you want to calculate the inverse cosecant.
Example: IMCSC("2+3i") returns approximately 0,0904+0,0412i.
GESTEP
Returns a numeric value indicating whether a number is greater than a threshold value, 1 for TRUE, 0 for FALSE.
GESTEP(number, step)
•number: The number you want to test whether it is greater than the step value.
•step: The threshold value that determines whether the number is greater.If the number is greater than or equal to the step value, GESTEP returns TRUE; otherwise, it returns FALSE.
Example: GESTEP(5, 3) returns 1 GESTEP(10, 20) returns 0 GESTEP(A1, 0) where A1 contains -4 returns FALSE.
Returns the seconds of a time value.
SECOND(serial_number)
serial_number: The serial number of the date-time value from which to extract the second.
Example: SECOND("12:30:45 PM") returns 45 SECOND("7/4/2022 8:15:30 AM") returns 30 SECOND("9:00:00 PM") returns 0.
LN
Returns the natural logarithm of a number.
LN(x)
x: The positive real number for which you want to find the natural logarithm.
Example: LN(1) returns 0 because e to the power of 0 is 1 LN(10) returns approximately 2.302585093 as this is the natural logarithm of 10 (i.e. e raised to what power gives 10) LN(A1) returns the natural logarithm of the value contained in cell A1.
IMCOSH
The IMCOSH function is used to return the hyperbolic cosine of a complex number.
IMCOSH(inumber)
inumber: The complex number for which to find the hyperbolic cosine.
Example: IMCOSH("2+3i") returns approximately -5.5569+6.54812i.
FVSCHEDULE
Calculates the future value of an initial principal after applying a series of compound interest rates.
FVSCHEDULE(principal, schedule)
•principal: The present value or principal of the investment.
•schedule: An array of growth rates applied to the investment.These can be in decimal or percentage form.
Example: FVSCHEDULE(1000,[0.05,0.10,0.15,0.20]) FVSCHEDULE(2000,[5%,10%,15%,20%]) FVSCHEDULE(50000,[2%,3%,4%,5%,6%]).
GROWTH
Returns an array of y-values generated by evaluating an exponential curve that fits the data points.
GROWTH(known_y's,[known_x's],[new_x's],[const])
•known_y's: The set of y-values that you already know in the relationship y = b*m^x.
•known_x's: Optional.The set of x-values that you already know in the relationship y = b*m^x.
•new_x's: Optional.The new x-values for which you want to generate corresponding y-values using the exponential curve.
•const: Optional.A logical value that specifies whether to force the constant b to equal 1.If const is TRUE, b is set equal to 1; if it is FALSE or omitted, b is calculated normally.
Example: GROWTH(A1:A6,B1:B6,["10","20"]) GROWTH(A1:A6,,B1:B3) GROWTH(A1:A6,B1:B6).
MIRR
Returns the modified internal rate of return for a series of periodic cash flows, considering both cost of investment and interest on reinvestment of cash.
MIRR(values, finance_rate, reinvest_rate)
•values: An array or reference to cells that contain cash flows.The first cash flow represents an initial investment and must be a negative number.All succeeding cash flows represent income received, and must be positive numbers.
•finance_rate: The interest rate paid on money used in a financial transaction.This is also called the financing rate.
•reinvest_rate: The interest rate received on money that is reinvested in a financial instrument.This is also called the reinvestment rate.
Example: MIRR([-1000, 150, 252, 302, 204], 0.1, 0.12) MIRR(A1:A6, B1, C1).
CHIDIST
Returns the one-tailed probability of the chi-squared distribution.
CHIDIST(x, degrees_freedom)
x: The value at which you want to evaluate the distribution.
degrees_freedom: The number of degrees of freedom of the distribution.Must be a positive integer.
Example: CHIDIST(2.5, 4) CHIDIST(6, 8) CHIDIST(1, 1).
XNPV
Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.
XNPV(rate, values, dates)
•rate: The discount rate to apply to the cash flows, expressed as a decimal.
•values: An array or range of cells representing the series of cash flows.
•dates: An array or range of cells representing the dates corresponding to each cash flow.
Example: XNPV(0.05, [-1000, 250, 250, 250, 250, 250], ["2022-01-01","2023-01-01","2024-01-01","2025-01-01","2026-01-01","2027-01-01"]) XNPV(0.1, [-5000, 1000, 2000, 3000], ["2019-01-01","2020-01-01","2021-01-01","2022-01-01"]) XNPV(0.06, [10000, -1500, -1500, -1500, -1500], ["2021-01-01","2022-01-01","2023-01-01","2024-01-01","2025-01-01"]).
COMBINA
Returns the number of combinations with repetitions for a given number of items.
COMBINA(number, number_chosen)
•number: The total number of items you have to choose from.
•number_chosen: The number of items you want to choose.Must be a positive integer.
Example: COMBINA(3, 2) returns 6 COMBINA(4, 3) returns 20 COMBINA(2, 1) returns 2.
TEXTSPLIT
Splits a text string into an array of substrings based on specified delimiters.
TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
•text: The text string to split.
•col_delimiter: The character or string used to separate columns within the text string.
•[row_delimiter]: Optional: The character or string used to separate rows within the text string.If not specified, the entire text is treated as one row.
•[ignore_empty]: Optional: A boolean value (TRUE or FALSE) that determines whether empty cells should be included in the result.By default, empty cells are included (TRUE).
•[match_mode]: Optional: A text value that specifies the matching mode.Possible values are 'exact' (default) or 'partial'.In 'partial' mode, the delimiter is treated as a regular expression pattern for splitting.
•[pad_with]: Optional: A text value used to pad columns to a consistent length.If specified, shorter columns are padded with this text to match the length of the longest column.
Example: TEXTSPLIT("apple, banana, cherry", ", ") returns [["apple", "banana", "cherry"]].
TOCOL
Converts an array into a single-column array.
TOCOL(array, [ignore], [scan_by_column])
•array: The array to be converted into a single-column array.
•[ignore]: Optional.A boolean value (TRUE or FALSE) that determines whether to ignore empty cells in the input array.By default, empty cells are not ignored (FALSE).
•[scan_by_column]: Optional.A boolean value (TRUE or FALSE) that determines whether to scan the array by column (TRUE) or by row (FALSE).By default, scanning is done by column (TRUE).
Example: TOCOL(A1:B5) returns a single column with all values within the range A1:B5.
CHISQ.DIST
Returns the one-tailed probability of the chi-squared distribution.
CHISQ.DIST(x, degrees_freedom, cumulative)
•x: The value at which you want to evaluate the distribution.
•degrees_freedom: The number of degrees of freedom of the distribution.Must be a positive integer.
•cumulative: A logical value that determines the form of the function.If TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function.
Example: CHISQ.DIST(2.5, 4, TRUE) CHISQ.DIST(6, 8, FALSE) CHISQ.DIST(1, 1, TRUE).
DELTA
Tests whether two values are equal.Returns 1 if the values are equal, or 0 otherwise.
DELTA(number1, [number2])
•number1: The first value you want to compare.
•numberN: Optional.The second value you want to compare.If omitted, assumes a default value of zero.
Example: DELTA(5,5) returns 1 DELTA(10,20) returns 0 DELTA(A1,A2) returns 1 if the values in A1 and A2 are the same, or 0 otherwise.
ERF
The ERF function returns the error function of a number.
ERF(x)
x: The number for which to calculate the error function.
Example: ERF(0) returns 0 ERF(1.5) returns 0.96610514647531 ERF(A2) returns the error function of the value in cell A2.
COUPPCD
Calculates the previous coupon date before the settlement date for a security that pays periodic interest.
COUPPCD(settlement, maturity, frequency, [basis])
•settlement: The settlement date.
•maturity: The maturity date.
•frequency: The number of coupon payments per year.
•basis: Optional.The type of day count basis to use.If omitted, assumed to be 0 (US or NASD) by default.
Example: COUPPCD("2022-01-01", "2022-06-30", 2) returns 44561.
F.INV.RT
Returns the inverse of the right-tailed F probability distribution.
F.INV.RT(probability, degrees_freedom1, degrees_freedom2)
•probability: A probability value between 0 and 1.
•degrees_freedom1: The numerator degrees of freedom for the distribution.
•degrees_freedom2: The denominator degrees of freedom for the distribution.
Example: F.INV.RT(0.05, 3, 5) returns approximately 5.409 F.INV.RT(0.01, 10, 20) returns approximately 3.368 F.INV.RT(0.025, 4, 6) returns approximately 6.227.
NUMBERVALUE
Converts text to a number in a locale-independent way.
NUMBERVALUE(text, [decimal_separator], [group_separator])
•text: The text to be converted to a number.
•decimal_separator: An optional argument that specifies the character used as the decimal separator.If omitted, Excel uses the system's regional settings.
•group_separator: An optional argument that specifies the character used as the group separator, such as a comma or period.If omitted, Excel uses the system's regional settings.
Example: NUMBERVALUE('1234.56') returns 1234.56 NUMBERVALUE('1,234.56', ',', '.') returns 1234.56 NUMBERVALUE('$1,234.56', '.', ',') returns 1234.56.
BINOM.INV
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value.
BINOM.INV(trials, probability_s, alpha)
•trials: The number of independent trials.
•probability_s: The probability of success on each trial.
•alpha: The criterion value, a number between 0 and 1 inclusive.
Example: BINOM.INV(10, 0.4, 0.5) returns 4 BINOM.INV(20, 0.2, 0.7) returns 5 BINOM.INV(30, 0.6, 0.9) returns 21.
BITAND
Returns a bitwise 'AND' of two numbers.
BITAND(number1, number2)
•number: The first number to be used in the bitwise operation.
•number: The second number to be used in the bitwise operation.
Example: BITAND(7, 3) returns 3 BITAND(10, 6) returns 2 BITAND(25, 18) returns 16.
FACT
Returns the factorial of a number.
FACT(x)
x: The non-negative integer for which to calculate the factorial.
Example: FACT(5) returns 120 FACT(0) returns 1 FACT(10) returns 3628800.
UNICHAR
Returns the Unicode character that is referenced by the given numeric value.
UNICHAR(num)
num: A number between 1 and 1,114,111 that represents the Unicode character you want to return.
Example: UNICHAR(65) returns "A" UNICHAR(8364) returns "€" UNICHAR(128591) returns "??".
CUMIPMT
Returns the cumulative interest paid on a loan between two periods.
CUMIPMT(rate, nper, pv, start_period, end_period, type)
•rate: The interest rate per period.
•nper: The total number of payment periods in the loan.
•pv: The present value (or principal) of the loan.
•start_period: The first payment period for which to calculate the interest.Payment periods are numbered starting with 1.
•end_period: The last payment period for which to calculate the interest.
•type: Optional.The timing of the payments.Use 0 for payments due at the end of the period or 1 for payments due at the beginning of the period.Default is 0.
Example: CUMIPMT(0.06/12,24,-5000,1,12,0) returns the cumulative interest paid on a $5,000 loan with a 6% annual interest rate and a 2-year term, where the first payment is due in the first month CUMIPMT(0.07/12,36,-10000,13,24,1) returns the cumulative interest paid on a $10,000 loan with a 7% annual interest rate and a 3-year term, where payments are made at the beginning of each month and the interest is calculated between the 13th and 24th payments CUMIPMT(0.05/4,8,30000,1,4,0) returns the cumulative interest paid on a $30,000 loan with a 5% quarterly interest rate and a 2-year term, where payments are due at the end of each quarter and the interest is calculated between the 1st and 4th payments.
COLUMNS
Returns the number of columns in a range or array.
COLUMNS(array)
array: The range or array of cells for which you want to count the number of columns.
Example: COLUMNS(A1:D5) returns 4 COLUMNS(A1:C1) returns 3.
BESSELY
Returns the Bessel function of the second kind, Y(x), for a given positive real number x.
BESSELY(x, n)
•x: The positive real number for which to calculate the Bessel function.
•n: The order of the Bessel function.Must be a nonnegative integer or a real number greater than -1.
Example: BESSELY(5, 2) returns 0.367662879 BESSELY(1.5, 0) returns 0.382448925 BESSELY(3, 4.2) returns -0,916682846.
DOLLAR
Converts a number to text using currency format with the specified number of decimal places.
DOLLAR(num, [decimals])
•num: The number you want to format as currency.
•decimals: Optional.The number of decimal places to display in the result.If omitted, assumes a default of 2 decimal places.
Example: DOLLAR(1234.5678) returns "$1,234.57" DOLLAR(9876.54321,3) returns "$9,876.543" DOLLAR(A1,0) formats the value in cell A1 as currency with no decimals.
FV
Calculates the future value of an investment based on a constant interest rate and periodic, constant payments.
FV(rate, nper, pmt, [pv], [type])
•rate: The interest rate per period.
•nper: The total number of payment periods in an annuity.
•pmt: The payment made each period.Payments are made at the end of the period by default.Use the optional 'type' parameter to specify payments at the beginning of the period.
•pv: The present value, or the lump-sum amount that a series of future payments is worth right now.
•type: Optional.Argument that specifies when payments are due.Use 0 (or omitted) for payments due at the end of the period, or use 1 for payments due at the beginning of the period.
Example: FV(0.05/12, 12*15, -200, 50000, 0).
MIDB
Returns a specific number of bytes from a text string starting at the position you specify.
MIDB(text, start_num, num_bytes)
•text: The text string that contains the characters you want to extract.
•start_num: Specifies the position of the first byte you want to extract.The first byte in text is 1.
•num_bytes: Specifies the number of bytes you want MIDB to return.
Example: MIDB('apple', 1, 3) returns 'app' MIDB('banana', 3, 2) returns 'na' MIDB('cherry', 2, 4) returns 'herr'.
FORECAST.ETS.SEASONALITY
Returns the length of the seasonal pattern detected in time series data using the Exponential Smoothing (ETS) algorithm.
FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])
•array: An array of numbers representing the historical data.
•array: An array of dates or numbers representing the timeline of the historical data.Should have the same number of elements as the 'values' parameter.
•[data_completion]: Optional.A boolean value (TRUE or FALSE) that specifies whether missing values in the historical data should be completed.By default, missing values are not completed (FALSE).
•[aggregation]: Optional.A text value that specifies the aggregation method for the data.Possible values are 'average' (default), 'sum', 'count', 'max', or 'min'.
Example: FORECAST.ETS.SEASONALITY(B2:B8, A2:A8).
CLEAN
Removes all nonprintable characters from a text string.
CLEAN(text)
text: The text string that contains the characters you want to remove.
Example: CLEAN("This is a test.") returns "This is a test.".
MOD
Returns the remainder after a number is divided by a divisor.
MOD(num, divisor)
•num: The number for which you want to find the remainder after division.
•divisor: The number by which to divide the number argument.
Example: MOD(10,3) returns 1 MOD(A1,B1) where cell A1 contains the number 15 and B1 contains the number 4 returns 3 MOD(-10,-3) returns -1.
CODE
Returns a numeric code for the first character in a text string.
CODE(text)
text: The text string containing the character for which you want the code.
Example: CODE("A") returns 65 CODE("a") returns 97 CODE("$") returns 36.
UNICODE
Returns the Unicode value of the first character in a text string.
UNICODE(text)
text: The text string containing the character for which you want to find the Unicode value.If the text string contains multiple characters, only the first character is used.
Example: UNICODE("A") returns 65 UNICODE("€") returns 8364.
IMTAN
Returns the tangent of a complex number.
IMTAN(inumber)
inumber: The complex number for which you want to calculate the tangent.
Example: IMTAN("1+i") returns "0.2718+1.0839i".
IMSINH
Returns the hyperbolic sine of a complex number.
IMSINH(inumber)
inumber: The complex number for which you want to calculate the hyperbolic sine.
Example: IMSINH("1+i") returns "0.63496+1.2985i".
IMPRODUCT
Returns the product of complex numbers.
IMPRODUCT(inumber1, [inumber2], ...)
•inumber1: The first complex number in the product.
•inumberN: Optional.Additional complex numbers you want to include in the product.
Example: IMPRODUCT("2+3i", "4+5i", "-6+7i") returns "-112-181i".
CRITBINOM
Calculates the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value.
CRITBINOM(trials, probability_s, alpha)
•trials: The number of Bernoulli trials.
•probability_s: The probability of success on each trial.
•alpha: The significance level at which to evaluate the probability.
Example: CRITBINOM(10,0.2,0.05) returns 0 CRITBINOM(20,0.5,0.01) returns 5 CRITBINOM(50,0.3,0.1) returns 8.
KURT
Returns the kurtosis of a data set, which is a measure of the peakedness or flatness of the distribution, relative to the normal distribution.
KURT(number1, [number2], ...)
•number1: The first number in the data set.
•[numberN]: Optional.Additional numbers in the data set.
Example: KURT(A1:A10) returns the kurtosis of the values in cells A1 through A10 KURT(1,2,3,4,5) returns the kurtosis of the values 1, 2, 3, 4, and 5 KURT(B2:B10, C2:C10) returns the kurtosis of the values in two columns.
STOCKHISTORY
Returns historical stock prices for a given date range and ticker symbol.
STOCKHISTORY(ticker, start_date, end_date, [interval], [headers])
•string: The ticker symbol for the stock for which to retrieve data.
•string: The start date of the time period for which to retrieve data.Must be in yyyy-mm-dd format or as a valid Excel date.
•string: The end date of the time period for which to retrieve data.Must be in yyyy-mm-dd format or as a valid Excel date.
•string: (optional) The frequency of the data to retrieve.Valid options are 'DAILY', 'WEEKLY', and 'MONTHLY'.Default is 'DAILY'.
•boolean: (optional) Determines whether to include column headers in the returned data.Default is TRUE.
Example: STOCKHISTORY("AAPL", "2021-01-01", "2021-12-31") returns the daily stock prices for Apple Inc. between January 1st, 2021 and December 31st, 2021 STOCKHISTORY("GOOGL", "2020-01-01", "2022-03-14", "WEEKLY") returns the weekly stock prices for Alphabet Inc. (Google) between January 1st, 2020 and March 14th, 2022 STOCKHISTORY("MSFT", A1, B1, "MONTHLY", FALSE) returns monthly stock prices for Microsoft Corporation between the dates in cells A1 and B1 without headers.
SQRT
Returns the positive square root of a number.
SQRT(x)
x: The number for which to calculate the square root.Must be non-negative.
Example: SQRT(25) returns 5 SQRT(2) returns approximately 1.41421356 SQRT(A1) returns the square root of the value in cell A1.
IMLOG10
Returns the base-10 logarithm of a complex number.
IMLOG10(inumber)
inumber: The complex number for which you want to calculate the base-10 logarithm.
Example: IMLOG10("1+i") returns "0.1505+0.3414i".
MMULT
Returns the matrix product of two arrays.
MMULT(array1, array2)
•array1: The first array or range to multiply.The number of columns in the first array must be the same as the number of rows in the second array.
•array2: The second array or range to multiply.The number of rows in the second array must be the same as the number of columns in the first array.
Example: MMULT(A1:C3, D1:F3) returns the matrix product of ranges A1:C3 and D1:F3.
F.DIST.RT
Returns the right-tailed F probability distribution.
F.DIST.RT(x, degrees_freedom1, degrees_freedom2)
•x: The input value at which to evaluate the function.
•degrees_freedom1: The numerator degrees of freedom for the distribution.
•degrees_freedom2: The denominator degrees of freedom for the distribution.
Example: F.DIST.RT(1.5,3,5) returns 0.322165371 F.DIST.RT(2.5,10,20) returns 0.038903636.
GEOMEAN
Returns the geometric mean of an array or range of positive numeric data.
GEOMEAN(number1,[number2],...)
•number1: The first number or range of numbers for which to calculate the geometric mean.
•[numberN]: Optional.Additional numbers or ranges of numbers for which to calculate the geometric mean, up to a maximum of 255 values.
Example: GEOMEAN(10,100,1000) returns 100 GEOMEAN([1,2,3,4,5]) returns 2.605171084.
OCT2BIN
Converts a octal number to binary.
OCT2BIN(num, [places])
•num: The octal number you want to convert to binary.The number cannot contain more than 10 characters (10 bits).
•places: Optional.Argument that specifies the minimum number of characters (bits) to use for the binary number.If omitted, Excel uses the smallest number of characters required.
Example: OCT2BIN(63) returns 110011 OCT2BIN(72) returns 111010.
NORMINV
Returns the inverse of the normal cumulative distribution for a specified mean and standard deviation.
NORMINV(probability, mean, standard_dev)
•probability: The probability associated with the normal distribution.
•mean: The arithmetic mean of the distribution.
•standard_dev: The standard deviation of the distribution.
Example: NORMINV(0.75, 80, 6) returns approximately 84.04.
DB
Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.
DB(cost, salvage, life, period, [month])
•cost: The initial cost of the asset.
•salvage: The value of the asset at the end of its useful life.
•life: The number of periods over which the asset will be depreciated.
•period: The period for which you want to calculate the depreciation.Must be greater than or equal to 1 and less than or equal to the life of the asset.
•month: Optional.The month in the first year for which you want to calculate depreciation.If omitted or zero, defaults to 12 (December).
Example: DB(10000, 2000, 10, 2) DB(50000, 5000, 5, 3, 6) DB(2500, 1000, 7, 4).
SHEET
Returns the sheet number of a reference.
SHEET(reference)
reference: A reference to a cell or range of cells in the same workbook.
Example: SHEET(A1) returns the sheet number of cell A1 SHEET('Sheet2'!B5) returns the sheet number of cell B5 on Sheet2 SHEET(Budget!A1:B5) returns the sheet number of the Budget worksheet.
BINOM.DIST
Returns the individual term binomial distribution probability.
BINOM.DIST(number_s, trials, probability_s, [cumulative])
•number_s: The number of successes in trials.
•trials: The number of independent trials.
•probability_s: The probability of success on each trial.
•cumulative: Optional.A logical value that specifies whether to return the cumulative distribution or the individual term binomial distribution probability.If omitted, cumulative defaults to TRUE (cumulative distribution).
Example: BINOM.DIST(3, 5, 0.5, FALSE) returns 0.3125.
CONCATENATE
Joins two or more text strings into one string.
CONCATENATE(text1, [text2], ...)
•text1: The first text string to concatenate.
•[textN]: Optional.Additional text strings to concatenate.
Example: CONCATENATE("Hello", " ", "world") returns "Hello world" CONCATENATE("Mary", " had a little lamb.") returns "Mary had a little lamb."CONCATENATE("Today is ", TEXT(TODAY(), "mm/dd/yyyy")) returns a string that includes today's date.
SLN
Returns the straight-line depreciation of an asset for a single period.
SLN(cost, salvage, life)
•cost: The initial cost of the asset.
•salvage: The value of the asset at the end of its useful life (also called the salvage value).
•life: The number of periods over which the asset will be depreciated.
Example: SLN(10000, 2000, 10) returns 800 SLN(5000, 500, 5) returns 900 SLN(250000, 10000, 25) returns 9600.
TODAY
Returns the current date as a serial number.
TODAY()
Example: TODAY() returns 44664 (if today's date is March 15, 2023) TODAY() returns 44665 (if today's date is March 16, 2023) TODAY() returns 44666 (if today's date is March 17, 2023).
DURATION
The DURATION function calculates the Macaulay duration of a security with an assumed par value of $100.
DURATION(settlement, maturity, coupon, yld, frequency, [basis])
•settlement: The security's settlement date.The security settlement date is the date after the issue date when the security is traded to the buyer.
•maturity: The security's maturity date.The maturity date is the date when the security expires.
•coupon: The security's annual coupon rate.
•yld: The security's annual yield.
•frequency: The number of coupon payments per year.
•[basis]: Optional.The type of day count basis to use.
Example: DURATION('12/31/2022', '12/31/2025', 0.08, 0.09, 2) returns 2,72170141.
TBILLEQ
Calculates the bond-equivalent yield for a Treasury bill based on a discount from face value.
TBILLEQ(settlement, maturity, discount)
•settlement: The Treasury bill's settlement date.
•maturity: The Treasury bill's maturity date.
•discount: The discount from face value, as a percentage (e.g., 98.5 for a 1.5% discount).
Example: TBILLEQ('2022-01-01', '2022-06-30', 98.5%).
VARA
Estimates the variance based on a sample, including numbers, text, and logical values.
VARA(value1, [value2], ...)
•value1: The first value or range of values that you want to calculate the variance of.At least one value is required.
•[valueN]: Additional values or ranges of values that you want to include in the calculation.You can include up to 255 values or ranges.The values can be numbers, text, or logical values (TRUE or FALSE).
Example: VARA(A1:A10) returns the variance of the values in cells A1 through A10, which can be a combination of numbers, text, and logical values VARA(B1:B10, C1:C10) returns the variance of the combined sets of values in cells B1 through B10 and C1 through C10, which can also include a mix of numbers, text, and logical values.
CHAR
Returns the character specified by a number.
CHAR(num)
num: A number that represents a character in the character set used by your computer.This can be a number between 1 and 255, or a formula or reference to a cell containing such a number.
Example: CHAR(65) returns "A" CHAR(97) returns "a" CHAR(36) returns "$".
LAMBDA
Allows you to define your own custom functions using a lambda expression, which is a shorthand way of defining anonymous functions.
LAMBDA(parameter1, [parameterN], expression)
•parameter1: The name of parameter to be defined in the function.
•[parameterN]: Optional.The nth name to define a parameter inside the function.
•expression: The expression that defines the function, can use the specified parameters.
Example: LAMBDA(x, y, x^2 + y^2) defines a custom function that takes two parameters (x and y) and returns their sum of squares.
ISEVEN
Checks if a given number is even and returns TRUE if the number is even, and FALSE otherwise.
ISEVEN(num)
num: The number that you want to test.
Example: ISEVEN(2) returns TRUE because 2 is even ISEVEN(-4) returns TRUE because -4 is even ISEVEN(B3) returns TRUE if cell B3 contains an even number.
ISREF
Checks if a given value is a valid cell reference and returns TRUE if the value is a valid cell reference, and FALSE otherwise.
ISREF(value)
value: The value that you want to test.
Example: ISREF(A1) returns TRUE because A1 is a valid cell reference ISREF("B2") returns FALSE because "B2" is not a valid cell reference ISREF(123) returns FALSE because 123 is not a valid cell reference.
DAYS
Calculates the number of days between two dates.
DAYS(end_date, start_date)
•end_date: The end date of the time period.
•start_date: The start date of the time period.
Example: DAYS("2022-03-15","2022-01-01") returns 73 days between January 1, 2022 and March 15, 2022 DAYS("2025-12-31","2000-01-01") returns 9496 days between January 1, 2000 and December 31, 2025.
BETAINV
Returns the inverse of the cumulative beta probability density function.
BETAINV(probability, alpha, beta, [A], [B])
•probability: The probability at which to evaluate the function.Must be a number between 0 and 1 (inclusive).
•alpha: The first parameter of the beta distribution.Must be greater than 0.
•beta: The second parameter of the beta distribution.Must be greater than 0.
•[A]: Optional.The lower bound of the interval of x.If omitted, A defaults to 0.
•[B]: Optional.The upper bound of the interval of x.If omitted, B defaults to 1.
Example: BETAINV(0.488, 2, 3) BETAINV(1.92, 4, 2, 0.5, 1) BETAINV(0.625, 1, 1, 0, 0.8).
COUPDAYS
Calculates the number of days in the coupon period that contains the settlement date for a security that pays periodic interest.
COUPDAYS(settlement, maturity, frequency, [basis])
•settlement: The settlement date.
•maturity: The maturity date.
•frequency: The number of coupon payments per year.
•[basis]: Optional.The type of day count basis to use.If omitted, assumed to be 0 (US or NASD) by default.
Example: COUPDAYS("2022-01-01", "2022-06-30", 2) returns 180.
MINUTE
Returns the minute of a time value.The minute is given as an integer, ranging from 0 to 59.
MINUTE(serial_number)
serial_number: The time that contains the minute you want to find.
Example: MINUTE("2:30:15 PM") returns 30 MINUTE("9/1/2022 5:45:00 AM") returns 45.
DOLLARDE
Converts a fractional dollar price expressed as a decimal to a fraction using the specified denominator.
DOLLARDE(decimal_dollar, fraction)
•number: The decimal dollar value you want to convert to a fraction.
•number: The denominator to use for the resulting fraction.
Example: DOLLARDE(1.25, 16) returns 2.5625.
IMREAL
Returns the real coefficient of a complex number.
IMREAL(inumber)
inumber: The complex number for which you want to return the real coefficient.
Example: IMREAL("3+4i") returns 3 IMREAL("2-i") returns 2 IMREAL("-5+2i") returns -5.
CHISQ.INV
Returns the inverse of the one-tailed probability of the chi-squared distribution.
CHISQ.INV(probability, degrees_freedom)
•probability: A probability associated with the chi-squared distribution.
•degrees_freedom: The number of degrees of freedom of the distribution.Must be a positive integer.
Example: CHISQ.INV(0.05, 3) returns 0.351846318 CHISQ.INV(0.01, 6) returns 0.87209033 CHISQ.INV(0.001, 10) returns 1.478743464.
INTERCEPT
Returns the y-axis intercept of a line defined by a set of x- and y-values.
INTERCEPT(known_y's, known_x's)
•known_y's: The array or range of y-values that define the line.
•known_x's: The array or range of corresponding x-values to known_y's.If omitted, Excel assumes a set of 1, 2, 3, ..., n.
Example: INTERCEPT(B2:B7, A2:A7) returns the y-intercept of the linear regression line for the data in the range A2:B7 INTERCEPT(C2:C6, D2:D6) returns the y-intercept of the linear regression line for the data in the range C2:D6.
SEQUENCE
Returns an array of sequential numbers with a given length and step value.
SEQUENCE(rows, [columns], [start], [step])
•rows: The number of rows to return.
•[columns]: Optional.The number of columns to return.Default is 1.
•[start]: Optional.The starting value for the sequence.Default is 1.
•[step]: Optional.The increment between each value in the sequence.Default is 1.
Example: SEQUENCE(5) returns [[1],[2],[3],[4],[5]] SEQUENCE(3,2,-1,0.5) returns [[-1, -0.5], [0, 0.5], [1, 1.5]] SEQUENCE(4,1,10) returns [[10], [11], [12], [13]].
MINIFS
Returns the smallest number among numbers that meet multiple criteria.
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
•min_range: The range of cells to find the minimum value.
•criteria_range1: The first range to evaluate based on the associated criteria.
•criteria1: The criteria to use for the first range.
•[criteria_rangeN, criteriaN]: Optional.Additional ranges to evaluate based on their associated criteria.
Example: MINIFS(A1:A10, B1:B10, ">0", C1:C10, "<5") returns the smallest value in range A1:A10 where the corresponding values in range B1:B10 are greater than 0 and the corresponding values in range C1:C10 are less than 5 MINIFS(D1:D10, D1:D10, ">10") returns the smallest value in range D1:D10 that is greater than 10 MINIFS(E1:E10, F1:F10, "apples", G1:G10, "oranges") returns the smallest value in range E1:E10 where the corresponding values in range F1:F10 are "apples" and the corresponding values in range G1:G10 are "oranges".
CHOOSE
Returns a value from a list of values based on its position in the list.
CHOOSE(index_num, value1, [value2], ...)
•number: The index number that corresponds to the value you want to return.Must be a positive integer.
•value1: The first value or cell reference in the list.
•valueN: Optional.Additional values or cell references in the list.
Example: CHOOSE(2, "Apple", "Banana", "Cherry") returns "Banana" CHOOSE(3, A1:C1) returns the value in cell C1 CHOOSE(1, 100, 200, 300) returns 100.
SHEETS
Returns the number of sheets in a reference.
SHEETS([reference])
reference: Optional.A reference to a cell or range of cells in the same workbook.If omitted, returns the number of sheets in the entire workbook.
Example: SHEETS() returns the total number of sheets in the workbook SHEETS(Annual!A1:C10) returns the number of sheets that contain data referenced in Annual!A1:C10 SHEETS(Sheet3!D4:E8) returns 1 if Sheet3 is the only sheet containing data referenced in the specified range.
MROUND
Rounds a number to the nearest multiple of another number.
MROUND(num, multiple)
•num: The number you want to round.
•multiple: The multiple to which you want to round number.
Example: MROUND(10,3) returns 9 MROUND(25.75,0.2) returns 25.8 MROUND(-10,-3) returns -9.
ERF.PRECISE
The ERF.PRECISE function returns the error function of a number, accurate to approximately 15 digits.
ERF.PRECISE(x)
x: The number for which to calculate the error function.
Example: ERF.PRECISE(0) returns 0 ERF.PRECISE(1.5) returns 0.96610514647531 ERF.PRECISE(A2) returns the error function of the value in cell A2.
TDIST
Calculates the probability of a Student's t-distribution with a given input value.
TDIST(x, degrees_freedom, tails)
•x: The input value for which to calculate the distribution.
•degrees_freedom: The number of degrees of freedom in the distribution.
•tails: The number of distribution tails to use.Can be either 1 for a one-tailed distribution or 2 for a two-tailed distribution.
Example: TDIST(1.5, 10, 1) returns approximately 0.082253 TDIST(2, 8, 2) returns approximately 0.08051.
SMALL
Returns the k-th smallest value in a data set.
SMALL(arr, k)
•arr: An array or range of numeric values from which to find the k-th smallest value.
•k: Indicates which value to return, where 1 is the smallest value, 2 is the second-smallest value, and so on.
Example: SMALL(A1:A10, 1) returns the smallest value in the range A1:A10 SMALL([3,4,2,8,9], 3) returns 4 SMALL(B2:B8, 5) returns the 5th smallest value in the range B2:B8.
FORECAST
Calculates or predicts a future value by using linear regression.
FORECAST(x, known_y's, known_x's)
•x: The x-value used to predict the y-value.
•known_y's: An array of y-values representing known data points that you want to use in the regression analysis.
•known_x's: An array of x-values representing known data points that you want to use in the regression analysis.The number of elements in known_x's should be equal to the number of elements in known_y's.
Example: FORECAST(10,A2:A8,B2:B8) FORECAST(20,A2:A8,B2:B8) FORECAST(30,A2:A8,B2:B8).
BAHTTEXT
Converts a number to Thai text and adds a suffix of 'Baht' for currency.
BAHTTEXT(number)
number: The number to be converted to Thai text.Must be between -922,337,203,685,477.58 and 922,337,203,685,477.58.
Example: BAHTTEXT(1234.56) returns "หนึ่งพันสองร้อยสามสิบสี่บาทห้าสิบหกสตางค์".
MEDIAN
Returns the median value in a range of numbers.
MEDIAN(number1, [number2], ...)
•number1: The first number or range of numbers that you want to find the median value for.
•[numberN]: Optional.Additional numbers or ranges of numbers that you want to find the median value for.You can specify up to 255 arguments.
Example: MEDIAN(A1:A10) returns the median value in the range A1:A10 MEDIAN(10, 20, 30) returns the number 20, which is the median value among the arguments MEDIAN(B1:B5, C1:C5) returns the median value between the two ranges B1:B5 and C1:C5.
MODE.SNGL
Returns the most frequently occurring value in a range of data.If multiple values occur equally frequently, the function returns the first one found.
MODE.SNGL(number1,[numberN],...)
•number1: The first number for which you want to find the mode.
•[numberN]: Optional.The nth number for which you want to find the mode.You can also enter up to 255 additional numbers or ranges.
Example: MODE.SNGL(1,2,2,3,4,4,4,5) returns 4 MODE.SNGL(A1:A10) returns the most frequently occurring value in the range A1 through A10 MODE.SNGL(B1:B12,C1:C12) returns the most frequently occurring value in the ranges B1:B12 and C1:C12.
SUMXMY2
Returns the sum of the squares of differences of corresponding values in two arrays or ranges.
SUMXMY2(array_x, array_y)
•array_x: An array or range that contains the first set of values.
•array_y: An array or range that contains the second set of values.
Example: SUMXMY2([1, 2, 3], [4, 5, 6]) returns 27 (since the sum of (1-4)^2, (2-5)^2, and (3-6)^2 is 9 + 9 + 9 = 27) SUMXMY2(A2:A6, B2:B6) returns the sum of the squares of the differences of each pair of values in cells A2 through A6 and B2 through B6 SUMXMY2(C2:C6, D2:D6) returns the sum of the squares of the differences of each pair of values in cells C2 through C6 and D2 through D6.
REGISTER.ID
Generates an ID number for a specified entity or object.
REGISTER.ID(entity)
string: The name of the entity or object to which the ID number relates.
Example: REGISTER.ID("Customer") returns "CUS1024" REGISTER.ID("Order") returns "ORD1025" REGISTER.ID("Product") returns "PROD1026".
BITXOR
Returns a bitwise 'XOR' of two numbers.
BITXOR(number1, number2)
•number: The first number to be used in the bitwise operation.
•number: The second number to be used in the bitwise operation.
Example: BITXOR(5, 3) returns 6 BITXOR(12, 8) returns 4 BITXOR(25, 18) returns 11.
MUNIT
Returns the Unit matrix for a given size.
MUNIT(size)
size: The size of the square unit matrix to return.Size is the number of rows (or columns) in the resulting matrix.
Example: MUNIT(3) returns [[1,0,0],[0,1,0],[0,0,1]] MUNIT(4) returns [[1,0,0,0];[0,1,0,0];[0,0,1,0];[0,0,0,1]].
ISO.CEILING
Rounds a number up to the nearest integer or to the nearest multiple of a specified significance, and returns a result that is on the ISO list of paper sizes.
ISO.CEILING(number, [significance])
•number: The number that you want to round.
•number: Optional.The multiple to which you want to round.If omitted, defaults to 1.
Example: ISO.CEILING(2.7) rounds up to 3 ISO.CEILING(5.5, 0.5) rounds up to 5.5 because 5.5 is the nearest multiple of 0.5 that is on the ISO list of paper sizes ISO.CEILING(B2:C3, 100) returns an array of rounded values based on the specified multiple of 100.
VSTACK
Stacks values from multiple ranges vertically into a single column.
VSTACK(range1, [rangeN], ...)
•range1: The first range of cells to stack.
•rangeN: Optional.Additional ranges of cells to stack.
Example: VSTACK(A1:A3,B1:B3) returns: A1 A2 A3 B1 B2 B3 VSTACK(A1:A3,'Text') returns: A1 A2 A3 Text.
TRIM
Removes all spaces from text except for single spaces between words.
TRIM(text)
text: Text - the text from which to remove excess spaces.
Example: TRIM(" apple ") returns "apple" TRIM(" banana split ") returns "banana split" TRIM(" grape") returns "grape".
YIELDDISC
Returns the annual yield of a discounted security, based on its price and face value.
YIELDDISC(settlement, maturity, pr, redemption, [basis])
•settlement: The security's settlement date.
•maturity: The security's maturity date.
•pr: The security's price per $100 face value.
•redemption: The security's redemption (face) value per $100 face value.
•[basis]: Optional.The day count basis to use in the calculation.Can be 0, 1, 2, 3, or 4.If omitted, it defaults to 0 (US (NASD) 30/360).
Example: YIELDDISC("2022-01-01", "2027-01-01", 95, 100) YIELDDISC("2022-01-01", "2032-01-01", 97.5, 100, 1) YIELDDISC("2022-01-01", "2037-01-01", 80, 100, 4).
PERCENTRANK.INC
Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set.
PERCENTRANK.INC(array, x, [significance])
•array: The array or range of data that defines relative standing.
•x: The value for which you want to know the rank.
•[significance]: Optional.The number of significant digits for the returned percentage value.Defaults to 3.
Example: PERCENTRANK.INC(A1:A10,B1) returns the rank of the value in B1 as a percentage of the values in A1 through A10 PERCENTRANK.INC(C1:C5,20,2) returns the rank of the value 20 in the range C1 through C5 as a percentage with 2 significant digits PERCENTRANK.INC(D1:D100,50) returns the rank of the value 50 in the range D1 through D100 as a percentage.
PERCENTRANK
Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set.If the data set has a small sample size, use PERCENTRANK.INC instead.
PERCENTRANK(array, x, [significance])
•array: The array or range of data that defines relative standing.
•x: The value for which you want to know the rank.
•[significance]: Optional.The number of significant digits for the returned percentage value.Defaults to 3.
Example: PERCENTRANK(A1:A10,B1) returns the rank of the value in B1 as a percentage of the values in A1 through A10 PERCENTRANK(C1:C5,20,2) returns the rank of the value 20 in the range C1 through C5 as a percentage with 2 significant digits PERCENTRANK(D1:D100,50) returns the rank of the value 50 in the range D1 through D100 as a percentage.
CHOOSEROWS
Returns a range of rows selected from a table based on their position in the table.
CHOOSEROWS(table, row_index1, [row_index2], ...)
•table: A range of cells that represents the table.
•row_index1: The index number of the first row you want to select.Must be a positive integer.
•[row_indexN]: Optional.Additional index numbers of rows you want to select.
Example: CHOOSEROWS(A1:D5, 1, 3) returns a range with rows 1 and 3 CHOOSEROWS(A1:D5, 2, 4) returns a range with rows 2 and 4 CHOOSEROWS(A1:E10, 1, 3, 5) returns a range with rows 1, 3, and 5.
ODDFPRICE
Calculates the price per $100 face value of a security with an odd first period.
ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•issue: The issue date of the security.
•first_coupon: The date of the first interest payment, which is an odd period.
•rate: The annual coupon rate of the security.
•yld: The annual yield of the security.
•redemption: The redemption value of the security per $100 face value.
•frequency: The number of coupon payments per year.
•[basis]: Optional.Argument that specifies the day count basis to use.If omitted, Excel uses the US (NASD) 30/360 basis.
Example: ODDFPRICE('2022-01-01', '2022-06-30', '2021-12-31', '2022-03-31', 0.08, 0.09, 100, 2).
NOW
Returns the current date and time as a date/time value.
NOW()
Example: NOW() returns the current date and time, for example: 3/15/2023 12:30:45 PM.
VDB
Calculates the depreciation of an asset for a specific period using the double-declining balance method or other methods that you specify.
VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
•cost: The initial cost of the asset.
•salvage: The value of the asset at the end of its useful life.Also called the salvage value or residual value of the asset.
•life: The number of periods over which the asset is depreciated (sometimes called the useful life of the asset).
•start_period: The period for which you want to calculate depreciation.The depreciation begins in this period.
•end_period: The period for which you want to stop calculating depreciation.Depreciation is calculated for all periods between Start_period and End_period.
•[factor]: Optional.The rate at which the balance declines during the useful life of the asset.If Factor is omitted, it defaults to 2 (the double-declining balance method).
•[no_switch]: Optional.A logical value that determines whether to switch to straight-line depreciation when depreciation is greater than the declining balance.If No_switch is TRUE or omitted, switching occurs.If No_switch is FALSE, switching does not occur.
Example: VDB(10000, 2000, 5, 1, 3, 1).
IMLOG2
Returns the base-2 logarithm of a complex number.
IMLOG2(inumber)
inumber: The complex number for which you want to calculate the base-2 logarithm.
Example: IMLOG2("1+i") returns "0,5+1,133i" IMLOG2("2+2i") returns "1,5+1,133i".
LENB
Returns the number of bytes in a text string.
LENB(text)
text: The text string whose byte length you want to find.
Example: LENB('apple') returns 5 LENB('banana') returns 6 LENB('cherry') returns 6.
YEAR
Returns the year corresponding to a given date.
YEAR(serial_number)
date: The date for which to extract the year.
Example: YEAR("2022-03-15") returns: 2022 YEAR("1995-12-31") returns: 1995 YEAR("2000-01-01") returns: 2000.
HYPGEOM.DIST
The HYPGEOM.DIST function is used to calculate the probability of a specified number of successes in a population sample, given the population size, number of successes in the population, and sample size.
HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)
•sample_s: The number of successes in the sample.
•number_sample: The size of the sample.
•population_s: The number of successes in the population.
•number_pop: The size of the population.
•cumulative: Optional.A logical value that determines the type of distribution to be used.If TRUE or omitted, returns the cumulative distribution function.If FALSE, returns the probability mass function.
Example: HYPGEOM.DIST(3, 5, 20, 8) HYPGEOM.DIST(2, 3, 6, 4, TRUE).
BINOM.DIST.RANGE
Calculates the probability of achieving a certain number of successes within a particular range of trials using the binomial distribution.
BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2])
•trials: The number of independent trials.
•probability_s: The probability of success on each trial.
•number_s: The smallest number of successes for which you want to calculate the probability.
•number_s2: Optional.The largest number of successes for which you want to calculate the probability.If omitted, number_s2 defaults to number_s.
Example: BINOM.DIST.RANGE(10, 0.4, 4).
MINA
Returns the smallest value in a list of supplied arguments, including numbers, arrays, and references.
MINA(value1,[value2],...)
•value1: The first value or range from which you want to find the minimum.
•[valueN]: Optional.Additional values or ranges from which you want to find the minimum.You can have up to 255 arguments.
Example: MINA(3, 5, 1) returns 1 MINA(A1:C10) returns the smallest value in the range A1 through C10 MINA([1, 2, 3, 4, 5]) returns 1.
CORREL
Returns the correlation coefficient between two data sets.
CORREL(array1, array2)
•array1: The first data set.An array or range of cells.
•array2: The second data set.An array or range of cells.
Example: CORREL(A2:A10, B2:B10) returns the correlation coefficient between the values in cells A2 through A10 and B2 through B10 CORREL(C5:C25, D5:D25) returns the correlation coefficient between the values in cells C5 through C25 and D5 through D25 CORREL(A1:A1000, B1:B1000) returns the correlation coefficient between the values in cells A1 through A1000 and B1 through B1000.
RANK
Returns the rank of a number in a list of numbers.The rank of a number is its size relative to other values in a list.If two or more values have the same rank, the highest rank is assigned to all of them and the next rank(s) is skipped.
RANK(number,ref,[order])
•number: The number whose rank is to be found.
•ref: An array or range of numbers representing the list of numbers.
•[order]: A number indicating how to rank the number: 0 (or omitted) for descending order, 1 for ascending order.
Example: RANK(2,A1:A10) RANK(90,A1:A10,1) RANK(B1,A1:A10).
Z.TEST
Returns the probability of a one-tailed, two-sample z-test.
Z.TEST(array1, [array2], [sigma])
•array1: The first set of data.
•array2: Optional.The second set of data.If omitted, array1 is used for both samples.
•sigma: Optional.The population (known) standard deviation.If omitted, the sample standard deviation is used instead.
Example: Z.TEST(A1:A5, B1:B5, 1).
EDATE
The EDATE function returns the date that is the specified number of months before or after the start date.
EDATE(start_date, months)
•start_date: The start date to use in the calculation.
•months: The number of months before or after the start date.A positive value for months yields a future date; a negative value yields a past date.
Example: EDATE('2022-01-15', 3) returns 44666 EDATE('2023-01-31', -5) returns 44804.
AGGREGATE
Returns an aggregate calculation on a range of cells, such as AVERAGE, COUNT, MAX, MIN, etc. with the option to ignore hidden rows and error values.
AGGREGATE(function_num, options, ref1, [ref2], ...)
•function_num: The number of the function to use for the aggregation.
•options: An option specifying whether to ignore hidden rows or error values.May be one of: 0 (default), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, or 12.
•ref1: The first range of cells to include in the aggregation.
•[refN]: Optional.Additional ranges of cells to include in the aggregation.
Example: AGGREGATE(1, 2, A1:A10) returns the average of visible cells in the range A1:A10 AGGREGATE(2, 6, A1:A10) returns the count of non-error cells in the range A1:A10 AGGREGATE(4, 3, A1:A10, B1:B10) returns the maximum of visible cells in the ranges A1:A10 and B1:B10.
NORM.INV
Returns the inverse of the normal cumulative distribution for a specified mean and standard deviation.
NORM.INV(probability, mean, standard_dev)
•probability: The probability associated with the normal distribution.
•mean: The arithmetic mean of the distribution.
•standard_dev: The standard deviation of the distribution.
Example: NORM.INV(0.75,80,6) returns approximately 84.046 NORM.INV(0.95,0,1) returns approximately 1,644.
IMAGINARY
The IMAGINARY function is used to return the imaginary coefficient of a complex number.
IMAGINARY(inumber)
inumber: The complex number for which to find the imaginary coefficient.
Example: IMAGINARY("3+4i") returns 4 IMAGINARY("-2-5i") returns -5 IMAGINARY("0+8i") returns 8.
RTD
Retrieves real-time data from a program that supports COM automation.
RTD(progID, server, topic1, [topic2], ...)
•string: The programmatic identifier (ProgID) of the application or object to retrieve data from.
•string: The name of the server on which the application is running.If the application is running on the local computer, this parameter can be an empty string or omitted.
•string: A topic used to identify the data you want to retrieve.
•string: (Optional) A second topic used to identify the data you want to retrieve.You can specify up to 253 additional topics.
Example: RTD("Excel.Application", "", "RTDServer.RTDFunctionName", "Argument1", "Argument2") returns the real-time data returned by the RTD function in the RTDServer add-in, with arguments "Argument1" and "Argument2".RTD("Word.Application", "", "SomeServer.Topic") returns the real-time data from the specified server and topic in Microsoft Word.
MODE.MULT
Returns an array of the most frequently occurring values in a range of data.
MODE.MULT(number1,[number2],...)
•number1: The first number you want to find the mode.
•numberN: The nth number you want to add to the array the mode will be calculated.
Example: MODE.MULT(1,2,2,3,4,4,4,5) returns [[2,4]] MODE.MULT(A1:A10) returns an array of the most frequently occurring values in the range A1 through A10 MODE.MULT(B1:B12,C1:C12,D1:D8) returns an array of the most frequently occurring values in the ranges B1:B12, C1:C12, and D1:D8.
PRODUCT
Returns the product of all values in a given range of cells.
PRODUCT(number1, [number2], ...)
•number1: The first number or range of numbers to multiply.
•[numberN]: Optional.Additional numbers or ranges of numbers to multiply.
Example: PRODUCT(A1:A5) returns the product of all values in the range A1 to A5 PRODUCT(2,4,6,8) returns 384, which is the product of 2*4*6*8 PRODUCT(B1:B10,C1:C10) returns the product of the values in columns B and C from rows 1 to 10.
HLOOKUP
Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify.
HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
•lookup_value: The value to search for in the first row of the table.Lookup_value can be a value or a reference.
•table_array: The table of information that HLOOKUP searches.Use a reference to a range or a range name.
•row_index_num: The row number within table_array from which to return a value.Row_index_num must be a positive integer.
•range_lookup: Optional.A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match.If range_lookup is TRUE or omitted, an approximate match is returned.In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned.If range_lookup is FALSE, HLOOKUP will only find an exact match.If one is not found, the function returns #N/A.
Example: HLOOKUP("B",A1:D4,3) HLOOKUP("C",A1:D4,2,FALSE) HLOOKUP(45,A1:D4,4).
TBILLPRICE
Calculates the price per $100 face value for a Treasury bill.
TBILLPRICE(settlement, maturity, discount)
•settlement: The Treasury bill's settlement date.
•maturity: The Treasury bill's maturity date.
@param discount: The Treasury bill's discount rate.
Example: TBILLPRICE("2022-01-01", "2022-06-30", 0.015) TBILLPRICE('2022-01-01', '2022-06-30', 0.012).
WRAPROWS
Wraps the text in a cell based on the number of rows specified.
WRAPROWS(vector, wrap_count, [pad_with])
•vector: The row or column vector to wrap.
•wrap_count: The number of values to wrap the vector after.Must be greater than 0.
•[pad_with]: Optional.A value to pad the vector with when wrapping.If not specified, the vector is wrapped without padding.
Example: WRAPROWS([1, 2, 3, 4, 5, 6], 3) returns [[1, 2, 3], [4, 5, 6]] WRAPROWS(['A', 'B', 'C', 'D'], 2, 'X') returns [['A', 'B'], ['C', 'D'], ['X', 'X']].
VAR.P
Calculates the variance based on an entire population.
VAR.P(number1, [number2], ...)
•number1: The first number or range of numbers that you want to calculate the variance of.At least one number is required.
•[numberN]: Optional.Additional numbers or ranges of numbers that you want to include in the calculation.You can include up to 255 numbers or ranges.
Example: VAR.P(1,2,3,4,5) returns 2 (because the variance of these numbers as a population is exactly 2) VAR.P(A1:A10) returns the variance of the values in cells A1 through A10 as a population VAR.P(B1:B10, C1:C10) returns the variance of the combined sets of values in cells B1 through B10 and C1 through C10 as a population.
NA
Returns the error value #N/A which means "value not available" or "no value exists".
NA()
Example: VLOOKUP("Nonexistent",A1:B10,2,FALSE) returns #N/A INDEX(A1:A5,MATCH(6,B1:B5,0)) returns #N/A because there is no match for 6 in range B1:B5 =IF(ISERROR(VLOOKUP("Nonexistent",A1:B10,2,FALSE)),NA(),VLOOKUP("Nonexistent",A1:B10,2,FALSE)) returns #N/A instead of #N/A error.
BIN2OCT
Converts a binary number to octal.
BIN2OCT(number, [places])
•number: The binary number to convert to octal.Must be a string of up to 10 characters containing only 0's and 1's.
•[places]: Optional.The number of characters to use.If omitted, places defaults to the minimum number necessary to represent the number.
Example: BIN2OCT(110101) returns 65 BIN2OCT(1000000000, 4) returns 7777777000 BIN2OCT(1111, 2) returns 17.
COT
Returns the cotangent of an angle provided in radians.
COT(x)
x: The angle for which you want to calculate the cotangent, in radians.
Example: COT(0) returns #DIV/0! COT(PI()/4) returns 1.
CONVERT
Converts a number from one measurement system to another.
CONVERT(number, from_unit, to_unit)
•number: The value to be converted.
•from_unit: The unit of measurement for the value.
•to_unit: The unit of measurement to which you want to convert the value.
Example: CONVERT(2.5, "ft", "m") returns 0.762 CONVERT(250, "lbm", "kg") returns 113.398 CONVERT(100, "mi", "km") returns 160.934
GAMMADIST
Calculates the gamma distribution, which is a continuous probability distribution that describes the shape of data that has positive skewness and a long right tail.
GAMMADIST(x, alpha, beta, cumulative)
•x: The value at which you want to evaluate the distribution.
•alpha: A parameter that specifies the shape of the distribution.Alpha must be greater than 0.
•beta: A parameter that specifies the scale of the distribution.Beta must be greater than 0.
•cumulative: Parameter that specifies whether to return the cumulative distribution function (CDF) or the probability density function (PDF).Enter TRUE to return the CDF, or FALSE to return the PDF.
Example: GAMMADIST(2,3,2,FALSE) GAMMADIST(10,5,1.5,TRUE) GAMMADIST(20,7,4,FALSE).
COUNTIFS
Counts the number of cells in a range that meet multiple specified criteria.
COUNTIFS(range1, criteria1, [range2], [criteria2], ...)
•range1: The first range of cells to be evaluated by the first criteria.
•criteria1: The criteria used to determine which cells to count in the first range.
•rangeN: Optional.Additional ranges of cells to be evaluated by additional criteria.
•criteriaN: Optional.Additional criteria used to determine which cells to count in additional ranges.
Example: COUNTIFS(A1:A10,">5") returns the number of cells in A1 through A10 that are greater than 5 COUNTIFS(B2:B5,"Bob",D2:D5,"Smith") returns the number of cells in ranges B2 through B5 and D2 through D5 that include "Bob" in column B and "Smith" in column D COUNTIFS(H1:H100,"<>0",I1:I100,">=10") returns the number of cells in column H from rows 1 through 100 that are not equal to 0 and the corresponding cells in column I that are greater than or equal to 10.
LEN
Returns the number of characters in a text string.
LEN(text)
text: The text string whose length you want to find.
Example: LEN('apple') returns 5 LEN('banana') returns 6 LEN('cherry') returns 6.
POISSON.DIST
Returns the Poisson distribution, which is a statistical measure that shows how many times an event is likely to occur within a specified period of time.
POISSON.DIST(x, mean, cumulative)
•x: The number of events.
•mean: The expected numeric value, or average, of the distribution.
•cumulative: A logical value that determines the form of the function.If cumulative is TRUE, POISSON.DIST returns the cumulative distribution function (CDF) value; if FALSE, it returns the probability mass function (PMF) value.
Example: POISSON.DIST(2,3,FALSE) returns 0.224, which is the probability of observing 2 events in a Poisson distribution with an expected value of 3 POISSON.DIST(2,3,TRUE) returns 0.423, which is the cumulative probability of observing 0, 1, or 2 events in a Poisson distribution with an expected value of 3.
LOGNORM.INV
Returns the inverse of the cumulative log-normal distribution for a specified probability, mean, and standard deviation.
LOGNORM.INV(probability, mean, standard_dev)
•probability: The probability for which you want to find the corresponding inverse of the cumulative log-normal distribution.
•mean: The mean of ln(x), where x is normally distributed.Must be positive.
•standard_dev: The standard deviation of ln(x), where x is normally distributed.Must be positive.
Example: LOGNORM.INV(0.5, 0, 1).
CHITEST
Compares the observed and expected frequencies of categories in a contingency table to determine whether there is a significant association between the variables.
CHITEST(actual_range, expected_range)
•actual_range: A range of cells containing the observed frequency counts.Each cell must contain a non-negative number.
•expected_range: A range of cells containing the expected frequency counts.Each cell must contain a non-negative number.
Example: CHITEST(A2:B4, C2:D4).
CHIINV
Returns the inverse of the one-tailed probability of the chi-squared distribution.
CHIINV(probability, degrees_freedom)
•probability: A probability associated with the chi-squared distribution.
•degrees_freedom: The number of degrees of freedom of the distribution.Must be a positive integer.
Example: CHIINV(0.05, 3) CHIINV(0.01, 6).
DATEVALUE
Converts a text string that represents a date to a serial number that Excel recognizes as a date.
DATEVALUE(date_text)
date_text: A text string that represents a date in a recognizable format.For example, "2022-01-01" or "January 1, 2022".
Example: DATEVALUE("2022-03-15") returns the serial number for March 15, 2022 DATEVALUE("2025-12-31") returns the serial number for December 31, 2025 DATEVALUE("2024-02-22") returns the serial number for February 22, 2024.
GAUSS
Returns the probability that a random variable follows a standard normal distribution.
GAUSS(z)
z: The value for which you want to calculate the probability of the standard normal distribution.
Example: GAUSS(-1.5) returns approximately -0.43319 GAUSS(2) returns approximately 0.477249868.
COUPDAYBS
Calculates the number of days from the beginning of the coupon period to the settlement date for a security that pays interest on a bond that has an odd first period.
COUPDAYBS(settlement, maturity, frequency, [basis])
•settlement: The settlement date.
•maturity: The maturity date.
•frequency: The number of coupon payments per year.
•[basis]: Optional.The type of day count basis to use.If omitted, assumed to be 0 (US or NASD) by default.
Example: COUPDAYBS("2022-01-01", "2022-08-30", 2) returns 121 COUPDAYBS("2022-01-01", "2022-08-30", 2, 1) returns 124.
TEXTJOIN
Joins together text strings with a specified delimiter.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
•delimiter: The character or string to separate the joined text values.
•ignore_empty: A logical value determining whether to ignore empty or blank cells in the resulting joined text.
•text1: The first text string to join.
•[text2]: Optional.Additional text strings to join.
Example: TEXTJOIN(", ", TRUE, "apple", "banana", "cherry") returns "apple, banana, cherry" TEXTJOIN("", FALSE, "Hello", "", "World") returns "HelloWorld" TEXTJOIN(" - ", TRUE, "John", "", "Smith") returns "John - Smith".
MATCH
Searches for a specified value in a range of cells, and returns the relative position of that value within the range.
MATCH(lookup_value, lookup_array, [match_type])
•lookup_value: The value that you want to match in the lookup_array.The lookup_value can be a number, text, logical value, or a reference to a cell containing any of these.
•lookup_array: The range of cells that you want to search for the lookup_value.The lookup_array must be one row or one column.
•match_type: Optional.A number that specifies how Excel matches the lookup_value with values in the lookup_array.1 = finds the largest value that is less than or equal to lookup_value (default), 0 = finds the first value that is exactly equal to lookup_value, -1 = finds the smallest value that is greater than or equal to lookup_value
Example: MATCH("apple", A1:A10, 0) searches for the exact text string "apple" in the range A1:A10 and returns the relative position of the first cell in which the text string was found MATCH(50, A1:A10) searches for the largest value in the range A1:A10 that is less than or equal to 50 and returns the relative position of the cell containing that value MATCH(TRUE, A1:A10) searches for the first logical value TRUE in the range A1:A10 and returns the relative position of the cell containing that value.
ATAN2
Returns the arctangent of the specified x- and y-coordinates, in radians.
ATAN2(x_num, y_num)
•x_num: The x-coordinate for which to calculate the arctangent.
•y_num: The y-coordinate for which to calculate the arctangent.
Example: ATAN2(0,1) returns 1.570796327 ATAN2(-1,-1) returns -2.35619449 ATAN2(3,4) returns 0.927295218.
STANDARDIZE
Returns a normalized value (z-score) given a value, a population mean, and a standard deviation.
STANDARDIZE(x, mean, standard_dev)
•x: The value to normalize.
•mean: The population mean to use as the basis of normalization.
•standard_dev: The standard deviation of the population to use as the basis of normalization.Must be positive.
Example: STANDARDIZE(80, 75, 5) returns 1 STANDARDIZE(70, 75, 5) returns -1 STANDARDIZE(A2, A3, A4) returns the z-score of the value in cell A2, based on the population mean in cell A3 and the population standard deviation in cell A4.
IMSQRT
Returns the square root of a complex number.
IMSQRT(inumber)
inumber: The complex number for which you want to calculate the square root.
Example: IMSQRT("1+i") returns "1.0987+0.4551i".
GETPIVOTDATA
Returns data stored in a PivotTable report.The data can be returned as a summary report or as a list report.
GETPIVOTDATA(data_field, pivot_table, [field1, item1],[field2,item2],...)
•data_field: The name of the data field that contains the data you want to retrieve.
•pivot_table: A reference to any cell within the PivotTable report that contains the data you want to retrieve.
•[field1]: Optional.The name of a field in the PivotTable report that corresponds to the field1 argument.
•[item1]: Optional.The item in the field specified in the field1 argument for which you want to retrieve data.
•[fieldN]: Optional.The name of a field in the PivotTable report that corresponds to the field2 argument.
•[itemN]: Optional.The item in the field specified in the field2 argument for which you want to retrieve data.
Example: GETPIVOTDATA("Sales",A3,"Region","West") GETPIVOTDATA("Quantity",B5,"Product","Apples","Region","East") GETPIVOTDATA("Amount",C11,"Region","South","Product","Oranges").
ATAN
Returns the arctangent of a number, in radians.
ATAN(number)
number: The number for which to calculate the arctangent.
Example: ATAN(0) returns 0 ATAN(1) returns 0.785398163 ATAN(-1) returns -0.785398163.
SIGN
Returns the sign of a number: 1 if the number is positive, -1 if negative, and 0 if zero.
SIGN(number)
number: The number for which to determine the sign.
Example: SIGN(42) returns 1 SIGN(-3.14) returns -1 SIGN(0) returns 0.
PDURATION
Calculates the duration of a security with periodic interest payments.
PDURATION(rate, pv, fv)
•rate: The annual interest rate of the security.
•pv: The present value of the security.
•fv: The future value, or final redemption value, of the security.
Example: PDURATION(0.08, 1000, 1100) returns approximately 1.238421134 PDURATION(0.05,500,600) returns approximately 3.736850652.
BESSELK
Returns the modified Bessel function of the second kind, K(x), for a given real or complex number x.
BESSELK(x, n)
•x: The real or complex number for which to calculate the modified Bessel function.
•n: The order of the Bessel function.Must be a nonnegative integer or a real number greater than -1.
Example: BESSELK(3, 2) BESSELK(1+2i, 0) BESSELK(-3.5, 4.2).
IMSEC
Returns the secant of a complex number.
IMSEC(inumber)
inumber: The complex number for which you want to calculate the secant.
Example: IMSEC("1+i") returns "5i" IMSEC("2+2i") returns "i" IMSEC("5-3i") returns "864i".
SLOPE
Returns the slope of a linear regression line that best fits a data set.
SLOPE(known_y's, known_x's)
•known_y's: An array or range of y-values that represent the dependent data.
•known_x's: An array or range of x-values that represent the independent data.Must be the same size as known_y's.
Example: SLOPE(A2:A10, B2:B10) returns the slope of the linear regression line that best fits the data points in the ranges A2:A10 and B2:B10 SLOPE([1,2,3,4],[5,6,7,8]) returns 1 SLOPE([10,20,30,40],[-5,-10,-15,-20]) returns -5.
DMIN
Returns the minimum value from a database table-like array based on specified criteria.
DMIN(database, field, criteria)
•database: The database range to use for the lookup.Should include headers for each column.
•field: The column header of the field you want to find the minimum value for.
•criteria: Criteria range or array to search for records that meet all of the specified conditions.
Example: DMIN(A1:C10,"Salary",[A1:A10,"John",C1:C10,"<1000"]) returns the lowest salary for John if their salary is less than 1000 DMIN(A1:C10,"Years",[B1:B10,"Sales",C1:C10,"Seattle"]) returns the lowest years worked for someone in Sales and located in Seattle DMIN(A1:E10,"Sales",[A1:A10,"Jane",B1:B10,">=3"]) returns the lowest sales total for Jane if they've been with the company for at least 3 years.
CHOOSECOLS
Returns a range of columns selected from a table based on their position in the table.
CHOOSECOLS(table, column_index1, [column_index2], ...)
•table: A range of cells that represents the table.
•column_index1: The index number of the first column you want to select.Must be a positive integer.
•[column_indexN]: Optional.Additional index numbers of columns you want to select.
Example: CHOOSECOLS(A1:D5, 1, 3) returns a range with columns A and C CHOOSECOLS(A1:D5, 2, 4) returns a range with columns B and D CHOOSECOLS(A1:E10, 1, 3, 5) returns a range with columns A, C, and E.
IPMT
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
IPMT(rate, per, nper, pv, [fv], [type])
•rate: The interest rate per period of the investment.
•per: The period for which you want to find the interest payment.
•nper: The total number of payment periods in the investment.
•pv: The present value, or the total amount that a series of future payments is worth now.
•fv: Optional.The future value, or a cash balance you want to attain after the last payment is made.If omitted, it defaults to 0.
•type: Optional.The timing of the payment.If omitted, it defaults to 0 (or at the end of the period).
Example: IPMT(0.1/12, 1, 24, 10000) returns the interest payment for the first month of a 2-year loan with monthly payments of $417 and a principal of $10,000 at a 10% annual interest rate IPMT(0.08/12, 6, 36, 20000, 5000) returns the interest payment for the sixth month of a 3-year loan with monthly payments of $686.53, a principal of $20,000, a 5,000 balloon payment and a 8% annual interest rate.
EXPON.DIST
Returns the exponential distribution for a specified set of parameters.
EXPON.DIST(x, lambda, cumulative)
•x: The value at which to evaluate the function.
•lambda: The parameter that determines the shape of the distribution.
•cumulative: A logical value that indicates whether to return the cumulative distribution function or the probability density function.
Example: EXPON.DIST(2,3,FALSE) EXPON.DIST(2,3,TRUE) EXPON.DIST(3,3,FALSE).
PI
Returns the mathematical constant pi, accurate to 15 digits.
PI()
Example: PI() returns 3.14159265358979, which is the value of the mathematical constant pi accurate to 15 digits 2*PI()*r returns the circumference of a circle with radius r.
SUBTOTAL
Returns a subtotal for a range using a specified aggregation function.
SUBTOTAL(function_num, ref1, [ref2], ...)
•function_num: The number that specifies the function to use for the subtotal.See documentation for a list of functions.
•ref1: The first range or reference to include in the subtotal calculation.
•refN: Optional.Additional ranges or references to include in the subtotal calculation.
Example: SUBTOTAL(9, B2:B10) returns the sum of the values in cells B2 through B10, ignoring any other subtotals in the range SUBTOTAL(101, B2:B10, D2:D10) returns the count of numeric values in both ranges B2:B10 and D2:D10, ignoring any other subtotals in the range SUBTOTAL(4, B2:B10, D2:D10) returns the maximum value in the ranges B2:B10 and D2:D10, ignoring any other subtotals in the range.
EXPAND
Expands a range of cells into individual cells.
EXPAND(array, rows, [columns], [pad_with])
•array: The array to be expanded.
•number: The number of rows for the expanded array.
•[columns]: Optional.The number of columns for the expanded array.If not specified, the array is expanded into a single column.
•[pad_with]: Optional.A value to pad the expanded array with when the dimensions are larger than the original array.If not specified, the additional cells remain empty.
Example: EXPAND(A1:A3, 4) returns [[A1], [A2], [A3], [#N/A]].
COUPDAYSNC
Calculates the number of days from the settlement date to the next coupon date for a security that pays periodic interest, but has an irregular first coupon period.
COUPDAYSNC(settlement, maturity, frequency, [basis])
•date: The settlement date.
•date: The maturity date.
•number: The number of coupon payments per year.
•number: Optional.The type of day count basis to use.If omitted, assumed to be 0 (US or NASD) by default.
Example: COUPDAYSNC("2022-01-01", "2022-06-30", 2).
LET
Allows you to define a calculation inside a formula and use the result of that calculation throughout the formula by referencing the name you give to the calculation.
LET(name1, name_value1, [calculation_or_name2, name_value2], ...)
•name: The name you want to give to the calculation.Must be a valid cell reference or range name.
•name_value1: The value of the first name.
•[calculation_or_nameN]: Optional: Additional names or expression for more calculations.
•[name_valueN]: Optional: Value for the nth name.
Example: LET(x, A1 + B1) + LET(y, A2 + B2) returns the sum of the values in cells A1 through B2, with the intermediate results stored in the names x and y, respectively LET(area, PI() * radius ^ 2) returns the area of a circle with radius defined elsewhere in the worksheet LET(total_sales, SUM(A1:A10)) / LET(num_salespeople, COUNTA(B1:B10)) returns the average sales per salesperson for the range A1:A10, with the intermediate results stored in the names total_sales and num_salespeople, respectively.
DATEDIF
Calculates the difference between two dates based on the specified interval.
DATEDIF(start_date, end_date, interval)
•start_date: The start date of the time period.
•end_date: The end date of the time period.
•interval: The type of time unit to use for the calculation: "Y" for full years, "M" for full months, and "D" for full days.Use "MD" to get the number of days between two dates that fall in different months, or "YM" to get the number of months between two dates that fall in different years.
Example: DATEDIF("2022-01-01","2022-03-15","d").
IFERROR
The IFERROR function is used to return a value if a formula results in error, and another value if it does not.
IFERROR(value, value_if_error)
•value: The value or formula to evaluate.If this produces an error, the value_if_error is returned.
•value_if_error: The value to return if the value argument produces an error.
Example: IFERROR(VLOOKUP(A1, B:C, 2, FALSE), "Not found") returns 'Not found' if the VLOOKUP function returns an error when searching for the value in cell A1 in column B. IFERROR(1/0, "Divide by zero error") returns the string 'Divide by zero error' since dividing by zero would produce an error.IFERROR(SUM(1,2,3), "Error") returns the sum of the values 1, 2, and 3 since the formula does not produce an error.
WORKDAY.INTL
Returns the date that is the indicated number of working days before or after a date (the starting date), excluding weekends and holidays.
WORKDAY.INTL(start_date, days, [weekend], [holidays])
•start_date: The starting date, expressed as a serial number or a date.
•days: The number of working days before or after the start date.A positive value for days generates a future date; a negative value generates a past date.
•weekend: Optional.A number or string representing which days of the week are weekend days and are not considered working days.The default is Saturday and Sunday ("0000000").For example, "1111111" represents a weekend of Friday and Saturday.You can also specify weekend using one or more of the following: 1 = Saturday 2 = Sunday 3 = Monday 4 = Tuesday 5 = Wednesday 6 = Thursday 7 = Friday.
•holidays: Optional.An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
Example: WORKDAY.INTL("2023-03-15", 5, "0000011") returns:"2023-03-22" WORKDAY.INTL("2023-03-15", -5, "0000011") returns: "2023-03-06" WORKDAY.INTL("2023-03-15", 10, "0000011", ["2023-03-20","2023-03-21"]) returns: "2023-04-04".
LARGE
Returns the k-th largest value in a range or array of numbers.
LARGE(array, k)
•array: An array or range of cells containing the values from which you want to find the k-th largest value.
•k: The position (from largest) in the array or range that you want to return.For example, 1 returns the largest value, 2 returns the second-largest value, and so on.
Example: LARGE(A1:A10, 1) returns the largest value in the range A1:A10 LARGE(B1:B10, 3) returns the third-largest value in the range B1:B10 LARGE([1,2,3,4,5], 2) returns the second-largest value in the array [1,2,3,4,5].
LOGNORMDIST
Returns the cumulative log-normal distribution for a specified value, mean, and standard deviation.
LOGNORMDIST(x, mean, standard_dev)
•x: The value for which you want to find the log-normal distribution.
•mean: The mean of ln(x), where x is normally distributed.Must be positive.
•standard_dev: The standard deviation of ln(x), where x is normally distributed.Must be positive.
Example: LOGNORMDIST(1, 0, 1) returns approximately 0.5, which is the probability that a normally distributed random variable with mean 0 and standard deviation 1 will be less than or equal to 1 when transformed to a log-normal distribution LOGNORMDIST(2, 1, 2) returns approximately 0.43903101, which is the probability that a normally distributed random variable with mean 1 and standard deviation 2 will be less than or equal to 2 when transformed to a log-normal distribution LOGNORMDIST(A1, B1, C1) returns the cumulative log-normal distribution for the value contained in cell A1 with mean B1 and standard deviation C1.
ACOT
Returns the arccotangent of a number, in radians.
ACOT(x)
x: The value for which to return the arccotangent.
Example: ACOT(1) returns 0.7853981634 ACOT(0) returns 1.570796327 ACOT(-1) returns 2.35619449.
HARMEAN
Returns the harmonic mean of a data set.
HARMEAN(number1, [number2],...)
•number1: The first number or range of numbers for which to calculate the harmonic mean.
•numberN: Optional.Additional numbers or ranges of numbers for which to calculate the harmonic mean, up to a maximum of 255 values.
Example: HARMEAN(5,10,20) returns 8.571428571 HARMEAN(A1:A5).
IFNA
The IFNA function is used to return a value if a formula returns the #N/A error, and another value if it does not.
IFNA(value, value_if_na)
•value: The value or formula to evaluate.If this produces the #N/A error, the value_if_na is returned.
•value_if_na: The value to return if the value argument produces the #N/A error.
Example: IFNA(VLOOKUP(A1, B:C, 2, FALSE), "Not found") returns 'Not found' if the VLOOKUP function results in the #N/A error when searching for the value in cell A1 in column B. IFNA(INDEX(D1:D10, MATCH(G1, A1:A10, 0)), "Not matched") returns 'Not matched' if the MATCH function results in the #N/A error because no match was found for the value in cell G1 in column A. IFNA(SUM(1,2,3), "Error") returns the sum of the values 1, 2, and 3 since the formula does not produce the #N/A error.
OCT2HEX
Converts a octal number to hexadecimal.
OCT2HEX(num, [places])
•num: The octal number you want to convert to hexadecimal.The number cannot contain more than 10 characters (digits).
•[places]: An optional argument that specifies the minimum number of characters (digits) to use for the hexadecimal number.If omitted, Excel uses the smallest number of characters required.
Example: OCT2HEX(63) returns 33 OCT2HEX(72) returns 3A OCT2HEX(777,4) returns 1FF.
WEEKDAY
Returns the day of the week corresponding to a given date.
WEEKDAY(serial_number, [return_type])
•serial_number: The serial number that represents the date whose day of the week you want to find.
•[return_type]: Optional.A number that determines the type of return value: 1 (or omitted): Numbers 1 (Sunday) through 7 (Saturday) 2: Numbers 1 (Monday) through 7 (Sunday) 3: Numbers 0 (Monday) through 6 (Sunday) 11: Numbers 1 (Monday) through 7 (Sunday) 12: Numbers 1 (Tuesday) through 7 (Monday) 13: Numbers 1 (Wednesday) through 7 (Tuesday) 14: Numbers 1 (Thursday) through 7 (Wednesday) 15: Numbers 1 (Friday) through 7 (Thursday) 16: Numbers 1 (Saturday) through 7 (Friday) 17: Numbers 1 (Sunday) through 7 (Saturday)
Example: WEEKDAY("2022-03-17") returns: 5 WEEKDAY("2023-01-01", 3) returns: 2 WEEKDAY("2023-02-22", 2) returns: 4.
ISNUMBER
Checks if a given value is a number and returns TRUE if the value is a number, and FALSE otherwise.
ISNUMBER(value)
value: The value that you want to test.
Example: ISNUMBER(123) returns TRUE because 123 is a number ISNUMBER("banana") returns FALSE because "banana" is not a number ISNUMBER(A1) returns TRUE if cell A1 contains a numerical value, and FALSE otherwise.
UPPER
Converts all lowercase letters in a text string to uppercase.
UPPER(text)
text: The text string that you want to convert to uppercase.If the text string contains numbers or special characters, they remain unchanged.
Example: UPPER("hello world") returns "HELLO WORLD", UPPER("123abc") returns "123ABC", UPPER("&$^%") returns "&$^%".
EVEN
The EVEN function rounds a number up to the nearest even integer.
EVEN(x)
x: The number to be rounded up to the nearest even integer.
Example: EVEN(3.2) returns 4 EVEN(5) returns 6 EVEN(A2) rounds the value in cell A2 up to the nearest even integer.
HOUR
Returns the hour component of a specific time, in numeric format.
HOUR(serial_number)
serial_number: The time from which to extract the hour component.Excel stores dates and times as serial numbers, for example "Jan 1, 2023 12:00 PM" is stored as 44420.5.
Example: HOUR("8:30:00 AM") returns 8 HOUR("15:45:00") returns 15 HOUR("23:59:01") returns 23.
GAMMALN.PRECISE
Calculates the natural logarithm of the gamma function, which is a generalization of the factorial function to real and complex numbers, using a more precise algorithm than GAMMALN.
GAMMALN.PRECISE(x)
x: The number for which you want to calculate the natural logarithm of the gamma function.
Example: GAMMALN.PRECISE(0.5) returns 0.5723649429 GAMMALN.PRECISE(2.5) returns 0.2846828705.
ACCRINTM
Returns the accrued interest for a security that pays interest at maturity.
ACCRINTM(issue, maturity, rate, par, [basis])
•issue: The security's issue date.
•maturity: The security's maturity date.
•rate: The security's annual interest rate.
•par: The security's face value.
•[basis]: Optional.The day count basis to use when calculating accrued interest.Defaults to 0 if not specified.
Example: ACCRINTM('1-Jan-2022', '1-Jan-2023', 0.05, 1000) ACCRINTM('1-Jan-2022', '1-Jan-2023', 0.05, 1000, 1) ACCRINTM('1-Jan-2022', '1-Jan-2023', 0.05, 1000, 2).
STDEVP
Calculates the standard deviation of an entire population, including text and logical values.
STDEVP(value1, [value2], ...)
•value1: The first value in the population.Can be a number, text, or logical value.
•valueN: Optional.Additional values in the population.Can be a number, text, or logical value
Example: STDEVP(2, "hello", TRUE, 8, "world") STDEVP(A1:A5).
ISNA
Checks if a given value is the error value #N/A and returns TRUE if the value is #N/A, and FALSE otherwise.
ISNA(value)
value: The value that you want to test.
Example: ISNA(#N/A) returns TRUE because #N/A is an error value ISNA(A1) returns TRUE if cell A1 contains the #N/A error value ISNA(B2) returns FALSE if cell B2 does not contain the #N/A error value.
RANK.AVG
Returns the rank of a specified value in a dataset, with ties receiving an average rank.The returned rank is based on the order of values in the array or range, with the largest value receiving a rank of 1.
RANK.AVG(number, ref, [order])
•number: The value for which to find the rank.
•array: An array or range of cells containing the values to rank.
•number: Optional.A numeric value specifying how to rank if two or more values are identical.0 or omitted ranks in descending order (largest to smallest), any other value ranks in ascending order (smallest to largest).
Example: RANK.AVG(B2,A2:A10) returns the rank of the value in cell B2 among the values in the range A2:A10, with ties receiving an average rank RANK.AVG(100,B2:B10,0) returns the rank of the value 100 among the values in the range B2:B10, with ties receiving an average rank and ranking in descending order (largest to smallest) RANK.AVG(C2,$D$2:$D$10,1) returns the rank of the value in cell C2 among the values in the range D2:D10, with ties receiving an average rank and ranking in ascending order (smallest to largest).
VAR.S
Calculates the variance based on a sample.Provides an unbiased estimate of the population variance.
VAR.S(number1, [number2], ...)
•number1: The first number or range of numbers that you want to calculate the variance of.At least one number is required.
•numberN: Additional numbers or ranges of numbers that you want to include in the calculation.You can include up to 255 numbers or ranges.
Example: VAR.S(1,2,3,4,5) returns 2.5 (because the variance of these numbers as a sample is 2.5) VAR.S(A1:A10) returns the variance of the values in cells A1 through A10 as a sample VAR.S(B1:B10, C1:C10) returns the variance of the combined sets of values in cells B1 through B10 and C1 through C10 as a sample.
RANDBETWEEN
Generates a random integer between two specified numbers (inclusive).The value of the number changes each time the worksheet is calculated or when the function is recalculated by pressing F9.
RANDBETWEEN(bottom, top)
•bottom: The smallest integer in the range of acceptable values.
•top: The largest integer in the range of acceptable values.
Example: RANDBETWEEN(1,10) returns a random integer between 1 and 10 (inclusive) IF(RANDBETWEEN(1,6)<=3, "Heads", "Tails") simulates the flip of a fair coin.
ATANH
Returns the inverse hyperbolic tangent of a number.
ATANH(x)
x: The number for which to calculate the inverse hyperbolic tangent.Must be between -1 and 1, exclusive.
Example: ATANH(0) ATANH(0.5) ATANH(-0.8).
TRIMMEAN
Returns the mean of the interior of a data set, based on a percentage you specify.
TRIMMEAN(array, percent)
•array: An array or range of numeric data.
•percent: The fractional number of data points to exclude from the calculation.Must be between 0 and 1.
Example: TRIMMEAN([10, 20, 30, 40, 50], 0.2) returns 35 (based on excluding the lowest and highest 20% of values) TRIMMEAN(A1:A100, 0.1) returns the mean of the middle 80% of values in cells A1 through A100 TRIMMEAN([1, 2, 3, 4, 5, 6], 0.5) returns 3.5.
SUMX2PY2
Returns the sum of the sum of squares of corresponding values in two arrays or ranges.
SUMX2PY2(array_x, array_y)
•array: An array or range that contains the first set of values.
•array: An array or range that contains the second set of values.
Example: SUMX2PY2([1, 2, 3], [4, 5, 6]) returns 91 (since the sum of 1^2 + 4^2, 2^2 + 5^2, and 3^2 + 6^2 is 17 + 29 + 45 = 91).
CUMPRINC
Returns the cumulative principal paid on a loan between two periods.
CUMPRINC(rate, nper, pv, start_period, end_period, type)
•rate: The interest rate per period.
•nper: The total number of payment periods in the loan.
•pv: The present value (or principal) of the loan.
•start_period: The first payment period for which to calculate the principal.Payment periods are numbered starting with 1.
•end_period: The last payment period for which to calculate the principal.
•type: Optional.The timing of the payments.Use 0 for payments due at the end of the period or 1 for payments due at the beginning of the period.Default is 0.
Example: CUMPRINC(0.06/12,24,-5000,1,12,0) returns the cumulative principal paid on a $5,000 loan with a 6% annual interest rate and a 2-year term, where the first payment is due in the first month CUMPRINC(0.07/12,36,-10000,13,24,1) returns the cumulative principal paid on a $10,000 loan with a 7% annual interest rate and a 3-year term, where payments are made at the beginning of each month and the principal is calculated between the 13th and 24th payments CUMPRINC(0.05/4,8,30000,1,4,0) returns the cumulative principal paid on a $30,000 loan with a 5% quarterly interest rate and a 2-year term, where payments are due at the end of each quarter and the principal is calculated between the 1st and 4th payments.
ABS
Returns the absolute value of a number.
ABS(x)
x: The number for which to return the absolute value.
Example: ABS(-7) returns 7 ABS(4.5) returns 4.5 ABS(0) returns 0.
CSCH
Calculates the hyperbolic cosecant of an angle given in radians.
CSCH(angle)
angle: The angle in radians for which to calculate the hyperbolic cosecant.
Example: CSCH(0) returns #DIV/0! CSCH(1) returns 0.850918128239322 CSCH(2) returns 0.275720565.
SINH
Returns the hyperbolic sine of a number.
SINH(x)
x: The number for which to calculate the hyperbolic sine.
Example: SINH(0) returns 0 SINH(1) returns approximately 1.1752 SINH(-2) returns approximately -3.62686.
TRUE
Returns the logical value TRUE.
TRUE()
Example: TRUE() returns TRUE.
ODDLPRICE
Calculates the price per $100 face value of a security with an odd last period.
ODDLPRICE(settlement, maturity, issue, last_interest, rate, yld, redemption, frequency, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•issue: The issue date of the security.
•last_interest: The date of the last interest payment, which is an odd period.
•rate: The annual coupon rate of the security.
•yld: The annual yield of the security.
•redemption: The redemption value of the security per $100 face value.
•frequency: The number of coupon payments per year.
•[basis]: Optional.Argument that specifies the day count basis to use.If omitted, Excel uses the US (NASD) 30/360 basis.
Example: ODDLPRICE('2022-01-01','2022-06-30','12/31/2021','3/31/2022',0.08,0.09,100,2) returns approximately 98.83 ODDLPRICE('2022-01-01','2022-06-30','12/31/2021','3/31/2022',0.05,0.06,100,4) returns approximately 97.31 ODDLPRICE('2022-01-01','2022-06-30','12/31/2021','3/31/2022',0.025,0.03,100,1,4) returns approximately 99.45.
DMAX
Returns the maximum value from a database table-like array based on specified criteria.
DMAX(database, field, criteria)
•database: The database range to use for the lookup.Should include headers for each column.
•field: The column header of the field you want to find the maximum value for.
•criteria: Criteria range or array to search for records that meet all of the specified conditions.
Example: DMAX(A1:C10,"Salary",[A1:A10,"John",C1:C10,">1000"]) returns the highest salary for John if their salary is greater than 1000 DMAX(A1:C10,"Years",[B1:B10,"Sales",C1:C10,"Seattle"]) returns the highest years worked for someone in Sales and located in Seattle DMAX(A1:E10,"Sales",[A1:A10,"Jane",B1:B10,">=3"]) returns the highest sales total for Jane if they've been with the company for at least 3 years.
TIMEVALUE
Converts a text string representing a time into a decimal value.
TIMEVALUE(time_text)
time_text: The text string representing the time to convert.Must be in a recognizable format, such as "5:45 PM" or "17:45".
Example: TIMEVALUE("9:30 AM") returns 0.3958333333 TIMEVALUE("16:15") returns 0.6770833333 TIMEVALUE("12:00:01 AM") returns 0.00001157408.
NEGBINOM.DIST
Returns the negative binomial distribution, which is the probability that there will be Number_f failures before the Number_s-th success, with Probability_s probability of a success.
NEGBINOM.DIST(number_f, number_s, probability_s, [cumulative])
•number_f: The number of failures.
•number_s: The threshold number of successes.
•probability_s: The probability of a success.
•[cumulative]: Optional.Logical value that specifies the form of the function.If cumulative is TRUE, NEGBINOM.DIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.If omitted, cumulative defaults to TRUE.
Example: NEGBINOM.DIST(10,15,0.25,FALSE) NEGBINOM.DIST(10,15,0.25,TRUE) NEGBINOM.DIST(3,5,0.4,FALSE).
SWITCH
Evaluates an expression against a list of cases and returns the result corresponding to the first matching case.
SWITCH(expression, value1, result1, [value2], [result2], ...[default])
•expression: The expression or value to evaluate.
•value1: The first value to compare with the expression.
•result1: The result to return if the expression matches the first value.
•valueN: Optional.Additional values to compare with the expression.
•resultN: Optional.Additional results to return if the expression matches the corresponding value.
•default: Optional.If specified, the default result to return if none of the values match the expression.If omitted and no match is found, #N/A error is returned.
Example: SWITCH(A2, 1, "one", 2, "two", 3, "three", "other") evaluates the value in cell A2 and returns "one" if it's 1, "two" if it's 2, "three" if it's 3, or "other" if it's something else.SWITCH(B2, "apples", 1, "pears", 2, "bananas", 3, 0) evaluates the value in cell B2 and returns 1 if it's "apples", 2 if it's "pears", 3 if it's "bananas", or 0 if it's something else.SWITCH(C2, 1, "yes", 0, "no", "unknown") evaluates the value in cell C2 and returns "yes" if it's 1, "no" if it's 0, or "unknown" if it's something else.
LOOKUP
Searches for a value in a range or array and returns a value in the same position from a second range or array.
LOOKUP(lookup_value, lookup_array, [result_array])
•lookup_value: The value to search for in the lookup_array.The lookup_value can be a number, text, logical value, or a reference to a cell containing any of these.
•lookup_array: The range of cells or an array that contains the values to be searched.The lookup_array must be one row or one column.
•r[esult_array]: Optional.The range of cells or an array that contains the values to be returned.If this argument is omitted, the function returns the corresponding value from the lookup_array.
Example: LOOKUP(2, A1:A10, B1:B10) searches for the value 2 in the range A1:A10 and returns the corresponding value from the same position in the range B1:B10 LOOKUP("apples", A1:A10, B1:B10) searches for the text "apples" in the range A1:A10 and returns the corresponding value from the same position in the range B1:B10 LOOKUP(0.5, [0,0.25,0.5,0.75,1], ["F","D","C","B","A"]) searches for the value 0.5 in the array [0,0.25,0.5,0.75,1] and returns the corresponding value "C" from the same position in the array ["F","D","C","B","A"].
ERFC.PRECISE
The ERFC.PRECISE function returns the complementary error function of a number, which is equal to 1 minus the error function of the same number.The result is accurate to approximately 15 digits.
ERFC.PRECISE(x)
x: The number for which to calculate the complementary error function.
Example: ERFC.PRECISE(0) ERFC.PRECISE(1.5) ERFC.PRECISE(A2) returns the complementary error function of the value in cell A2.
CEILING.PRECISE
Rounds a number up to the nearest multiple of a specified significance, using bankers' rounding (round half up).
CEILING.PRECISE(number, [significance])
•number: The number to be rounded up.
•[significance]: Optional.The multiple to which you want to round the number.If omitted, the default value is 1.
Example: CEILING.PRECISE(4.3, 1) returns 5 CEILING.PRECISE(7.8, 0.5) returns 8 CEILING.PRECISE(-2.5, -2) returns -2.
INDEX
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
INDEX(array, row_num, [column_num])
•array: An array of cells from which you want to retrieve data.
•row_num: The row number of the cell you want to retrieve.If row_num is omitted, column_num must also be omitted.
•column_num: Optional.The column number of the cell you want to retrieve.If column_num is omitted, it defaults to the same value as row_num.
Example: INDEX(A1:B2, 1, 2) returns the value in cell B1 INDEX(A1:B2, 2, 1) returns the value in cell A2 INDEX(A1:B2, 2) returns the values in the second row of the array.
DSTDEVP
The DSTDEVP function calculates the standard deviation of a population.
DSTDEVP(database, field, criteria)
•database: The range or array containing the dataset representing the population.
•field: The specific column or array within the database from which to calculate the standard deviation.
•criteria: The conditions or criteria determining which data points are included in the calculation.
Example: DSTDEVP(A2:A10, B2:B10, C2:C10) returns the standard deviation of the population based on the specified database, field, and criteria.
CEILING
Rounds a number up to the nearest multiple of a specified value.
CEILING(num, significance)
•num: The number to be rounded up.
•significance: Optional.The multiple to which you want to round the number.If omitted, the default value is 1.
Example: CEILING(4.3, 1) returns 5 CEILING(7.8, 0.5) returns 8 CEILING(-2.5, -2) returns -2.
CELL
Returns information about the formatting, location, or contents of a cell.
CELL(info_type, [reference])
•info_type: A text value that specifies what type of cell information you want to retrieve.It must be enclosed in quotation marks.
•[reference]: Optional.The cell location for which you want information.If omitted, the function uses the last cell that was changed.
Example: CELL("address", A1) returns $A$1 CELL("color", B3) returns 4 CELL("format", C5) returns "General".
DEGREES
Converts radians to degrees.
DEGREES(angle)
angle: The angle in radians you want to convert to degrees.
Example: DEGREES(PI()) returns 180 (degrees in a half circle) DEGREES(0.5236) returns 30 (degrees in an acute angle) DEGREES(-1.0472) returns -60 (degrees in an obtuse angle).
BITRSHIFT
Shifts bits to the right, padding with zeroes on the left.
BITRSHIFT(num, shift_amount)
•num: The number whose bits you want to shift.
•shift_amount: The number of bits to shift.A positive value shifts the bits to the right and a negative value shifts them to the left.
Example: BITRSHIFT(8, 2) returns 2 BITRSHIFT(16, -1) returns 32 BITRSHIFT(7, 3) returns 0.
FINDB
Returns the position of a string within another, but allowing for case sensitivity.Search starts from the right hand side of the string.
FIND(find_text, within_text, start_num)
•find_text: The text to find.
•within_text: The text to search within.
•start_num: Optional.The starting position of the search.If omitted, search starts at the end of the string.
Example: FINDB("o","Hello World") returns 8 FINDB("O","Hello World") returns 0 FINDB("l","Hello World",3) returns 3.
FIND
Returns the position of a string within another, but allowing for case sensitivity.Search starts from the left hand side of the string.
FIND(find_text, within_text, start_num)
•find_text: The text to find.
•within_text: The text to search within.
•start_num: Optional.The position to start the search.If omitted, search starts at the beginning of the string.
Example: FIND("o","Hello World") returns 5 FIND("O","Hello World") returns 0 FIND("l","Hello World",3) returns 3.
WRAPCOLS
Wraps the text in a cell based on the number of columns specified.
WRAPCOLS(vector, wrap_count, [pad_with])
•vector: The vector to wrap.
•wrap_count: The number of values per column.Must be greater than 0.
•[pad_with]: Optional.A value to pad the last column when wrapping.If not specified, the last column remains unaltered.
Example: WRAPCOLS([1, 2, 3, 4, 5, 6], 2) returns [[1, 2], [3, 4], [5, 6]] WRAPCOLS(['A', 'B', 'C', 'D'], 3, 'X') returns [['A', 'B', 'C'], ['D', 'X', 'X']] WRAPCOLS([4, 5, 6, 7, 8], 4) returns [[4, 5, 6, 7], [8]].
ISODD
Checks if a given number is odd and returns TRUE if the number is odd, and FALSE otherwise.
ISODD(x)
x: The number that you want to test.
Example: ISODD(3) returns TRUE because 3 is an odd number ISODD(-5) returns TRUE because -5 is an odd number ISODD(B3) returns TRUE if cell B3 contains an odd number.
SUMPRODUCT
Returns the sum of the products of corresponding ranges or arrays.
SUMPRODUCT(array1, [array2], ...)
•array1: The first array or range to multiply and add.
•arrayN: Optional.Additional arrays or ranges to multiply and add.
Example: SUMPRODUCT(A2:A6, B2:B6) returns the sum of the products of each pair of numbers in cells A2 through A6 and B2 through B6 SUMPRODUCT([1, 2, 3], [4, 5, 6]) returns 32 (the sum of the products of each pair of numbers in the two arrays) SUMPRODUCT(A2:C6, D2:F6) returns the sum of the products of each set of three corresponding numbers in the two ranges.
ISOWEEKNUM
Returns the ISO week number for a given date as an integer between 1 and 53, where week 1 is the week containing January 4.
ISOWEEKNUM(date)
date: The date for which you want to find the ISO week number.
Example: ISOWEEKNUM("2022-01-01") returns 52 because January 1, 2022 falls in the last week of 2021 according to the ISO standard ISOWEEKNUM(D3) returns the ISO week number for the date in cell D3 ISOWEEKNUM(TODAY()) returns the current ISO week number based on the system clock.
REPLACEB
Replaces a sequence of bytes in a string with another set of bytes.
REPLACEB(old_text, start_num, num_bytes, new_text)
•old_text: The text string that contains the bytes you want to replace.
•start_num: The position of the first byte you want to replace in old_text.
•num_bytes: The number of bytes you want to replace in old_text.
•new_text: The replacement set of bytes.
Example: REPLACEB("Hello World", 7, 5, "Universe") returns "Hello Universe" REPLACEB("John Doe", 2, 4, "Dane") returns "JDone" REPLACEB("123456789", 3, 3, "ABC") returns "12ABC789".
FLOOR
Rounds a number down to the nearest multiple of a specified factor.
FLOOR(num, significance)
•num: The number that you want to round down.
•significance: The multiple to which you want to round.Default is 1.
Example: FLOOR(3.7, 1) returns 3 FLOOR(6.8, 2) returns 6 FLOOR(-2.5, -1) returns -2.
SERIESSUM
Returns the sum of a power series based on the formula
SERIESSUM(x, n, m, coefficients)
•x: The value at which to evaluate the power series.
•n: The number of terms in the power series.
•m: An array or reference to a range of cells with the coefficients of the power series.
•coefficients: An array or reference to a range of cells with the exponents of the power series.The number of exponents should match the number of terms (n).
Example: SERIESSUM(2, 3, 3, [1, 2, 3]) SERIESSUM(0.5, 4, 4, [1, 2, 3, 4], [0, 2, 4, 6]) SERIESSUM(3, 2, 2, [10, -2], [0, 1]).
COUPNCD
Calculates the next coupon date after the settlement date for a security that pays periodic interest.
COUPNCD(settlement, maturity, frequency, [basis])
•settlement: The settlement date.
•maturity: The maturity date.
•frequency: The number of coupon payments per year.
•[basis]: Optional.The type of day count basis to use.If omitted, assumed to be 0 (US or NASD) by default.
Example: COUPNCD("2022-01-01","2022-06-30", 2) COUPNCD("2022-01-01","2022-06-30", 4, 1) COUPNCD("2022-01-01","2022-06-30", 2, 3).
COSH
Returns the hyperbolic cosine of a number.
COSH(x)
x: The number for which you want to calculate the hyperbolic cosine.
Example: COSH(0) returns 1 COSH(1) returns 1.543 COSH(-1) returns 1.543.
COUPNUM
Calculates the number of coupon payments between the settlement date and the maturity date for a security that pays periodic interest.
COUPNUM(settlement, maturity, frequency, [basis])
•settlement: The settlement date.
•maturity: The maturity date.
•frequency: The number of coupon payments per year.
•[basis]: Optional.The type of day count basis to use.If omitted, assumed to be 0 (US or NASD) by default.
Example: COUPNUM("2022-01-01", "2022-06-30", 2) COUPNUM("2022-01-01", "2022-06-30", 4, 1) COUPNUM("2022-01-01", "2022-06-30", 2, 3).
CUBEMEMBERPROPERTY
Returns the value of a member property from the cube.
CUBEMEMBERPROPERTY(connection, member_expression, property_name)
•connection: The name of the Excel data source connection.
•member_expression: A valid Multidimensional Expressions (MDX) expression that returns a member or tuple from the cube.
•property_name: The name of the member property to retrieve from the cube.
Example: CUBEMEMBERPROPERTY("Sales Data","[Date].[Calendar Year].&[2022]","Calendar Year") returns the calendar year for the 2022 member from the Date dimension in the Sales Data cube CUBEMEMBERPROPERTY("Budget Data","[Product].[All Products].[Bikes]","Color") returns the color property for the Bikes member from the Product dimension in the Budget Data cube CUBEMEMBERPROPERTY("Inventory Data","[Store].[Store Name].&[Seattle]","Region") returns the region property for the Seattle store member from the Store dimension in the Inventory Data cube.
JIS
Converts a number to text in the JIS (Japanese Industrial Standards) format.
JIS(number)
number: The number that you want to convert to text.
Example: JIS(123) JIS(456.789).
COMBIN
Returns the number of combinations for a given number of items.
COMBIN(number, number_chosen)
•number: The total number of items you have to choose from.
•number_chosen: The number of items you want to choose.
Example: COMBIN(5, 3) returns 10 COMBIN(8, 2) returns 28 COMBIN(10, 1) returns 10.
CONFIDENCE.T
Returns the confidence interval for a population mean, using a Student's t-distribution.
CONFIDENCE.T(alpha, standard_dev, size)
•alpha: The significance level used to compute the confidence level.Often represented as a decimal; for example, use 0.05 for a 95% confidence level.
•standard_dev: The population standard deviation for the data range and is assumed to be known.Must be greater than 0.
•size: The sample size.
Example: CONFIDENCE.T(0.05, 1.5, 30) CONFIDENCE.T(0.01, 2, 50) CONFIDENCE.T(0.1, 3, 20).
INDIRECT
Returns the reference specified by a text string.
INDIRECT(ref_text, [a1])
•ref_text: A cell reference, defined as a string.
•a1: Optional.A logical value that specifies what type of reference is contained in the cell ref_text.If omitted, it defaults to TRUE, meaning that A1-style references are used.
Example: INDIRECT("A1") returns 5, assuming cell A1 contains the value 5 INDIRECT(C4) returns the value in cell A1, assuming cell C4 contains the value "A1" SUM(INDIRECT(A3)) returns the sum of the range B6, assuming cell A3 contains the value "B6".
SKEW.P
Returns the population skewness of a distribution, which is a measure of asymmetry.
SKEW.P(number1, [number2], ...)
•number1: The first number or range of numbers for which to calculate the population skewness.
•numberN: Optional.Additional numbers or ranges of numbers for which to calculate the population skewness.Up to 255 arguments can be provided.
Example: SKEW.P(A1:A100) returns the population skewness of the values in cells A1 through A100 SKEW.P(1,2,3,4,5) returns 0 SKEW.P(-1,0,1,2) returns approximately 0.37139.
ROW
Returns the row number of a reference.
ROW(reference)
reference: The cell or range of cells for which you want to return the row number.
Example: ROW(A1) returns 1 ROW(B5:C10) returns 5.
AVERAGEIF
Returns the average (arithmetic mean) of all cells in a range that meet a specified criteria.
AVERAGEIF(range, criteria, [average_range])
•range: The range of cells to be evaluated by the criteria.
•criteria: The criteria used to determine which cells to include in the average.Can be a number, expression, cell reference, or text string.
•average_range: Optional.The range of cells to be averaged.If omitted, the range parameter is also used as the average range.
Example: AVERAGEIF(A1:A5, '>3') AVERAGEIF(B2:F2, 'red', B3:F5) AVERAGEIF(C2:C9, '<>0', D2:D9).
ACCRINT
Returns the accrued interest for a security that pays periodic interest.
ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
•issue: The security's issue date.
•first_interest: The security's first interest date.
•settlement: The security's settlement date.
•rate: The security's annual interest rate.
•par: The security's face value.
•frequency: The number of coupon payments per year.Must be one of: 1 (annual), 2 (semi-annual), 4 (quarterly).
•[basis]: Optional.The day count basis to use when calculating accrued interest.Defaults to 0 if not specified.
•[calc_method]: Optional.Whether to use the US (True) or European (False) method for calculating accrued interest.Defaults to False if not specified.
Example: ACCRINT("2022-01-01", "2022-07-01", "2023-01-01", 0.05, 1000, 2, 0, FALSE).
INTRATE
Returns the interest rate for a fully invested security.
INTRATE(settlement, maturity, investment, redemption, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•investment: The amount invested in the security.
•redemption: The amount the security is worth at maturity.
•[basis]: Optional.The day count basis to use.If omitted, it defaults to 0 (or actual/actual).
Example: INTRATE("1-Jan-2023", "1-Jan-2024", 100000, 120000) returns the annual interest rate for a security with a settlement date of January 1, 2023, maturity date of January 1, 2024, an initial investment of $100,000 and a redemption value of $120,000 INTRATE("1-Jan-2022", "1-Jan-2025", 150000, 200000, 1) returns the annual interest rate for a security with a settlement date of January 1, 2022, maturity date of January 1, 2025, an initial investment of $150,000, a redemption value of $200,000 and a 30/360 day count basis.
PHI
Returns the value of the standard normal cumulative distribution function (CDF) for a specified value.
PHI(z)
z: The value for which you want to calculate the standard normal CDF.The standard normal distribution has a mean of zero and a standard deviation of one.
Example: PHI(1.96) PHI(-2.5).
NEGBINOMDIST
Returns the negative binomial distribution, which is the probability that there will be k failures before the rth success, with probability p of success.
NEGBINOMDIST(number_f, number_s, probability_s)
•number_f: The number of failures.
•number_s: The threshold number of successes.
•probability_s: The probability of a success.
Example: NEGBINOMDIST(3,5,0.4) NEGBINOMDIST(10,15,0.25).
ACOS
Returns the arccosine of a number, in radians.
ACOS(x)
x: The cosine for which to return the arccosine.Must be between -1 and 1 inclusive.
Example: ACOS(-1) returns 3.141592654 ACOS(0) returns 1.570796327.
POISSON
Calculates the Poisson distribution probability mass function.
POISSON(x, mean, [cumulative])
•x: The number of events.
•mean: The expected numeric value, or average, of the distribution.
•[cumulative]: Optional.A logical value that determines the form of the function.If cumulative is TRUE, POISSON returns the cumulative distribution function (CDF) value; if FALSE, it returns the probability mass function (PMF) value.
Example: POISSON(2,3,FALSE) POISSON(A1,B1,C1).
CUBERANKEDMEMBER
Returns the nth ranked member from a set in a cube.
CUBERANKEDMEMBER(connection, set_expression, rank)
•connection: The name of the Excel data source connection.
•set_expression: A valid Multidimensional Expressions (MDX) expression that returns a set from the cube.
•rank: The rank of the member to be returned from the set.
Example: CUBERANKEDMEMBER("Sales Data","[Product].[Product Line].&[Mountain].[Products].Children",1) returns the highest-selling product in the Mountain product line for the Sales Data cube CUBERANKEDMEMBER("Budget Data","[Employee].[Employees By Department].[Sales]",[Measures].[Salary],2) returns the second-highest paid employee in the Sales department for the Budget Data cube CUBERANKEDMEMBER("Inventory Data","[Geography].[Geography].[Country].&[United States]",5) returns the fifth-ranked country in terms of inventory for the Inventory Data cube.
DSUM
The DSUM function adds the numbers in a column or database that meet specified criteria.
DSUM(database, field, criteria)
•database: The range of cells that makes up the database.
•field: The column label that contains the numbers to be added.
•criteria: The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
Example: DSUM(A1:C10,'Age',D1:D2) DSUM(A1:C10,'Salary',D1:D1).
RECEIVED
Returns the amount received at maturity for a fully invested security.
RECEIVED(settlement, maturity, investment, discount, [basis])
•settlement: The security's settlement date.
•maturity: The security's maturity date.
•investment: The amount invested in the security.
•discount: The security's discount rate.
•[basis]: The type of day count basis to use: 0 or omitted for US (NASD) 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365, 4 for European 30/360.
Example: RECEIVED("1/1/2023","6/1/2023",1000,10%) RECEIVED("1/1/2023","6/1/2023",1000,10%,1).
IMSUM
Returns the sum of complex numbers.
IMSUM(inumber1, [inumber2], ...)
•inumber1: The first complex number to add.
•inumberN: Optional.Additional complex numbers you want to include in the sum.
Example: IMSUM("1+i", "2+3i") returns "3+4i" IMSUM("3+4i", "-1+2i") returns "2+6i".
PERMUT
Returns the number of permutations for a given number of objects that can be selected from a set.
PERMUT(number, number_chosen)
•number: The total number of objects from which to choose.
•number_chosen: The number of objects to choose.If omitted, number_chosen is assumed to be equal to number.
Example: PERMUT(5,3) returns 60, which is the number of ways to select 3 objects from a set of 5 objects PERMUT(6) returns 720, which is the number of ways to arrange 6 objects in different order PERMUT(A1,B1) returns the number of permutations of B1 objects that can be chosen from a set of A1 objects.
STDEV.P
Calculates the standard deviation of an entire population.
STDEV.P(number1, [number2], ...)
•number1: The first number in the population.
•numberN: Optional.Additional numbers in the population.
Example: STDEV.P(2, 4, 6, 8, 10) returns approximately 2.83 STDEV.P(A1:A5) returns the exact standard deviation of the values in cells A1 through A5 as a population STDEV.P(A1, A3, A5) returns the exact standard deviation of the values in cells A1, A3, and A5 as a population.
ODD
Rounds a number up to the nearest odd integer.
ODD(x)
x: The number you want to round up to the nearest odd integer.
Example: ODD(1.5) returns 3 ODD(-2.5) returns -3 ODD(4) returns 5.
TYPE
Returns a number representing the data type of a value.
TYPE(value)
value: Value - the value for which you want to return the data type.Can be a reference to a cell containing a value.
Example: TYPE("text") returns 2 (because "text" is a text string) TYPE(123) returns 1 (because 123 is a number) TYPE(TRUE) returns 4 (because TRUE is a Boolean value).
RRI
Calculates the interest rate of a fully invested security.
RRI(nper, pv, fv)
•nper: The total number of payment periods for the investment.
•pv: The present value, or the lump-sum amount that a series of future payments is worth right now.
•fv: The future value, or a cash balance you want to attain after the last payment is made.
Example: RRI(12, -1000, 2000) RRI(36, -10000, 15000) RRI(24, -5000, 10000).
CONFIDENCE.NORM
Returns the confidence interval for a population mean, using a normal distribution.
CONFIDENCE.NORM(alpha, standard_dev, size)
•alpha: The significance level used to compute the confidence level.Often represented as a decimal; for example, use 0.05 for a 95% confidence level.
•standard_dev: The population standard deviation for the data range and is assumed to be known.Must be greater than 0.
•size: The sample size.
Example: CONFIDENCE.NORM(0.05, 1.5, 30) CONFIDENCE.NORM(0.01, 2, 50) CONFIDENCE.NORM(0.1, 3, 20).
BASE
Converts a number from decimal to binary, octal, or hexadecimal numbering systems.
BASE(number, radix, [min_length])
•number: The decimal number to be converted.
•radix: The base of the numbering system to convert to.Can be 2 (binary), 8 (octal), or 16 (hexadecimal).
•min_length: Optional.The minimum length of the result.If omitted, the result is not padded with leading zeros.
Example: BASE(255, 2) returns '11111111'.
NORM.S.INV
Returns the inverse of the standard normal cumulative distribution.
NORM.S.INV(probability)
probability: The probability associated with a normal distribution.
Example: NORM.S.INV(0.025) NORM.S.INV(0.97725) NORM.S.INV(0.398942).
TOROW
Returns the number of the row that corresponds to a specified cell reference.
TOROW(array, [ignore], [scan_by_column])
•array: The array you want to transform into a single row.
•[ignore]: A logical value that, when set to TRUE, ignores empty cells in the array.
•[scan_by_column]: A logical value that, when set to TRUE, scans the array by column; otherwise, it scans by row.
Example: TOROW("B12") TOROW("Z45") TOROW("AA99").
CSC
Calculates the cosecant of an angle given in radians.
CSC(angle)
angle: The angle in radians for which to calculate the cosecant.
Example: CSC(0) returns #DIV/0! CSC(1) returns 1.18839510577812.
INT
Returns the integer part of a number by rounding down to the nearest integer.
INT(x)
x: The number for which you want to retrieve the integer part.
Example: INT(3.14) returns 3 INT(-3.14) returns -4 INT(5) returns 5.
CHISQ.INV.RT
Returns the inverse of the one-tailed probability of the chi-squared distribution.
CHISQ.INV.RT(probability, degrees_freedom)
•probability: A probability associated with the chi-squared distribution.
•degrees_freedom: The number of degrees of freedom of the distribution.Must be a positive integer.
Example: CHISQ.INV.RT(0.05, 3) CHISQ.INV.RT(0.01, 6) CHISQ.INV.RT(0.001, 10).
SEARCHB
Returns the starting position of a text string within another text string, starting from the leftmost character.If the text is not found, returns #VALUE!.This function is case-sensitive and considers double-byte characters as separate characters.
SEARCHB(find_text,within_text,[start_num])
•text: The text to find.
•text: The text to search within.
•number: Optional.The starting position of the search.Default is 1.
Example: SEARCHB("日本","Japan is 日本 in Japanese.") returns 10 SEARCHB("日","Japan is 日本 in Japanese.") returns #VALUE! SEARCHB("is","The sky is blue.",6) returns 9.
DEC2BIN
Converts a decimal number to binary format.
DEC2BIN(number, [places])
•number: The decimal number you want to convert to binary.
•[places]: Optional.The minimum number of characters to use for the binary number.If omitted or zero, the function will use as many characters as necessary.
Example: DEC2BIN(255) returns 11111111 DEC2BIN(42,8) returns 00101010.
ISTEXT
Checks if a given value is text and returns TRUE if the value is text, and FALSE otherwise.
ISTEXT(value)
value: The value that you want to test.
Example: ISTEXT("banana") returns TRUE because "banana" is a text value ISTEXT(123) returns FALSE because 123 is not a text value ISTEXT(A1) returns TRUE if cell A1 contains a text value, and FALSE otherwise.
FORECAST.ETS
Calculates or predicts a future value based on existing (historical) values by using the Exponential Smoothing (ETS) algorithm.
FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
•target_date: The date for which you want to predict a value.
•values: An array of numbers representing the historical data.
•timeline: An array of dates or numbers representing the timeline of the historical data.Should have the same number of elements as the 'values' parameter.
•[seasonality]: Optional.An integer specifying the number of periods in a complete season.Default is automatically detected.
•[data_completion]: Optional.A logical value that specifies whether to fill in missing data points.Default is FALSE (missing data points are not filled).
•[aggregation]: Optional.A string that specifies how to aggregate data for each period.Default is "AVERAGE".Other options include "SUM", "COUNT", and "MIN".
Example: FORECAST.ETS("4/1/2023", B2:B8, A2:A8) FORECAST.ETS("5/1/2023", B2:B8, A2:A8) FORECAST.ETS("6/1/2023", B2:B8, A2:A8).
ARABIC
Converts a Roman numeral to an Arabic numeral.
ARABIC(text)
string: The Roman numeral to convert to an Arabic numeral.
Example: ARABIC('XII') returns 12 ARABIC('MCMXCIV') returns 1994.
XIRR
Returns the internal rate of return for a series of cash flows, taking into account both the time value of money and the irregular intervals between cash flows.
XIRR(values, dates, [guess])
•values: An array or range of cells representing the series of cash flows.
•dates: An array or range of cells representing the dates corresponding to each cash flow.
•[guess]: Optional.A number that you guess is close to your result.If omitted, it defaults to 0.1 (10%).
Example: XIRR([-1000, 250, 250, 250, 250, 250], ["2022-01-01","2023-01-01","2024-01-01","2025-01-01","2026-01-01","2027-01-01"]) XIRR([-5000, 1000, 2000, 3000], ["2019-01-01","2020-01-01","2021-01-01","2022-01-01"], 0.1) XIRR([10000, -1500, -1500, -1500, -1500], ["2021-01-01","2022-01-01","2023-01-01","2024-01-01","2025-01-01"], 0.1).
WEIBULL.DIST
Returns the Weibull distribution, which is often used in reliability analysis.
WEIBULL.DIST(x, alpha, beta, cumulative)
•x: The value at which to evaluate the distribution.
•alpha: A parameter to set the shape of the distribution.
•beta: A parameter to set the scale of the distribution.
•[cumulative]: A logical value that indicates which form of the Weibull distribution to use: TRUE or omitted: The cumulative distribution function FALSE: The probability density function
Example: WEIBULL.DIST(60, 50, 10, TRUE) WEIBULL.DIST(100, 50, 10, FALSE) WEIBULL.DIST(A2, B2, C2, TRUE).
CUBEVALUE
Returns the value of a cell in a cube, as calculated by a Multidimensional Expressions (MDX) query.
CUBEVALUE(connection, mdx_expression)
•connection: The name of the Excel data source connection.
•mdx_expression: A valid Multidimensional Expressions (MDX) expression that returns a single cell from the cube.
Example: CUBEVALUE("Sales Data","([Date].[Calendar Year].&[2022],[Product].[All Products].[Bikes],[Measures].[Revenue])") returns the revenue amount for the Bikes product in the year 2022 from the Sales Data cube CUBEVALUE("Budget Data","([Department].[All Departments].[Sales],[Employee].[All Employees].[Jane],[Measures].[Salary])") returns the salary amount for Jane in the Sales department from the Budget Data cube CUBEVALUE("Inventory Data","([Store].[Store Name].[Seattle],[Product].[All Products].[Accessories],[Measures].[Inventory Count])") returns the inventory count for Accessories in the Seattle store from the Inventory Data cube.
HEX2OCT
Converts a hexadecimal number to octal.
HEX2OCT(number,[places])
•number: The hexadecimal number you want to convert to octal.
•[places]: Optional.The number of characters to use.Places must be a multiple of 3.If places is omitted, HEX2OCT uses the minimum number of characters necessary.
Example: HEX2OCT("B",2) HEX2OCT("FF",4) HEX2OCT("1AC").
GAMMAINV
Calculates the inverse of the gamma cumulative distribution function for a specified probability and parameter values.
GAMMAINV(probability, alpha, beta)
•probability: The probability that corresponds to the gamma distribution.
•alpha: A parameter that specifies the shape of the distribution.Alpha must be greater than 0.
•beta: A parameter that specifies the scale of the distribution.Beta must be greater than 0.
Example: GAMMAINV(0.3,3,2) GAMMAINV(0.7,5,1.5) GAMMAINV(0.9,7,4).
ASC
Convert full-width characters to half-width characters.
ASC(text)
string: The text containing full-width characters to be converted to half-width characters.
Example: ASC("ＡＰＰＬＥ") returns "APPLE" ASC("ｃｏｎｖｅｒｓｉｏｎ") returns 'conversion'.
CUBEMEMBER
Returns a member or tuple from a cube.
CUBEMEMBER(connection, member_expression)
•connection: The name of the Excel data source connection.
•member_expression: A valid Multidimensional Expressions (MDX) expression that returns a member or tuple from the cube.
Example: CUBEMEMBER("Sales Data","[Date].[Calendar Year].&[2022]") returns the calendar year 2022 member from the Date dimension in the Sales Data cube CUBEMEMBER("Budget Data","[Product].[All Products].[Bikes]") returns the Bikes member from the Product dimension in the Budget Data cube CUBEMEMBER("Inventory Data","[Store].[Store Name].&[Seattle]") returns the Seattle store member from the Store dimension in the Inventory Data cube.
RIGHTB
Returns a specified number of bytes from the end of a text string.
RIGHTB(text, number_of_bytes)
•text: The text string containing the bytes to return.
•number_of_bytes: The number of bytes to return from the end of the text string.
Example: RIGHTB("Hello World", 5) returns "World" RIGHTB("Excel Functions", 9) returns "Functions" RIGHTB("12345", 2) returns "45".
ISBLANK
Returns TRUE if a specified cell is empty, and FALSE otherwise.
ISBLANK(value)
value: The value or reference to the cell that you want to test.
Example: ISBLANK(A1) returns TRUE if cell A1 is empty ISBLANK(B2) returns FALSE if cell B2 contains any value (including a formula) ISBLANK(C3+D3) returns TRUE if the formula in C3+D3 evaluates to an empty cell.
NORMSINV
Returns the inverse of the standard normal cumulative distribution.
NORMSINV(probability)
probability: The probability associated with a normal distribution.
Example: NORMSINV(0.025) NORMSINV(0.97725) NORMSINV(0.398942).
ISERR
Returns TRUE if a value is any error value except #N/A, and FALSE otherwise.
ISERR(value)
value: The value or reference to the cell that you want to test.
Example: ISERR(A1) returns TRUE if cell A1 contains any error value except #N/A ISERR(B2) returns TRUE if cell B2 contains a #DIV/0! error ISERR(C3+D3) returns FALSE if the formula in C3+D3 evaluates to an error value other than #N/A.
NPER
Calculates the number of payment periods for an investment based on a constant interest rate and periodic payments.
NPER(rate, payment, present_value, [future_value], [type])
•rate: The interest rate per period.
•payment: The payment made each period and cannot change over the life of the investment.
•present_value: The present value, or the lump-sum amount that a series of future payments is worth right now.
•[future_value]: Optional.The future value, or a cash balance one wants to attain after the last payment is made.If omitted, the future value is assumed to be 0 (the loan will be paid off).
•[type]: Optional.Argument that identifies when payments are due.Set it to 0 or omit it to indicate that payments are due at the end of the period.Set it to 1 to indicate that payments are due at the beginning of the period.
Example: NPER(0.06/12,-100,4000) NPER(0.09/4,-5000,13000,10000) NPER(0.1/12,-350,5000,0,1).
AREAS
Returns the number of areas in a reference.
AREAS(reference)
reference: A reference to the range for which to count the areas.
Example: AREAS(A1:A10) returns 1 AREAS(A1:C3, E1:F3) returns 2 AREAS(A1:C3, A5:C7, F1:H3) returns 3.
RANK.EQ
Returns the rank of a number in a list of numbers.The rank of a number is its size relative to other values in a list.If more than one value has the same rank, the average rank is returned.
RANK.EQ(number,ref,[order])
•number: The number whose rank is to be found.
•ref: An array or range of numbers representing the list of numbers.
•[order]: Optional.A number indicating how to rank the number: 0 (or omitted) for descending order, 1 for ascending order.
Example: RANK.EQ(2,A1:A10) RANK.EQ(90,A1:A10,1) RANK.EQ(B1,A1:A10).
HSTACK
The HSTACK function is used to horizontally stack values from multiple arrays into a single array.
HSTACK(array1, [array2], [array3], ...)
•array1: An array or range of cells containing the values to be stacked.
•arrayN: Optional.An additional array or range of cells containing the values to be stacked.
Example: HSTACK([1,2,3],[4,5,6]) returns [[1,2,3,4,5,6]] HSTACK([1,2,3],[4,5,6],[7,8,9]) returns [1,2,3,4,5,6,7,8,9] HSTACK(A1:A3,B1:B3,C1:C3) where A1:C3 contains the values 1-9, returns [1,2,3,4,5,6,7,8,9].
IMLN
Returns the natural logarithm of a complex number.
IMLN(inumber)
inumber: The complex number for which you want to calculate the natural logarithm.
Example: IMLN("1+i") IMLN("2+2i") IMLN("5-3i").
FLOOR.PRECISE
Rounds a number down to the nearest integer or to the nearest multiple of a specified factor, based on a specified significance.
FLOOR.PRECISE(number, [significance])
•number: The number that you want to round down.
•[significance]: Optional.The significance of the rounding.Default is 1.
Example: FLOOR.PRECISE(24.3) FLOOR.PRECISE(6.8, 2) FLOOR.PRECISE(-8.1, 0.1).
BINOMDIST
Returns the individual term binomial distribution probability.
BINOMDIST(number_s, trials, probability_s, cumulative)
•number_s: The number of successes in trials.
•trials: The number of independent trials.
•probability_s: The probability of success on each trial.
•cumulative: A logical value that specifies whether to return the cumulative distribution or the individual term binomial distribution probability.If omitted, cumulative defaults to TRUE (cumulative distribution).
Example: BINOMDIST(3, 5, 0.5, FALSE) BINOMDIST(2, 10, 0.1, TRUE) BINOMDIST(4, 8, 0.7, FALSE).
CHISQ.DIST.RT
Returns the one-tailed probability of the chi-squared distribution.
CHISQ.DIST.RT(x, degrees_freedom)
•x: The value at which you want to evaluate the distribution.
•degrees_freedom: The number of degrees of freedom of the distribution.Must be a positive integer.
Example: CHISQ.DIST.RT(2.5, 4) CHISQ.DIST.RT(6, 8) CHISQ.DIST.RT(1, 1).
QUARTILE.EXC
Calculates the exclusive quartile of a dataset, which is a value that separates the lowest 25% from the highest 75% of values.This function uses a slightly different calculation than the QUARTILE function.
QUARTILE.EXC(array, quart)
•array: The array or range of data for which to determine the quartile.
•quart: The quartile to return.1 returns the minimum value, 2 returns the value at the first quartile, 3 returns the value at the median (second quartile), and 4 returns the value at the third quartile.
Example: QUARTILE.EXC(A1:A10,2) returns the value at the first quartile (the value separating the lowest 25% of values from the highest 75%) for the range A1:A10 using the exclusive method QUARTILE.EXC(B2:B20,3) returns the value at the median (the second quartile) for the range B2:B20 using the exclusive method QUARTILE.EXC(C1:C100,4) returns the value at the third quartile (the value separating the lowest 75% of values from the highest 25%) for the range C1:C100 using the exclusive method.
RANDARRAY
Generates an array of random numbers within a specified range or format.
RANDARRAY([rows], [columns], [min], [max], [integer])
•[rows]: Optional.The number of rows in the output array.Must be greater than or equal to 1.If not specified, defaults to 1.
•[columns]: Optional.The number of columns in the output array.Must be greater than or equal to 1.If not specified, defaults to 1.
•[min]: Optional.The minimum value for random numbers.If not specified, defaults to 0.
•[max]: Optional.The maximum value for random numbers.If not specified, defaults to 1.
•[integer]: Optional.A logical value specifying whether the generated numbers should be integers (TRUE) or decimals (FALSE).If not specified, defaults to FALSE.
Example: RANDARRAY(3, 2, 10, 20, FALSE) returns a 3x2 array of random decimal numbers between 10 and 20 SUM(RANDARRAY(1000, 1, 1, 6, TRUE)) returns the sum of 1000 randomly generated integers between 1 and 6 RANDARRAY(A1, B1, -1, 1, TRUE) returns an array with dimensions specified by the values in cells A1 and B1 containing random integers between -1 and 1.
VAR
Estimates the variance based on a sample.
VAR(number1, [number2], ...)
•number1: The first number or range of numbers that you want to calculate the variance of.At least one number is required.
•numberN: Optional.Additional numbers or ranges of numbers that you want to include in the calculation.You can include up to 255 numbers or ranges.
Example: VAR(1,2,3,4,5) VAR(A1:A10) returns the variance of the values in cells A1 through A10 VAR(B1:B10, C1:C10) returns the variance of the combined sets of values in cells B1 through B10 and C1 through C10.
T.DIST
Returns the left-tailed Student's t-distribution.
T.DIST(x, degrees_freedom, cumulative)
•x: The numeric value at which to evaluate the distribution.
•degrees_freedom: The number of degrees of freedom.
•cumulative: A logical value that determines the form of the function.If TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function.
Example: T.DIST(1.5, 10, TRUE) T.DIST(2, 8, FALSE) T.DIST(1, 5, FALSE).
T.DIST.RT
Returns the right-tailed Student's t-distribution.
T.DIST.RT(x, degrees_freedom)
•x: The numeric value at which to evaluate the distribution.
•degrees_freedom: The number of degrees of freedom.
Example: T.DIST.RT(1.5, 10) T.DIST.RT(2, 8) T.DIST.RT(1, 5).
FORMULATEXT
Returns the formula in a cell as text.
FORMULATEXT(reference)
reference: A reference to the cell you want to get the formula of.
Example: FORMULATEXT(A2) returns "=SUM(B2:B5)" in case that is the value of A2 FORMULATEXT(C3) returns "=IF(A1>B1,A1-B1,B1-A1)" in case that is the value of C3 FORMULATEXT(D4) returns "=AVERAGE(A1:A10)" in case that is the value of D4.
MAXA
Returns the largest value in a range of cells or an array.
MAXA(number1, [number2], ...)
•number1: The first number, cell reference, or range of numbers that you want to find the maximum value for.
•numberN: Optional.Additional numbers, cell references, or ranges of numbers that you want to find the maximum value for.You can specify up to 255 arguments.
Example: MAXA(A1:A10) returns the largest value in the range A1:A10 MAXA(B1:C5) returns the largest value in the array B1:C5 MAXA(10, 20, 30) returns the number 30, which is the largest value among the arguments.
OCT2DEC
Converts a octal number to decimal.
OCT2DEC(number)
number: The octal number you want to convert to decimal.The number cannot contain more than 10 characters (digits).
Example: OCT2DEC(63) returns 51 OCT2DEC(72) returns 58 OCT2DEC(777) returns 511.
LOG
Returns the logarithm of a number to a specified base.
LOG(number, [base])
•number: The positive real number for which you want to find the logarithm.
•[base]: Optional.The base of the logarithm you want to find.If omitted, base 10 is used.
Example: LOG(1) returns 0 because log base 10 of 1 is 0 LOG(100, 10) returns 2 because log base 10 of 100 is 2 LOG(A1, 2) returns the logarithm base 2 of the value contained in cell A1.
T
Returns the one-tailed probability of a Student's t-distribution where the probability density function (pdf) is defined by the degrees of freedom.
T(value)
value: The value to check if it's text.
Example: T("Hello") returns "Hello" T(123) returns "" T(A1) returns the text value if A1 contains text, or an empty string if it doesn't.
STDEV.S
Estimates the standard deviation of a population based on a sample of numbers.
STDEV.S(number1, [number2], ...)
•number1: The first number in the sample.
•numberN: Optional.Additional numbers in the sample.
Example: STDEV.S(2, 4, 6, 8, 10) STDEV.S(A1:A5) returns the estimated standard deviation of the values in cells A1 through A5 as a sample STDEV.S(A1, A3, A5) returns the estimated standard deviation of the values in cells A1, A3, and A5 as a sample.
STDEVPA
Estimates the standard deviation of an entire population, including text and logical values.
STDEVPA(value1, [value2], ...)
•value1: The first value in the population.Can be a number, text, or logical value.
•valueN: Optional.Additional values in the population.Can be a number, text, or logical value.
Example: STDEVPA(2, "hello", TRUE, 8, "world") STDEVPA(A1:A5) returns the estimated standard deviation of the values in cells A1 through A5 as a population, including any text or logical values STDEVPA(A1, B1, C1) returns the estimated standard deviation of the values in cells A1, B1, and C1 as a population, including any text or logical values.
TRANSPOSE
Returns a transposed range of cells.
TRANSPOSE(array)
array: A range of cells to transpose
Example: TRANSPOSE(A1:C3) returns a transposed range of cells from A1 to C3.
BIN2DEC
Converts a binary number to decimal.
BIN2DEC(number)
number: The binary number to convert to decimal.Must be a string of up to 10 characters containing only 0's and 1's.
Example: BIN2DEC('110101') returns 53.
LOG10
Returns the base-10 logarithm of a number.
LOG10(number)
number: The positive real number for which you want to find the base-10 logarithm.
Example: LOG10(1) returns 0 because log base 10 of 1 is 0 LOG10(100) returns 2 because log base 10 of 100 is 2 LOG10(A1) returns the base-10 logarithm of the value contained in cell A1.
EXPONDIST
Returns the exponential distribution for a specified set of parameters.
EXPONDIST(x, lambda, cumulative)
•x: The value at which to evaluate the function.
•lambda: The parameter that determines the shape of the distribution.
•cumulative: A logical value that indicates whether to return the cumulative distribution function or the probability density function.
Example: EXPONDIST(2,3,FALSE) EXPONDIST(2,3,TRUE) EXPONDIST(3,3,FALSE).
MONTH
Returns the month of a date represented by a serial number.The month is given as an integer, ranging from 1 (January) to 12 (December).
MONTH(serial_number)
serial_number: The date for which you want to determine the month.Excel stores dates as sequential serial numbers so that they can be used in calculations.
Example: MONTH(DATE(2022,3,15)) returns 3 MONTH(A1) where cell A1 contains the date 5/25/2023 returns 5 MONTH(TODAY()) returns the current month.
GAMMA.INV
Calculates the inverse of the gamma cumulative distribution function for a specified probability and parameter values.
GAMMA.INV(probability, alpha, beta)
•probability: The probability that corresponds to the gamma distribution.
•alpha: A parameter that specifies the shape of the distribution.Alpha must be greater than 0.
•beta: A parameter that specifies the scale of the distribution.Beta must be greater than 0.
Example: GAMMA.INV(0.3,3,2) GAMMA.INV(0.7,5,1.5) GAMMA.INV(0.9,7,4).
BYROW
Returns a reference to a range that is a specified number of rows above or below a starting range.
BYROW(array, [function])
•array: The array containing the rows to process.
•[function]: Optional: The LAMBDA function to apply to each row of the array.If not specified, the function is assumed to be already defined.
Example: BYROW(A1:B3, MyLambdaFunction) returns an array of results by applying MyLambdaFunction to each row in A1:B3.BYROW(C4:F8) returns an array of results by applying a pre-defined LAMBDA function to each row in C4:F8.BYROW(G2:H5, AnotherLambda) returns an array of results by applying AnotherLambda to each row in G2:H5.
PHONETIC
Returns the phonetic (furigana) representation of a text string.
PHONETIC(text, [format_text])
•string: The text string for which you want to generate the phonetic (furigana) representation.
•string: Optional.A format code that controls the phonetic (furigana) formatting of the text string.If omitted, the default format code is used.
Example: PHONETIC("東京") returns "とうきょう", which is the phonetic (furigana) representation of the text string "東京" PHONETIC("こんにちは") returns "こんにちは", which is the phonetic (furigana) representation of the text string "こんにちは" PHONETIC(A1) returns the phonetic (furigana) representation of the text string in cell A1.
DCOUNT
Counts the number of cells in a database that meet specified criteria.
DCOUNT(database, field, criteria)
•database: The range of cells that makes up the database, including the headers.
•field: The column header indicating the field to be counted.
•criteria: The range of cells that contains the criteria.Each column in the criteria range should contain a separate criterion and the first row should contain the column headers that match the database headers.
Example: DCOUNT(A1:D10,"Sales",A12:B13) returns the count of values in the "Sales" column of the database A1:D10 where the values in column A meet the criteria in A12:B13 DCOUNT(A1:E20,"Units Sold",A23:D24) returns the count of values in the "Units Sold" column of the database A1:E20 where the values in columns A through D meet the criteria in A23:D24 DCOUNT(SalesData,"Revenue",Criteria) returns the count of values in the "Revenue" column of the named range SalesData where the values in the named range Criteria meet the criteria.
PRICEMAT
Returns the price per $100 face value of a security that pays interest at maturity.
PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•issue: The issue date of the security.
•rate: The annual interest rate of the security.
•yld: The annual yield of the security.
•[basis]: Optional.The day count basis to use for calculating bond interest.If omitted, defaults to 0 (US (NASD) 30/360).
Example: PRICEMAT("1/1/2023","1/1/2043","2022-01-01",6%,8%).
STDEVA
Estimates the standard deviation of a population based on a sample of numbers, including text and logical values.
STDEVA(value1, [value2], ...)
•value1: The first value in the sample.Can be a number, text, or logical value.
•valueN: Optional.Additional values in the sample.Can be a number, text, or logical value.
Example: STDEVA(2, "hello", TRUE, 8, "world") STDEVA(A1:A5) returns the estimated standard deviation of the values in cells A1 through A5, including any text or logical values STDEVA(A1, B1, C1) returns the estimated standard deviation of the values in cells A1, B1, and C1, including any text or logical values.
SUMSQ
Returns the sum of the squares of a range of numbers or cells.
SUMSQ(number1, [number2], ...)
•number1: The first number or range to square and add.
•numberN: Optional.Additional numbers or ranges to square and add.
Example: SUMSQ(3, 4) returns 25 (since 3^2 + 4^2 = 9 + 16 = 25) SUMSQ(A2:A6) returns the sum of the squares of the values in cells A2 through A6 SUMSQ(B2:B6, D2:D6) returns the sum of the squares of the values in both ranges B2 through B6 and D2 through D6.
ENCODEURL
The ENCODEURL function encodes a text string for use as a valid part of a URL.
ENCODEURL(text)
text: The text string to encode.
Example: ENCODEURL('https://www.example.com') returns https%3A%2F%2Fwww.example.com ENCODEURL('apples&oranges') returns apples%26oranges ENCODEURL(A2) returns the encoded version of the text in cell A2.
EUROCONVERT
The EUROCONVERT function converts a number from one currency to another.
EUROCONVERT(number, source_currency, target_currency, full_precision, triangulation)
•number: The number to convert.
•source_currency: The currency code for the source currency, in ISO format.
•target_currency: The currency code for the target currency, in ISO format.
•full_precision: An optional logical value, either TRUE or FALSE, that specifies whether to use full precision in the calculation.Defaults to FALSE.
•triangulation: An optional logical value, either TRUE or FALSE, that specifies whether to use triangular conversion when there is no direct exchange rate between the source and target currencies.Defaults to FALSE.
Example: EUROCONVERT(100,'EUR','DEM') returns the equivalent of 100 Euros in Deutsche Marks EUROCONVERT(50,'DEM','EUR',TRUE) returns the equivalent of 50 Deutsche Marks in Euros with full precision EUROCONVERT(A2,B2,C2,D2,E2) returns the equivalent of the value in cell A2, converted from the source currency in cell B2 to the target currency in cell C2, using full precision if the value in cell D2 is TRUE, and using triangular conversion if the value in cell E2 is TRUE.
DAVERAGE
Returns the average of selected database entries based on specified criteria.
DAVERAGE(database, field, criteria)
•database: The range of cells that makes up the database, including the headers.
•field: The column header indicating the field to be averaged.
•criteria: The range of cells that contains the criteria.Each column in the criteria range should contain a separate criterion and the first row should contain the column headers that match the database headers.
Example: DAVERAGE(A1:D10,"Sales",A12:B13) returns the average of values in the "Sales" column of the database A1:D10 where the values in column A meet the criteria in A12:B13 DAVERAGE(A1:E20,"Units Sold",A23:D24) returns the average of values in the "Units Sold" column of the database A1:E20 where the values in columns A through D meet the criteria in A23:D24 DAVERAGE(SalesData,"Revenue",Criteria) returns the average of values in the "Revenue" column of the named range SalesData where the values in the named range Criteria meet the criteria.
ROUND
Rounds a number to a specified number of digits.
ROUND(number, num_digits)
•number: The number you want to round.
•num_digits: The number of digits to which you want to round the number.
Example: ROUND(3.14159, 2) returns 3.14 ROUND(99.95, 0) returns 100 ROUND(1234.56789, -2) returns 1200.
EXACT
The EXACT function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.The comparison is case-sensitive.
EXACT(text1, text2)
•text1: The first text string to compare.
•text2: The second text string to compare.
Example: EXACT("apple", "apple") returns TRUE EXACT("apple", "Apple") returns FALSE EXACT(A2, B2) compares the values in cells A2 and B2 and returns TRUE if they are exactly the same, FALSE otherwise.
FREQUENCY
Calculates how often values occur within a range of values, and then returns a vertical array of numbers.
FREQUENCY(data_array, bins_array)
•data_array: An array or range of values for which you want to count frequencies.
•bins_array: An array or range of intervals into which you want to group the values in 'data_array'.
Example: FREQUENCY(A2:A10,B2:B5) FREQUENCY(A2:A10,C2:C6) FREQUENCY(A2:A10,D2:D7).
ODDFYIELD
Calculates the yield of a security with an odd first period.
ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])
•settlement: The settlement date of the security.
•maturity: The maturity date of the security.
•issue: The issue date of the security.
•first_coupon: The date of the first interest payment, which is an odd period.
•rate: The annual coupon rate of the security.
•pr: The price per $100 face value of the security.
•redemption: The redemption value of the security per $100 face value.
•frequency: The number of coupon payments per year.
•[basis]]: Optional argument that specifies the day count basis to use.If omitted, Excel uses the US (NASD) 30/360 basis.
Example: ODDFYIELD('2022-01-01','2022-06-30','12/31/2021','3/31/2022',0.08,99.25,100,2).
IMCSCH
Calculates the inverse hyperbolic cosecant of a complex number.
IMCSCH(inumber)
inumber: The number for which you want to calculate the inverse hyperbolic cosecant.
Example: IMCSCH("4+3i").
RADIANS
Converts degrees into radians.One radian is equal to the angle made at the center of a circle by an arc whose length is equal to the radius of the circle.
RADIANS(angle)
angle: The angle to convert from degrees to radians.
Example: RADIANS(45) returns approximately 0.7854, which is the equivalent angle in radians of 45 degrees RADIANS(90) returns approximately 1.5708, which is the equivalent angle in radians of 90 degrees RADIANS(A1) returns the equivalent angle in radians of the value in cell A1, which is assumed to be in degrees.
MID
Returns a specific number of characters from a text string starting at the position you specify.
MID(text, start_num, num_chars)
•text: The text string that contains the characters you want to extract.
•start_num: Specifies the position of the first character you want to extract.The first character in text is 1.
•num_chars: Specifies the number of characters you want MID to return.
Example: MID('apple', 1, 3) returns 'app' MID('banana', 3, 2) returns 'na' MID('cherry', 2, 4) returns 'herr'.
FACTDOUBLE
Returns the factorial of a number, multiplied by the factorial of number-2.
FACTDOUBLE(number)
number: The non-negative integer for which to calculate the double factorial.
Example: FACTDOUBLE(5) returns 15 FACTDOUBLE(6) returns 48 FACTDOUBLE(10) returns 3840.
PERMUTATIONA
Returns the number of permutations for a given number of objects that can be selected from a set with repetition.
PERMUTATIONA(number, number_chosen)
•number: The total number of objects from which to choose.
•number_chosen: The number of objects to choose.If omitted, number_chosen is assumed to be equal to number.
Example: PERMUTATIONA(5,3) returns 125, which is the number of ways to select 3 objects from a set of 5 objects when repetition is allowed.
BITLSHIFT
Shifts bits to the left, padding with zeroes on the right.
BITLSHIFT(number, shift_amount)
•number: The number whose bits you want to shift.
•shift_amount: The number of bits to shift.A positive value shifts the bits to the left and a negative value shifts them to the right.
Example: BITLSHIFT(8, 2) returns 32 BITLSHIFT(16, -1) returns 8.
VALUE
Converts a text string that represents a number to a number.
VALUE(text)
text: The text string that contains the number you want to convert.The text can be in any of the constant number, date, or time formats recognized by Excel.
Example: VALUE("123") returns 123 VALUE("$456") returns 456 VALUE("12:34") returns 0.523611111.
PERCENTRANK.EXC
Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set.
PERCENTRANK.EXC(array, x, [significance])
•array: The array or range of data that defines relative standing.
•x: The value for which you want to know the rank.
•[significance]: Optional.The number of significant digits for the returned percentage value.Defaults to 3.
Example: PERCENTRANK.EXC(A1:A10,B1) returns the rank of the value in B1 as a percentage of the values in A1 through A10 PERCENTRANK.EXC(C1:C5,20,2) returns the rank of the value 20 in the range C1 through C5 as a percentage with 2 significant digits PERCENTRANK.EXC(D1:D100,50) returns the rank of the value 50 in the range D1 through D100 as a percentage.
FINV
Returns the inverse of the F probability distribution for a specified probability and degrees of freedom.
FINV(probability, degrees_freedom1, degrees_freedom2)
•probability: The probability of the F distribution.Must be between 0 and 1.
•degrees_freedom1: The numerator degrees of freedom for the F distribution.
•degrees_freedom2: The denominator degrees of freedom for the F distribution.
Example: FINV(0.05, 3, 5) FINV(0.01, 10, 20) FINV(0.025, 4, 6).
FISHER
Returns the Fisher transformation of a specified value.
FISHER(x)
x: The numeric value for which to find the Fisher transformation.
Example: FISHER(0.5) returns 0.549306144334055 FISHER(0.8) returns 1.09861228866811.
ROMAN
Converts an Arabic numeral to a Roman numeral.
ROMAN(number, [style])
•number: The Arabic numeral you want to convert to a Roman numeral.
•style: Optional.A number that determines the type of Roman numeral to use in the conversion.If omitted or set to 0, ROMAN uses the classic Roman numeral form.
Example: ROMAN(10) returns "X" ROMAN(3999) returns "MMMCMXCIX" ROMAN(2021, 2) returns "MMXXI".
WEIBULL
Returns the Weibull distribution, which is often used in reliability analysis.
WEIBULL(x, alpha, beta, cumulative)
•x: The value at which to evaluate the distribution.
•alpha: A parameter to set the location of the distribution.
•beta: A parameter to set the scale of the distribution.
•cumulative: Optional.A logical value that indicates which form of the Weibull distribution to use: TRUE or omitted: The cumulative distribution function FALSE: The probability density function
Example: WEIBULL(60, 50, 10, TRUE) WEIBULL(100, 50, 10, FALSE) WEIBULL(A2, B2, C2, TRUE) returns: The cumulative distribution function of the Weibull distribution with parameters in cells B2 and C2 at the value in cell A2.
NPV
Calculates the net present value of an investment based on a series of future cash flows and a discount rate.
NPV(rate, value1, [value2], ...)
•rate: The rate of discount over the length of one period.
•value1: The first cash flow in the series.It must occur at the end of the first period.
•valueN: An additional cash flow occurring at the end of the second period and so on.
Example: NPV(0.1,-10000,3000,4200,6800) NPV(0.09,-5000,1500,2000,2500,3000) NPV(0.12,-8000,1200,1300,1550,1600).
DOLLARFR
Converts a fraction to a decimal dollar value using the specified denominator.
DOLLARFR(decimal_dollar, fraction)
•decimal_dollar: A decimal number
•fraction: The integer to use in the denominator of a fraction.
Example: DOLLARFR(1.125, 16) returns 1.02 DOLLARFR(1.125, 32) returns 1.04.
CUBESET
Defines a calculated set of members or tuples by sending a set expression to the cube on the specified connection.
CUBESET(connection, set_expression, [caption])
•connection: The name of the Excel data source connection.
•set_expression: A valid Multidimensional Expressions (MDX) expression that defines the calculated set.
•[caption]: Optional.The caption of the calculated set.If omitted, the set_expression parameter is used as the caption.
Example: CUBESET("Sales Data","[[Date].[Calendar Year].&[2022],[Date].[Calendar Year].&[2023]]") returns a set of calendar year members for the years 2022 and 2023 from the Date dimension in the Sales Data cube CUBESET("Budget Data","CrossJoin([[Product].[All Products].[Bikes],[Product].[All Products].[Accessories]], [[Employee].[All Employees].[Jane],[Employee].[All Employees].[John]])","Product and Employee") returns a set of all combinations of bikes and accessories with Jane and John employees from the Product and Employee dimensions in the Budget Data cube CUBESET("Inventory Data","TopCount([Store].[Store Name].Children,5,[Measures].[Inventory Count])", "Top 5 Stores") returns a set of the top 5 stores in terms of inventory count from the Store dimension in the Inventory Data cube.
VARPA
Calculates the variance based on an entire population, including numbers, and logical values.
VARPA(value1, [value2], ...)
•value1: The first value or range of values that you want to calculate the variance of.At least one value is required.
•value: Optional.Additional values or ranges of values that you want to include in the calculation.You can include up to 255 values or ranges.The values can be numbers, or logical values (TRUE or FALSE).
Example: VARPA(1, TRUE) VARPA(A1:A10) VARPA(B1:B10, C1:C10).
DECIMAL
Converts a text representation of a number in a given base into a decimal number.
DECIMAL(text, radix)
•text: The text string containing the number you want to convert.
•radix: Optional.The base of the number you want to convert.If omitted, assumes base 10.
Example: DECIMAL("101010",2) returns 42 DECIMAL("FF",16) returns 255 DECIMAL("1000",8) returns 512.
STDEV
Estimates the standard deviation of a population based on a sample of numbers.
STDEV(number1, [number2], ...)
•number1: The first number in the sample.
•numberN: Optional.Additional numbers in the sample.
Example: STDEV(2, 4, 6, 8, 10) STDEV(A1:A5) returns the estimated standard deviation of the values in cells A1 through A5 STDEV(A1, A3, A5) returns the estimated standard deviation of the values in cells A1, A3, and A5.
WEBSERVICE
Retrieves data from a web service on the internet or intranet.
WEBSERVICE(url)
url: The URL of the web service to retrieve data from.
Example: WEBSERVICE("https://api.example.com/data") returns:["key1": "value1", "key2": "value2"] WEBSERVICE("http://localhost:8080/data") returns:["key1": 123, "key2": "text"] WEBSERVICE("https://www.google.com/") returns:The HTML content of the Google homepage.
DSTDEV
The DSTDEV function calculates the standard deviation of a population based on a sample of that population.
DSTDEV(database, field, criteria)
•database: The database or array of data points representing a sample from the population.
•field: The specific field or range within the database containing the data to be considered.
•criteria: Optional: The conditions or criteria to apply when selecting data from the database.If not specified, all data is considered as the sample.
Example: DSTDEV(A2:A10, B2:B10, C2:C10) estimates the standard deviation based on the sample defined by the database, field, and criteria.
TINV
Returns the inverse of the Student's t-distribution as a two-tailed probability.
TINV(probability, deg_freedom)
•probability: A probability associated with the two-tailed Student's t-distribution
•deg_freedom: The number of degrees of freedom
Example: TINV(0.05, 10) TINV(0.05, 20) TINV(0.05,30).
YIELDMAT
Returns the yield of a security that pays interest at maturity, based on its price and face value.
YIELDMAT(settlement, maturity, issue, rate, pr, [basis])
•settlement: The security's settlement date.
•maturity: The security's maturity date.
•issue: The security's issue date.
•rate: The security's annual coupon rate.
•pr: The security's price per $100 face value.
•[basis]: Optional.The day count basis to use in the calculation.Can be 0, 1, 2, 3, or 4.If omitted, it defaults to 0 (US (NASD) 30/360).
Example: YIELDMAT("2021-09-01", "2022-03-01", "2021-09-01", 0.08, 98, 1) YIELDMAT("2021-09-01", "2025-01-01", "2021-09-01", 0.06, 95, 4).
ACOSH
Returns the inverse hyperbolic cosine of a number.
ACOSH(x)
x: The value for which to return the inverse hyperbolic cosine.Must be greater than or equal to 1.
Example: ACOSH(1) returns 0 ACOSH(2) returns 1.316957897 ACOSH(10) returns 2.993222846.
LEFT
Returns a specified number of characters from the beginning (left side) of a text string.
LEFT(text, [num_chars])
•text: The text string that contains the characters you want to extract.
•[num_chars]: Optional.Specifies how many characters you want LEFT to return.
Example: LEFT('apple', 3) returns 'app' LEFT('banana', 2) returns 'ba' LEFT('cherry', 5) returns 'cherr'.
ZTEST
Returns the probability of a one-tailed, one-sample z-test.
ZTEST(array, [x], [sigma])
@param array: The array or range of data to test.
•x: Optional.The hypothesized value of the population mean.If omitted, the sample mean is used instead.
•sigma: Optional.The population (known) standard deviation.If omitted, the sample standard deviation is used instead.
Example: ZTEST([1, 2, 3, 4, 5], 3) returns: 0.5.
DATE
Returns the date based on a year, month, and day.
DATE(year, month, day)
•year: The year as an integer.
•month: The month as an integer (1-12).
•day: The day of the month as an integer (1-31).
Example: DATE(2022,3,15) returns March 15, 2022 DATE(2023,12,31) returns December 31, 2023 DATE(2024,2,29) returns February 29, 2024 (a leap year).
ROWS
Returns the number of rows in a range or array.
ROWS(array)
array: The range or array for which you want to count the number of rows.
Example: ROWS(A1:A10) returns 10 ROWS(B2:C5) returns 4 ROWS([1,2,3;4,5,6]) returns 2.
CEILING.MATH
Rounds a number up to the nearest multiple of a specified significance, regardless of the sign of the number.
CEILING.MATH(number, [significance], [mode])
•number: The number to be rounded up.
•[significance]: Optional.The multiple to which you want to round the number.If omitted, the default value is 1.
•[mode]: Optional.A value that determines how to round the number.If omitted, or if the value is 0 or omitted, the function uses the default rounding mode (up).
Example: CEILING.MATH(4.3, 1) returns 5 CEILING.MATH(-7.8, 2) returns -6 CEILING.MATH(3.5, 0.1, 1) returns 3.6.
WORKDAY
Returns the date that is the indicated number of working days before or after a date (the starting date).
WORKDAY(start_date, days, [holidays])
•start_date: The starting date, expressed as a serial number or a date.
•days: The number of working days before or after the start date.A positive value for days generates a future date; a negative value generates a past date.
•[holidays]: Optional.An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
Example: WORKDAY("2023-03-15", 5) returns:"2023-03-22" WORKDAY("2023-03-15", -5) returns:"2023-03-08" WORKDAY("2023-03-15", 10, ["2023-03-20","2023-03-21"]) returns:"2023-04-01".
AMORLINC
Returns the depreciation for an accounting period, based on the straight-line method.
AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])
•cost: The initial cost of the asset.
•date_purchased: The date the asset was purchased.
•first_period: The date of the end of the first period.
•salvage: The value of the asset at the end of its useful life.
•period: The period for which to calculate depreciation.
•rate: The rate at which the asset is to be depreciated per period.
•[basis]: Optional.The day count basis to use.Defaults to 0 if not specified.
Example: AMORLINC(10000, "2018-01-01", "2018-12-31", 2000, 6, 0.2).
PERCENTILE
Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive.If the data set has a small sample size or if k = 0 or k = 1, use PERCENTILE.INC instead.
PERCENTILE(array, k)
•array: The array or range of data that defines relative standing.
•k: The percentile to return, expressed as a decimal between 0 and 1, inclusive.
Example: PERCENTILE(A1:A10,0.9) returns the value at the 90th percentile in the range A1 through A10 PERCENTILE(B1:B5,0.8) returns the value at the 80th percentile in the range B1 through B5 PERCENTILE(C1:C100,0.99) returns the value at the 99th percentile in the range C1 through C100.
INFO
Returns information about the current operating environment, such as the version of Excel and the operating system.
INFO(type_text)
type_text: A text string specifying the type of information you want to retrieve.Currently, only the following information types are supported: 'directory', 'numfile', 'origin', 'osversion', 'recalc', 'release', and 'system'.
Example: INFO("directory") returns the directory where the active workbook is stored INFO("osversion") returns the version number of the operating system running Excel INFO("release") returns the release number of Excel being used.
TAKE
Returns a specified number of characters from the start of a text string.
TAKE(array, rows, [columns])
•array: The array from which to extract rows or columns.
•rows: The number of rows to extract from the start or end of the array.A positive value extracts from the start, and a negative value extracts from the end.
•[columns]: Optional.The number of columns to extract from the start or end of the array.A positive value extracts from the start, and a negative value extracts from the end.If not specified, all columns are included.
Example: TAKE(A1:D4, 2, 3) returns the first 2 rows and 3 columns of the array.TAKE(E1:H4, -3) returns the last 3 rows of the array.TAKE(I1:K4, 2, -1) returns the first 2 rows and excludes the last column of the array.
MAKEARRAY
Creates an array of specified dimensions and fills it with either a specified value or no value.
MAKEARRAY(rows, columns, function)
•rows: The number of rows in the array.
•columns: The number of columns in the array.
•function: The LAMBDA function to apply for generating the array values.
Example: MAKEARRAY(3, 2, MyLambdaFunction) returns a 3x2 array by applying MyLambdaFunction to each element.MAKEARRAY(2, 2, AnotherLambda) returns a 2x2 array by applying AnotherLambda to each element.MAKEARRAY(4, 4, SomeOtherLambda) returns a 4x4 array by applying SomeOtherLambda to each element.
CONFIDENCE
Returns the confidence interval for a population mean.
CONFIDENCE(alpha, standard_dev, size)
•alpha: The significance level used to compute the confidence level.Often represented as a decimal; for example, use 0.05 for a 95% confidence level.
•standard_dev: The population standard deviation for the data range and is assumed to be known.Must be greater than 0.
•size: The sample size.
Example: CONFIDENCE(0.05, 1.5, 30) returns 0.5367558243.
DGET
Extracts a single value from a database table-like array based on specified criteria.
DGET(database, field, criteria)
•database: The database range to use for the lookup.Should include headers for each column.
•field: The column header of the field you want to extract the value from.
•criteria: Criteria range or array to search for records that meet all of the specified conditions.
Example: DGET(A1:C10,"Salary",[A1:A10,"John",C1:C10,">1000"]) returns the salary for John if their salary is greater than 1000 DGET(A1:C10,"Name",[A1:A10,"*doe*",B1:B10,">=3"]) returns the name of the first person whose last name contains "doe" and has been with the company for at least 3 years DGET(A1:E10,"Phone",[A1:A10,"Jane",B1:B10,"<>Sales",C1:C10,"<>Seattle"]) returns the phone number for Jane if they're not in Sales and not located in Seattle.
ISLOGICAL
Checks if a given value is a logical value (TRUE or FALSE) and returns TRUE if the value is a logical value, and FALSE otherwise.
ISLOGICAL(value)
value: The value that you want to test.
Example: ISLOGICAL(TRUE) returns TRUE because TRUE is a logical value ISLOGICAL(FALSE) returns TRUE because FALSE is a logical value ISLOGICAL(C3) returns TRUE if cell C3 contains a logical value, and FALSE otherwise.
SYD
Returns the straight-line depreciation of an asset for a specific period using the sum-of-years digits method.
SYD(cost, salvage, life, period)
•cost: The initial cost of the asset.
•salvage: The value of the asset at the end of its useful life (sometimes called "salvage value").
•life: The number of periods over which the asset is being depreciated.
•period: The period for which you want to calculate the depreciation.Must be greater than or equal to 1 and less than or equal to the life of the asset.
Example: SYD(10000, 1000, 5, 3) returns 1800 (the amount the asset depreciates in year 3, calculated using the sum-of-years digits method).
COVARIANCE.P
Calculates the population covariance, which is a measure of how much two random variables change together, between two specified sets of data.
COVARIANCE.P(array1, array2)
•array1: The first array or range of data.
•arrayN: The second array or range of data.Must be equal in length to array1.
Example: COVARIANCE.P([1,2,3,4],[5,6,7,8]) returns 1.25.
DEVSQ
Calculates the sum of squares of deviations from the mean of a dataset.
DEVSQ(number1, [number2], ...)
•number1: The first number in the dataset.
•numberN: Optional.Additional numbers or ranges containing numbers you want to include in the calculation.
Example: DEVSQ(1,2,3,4,5) returns 10 DEVSQ(A1:A5) returns the sum of squares of deviations from the mean for the range A1:A5.
COMPLEX
Converts real and imaginary coefficients into a complex number in x + yi or x + yj text format.
COMPLEX(real_num, [i_num], [suffix])
•real_num: The real coefficient of the complex number.
•[i_num]: Optional.The imaginary coefficient of the complex number.If omitted, assumed to be 0.
•[suffix]: Optional.The suffix to use for the imaginary component of the complex number.Can be either "i" or "j".If omitted, assumed to be "i".
Example: COMPLEX(3, 4) returns "3+4i" COMPLEX(-2, -1, "j") returns "-2-1j" COMPLEX(0, 5) returns "5i".
BIN2HEX
Converts a binary number to hexadecimal.
BIN2HEX(number, [places])
•number: The binary number to convert to hexadecimal.Must be a string of up to 10 characters containing only 0's and 1's.
•[places]: Optional.The number of characters to use.If omitted, places defaults to the minimum number necessary to represent the number.
Example: BIN2HEX('110101') returns '35'.
FILTER
Returns an array filtered by a set of criteria.
FILTER(array, include, [if_empty])
•array: The array or range to filter.
•include: The array or range containing the criteria values to include.
•[if_empty]: Optional.The value to return if the result is empty.If omitted, blank cells are returned.
Example: FILTER([1,2,3,4,5], [TRUE,FALSE,TRUE,FALSE,TRUE]) returns [1,3,5] FILTER(A1:A10, B1:B10>50) returns [73, 78, 81] FILTER(B2:E6, F2:F6) returns [[2, 4, 6, 8], [11, 13, 15, 17]].
UNIQUE
Returns a list of unique values in a range or array.
UNIQUE(array, [by_col], [exactly_once])
•array: The range or array from which to return unique values.
•[by_col]: A logical value that specifies how to compare.If TRUE or omitted, the function compares each column as a separate group.If FALSE, the function compares each row as a separate group.
•[exactly_once]: A logical value that specifies whether to return only values that appear exactly once in the source range or array.If TRUE, only unique values are returned.If omitted or FALSE, all values are returned.
Example: UNIQUE(A1:A10) returns a list of unique values in the range A1:A10 UNIQUE(A1:B10, TRUE, TRUE) returns a list of unique values by column that appear exactly once in the range A1:B10 UNIQUE([1,2,3;1,4,5;6,7,8]) returns [1,2,3,4,5,6,7,8].
EOMONTH
The EOMONTH function returns the last day of the month that is a specified number of months before or after a given date.
EOMONTH(start_date, months)
•start_date: The start date to use in the calculation.
•months: The number of months before or after the start date.A positive value for months yields a future date; a negative value yields a past date.
Example: EOMONTH("2022-01-15", 3) returns 44681.
PERCENTILE.INC
Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive.
PERCENTILE.INC(array, k)
•array: The array or range of data that defines relative standing.
•k: The percentile to return, expressed as a decimal between 0 and 1, inclusive.
Example: PERCENTILE.INC(A1:A10,0.9) returns the value at the 90th percentile in the range A1 through A10 PERCENTILE.INC(B1:B5,0.8) returns the value at the 80th percentile in the range B1 through B5 PERCENTILE.INC(C1:C100,0.99) returns the value at the 99th percentile in the range C1 through C100.
TEXT
Converts a value to text in a specified number format.
TEXT(value, format_text)
•value: The value to convert to text.
•format_text: The number format to apply to the converted text value.
Example: TEXT(1234.5678, "$#,##0.00") returns "$1,234.57" TEXT(0.75, "0%") returns "75%" TEXT("Hello", "00000") returns "Hello".
RAND
Generates a random decimal number between 0 and 1.The value of the number changes each time the worksheet is calculated or when the function is recalculated by pressing F9.
RAND()
Example: RAND() returns a random decimal number between 0 and 1 on each calculation IF(RAND()>0.5, "Heads", "Tails") simulates the flip of a biased coin with a 50% chance of landing on Heads.
IMPOWER
Returns a complex number raised to a power.
IMPOWER(inumber, number)
•inumber: The complex number that you want to raise to a power.
•number: The power to which you want to raise the complex number.
Example: IMPOWER("2+3i", 3) returns "-46+9i".
TEXTBEFORE
Returns the substring of text before a specified character or string.
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
•text: The text from which to extract the substring.
•delimiter: The character or string before which to stop extracting the substring.
•[instance_num]: Optional.The instance number (default is 1) that specifies which occurrence of the delimiter to use for extraction.
•[match_mode]: Optional.The matching mode (default is case-sensitive) to determine how to find the delimiter.
•[match_end]: Optional.A logical value (default is FALSE) to specify whether to include the delimiter in the result.
•[if_not_found]: Optional.The value to return if the delimiter is not found in the text.
Example: TEXTBEFORE("apple,banana,cherry", ",") returns "apple" TEXTBEFORE("Hello World", "World") returns "Hello " TEXTBEFORE("John Smith", " ") returns "John".
ROUNDDOWN
Rounds a number down to a specified number of digits.
ROUNDDOWN(number, num_digits)
•number: The number you want to round down.
•num_digits: The number of digits to which you want to round down the number.
Example: ROUNDDOWN(3.14159, 2) returns 3.14 ROUNDDOWN(99.95, 0) returns 99 ROUNDDOWN(1234.56789, -2) returns 1200.
YIELD
Returns the yield on a security that pays periodic interest, based on its price and the interest rate.
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
•settlement: The security's settlement date.
•maturity: The security's maturity date.
•rate: The security's annual coupon rate.
•pr: The security's price per $100 face value.
•redemption: The security's redemption (face) value per $100 face value.
•frequency: The number of coupon payments per year.Must be 1, 2, or 4.
•[basis]: Optional.The day count basis to use in the calculation.Can be 0, 1, 2, 3, or 4.If omitted, it defaults to 0 (US (NASD) 30/360).
Example: YIELD("2022-01-01", "2027-01-01", 0.05, 95, 100, 2) YIELD("2022-01-01", "2032-01-01", 0.08, 97.5, 100, 1, 1).
REDUCE
Returns the reduced form of a fraction.
REDUCE(initial_value, array, function)
•initial_value: The initial value of the accumulator.
•array: The array of values to reduce.
•function: The LAMBDA function to apply to each value in the array.
Example: REDUCE(0, [1, 2, 3, 4], MyLambdaFunction) returns the accumulated result of applying MyLambdaFunction to each element in the array.REDUCE(100, [10, 20, 30], AnotherLambda) returns the accumulated result of applying AnotherLambda to each element in the array starting from the initial value 100.REDUCE(1, [2, 3, 4], SomeOtherLambda) returns the accumulated result of applying SomeOtherLambda to each element in the array starting from the initial value 1.
SEARCH
Returns the starting position of a text string within another text string, starting from the leftmost character.If the text is not found, returns #VALUE!
SEARCH(find_text, within_text,[start_num])
•find_text: The text to find.
•within_text: The text to search within.
•[start_num]: Optional.The starting position of the search.Default is 1.
Example: SEARCH("blue","The sky is blue.")SEARCH("red","The sky is blue.").
N
Returns a value converted to a number.
N(value)
any: The value you want to convert to a number.If value is a cell reference, its contents are used.
Example: N(42) returns 42 N("-42") returns -42 N("$1,000.00") returns 1000.
ASIN
Returns the inverse sine of a number, in radians.
ASIN(x)
x: The number for which to calculate the inverse sine.Must be between -1 and 1, inclusive.
Example: ASIN(0) returns 0 ASIN(1) returns 1.570796327.
DBCS
Checks whether a text string contains double-byte characters, which are used in some languages such as Japanese and Chinese.
DBCS(text)
text: The text string to be checked for double-byte characters.
Example: DBCS("こんにちは") returns TRUE because the string contains double-byte characters DBCS("Hello") returns FALSE because the string does not contain double-byte characters DBCS("你好") returns TRUE because the string contains double-byte characters.
SUBSTITUTE
Replaces a specified occurrence of text in a string with another text.
SUBSTITUTE(text, old_text, new_text, [instance_num])
•text: The text or the reference to a cell containing text in which you want to substitute characters.
•old_text: The text you want to replace.
•new_text: The text you want to replace the old text with.
•[instance_num]: Optional.The occurrence of old_text in text that you want to substitute.If omitted, all occurrences are substituted.
Example: SUBSTITUTE("apples and oranges", "apples", "bananas") returns "bananas and oranges" SUBSTITUTE(A2, B2, C2) replaces the text in cell B2 with the text in cell C2 in the text string in cell A2 SUBSTITUTE("one fish two fish red fish blue fish", "fish", "cat", 2) returns "one fish two cat red fish blue fish", replacing only the second occurrence of "fish" with "cat".
DCOUNTA
Counts the number of non-blank cells in a database that meet specified criteria.
DCOUNTA(database, field, criteria)
•database: The range of cells that makes up the database, including the headers.
•field: The column header indicating the field to be counted.
•criteria: The range of cells that contains the criteria.Each column in the criteria range should contain a separate criterion and the first row should contain the column headers that match the database headers.
Example: DCOUNTA(A1:D10,"Sales",A12:B13) returns the count of non-blank values in the "Sales" column of the database A1:D10 where the values in column A meet the criteria in A12:B13 DCOUNTA(A1:E20,"Units Sold",A23:D24) returns the count of non-blank values in the "Units Sold" column of the database A1:E20 where the values in columns A through D meet the criteria in A23:D24 DCOUNTA(SalesData,"Revenue",Criteria) returns the count of non-blank values in the "Revenue" column of the named range SalesData where the values in the named range Criteria meet the criteria.
T.TEST
Returns the probability associated with a Student's t-test.
T.TEST(array1, array2, tails, type)
•array1: The first array or range of data.
•array2: The second array or range of data.If omitted, T.TEST will test for the difference in means between Array1 and a hypothetical mean of zero.
•tails: Specifies the number of distribution tails to test.If omitted, the default value is 2 (which tests for a two-tailed distribution).
•type: Specifies the type of t-test to perform.If omitted, the default value is 2 (which performs a paired t-test).
Example: T.TEST(A1:A10, B1:B10, 2, 2) returns the probability associated with a two-sample paired t-test of the ranges A1:A10 and B1:B10 T.TEST(A1:A10, 0, 1, 1) returns the probability associated with a one-sample t-test that the mean of A1:A10 is equal to 0 T.TEST(A1:A10, B1:B10, 1) returns the probability associated with a two-sample equal variance t-test of the ranges A1:A10 and B1:B10.
VARP
Calculates the variance based on an entire population.Provides a precise measure of how far the values in a population vary from the population mean.
VARP(number1, [number2], ...)
•number1: The first number or range of numbers that you want to calculate the variance of.At least one number is required.
•numberN: Additional numbers or ranges of numbers that you want to include in the calculation.You can include up to 255 numbers or ranges.
Example: VARP(1,2,3,4,5) returns 2 (because the variance of these numbers as a population is exactly 2) VARP(A1:A10) returns the variance of the values in cells A1 through A10 as a population VARP(B1:B10, C1:C10) returns the variance of the combined sets of values in cells B1 through B10 and C1 through C10 as a population.
RATE
Returns the interest rate per period of an annuity.
RATE(nper, pmt, pv, [fv], [type], [guess])
•nper: The total number of payment periods.
•pmt: The amount paid each period, such as a loan or lease payment.
•pv: The present value, or the total amount that a series of future payments is worth now.
•[fv]: Optional.The future value, or a cash balance you want to attain after the last payment is made.If omitted, assumed to be 0.
•[type]: Optional.When payments are due: 0 for end of period (default), or 1 for beginning of period.
•[guess]: Optional.Your guess for what the rate will be; if omitted, RATE uses 10 percent.
Example: RATE(12, 100, -1000).
HYPERLINK
The HYPERLINK function is used to create a clickable link that opens a webpage, document, or email address.
HYPERLINK(link_location, [friendly_name])
•link_location: The web address, file path, or email address to be linked.
•friendly_name: Optional.The text to display as the clickable link.If omitted, the link_location will be displayed.
Example: HYPERLINK("https://www.google.com", "Google") creates a hyperlink with the text 'Google' that links to https://www.google.com HYPERLINK(B1,"Click Here") creates a hyperlink in cell A1 with the text 'Click Here' that links to the value in cell B1.HYPERLINK("mailto:email@example.com") creates a hyperlink that opens an email addressed to email@example.com.
COVAR
Calculates the covariance, which is a measure of how much two random variables change together, between two specified sets of data.
COVAR(array1, array2)
•array1: The first array or range of data.
•array2: The second array or range of data.Must be equal in length to array1.
Example: COVAR([1,2,3,4],[5,6,7,8]) returns 1.25.
EFFECT
The EFFECT function calculates the effective annual interest rate given the nominal annual interest rate and the number of compounding periods per year.
EFFECT(nominal_rate, npery)
•nominal_rate: The nominal annual interest rate.
•npery: The number of compounding periods per year.
Example: EFFECT(0.06, 12) returns 0.061678.
AND
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluates to FALSE.
AND(logical1, [logical2], ...)
•logical1: The first condition to test.
•logicalN: Optional.Additional conditions to test.You can specify up to 255 conditions.
Example: AND(TRUE, TRUE) returns TRUE AND(5=5, 3>2) returns TRUE AND(FALSE, 1=1) returns FALSE.
SIN
Returns the sine of an angle in radians.
SIN(x)
x: The angle in radians for which to calculate the sine.
Example: SIN(0) returns 0 SIN(PI()/6) returns 0.5.
QUARTILE
Calculates the quartile of a dataset, which is a value that separates the lowest 25% from the highest 75% of values.
QUARTILE(array, quart)
•array: The array or range of data for which to determine the quartile.
•quart: The quartile to return.1 returns the minimum value, 2 returns the value at the first quartile, 3 returns the value at the median (second quartile), and 4 returns the value at the third quartile.
Example: QUARTILE(A1:A10, 2) returns the value at the first quartile (the value separating the lowest 25% of values from the highest 75%) for the range A1:A10 QUARTILE(B2:B20, 3) returns the value at the median (the second quartile) for the range B2:B20 QUARTILE(C1:C100, 4) returns the value at the third quartile (the value separating the lowest 75% of values from the highest 25%) for the range C1:C100.
SINGLE
The SINGLE function in Excel returns the value from a range that shares the same column or row as the reference cell.
SINGLE(reference)
reference: The reference cell whose row or column you want to match to retrieve the corresponding value from the range.
Example: SINGLE(A1:A10) returns the value from a cell within the range A1:A10 that shares the same row as the cell that the formula is executed on.
TABLE
The TABLE function is exclusive to jspreadsheet and returns the jspreadsheet object instance.
TABLE()
Example: TABLE() returns the jspreadsheet object instance.
THISROWCELL
The THISROWCELL function retrieves the value of a cell located in the same row as the calling cell, specified by the column index.
THISROWCELL(column_index)
column_index: The numeric index of the column (e.g., 1 for the first column, 2 for the second column) to identify the cell to retrieve the value from within the same row as the calling cell.
Example: THISROWCELL(2) retrieves the value from the cell in the second column on the same row as the calling cell.
NULL
The NULL function takes no parameters and returns a null value.
NULL()
Example: NULL() returns a null value.
VAL
The VAL function retrieves a value from specific coordinates within a dataset, allowing optional processing based on the 'processed' parameter.
VAL(column_index, row_index, processed)
•column_index: The numeric index of the column from which to retrieve the value.
•row_index: The numeric index of the row from which to retrieve the value.
•processed: A boolean value (true/false) indicating whether to process the retrieved value (e.g., apply calculations or evaluate formulas) before returning it.
Example: VAL(2, 3, false) retrieves the raw value from the cell located in the second column and third row without processing.
NE
Returns if value1 is not equal to value2
NE(value1, value2)
•value1: The first value to evaluate.
•value2: The second value to evaluate.
Example: NE(1, 2) returns TRUE NE(2, 2) returns FALSE.
UMINUS
Returns the negation of a number.
UMINUS(number)
number: The number to be negated.
Example: UMINUS(5) returns -5 UMINUS(-10) returns 10 UMINUS(A1) returns the negation of the value in cell A1.
POW
Returns the result of raising a number to a power.
POW(number, power)
•number: The number to raise to the power.
•power: The power to raise the number to.
Example: POW(2, 3) returns 8 POW(10, 2) returns 100.
ISBETWEEN
Returns true if a value is between a lower and upper bound, inclusive.
ISBETWEEN(value, lower_bound, upper_bound)
•number: The value to check if it is between the lower and upper bounds.
•number: The lower bound of the range.
•number: The upper bound of the range.
Example: ISBETWEEN(5, 1, 10) returns true ISBETWEEN(15, 1, 10) returns false.
ADD
Returns the sum of two numbers.
ADD(number1, number2)
•number: The first number to add.
•number: The second number to add.
Example: ADD(5, 10) returns 15.
EQ
Determines if two values are equal.
EQ(value1, value2)
•value1: The first value to compare.
•value2: The second value to compare.
Example: EQ(5, 5) returns true EQ(10, 5) returns false EQ(A2, B2) returns true if the values in cells A2 and B2 are equal.
GT
Determines whether the first value is greater than the second.
GT(value1, value2)
•value1: The first value to compare.
•value2: The second value to compare.
Example: GT(5, 5) returns false GT(10, 5) returns true GT(5, 10) returns false.
LT
Determines whether the first value is less than the second.
LT(value1, value2)
•value1: The first value to compare.
•value2: The second value to compare.
Example: LT(5, 5) returns false LT(10, 5) returns false LT(5, 10) returns true.
LTE
Determines whether the first value is less than or equal to the second.
LTE(value1, value2)
•value1: The first value to compare.
•value2: The second value to compare.
Example: LTE(5, 5) returns true LTE(10, 5) returns false LTE(5, 10) returns true.
REGEXEXTRACT
Extracts parts of a text using a regular expression.
REGEXEXTRACT(text, regex)
•text: Text from which the parts will be extracted.
•regex: Regular expression that dictates which parts should be extracted.
Example: REGEXEXTRACT("The price today is $826.25", "[0-9]+.[0-9]+[0-9]+") returns "826.25".
REGEXREPLACE
Replaces parts of a text using a regular expression.
REGEXREPLACE(text, regex, replacement)
•text: Text in which replacements occur.
•regex: Regular expression that dictates which parts should be replaced.
•replacement: Text that is inserted into the text of the first argument.
Example: REGEXREPLACE("The price today is (price)", "\(price\)", "$10") returns "The price today is $10".
REGEXMATCH
Determines whether a text matches a regular expression.
REGEXMATCH(text, regex)
•text: Text that is tested with the regular expression.
•regex: Regular expression used to test the text.
Example: REGEXMATCH("Price: $10.50","[0-9]+.[0-9]+$") returns true REGEXMATCH("Price: $10","[0-9]+.[0-9]+$") returns false.
DIVIDE
Returns the division result of two numbers.
DIVIDE(number1, number2)
•number1: The dividend.
•number2: The divisor.
Example: DIVIDE(10, 2) returns 5 DIVIDE(A2, B2) returns the division result of the values in cells A2 and B2.
COUNTUNIQUE
Returns the number of unique values in a range of cells.
COUNTUNIQUE(value1, [value2], ...)
•value: The first value or range to count unique values from.
•value: Optional.Additional values or ranges to count unique values from.
Example: COUNTUNIQUE(1, 2, 3, 4, 5) returns 5 COUNTUNIQUE(A2:A6) returns the number of unique values in cells A2 through A6 COUNTUNIQUE(B2:B6, D2:D6) returns the number of unique values in both ranges B2 through B6 and D2 through D6.
UNARY_PERCENT
Converts a number into a percentage fraction by dividing it by 100.
UNARY_PERCENT(x)
x: The number to convert to a percentage fraction.
Example: UNARY_PERCENT(10) returns 0.1 UNARY_PERCENT(200) returns 2.
UPLUS
Returns the specified number, unchanged.
UPLUS(number)
number: The number to be returned.
Example: UPLUS(5) returns 5 UPLUS(-7) returns -7.
SPLIT
Splits a text string into a table of substrings based on a delimiter.
SPLIT(text, delimiter)
•text: The text string to be split.
•delimiter: The character or characters that separate the substrings in the text string.
Example: SPLIT('apple,banana,orange', ',') returns ['apple', 'banana', 'orange'] SPLIT('Hello world', ' ') returns ['Hello', 'world'].
MULTIPLY
Returns the product of two numbers.
MULTIPLY(factor1, factor2)
•factor1: First number to multiply.
•factor2: Second number to multiply.
Example: MULTIPLY(5, 10) returns 50 MULTIPLY(10, -1) returns -10.