Excel Formulas and Functions

Formula: CELL(“filename”,A1)This example returns the file name of the workbook containing the cell A1.

Formula: ERROR.TYPE(#VALUE!)This will return the number 2, which corresponds to the #VALUE! error.

Formula: INFO(“numfile”)This example returns the number of currently open files.

Formula: ISBLANK(A1)This formula will return TRUE if cell A1 is empty, and FALSE if it is not.

Formula: ISERR(A1)In this example, the ISERR function will return TRUE if the value in cell A1 is an error value, and FALSE if it is not.

Formula: ISERROR(A1)

Formula: ISEVEN(2) will return TRUE because 2 is an even number.

Formula: To check if cell A1 contains a formula, the following formula can be used:=ISFORMULA(A1)

Formula: ISLOGICAL(TRUE) returns TRUE

Formula: ISNA(A1)If A1 contains the #N/A error value, then the ISNA function will return TRUE. Otherwise, it will return FALSE.

Formula: ISNONTEXT(“Hello”)This will return FALSE as “Hello” is text.

Formula: ISNUMBER(A1) will return TRUE if A1 contains a number, and FALSE if A1 does not contain a number.

Formula: ISODD(3)This will return TRUE because 3 is an odd number.

Formula: ISOMITTED(A1)

Formula: ISREF(A1) returns TRUE if A1 is a valid cell reference, FALSE if not.

Formula: ISTEXT(A1)This formula will return TRUE if the value in cell A1 is text and FALSE if not.

Formula: To calculate the number of periods for an investment of $1000 with a payment of $50 and an interest rate of 5%:NPER(5%, -50, 1000)Result: 20

Formula: NA()

Formula: SHEET(A1)This would return the sheet number of the cell A1.

Formula: SHEETS(“Sheet1:Sheet3”)This would return the number 3, since the reference includes three sheets.

Formula: TYPE(A1)If cell A1 contains the value “Hello”, the result of the function would be 1, indicating that the value is a text string.

Formula: ASC(“A”)The result of this formula would be 65, which is the ASCII code for the letter A.

Formula: ARRAYTOTEXT({1,2,3,4,5},”,”,”‘”)Result: ‘1’,’2′,’3′,’4′,’5′

Formula: BAHTTEXT(12345)Result: ???????????????????????????????????????

Formula: CHAR(65)This will return the character “A”.

Formula: CLEAN(“Hello!@#$%^&*World”) Result: HelloWorld

Formula: CODE(“A”) returns 65.

Formula: CONCAT(“Hello “, “World”) , Result: Hello World

CONCAT(A1:A5), concatenated all information in the RangeA1:A5

Formula: CONCATENATE(“Hello”,” “,”World”) Result: Hello World

Formula: DBCS(“customers”,”name”,”state=’CA’”)

Formula: DOLLAR(A2, 2)This example will convert the number in cell A2 to text, using two decimal places.

Formula: EXACT(“Apple”,”apple”)Result: FALSE

Formula: FIXED(123.4567,2,TRUE)Result: 123.46

Formula: :LEFT(“Excel”, 2)Result: ExLEFTB:The LEFTB function in Excel returns the leftmost characters from a text string based on the number of bytes.
Syntax: LEFTB(text, [num_bytes])text: The text string from which you want to extract the characters.num_bytes: [optional] The number of bytes you want to extract from the left of the text string.
Example:=LEFTB(“Excel”, 3)Result: Exc”

Formula: LEN(“Hello World”)Result: 11LENBs:Description: The LENBs function is used to count the number of bytes in a text string.Syntax: LENBs(text)LENBs(“Hello World”)Result: 11

Formula: RIGHT(“Excel Sheet”, 5)Result:Sheet

Formula: LOWER(“EXCEL FUNCTION”)Result: excel function

Formula: NUMBERVALUE(“$1,234.56”)This would return 1234.56.

Formula: PHONETIC(“Excel”)Result: “ECKS-ELL”

Formula: PROPER(“this is a test”)Result: This Is A Test

Formula: REPT(“Hello”, 3)Result: HelloHelloHello

Formula: SUBSTITUTE(A2,”red”,”blue”)This example would replace all instances of “red” with “blue” in the text string in cell A2.

Formula: T.TEST(A1:A10, B1:B10, 2, 2)This example would calculate the probability associated with a two-sample t-test with unequal variances using the data in range A1:A10 and B1:B10.

Formula: TEXT(A1,”$#,##0.00″)

Formula: TEXTAFTER(“Hello World”,”o”)Result: ” World”

Formula: TEXTBEFORE(“www.example.com”,”.”)Result: www

Formula: TEXTJOIN(“, “, TRUE, B2:B4)This example combines the text from cells B2, B3, and B4, and includes a comma and space between each value. The “TRUE” argument ignores any empty cells in the range.

Formula: TEXTSPLIT(“Apple,Banana,Orange”,”,”)This formula will return the result “Apple, Banana, Orange”.

Formula: TRIM(” This is a test “)Result: “This is a test”

Formula: UNICHAR(65)This will return the character “A” in the cell.

Formula: UNICODE(“A”)Result: 65

Formula: UPPER(“Hello World”)Result: HELLO WORLD

Formula: VALUE(“2”) returns 2

Formula: VALUETOTEXT(A1,”$#,##0.00″)This example will convert the numerical value in cell A1 into a text string in the format “$#,##0.00”.

Formula: DATE(2021,5,1) will return the date 5/1/2021.

Formula: To calculate the number of days between two dates, the syntax would be:DATEDIF(A1,B1,”d”)

Formula: DATEVALUE(“1/1/2020”)This example would return the serial number 43831, which is the number that Excel recognizes as January 1, 2020.

Formula: DAYS(DATE(2020,10,1),DATE(2020,9,1))This example returns the number of days between October 1st, 2020 and September 1st, 2020, which is 30 days.

Formula: DAYS(A2,A1) where A1 contains the start date and A2 contains the end date.

Formula: DAYS360(A2,B2)This formula will calculate the number of days between the dates in cells A2 and B2.

Formula: EDATE(TODAY(), 3)This formula will return a date that is 3 months after the current date.

Formula: EOMONTH(A2, 3) where A2 contains a date value. This will return the last day of the month 3 months after the date in A2.

Formula: HOUR(A2) returns the hour part of the time in cell A2.

Formula: ISOWEEKNUM(A2)where A2 contains a date value.

Formula: MINUTE(A2) where A2 contains the time 10:15 AM. The result would be 15.

Formula: MONTH(A2)In this example, the function will return the month number of the date in cell A2.

Formula: NETWORKDAYS(A2,A3)This example would calculate the number of working days between the dates in cells A2 and A3.

Formula: NETWORKDAYS.INTL(A1,B1,11,C1:C5)This example calculates the number of workdays between the dates in A1 and B1, with weekends set to Saturday and Sunday (11), and holidays in the range C1:C5.

Formula: NOW()This formula will return the current date and time, for 2/26/2020 4:54:00 PM

Formula: SECOND(A1) where A1 contains the time value 09:15:30, the result would be 30.

Formula: TIME(14,30,45) returns the value 14:30:45.

Formula: TIMEVALUE(“10:30 AM”) will return 0.4375.

Formula: TODAY()This will return the current date in the cell.

Formula: WEEKDAY(A2, 1)Where A2 is a cell containing a date, the WEEKDAY function will return a number from 1-7 corresponding to the day of the week. For example, if A2 contains the date “2/14/2021”, the WEEKDAY function will return 4, corresponding to Wednesday.

Formula: WEEKNUM(A1,1)This example will return the week number of the date in cell A1 using the return type of 1 (week starts on Sunday).

Formula: WORKDAY(DATE(2020,1,1), 10)This example returns the date 10 workdays after January 1, 2020.

Formula: WORKDAY.INTL(DATE(2020, 1, 1), 10, 11, A2:A10)This example returns the date 10 workdays after January 1, 2020, taking into account weekends 11 (Saturday and Sunday) and holidays specified in cells A2:A10.

Formula: YEAR(A1) where A1 is a cell containing a date value. The result would be the four-digit year of the date in A1.

Formula: YEARFRAC(B2,C2,1)This example will calculate the fraction of the year between the dates in cells B2 and C2, using the US (NASD) 30/360 day count basis.

Formula: BYCOLUMN(A1:C10, 2, TRUE)This example will sort the range A1:C10 by the second column in ascending order.

Formula: If the range A1:C3 contains the following numbers:A1: 1A2: 2A3: 3B1: 4B2: 5B3: 6C1: 7C2: 8C3: 9Then the formula BYROW(A1:C3, 2) will return the array {2, 5, 8}.

Formula: FALSE()This formula will return the value FALSE.

Formula: IF(A2>B2, “A is greater than B”, “B is greater than A”)

Formula: IFERROR(A1/B1, 0)

Formula: IFNA(0,A1/A2)

Formula: IFS(A1=1, “Yes”, A1=2, “No”, A1=3, “Maybe”)This formula will check the value in cell A1 and return “Yes” if it is equal to 1, “No” if it is equal to 2, and “Maybe” if it is equal to 3.

Formula: LAMBDA(x, y, x+y)This function adds two arguments (x and y) and returns the sum of the two.

Formula: MAKEARRAY(1,2,3,4)This will return an array of {1;2;3;4}.

Formula: MAP(2, {1,2,3}, {10,20,30})This example would return 20 as the result.

Formula: NOT(TRUE) returns FALSE.

Formula: OR(A1=”Yes”, B1=”Yes”)

Formula: REDUCE(SUM, A1:A10, 0)This example will return the sum of the values in the range A1:A10, starting with an initial value of 0.

Formula: SCAN(“Hello World”, ” “, 1, 4)The example above will return “Hello” as the text string from the start position of 1 to the character found, which is a space.

Formula: SWITCH(A1,”A”,1,”B”,2,”C”,3,”D”,4,0)In this example, the SWITCH function will evaluate the value in cell A1 and return a result based on the following conditions:If A1 = “A”, the result is 1If A1 = “B”, the result is 2If A1 = “C”, the result is 3If A1 = “D”, the result is 4If none of the conditions are met, the result is 0

Formula: TRUE()This formula will return the logical value TRUE.

Formula: XOR(TRUE, FALSE, TRUE)This example would return TRUE.

Formula: ADDRESS(2,3,4,TRUE,”Sheet1″)This example will return the address of cell C4 (row 2, column 3) on Sheet1 as $C$4.

Formula: AREAS(A1:B2)This formula will return the value 2, since the range A1:B2 contains two areas.

Formula: CHOOSE(2, “red”, “blue”, “green”)This example would return “blue” as the output.

Formula: CHOOSECOLS(A1:F10, “>50”)This example will return an array of column numbers from A1:F10 that contain values greater than 50.

Formula: CHOOSEROWS(A1:A10, “>5”)This will return an array of rows from A1:A10 where the value is greater than 5.

Formula: COLUMN(A1)This will return the value 1, since A1 is the first column in the worksheet.

Formula: COLUMNS(A1:B2)This example would return 2, since the range A1:B2 contains two columns.

Formula: DROPDOWN(A1:A5, 2, “Select an Option”, “Choose an Option”)

Formula: EXPAND(A1,2,2)This would expand the range of cells from A1 to C2.

Formula: FILTER(A2:C10, B2:B10=”Yes”, “No matches”)This example will return the values in column A that correspond to the rows in column B that contain the value “Yes”. If no matches are found, it will return “No matches”.

Formula: FORMULATEXT(A1)This example would return the formula in cell A1 as text.

Formula: GETPIVOTDATA(“Sales”,A2,”Region”,”West”)

Formula: HLOOKUP(B2,A2:D7,3,FALSE)In this example, the HLOOKUP function searches for the value in cell B2 in the first row of the table (A2:D7). It then returns the corresponding value in the third row of the same column. The range_lookup argument is set to FALSE to ensure an exact match is found.

Formula: HSTACK({1,2,3}, {4,5,6}, {7,8,9}) returns {1,2,3,4,5,6,7,8,9}

Formula: HYPERLINK(“www.google.com”, “Google”)This will create a link to Google’s website with the friendly name “Google”.

Formula: INDEX(A1:C3, 2, 3)This formula will return the value at the intersection of the second row and third column of the range A1:C3.

Formula: INDIRECT(“A1”) returns the value of cell A1.

Formula: LOOKUP(A2,B2:B6,C2:C6)

Formula: MATCH(A1,B1:B10,0)This example looks for the value in cell A1 in the range of cells B1:B10. The match_type is set to 0, which means that Excel will find an exact match for the value in A1. The function will return the relative position of the value in A1 in the range B1:B10.

Formula: OFFSET(B2, 2, 3, 2, 1)This will return the range C4:C5, which is two rows and three columns away from cell B2.

Formula: ROW(A1) returns 1.

Formula: ROWS(A1:B10)This will return the number 10, as there are 10 rows in the range A1:B10.

Formula: RTD(“MSFTQuote.RTD”,,”MSFT”)

Formula: SORT(A2:D6, 2, 1, TRUE)This example will sort the range of data in A2:D6 by the second column in ascending order.

Formula: SORTBY(A2:F6, 2, 1, 5, -1)This example will sort the range A2:F6 based on the values in column 2 in ascending order and the values in column 5 in descending order.

Formula: TAKE(“Excel”,3)Result: Exc

Formula: TOCOL(“A1”) returns 1

Formula: TOROW(A1)This example returns the row number of cell A1, which is 1.

Formula: TRANSPOSE(A1:C3)This example would take the range of cells from A1 to C3 and transpose them so that the rows become columns and the columns become rows.

Formula: UNIQUE(A1:A10)This will return a list of unique values from the range A1:A10.

Formula: VLOOKUP(A2, C2:E5, 3, FALSE)This example looks up the value in cell A2 in the leftmost column of the table in cells C2 to E5 and returns the value in the same row from the third column of the table.

Formula: VSTACK(A1:A5, B1:B5)

Formula: WRAPCOLS(A1, 3)This will wrap the contents of cell A1 across 3 columns.

Formula: WRAPROWS(A1)

Formula: XLOOKUP(A2, B2:E5, 3, “Not Found”, 0, 1)In this example, the lookup_value is A2, the lookup_array is B2:E5, the return_column_number is 3, the not_found value is “Not Found”, the match_mode is 0 and the search_mode is 1.

Formula: XMATCH(“Apple”, {“Orange”, “Banana”, “Apple”, “Grape”}, 0)This example will return 3, since “Apple” is the third item in the array.

Formula: ABS(-5)Result: 5

Formula: To find the arccosine of 0.5, the formula would be:=ACOS(0.5)The result would be 1.0471975511966 radians.

Formula: ACOSH(3)The result of this formula is 1.762747174039086.

Formula: ACOT(0.5)This formula returns 1.10714871779409.

Formula: ACOTH(2)This formula returns 0.5493061443340548.

Formula: AGGREGATE(3,6,A1:A10)This formula will calculate the average of the values in the range A1:A10.

Formula: ARABIC(“XVII”) returns 17

Formula: ASIN(0.5)This will return the arcsine of 0.5, which is equal to 0.5235987755982988.

Formula: ASINH(2)This formula returns 1.4436354751788.

Formula: ATAN(1) returns 0.785398163397448

Formula: ATAN2(2, 3)This example returns the arctangent of 2 and 3, which is 0.982793723247329 radians or 56.3099324740202 degrees.

Formula: ATANH(0.5)This example would return 0.5493061443340548, which is the inverse hyperbolic tangent of 0.5.

Formula: BASE(10101, 2, 8)This example would convert the binary number 10101 to its decimal equivalent, 21. The min_length argument is optional and is used to pad the result with zeros to the specified length.

Formula: CEILING(A2,0.1)This formula will round the value in cell A2 up to the nearest multiple of 0.1.

Formula: CEILING.MATH(4.2, 0.5, 1)This example returns 4.5, as it rounds 4.2 up to the nearest multiple of 0.5, which is 4.5.

Formula: CEILING.PRECISE(14.8, 0.1)Result: 14.9

Formula: If you want to calculate the number of combinations of 5 objects taken 3 at a time, the formula would be COMBIN(5,3). The result of this formula would be 10.

Formula: COMBINA(6,3)This example returns the number of combinations possible when selecting 3 items from a set of 6 items. The result is 20.

Formula: COS(PI()) returns -1, which is the cosine of PI radians.

Formula: COSH(2) returns 3.7621956910836

Formula: COT(PI()/4)This example would return the cotangent of pi/4, which is equal to 1.

Formula: COTH(0.5) returns 1.31

Formula: CSC(PI()/4)This will return the value of 1.4142135623731, which is the complementary sine of PI/4.

Formula: CSCH(2)Result: 0.275720564771759

Formula: DECIMAL(1011,2)This example would return the decimal value of 11.

Formula: DEGREES(3.14159265358979)Result: 180

Formula: EVEN(3.2)Result: 4

Formula: EXP(2)Result: 7.38905609893065

Formula: FACT(5)This formula will return 120, which is the factorial of 5 (5x4x3x2x1).

Formula: To calculate the double factorial of 8, the formula would be =FACTDOUBLE(8). The result would be 8 x 6 x 4 x 2 = 384.

Formula: FLOOR(4.7, 0.5)The result of this formula is 4.5.

Formula: FLOOR.MATH(12.5, 0.5)Result: 12

Formula: FLOOR.PRECISE(3.14159, 0.01)The result of this example is 3.14.

Formula: GCD(12, 18)The result of this formula is 6, as 6 is the greatest common divisor of 12 and 18.

Formula: INT(3.14)Result: 3

Formula: ISO.CEILING(3.14,0.1)Result: 3.2

Formula: LCM(2,3,4)This example would return 12, as 12 is the smallest number that 2, 3, and 4 all have in common.

Formula: Let(A, 10+5)This assigns the value 15 to the name A.

Formula: To calculate the natural logarithm of 10, the formula would be:=LN(10)The result would be 2.302585092994046.

Formula: LOG(8,2)This example returns the logarithm of 8 to the base of 2, which is 3.

Formula: LOG10(100)The result of this formula is 2, as 100 is equal to 10^2.

Formula: MDETERM({1,2;3,4})The result of this formula is -2, which is the determinant of the given matrix.

Formula: MINVERSE({1,2;3,4})Result: {-2,1;1.5,-0.5}

Formula: MMULT(A1:B2,C1:D2)

Formula: MOD(15,4)This will return a result of 3, as 15 divided by 4 is 3 with a remainder of 3.

Formula: MROUND(7,2)The result of this formula is 8.

Formula: MULTINOMIAL(2,3,4)This example returns the multinomial coefficient of 2, 3 and 4, which is 120.

Formula: MUNIT(10)This will return the unit of measure associated with 10, which is “none”.

Formula: ODD(8.2)Result: 9

Formula: PI()Result: 3.14159265358979

Formula: POWER(2,3)This will return 8, as 2 to the power of 3 is 8.

Formula: PRODUCT(2,3,4,5)Result: 120

Formula: QUOTIENT(10,3) returns 3

Formula: RADIANS(45)This function would return the result 0.78539816339745.

Formula: RAND()Result: 0.838592582

Formula: RANDARRAY(2,3,1,10)This will generate a 2×3 array of random numbers between 1 and 10.

Formula: RANDBETWEEN(1,10)This will generate a random number between 1 and 10.

Formula: ROMAN(10)Result: X

Formula: ROUND(3.14159, 2)This example would return 3.14.

Formula: ROUNDDOWN(2.567,2)Result: 2.56

Formula: ROUNDUP(3.14159, 2) returns 3.15

Formula: SEC(45)This will return the secant of 45 degrees, which is 1.4142135623731.

Formula: SECH(1)The result of this formula is 0.85091812823932.

Formula: To calculate the series sum for x = 5, n = 0, m = 5 and coefficients = 1, 2, 3, 4, 5, 6, the formula would be:=SERIESSUM(5, 0, 5, 1,2,3,4,5,6)The result of this formula is 441.

Formula: SEQUENCE(3,2,1,2)This would generate the following array:{1,3; 5,7; 9,11}

Formula: SIGN(5)This formula will return 1, since 5 is a positive number.

Formula: SIN(45)Result: 0.70710678118

Formula: SINH(2) returns 3.626860407847019

Formula: SQRT(9)Result: 3

Formula: SQRTPI(2)The result of this formula is 2.506628274631.

Formula: SUBTOTAL(9,A2:A10)This example would calculate the sum of the range A2:A10.

Formula: SUM(A1:A5)

Formula: SUMIF(A2:A10,”>20″,B2:B10)This formula will sum all values in B2:B10 that are greater than 20, based on the corresponding values in A2:A10.

Formula: To sum the values in cell range A1:A10, if the corresponding values in cell range B1:B10 are greater than 5, the formula would be:=SUMIFS(A1:A10,B1:B10,”>5″)

Formula: SUMPRODUCT(A1:A5, B1:B5)

Formula: SUMSQ(2,3,4)This example will return the sum of the squares of 2, 3, and 4, which is 29.

Formula: SUMX2MY2({1,2,3},{4,5,6})This formula will calculate the sum of the squares of the differences between the two arrays, which is equal to 54.

Formula: SUMX2PY2(A1:A5,B1:B5)This will return the square root of the sum of the squares of the values in A1:A5 and B1:B5.

Formula: SUMXMY2(A1:A5,B1:B5)

Formula: TAN(45) returns the value 1.

Formula: TANH(0.5)The result of this formula is 0.4621171572600098

Formula:=TRUNC(3.14159,2)Result: 3.14

Formula: AVEDEV(2,4,6,8)The result of this function is 2, which is the average of the absolute deviations of 2, 4, 6, and 8 from their mean of 5.

Formula: AVERAGE(10,20,30,40)Result: 25

Formula: AVERAGEA(1,2,3,4,5)Result: 3

Formula: AVERAGEIF(A2:A9, “>50”, B2:B9)This formula will return the average of all numbers in range B2:B9, where the corresponding cell in range A2:A9 is greater than 50.

Formula: To calculate the average of the numbers in the range A1:A10, if the corresponding cells in the range B1:B10 contain the value “apple”, the formula would be: AVERAGEIFS(A1:A10, B1:B10, “apple”)

Formula: To calculate the cumulative beta probability density function for a given set of parameters x = 0.5, alpha = 2, beta = 3, and cumulative = TRUE, the following formula is used:=BETA.DIST(0.5, 2, 3, TRUE)The result of this formula is 0.7421875.

Formula: BETA.INV(0.7, 2, 5, 0, 1)This example returns the value 0.837, which is the inverse of the cumulative distribution function for the specified beta distribution with a probability of 0.7, alpha of 2, and beta of 5.

Formula: BINOM.DIST(2, 10, 0.5, FALSE)This example returns the probability of 2 successes in 10 independent trials, each with a probability of success of 0.5.

Formula: To calculate the probability of getting between 2 and 4 successes in a sequence of 5 independent Bernoulli trials, with a probability of success of 0.4 in each trial, the formula would be: BINOM.DIST.RANGE(5, 0.4, 2, 4)

Formula: Suppose you want to find the number of successes given a probability of 0.5 and 10 trials. The formula would be:=BINOM.INV(0.5,10,0.5)The result would be 5, meaning that there is a 50% chance of 5 successes in 10 trials.

Formula: CHISQ.DIST(4,3,TRUE)This example returns the cumulative probability of a chi-squared distribution with 4 degrees of freedom. The result is 0.2266.

Formula: CHISQ.DIST.RT(3,4)This example returns the right-tailed probability of the chi-squared distribution with 3 degrees of freedom. The result is 0.0682689492137086.

Formula: CHISQ.INV(0.95,2)This example returns the inverse of the left-tailed probability of the chi-squared distribution with a probability of 0.95 and 2 degrees of freedom. The result is 5.9915.

Formula: CHISQ.INV.RT(0.95,2)This example returns the inverse of the right-tailed probability of the chi-squared distribution with a probability of 0.95 and a degree of freedom of 2. The result is 5.991464547.

Formula: CHISQ.TEST(A1:B10, C1:D10)

Formula: To calculate the 95% confidence interval for a sample of 100 observations with a standard deviation of 10, the formula would be:1.95996398454005The result would be a margin of error of 2.262.

Formula: To calculate the 95% confidence interval for a sample mean of 10, with a standard deviation of 4, and a sample size of 25, the formula would be: CONFIDENCE.T(0.05, 4, 25) which would return a result of 8.8 to 11.2.

Formula: CORREL(A1:A10, B1:B10)This example returns the correlation coefficient of the values in cells A1 through A10 and B1 through B10.

Formula: COUNT(A1:A6, C1:C3), This example counts numbers across multiple ranges, say A1:A6 and C1:C3 where C1 to C3 also contain numbers.

Formula: COUNTA(A1:A10)This example will count the number of cells in the range A1:A10 that contain data.

Formula: COUNTBLANK(A1:A10)This formula would count the number of blank cells in the range A1 to A10.

Formula: COUNTIF(A1:A10, “>5”)This example will count the number of cells in range A1:A10 that are greater than 5.

Formula: COUNTIFS (A1:A10, “>20”, B1:B10, “>30”)This example will count the number of cells in the range A1:A10 that are greater than 20, and the number of cells in the range B1:B10 that are greater than 30.

Formula: COVARIANCE.P(A2:A7,B2:B7)This example returns the population covariance of the values in cells A2 through A7 and the values in cells B2 through B7.

Formula: COVARIANCE.S(A1:A5,B1:B5)This example will calculate the sample covariance of the two sets of values in the range A1:A5 and B1:B5.

Formula: DEVSQ(2, 3, 4, 5)This example would return the sum of squares of deviations of the data points 2, 3, 4, and 5 from their sample mean, which is 3.5. The result would be 4.5.

Formula: EXPON.DIST(2,1,TRUE)This formula will return the probability that a random variable, which follows an exponential distribution with rate parameter 1, is less than or equal to 2.

Formula: F.DIST(2,2,4)This example returns the probability of a value of 2 occurring given 2 and 4 degrees of freedom.

Formula: F.DIST.RT(4,5,6)This example will return the probability that the observed variance in a sample is greater than 4, when the degrees of freedom for the sample and the population are 5 and 6, respectively.

Formula: F.INV(0.05, 5, 10)This example returns the x-value associated with a probability of 0.05, with 5 degrees of freedom in the numerator and 10 degrees of freedom in the denominator.

Formula: F.INV.RT(0.95,2,3)This example returns the inverse cumulative distribution for a probability of 0.95 with two degrees of freedom in the numerator and three degrees of freedom in the denominator. The result is 6.867.

Formula: F.TEST(A1:A10, B1:B10)This function returns the probability that the two sets of data in cells A1:A10 and B1:B10 have the same variance.

Formula: FISHER(0.5) returns 0.279415498198926

Formula: FISHERINV(0.9) returns 0.47140452079103

Formula: FORECAST(4,A1:A4,B1:B4)This example uses the FORECAST function to calculate the future value of 4 based on the existing values in cells A1-A4 and B1-B4.

Formula: FORECAST.ETS(A1:A10, B1:B10, 11, 0, 1, 95)This example uses the values in cells A1 to A10 and B1 to B10 to predict the value in cell A11 using the Exponential Triple Smoothing (ETS) algorithm with a trend of 1 and a confidence of 95%.

Formula: FORECAST.ETS.CONFINT(B2:B13,C2:C13,90%)This example will return a 90% confidence interval for the forecast generated by the FORECAST.ETS function using the known y values in the range B2:B13 and the known x values in the range C2:C13.

Formula: FORECAST.ETS.SEASONALITY(B2, B3:B12, 12, 1, 0)This example uses the FORECAST.ETS.SEASONALITY function to predict the value in cell B2 based on the values in cells B3:B12. The seasonality is set to 12, data completion is set to 1, and aggregation is set to 0.

Formula: FORECAST.ETS.STAT(A1:A10, B1:B10, 11, “stat”, 12, “average”)

Formula: To predict the sales for the month of April based on the existing sales data for the months of January, February and March, the following formula can be used:FORECAST.LINEAR(4, B2:B4, A2:A4)Where A2:A4 contains the months of January, February and March, and B2:B4 contains the sales figures for those months.

Formula: FREQUENCY(A2:A8,B2:B4)This example will return a frequency distribution of the values in range A2:A8, using the range B2:B4 as the bins.

Formula: GAMMA(5)This formula will return the gamma value of 5.

Formula: GAMMA.DIST(2,2,2,TRUE)This example returns the cumulative gamma distribution for x=2, alpha=2, and beta=2. The result is 0.632120558828558.

Formula: To calculate the inverse of the gamma cumulative distribution with a probability of 0.5, an alpha of 2, and a beta of 3, the following formula would be used:=GAMMA.INV(0.5,2,3,TRUE)The result of this formula would be 3.

Formula: GAMMALN(7)The result of this example would be 1.94591.

Formula: GAMMALN.PRECISE(5)The result of this function would be the natural logarithm of the gamma function, G(5), to a high degree of precision.

Formula: GAUSS(1.2)This function returns the value of 0.8849.

Formula: GEOMEAN(2,3,4,5)This example would return 3.5, which is the geometric mean of the set of numbers (2, 3, 4, and 5).

Formula: GROWTH(B2:B6,A2:A6,A7:A10,TRUE)

Formula: HARMEAN(2,4,6)Result: 3.6

Formula: To calculate the probability of getting 3 successes in a sample of 10 from a population of 20, the formula would be: HYPGEOM.DIST(10, 20, 3, FALSE).

Formula: INTERCEPT(B2:B7,A2:A7)This formula returns the y-intercept of the linear regression line based on the data in cells A2:A7 and B2:B7.

Formula: KURT(2,4,6,8,10)This formula would return -1.2, which is the kurtosis of the given data set.

Formula: LARGE(A1:A10, 3)This example would return the third largest value from the range of values in cells A1 to A10.

Formula: LINEST(B2:B11,A2:A11,TRUE,TRUE)

Formula: LOGEST(A2:A10, B2:B10, TRUE, TRUE)This example will calculate an exponential curve that best fits the data points in cells A2:A10 and B2:B10, and return an array of values that describe the exponential curve, including the y-intercept, the slope, and the correlation coefficient.

Formula: LOGNORM.DIST(2, 3, 0.5, TRUE)This example returns the cumulative lognormal distribution of 2, given a mean of 3 and a standard deviation of 0.5.

Formula: LOGNORM.INV(0.5, 2, 0.5)This example returns the inverse of the lognormal cumulative distribution for a probability of 0.5, a mean of 2 and a standard deviation of 0.5. The result is 1.897.

Formula: MAX(A1:A10)This example returns the largest value from the range A1:A10.

Formula: MAXA(A1:A10)This example returns the maximum value in the range of cells A1 to A10.

Formula: MAXIFS(A1:A10, B1:B10, “>=10”, C1:C10, “<=20”)This formula returns the maximum value in the range A1:A10, where the values in the range B1:B10 are greater than or equal to 10 and the values in the range C1:C10 are less than or equal to 20.

Formula: MEDIAN(2, 4, 5, 7, 9)Result: 5

Formula: MIN(2,4,6,8)Result: 2

Formula: MINA(2,5,8,1,6)This will return 1, which is the minimum value in the array.

Formula: MINIFS(C2:C10,A2:A10,”>10″,B2:B10,”<20″)This formula will find the minimum value in the range C2:C10 where the values in A2:A10 are greater than 10 and the values in B2:B10 are less than 20.

Formula: MODE.MULT(A2:A10)This formula returns the most frequently occurring value in the range A2:A10.

Formula: MODE.SNGL(1,2,2,3,3,3,4,4,4,4)The result of this example would be 4, since 4 is the most frequently occurring value in the set.

Formula: NEGBINOM.DIST(4, 2, 0.5, FALSE)This formula returns the probability of having 2 successes in 4 independent Bernoulli trials with a probability of success of 0.5.

Formula: NORM.DIST(2,1,1,TRUE)This formula returns the cumulative normal distribution for a mean of 1 and a standard deviation of 1, with a value of 2. The result is 0.97725.

Formula: NORM.INV(0.5, 0, 1) The above formula will return a value of 0, which is the inverse of the standard normal cumulative distribution at the probability of 0.5.

Formula: To find the probability that a random variable with a normal distribution will be less than or equal to 2.5, use the formula =NORM.S.DIST(2.5, TRUE). The result is 0.993790.

Formula: To calculate the probability of a value being greater than 0.5, the formula would be NORM.S.INV(0.5). This would return 0.67448975019608.

Formula: PEARSON(A1:A10,B1:B10)

Formula: PERCENTILE.EXC(A1:A10, 0.5)This example will return the 50th percentile of the values in the range A1:A10.

Formula: PERCENTILE.INC(B2:B10, 0.5)This example would return the 50th percentile of the values in the range B2:B10.

Formula: PERCENTRANK.EXC(A2:A10, A7, 4)In this example, the PERCENTRANK.EXC function returns the rank of the value in cell A7 (which is 5) as a percentage of the values in the range A2:A10. The third argument, 4, specifies that the result should be calculated to four decimal places.

Formula: PERCENTRANK.INC(A2:A8,A4,4)In this example, the PERCENTRANK.INC function would return the rank of the value in cell A4 as a percentage of the data set in cells A2 to A8, with 4 decimal places of significance.

Formula: To calculate the number of permutations of 8 objects taken 3 at a time, the formula would be: =PERMUT(8,3)The result would be 336.

Formula: PERMUTATIONA(5,2)This example returns the number of permutations for 5 objects that can be selected from 2 objects, which is 10.

Formula: PHI(A1:A10, B1:B10)

Formula: POISSON.DIST(5,3,FALSE)This formula returns the probability of 5 events occurring in a given period of time when the rate of events is 3.

Formula: PROB(A1:A5,B1:B5,20,30)

Formula: QUARTILE.EXC(A1:A10,3)This example returns the third quartile of the data in cells A1 through A10.

Formula: The formula =QUARTILE.INC(A1:A10,2) returns the second quartile (median) of the data set in cells A1:A10.

Formula: RANK.AVG(B2,B2:B7,1)This example returns the rank of the number in cell B2 in the range B2:B7, in ascending order.

Formula: RANK.EQ (5, {1,2,3,5,6,7}, 0)The example returns 4, as 5 is the 4th number in the list.

Formula: RSQ(A1:A6,B1:B6)

Formula: SKEW(A2:A10)This function will return the skewness of the distribution of the values in cells A2 through A10.

Formula: SKEW.P(2,4,6,8,10)The result of this formula is 0, indicating that the data set is perfectly symmetrical.

Formula: SLOPE (A1:A10, B1:B10)

Formula: SMALL(A1:A10, 3)This example returns the 3rd smallest value from the range A1:A10.

Formula: STANDARDIZE(3, 5, 2)This example returns -1, which is the normalized value from a distribution with a mean of 5 and a standard deviation of 2.

Formula: STDEV.P(1,2,3,4,5)This example would return 1.58113883008419.

Formula: STDEV.S(2,4,6,8)This will return 2, the standard deviation of the sample set.

Formula: STDEVA(2,4,6,8,10)This example returns 2.58198889747161, which is the standard deviation of the given sample.

Formula: STDEVPA(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)This will return 2.87228132326901.

Formula: STEYX(A2:A6, B2:B6)

Formula: T.DIST(1.5,5,TRUE)This example returns the cumulative probability associated with a t-distribution with a value of 1.5 and 5 degrees of freedom.

Formula: To calculate the probability of obtaining a value of 2 or less in a t-distribution with 20 degrees of freedom, the formula would be =T.DIST.2T(2,20,TRUE). This will return the probability of 0.97725.

Formula: T.DIST.RT(2,5,TRUE)This example returns the cumulative probability of 2 for a t-distribution with 5 degrees of freedom.

Formula: T.INV(0.95, 10)This example returns the t-value of 0.816496580927726, which is the t-value for a 95% confidence interval with 10 degrees of freedom.

Formula: T.INV.2T(0.05,10)This example returns the two-tailed probability of the Student’s t-distribution with a probability of 0.05 and 10 degrees of freedom. The result is 2.228.

Formula: T.TEST(A1:A10, B1:B10, 2, 1)This example would calculate the two-tailed probability associated with the paired t-test of the data in the range A1:A10 and B1:B10.

Formula: TREND(A2:A5, B2:B5, B6, FALSE)This example would calculate the linear trend line for the data points in cells A2 to A5, using the corresponding x values in cells B2 to B5. It would then extend the trend line to the x value in cell B6. The FALSE argument specifies that the trend line should not include a constant.

Formula: TRIMMEAN(A2:A8, 0.2)This example would calculate the mean of the dataset in the range A2:A8, excluding the top and bottom 20% of the data points.

Formula: VAR.P(1,2,3,4,5)This example would return 2.5, which is the population variance of the set of values (1,2,3,4,5).

Formula: VAR.S(2, 4, 6, 8, 10)This example would calculate the sample variance of the population of numbers (2, 4, 6, 8, 10) and return 8.

Formula: VARA(5, 10, 15, 20)This example will return the variance of the numbers 5, 10, 15, and 20, which is 25.

Formula: VARPA(2,4,6,8,10)Result: 8

Formula: WEIBULL.DIST(3,2,4,TRUE)This formula will return the cumulative probability that a value is less than or equal to 3 in a Weibull distribution with a shape parameter of 2 and a scale parameter of 4.

Formula: Z.TEST(A2:A10, 7, 2)This example would calculate the one-tailed or two-tailed probability-value of a z-test using the data in cells A2 through A10, with a mean of 7 and a standard deviation of 2.

Formula: ACCRINT(“1/1/2020″,”2/1/2020″,”3/1/2020”,0.05,1000,2,0,0)This example calculates the accrued interest for a security with an issue date of 1/1/2020, first interest date of 2/1/2020, settlement date of 3/1/2020, interest rate of 5%, par value of 1000, and a frequency of 2 (semi-annually). The basis and calculation method are both set to 0.

Formula: ACCRINTM(“1/1/2019”, “1/1/2019”, “1/15/2019”, 0.06, 1000, 2, 0, 0)This example returns the amount of interest accrued on a bond with an issue date of 1/1/2019, first interest date of 1/1/2019, settlement date of 1/15/2019, annual interest rate of 6%, par value of $1,000, and semi-annual frequency, using the US (NASD) 30/360 day count basis and the price basis method.

Formula: AMORDEGRC(10000, DATE(2015,1,1), DATE(2015,4,1), 1000, 12, 4)This example returns the depreciation of an asset with a cost of 10,000 purchased on January 1, 2015, with a first period ending on April 1, 2015, a salvage value of 1,000, a period of 12, and a depreciation coefficient of 4.

Formula: To calculate the depreciation of an asset that cost $10,000, was purchased on 1/1/2020, and has a salvage value of $1,000 after 5 years, the following formula can be used:AMORLINC(10000, “1/1/2020”, “1/1/2020”, 1000, 5)

Formula: COUPDAYBS(“1/15/2020”, “7/15/2020”, 2, 0)This example returns the number of days from the beginning of the coupon period to the settlement date of January 15, 2020, with a maturity date of July 15, 2020 and a frequency of 2 (semi-annual). The basis is 0 (US (NASD) 30/360). The result is 91.

Formula: COUPDAYS(“9/15/2020”, “9/15/2021”, 2, 0)This example returns the number of days in the coupon period that contains the settlement date of 9/15/2020, with a maturity date of 9/15/2021, a frequency of 2 (semi-annual payments), and a basis of 0 (US (NASD) 30/360). The result is 180.

Formula: COUPDAYSNC(DATE(2020,8,15),DATE(2022,2,15),2,0)This example returns the number of days in the coupon period that contains the settlement date (15th August 2020) for a security with a periodic interest payment with a maturity date of 15th February 2022 and a frequency of 2 (semi-annual). The basis argument is optional and is set to 0 in this example.

Formula: COUPNCD(DATE(2020,1,1), DATE(2022,1,1), 2, 0)This example returns the next coupon date after the settlement date of January 1, 2020 for a security with a maturity date of January 1, 2022 and a frequency of 2 (semiannual) payments. The basis is 0 (US (NASD) 30/360).

Formula: COUPNUM(DATE(2020,1,1), DATE(2025,12,31), 2, 0)This example calculates the number of coupon payments between 1 January 2020 and 31 December 2025, assuming a frequency of 2 payments per year and a basis of 0 (US (NASD) 30/360). The result is 6.

Formula: COUPPCD(DATE(2021,1,1), DATE(2021,6,30), 2, 0)This example returns 3/1/2021, which is the next coupon payment date after the settlement date of 1/1/2021, with a maturity date of 6/30/2021, a frequency of 2 (semi-annual), and a basis of 0 (US (NASD) 30/360).

Formula: To calculate the cumulative interest paid on a loan of $10,000 at an annual interest rate of 5% over a period of 5 years, starting from the 2nd year, the formula would be:=CUMIPMT(0.05, 5, 10000, 2, 5, 0)The result would be $1,250.

Formula: To calculate the cumulative principal paid on a loan with an interest rate of 5%, a total number of periods of 10, a present value of $2000, a start period of 3 and an end period of 6, the formula would be:=CUMPRINC(5%, 10, 2000, 3, 6, 0)The result would be $600, indicating that $600 of the principal was paid between the 3rd and 6th periods.

Formula: DB(10000,1000,5,2)This example returns the depreciation of an asset with a cost of $10,000, a salvage value of $1,000, a life of 5 years, and a period of 2 years.

Formula: DDB(10000,1000,5,2,2)This example calculates the depreciation of an asset for the second period, given a cost of $10,000, a salvage value of $1,000, a life of 5 periods, and a factor of 2.

Formula: DISC(DATE(2020,6,15),DATE(2021,6,15),0.1,100,1)This example returns the discount rate for a security with a settlement date of June 15, 2020, a maturity date of June 15, 2021, a price of 0.1, and a redemption value of 100, using the US (NASD) 30/360 day count basis.

Formula: DOLLARDE(3/32, 32)Result: 0.09375

Formula: DOLLARFR(123.45,2)Result: 123,45 €

Formula: DURATION(DATE(2020,1,1),DATE(2021,1,1),2,0)This formula will return the number of days between January 1st, 2020 and January 1st, 2021, assuming a semi-annual frequency and a US (NASD) 30/360 day count basis. The result will be 365.

Formula: EFFECT(0.05, 12)This example would return 0.0514, which is the effective annual interest rate for a nominal interest rate of 5% and 12 compounding periods per year.

Formula: FV(0.06, 10, -100)This example returns the future value of an investment of $100 for 10 periods at an interest rate of 6%.

Formula: FVSCHEDULE(1000, {0.1, 0.1, 0.1, 0.1})This example returns the future value of an investment of $1000 with four periodic payments of 10% each. The result is $1,464.

Formula: INTRATE(DATE(2020, 1, 1), DATE(2021, 1, 1), 1000, 1100, 0)This example calculates the interest rate for a security that is invested on January 1, 2020, matures on January 1, 2021, and has an investment of $1000 and a redemption of $1100. The basis is set to 0.

Formula: To calculate the interest payment for the 3rd period of a loan with a rate of 5%, a present value of $1000 and a total of 10 periods, the formula would be:=IPMT(5%,3,10,1000)This would return a result of -$20.83, indicating that the interest payment for the 3rd period is $20.83.

Formula: IRR(A2:A7,0.1)This example uses the IRR function to calculate the internal rate of return for a series of cash flows in cells A2 to A7, with an initial guess of 0.1.

Formula: ISPMT(0.08, 3, 12, 10000)This example returns a result of -66.67, which is the interest paid during the 3rd period of an investment with a rate of 8%, 12 periods and a present value of 10,000.

Formula: MDURATION(DATE(2018,1,1),DATE(2022,1,1),0.06,0.05,2,0)This example returns the Macauley duration of a security with a settlement date of January 1, 2018, a maturity date of January 1, 2022, a coupon rate of 6%, an annual yield of 5%, and a frequency of semi-annual payments, using a US (NASD) 30/360 day count basis.

Formula: MIRR(B2:B6, 10%, 20%)In this example, the MIRR function returns the modified internal rate of return for the cash flows in the range B2:B6, with a 10% finance rate and a 20% reinvestment rate.

Formula: NOMINAL(0.1, 4)Result: 0.0943

Formula: To calculate the number of periods for an investment of $10,000 with a payment of $500 and an interest rate of 5%, the formula would be:=NPER(0.05, -500, 10000)The result is 18.

Formula: NPV(0.1, -1000, 500, 500, 500)This example would calculate the net present value of an investment with an initial investment of -1000, and three subsequent payments of 500 each at a rate of 0.1.

Formula: ODDFPRICE(DATE(2021,1,1),DATE(2021,7,1),DATE(2020,7,1),0.06,0.06,0.06,100,2,0)This example calculates the price per $100 face value of a security with a settlement date of January 1, 2021, a maturity date of July 1, 2021, an issue date of July 1, 2020, a first coupon of 0.06, a rate of 0.06, a yield of 0.06, a redemption value of 100, and a frequency of 2 (semi-annual). The basis is set to 0.

Formula: ODDFYIELD(DATE(2020,1,1),DATE(2023,1,1),DATE(2020,1,1),0.06,0.05,100,100,2,1)This example returns the odd yield of a security with a settlement date of January 1, 2020, a maturity date of January 1, 2023, an issue date of January 1, 2020, a first coupon of 0.06, a rate of 0.05, a price of 100, a redemption value of 100, and a frequency of 2 (semi-annually). The optional basis parameter is set to 1 (US (NASD) 30/360).

Formula: ODDLPRICE(“1/1/2020″,”1/1/2021”,0.05,0.06,100,2,1)This example returns the price per unit of an odd lot with a settlement date of 1/1/2020, a maturity date of 1/1/2021, a rate of 0.05, a yield of 0.06, a redemption value of 100, and a frequency of 2 (semi-annual), using the US (NASD) 30/360 day count basis.

Formula: ODDLYIELD(DATE(2021,1,1), DATE(2024,1,1), DATE(2020,7,1), DATE(2021,7,1), 0.05, 100, 100, 2, 0)This example calculates the yield of a security with a settlement date of 1/1/2021, maturity date of 1/1/2024, issue date of 7/1/2020, first coupon date of 7/1/2021, rate of 0.05, price of 100, redemption of 100, frequency of 2, and basis of 0.

Formula: Suppose you have a loan of $10,000 with a periodic payment of $200 and a future value of $0. The number of periods required to pay off the loan is calculated by using the PDURATION function as follows:=PDURATION(10000, 200, 0)The result is 50, meaning it will take 50 periods (months, years, etc.) to pay off the loan.

Formula: PMT(0.06/12, 60, 1000, 0, 0)This example calculates the monthly payment for a loan of $1000 at 6% interest for 60 months. The payment is $19.55.

Formula: To calculate the principal payment for the 3rd period of a loan with a 6% annual interest rate, a present value of $10,000, and a total of 5 periods, the formula would be:=PPMT(6%,3,5,10000,0,0)The result would be -$2,037.50, which is the principal payment for the 3rd period.

Formula: PRICE(DATE(2020,1,1), DATE(2022,1,1), 0.05, 0.06, 100, 2, 0)This example returns the price per $100 face value of a security that pays semiannual interest at a rate of 5%, with a yield of 6%, and a redemption value of $100. The settlement date is January 1, 2020, and the maturity date is January 1, 2022.

Formula: PRICEDISC(“2/2/2020”, “2/2/2021”, 0.05)This example would calculate the price of a security with a settlement date of 2/2/2020, a maturity date of 2/2/2021, and a discount rate of 0.05.

Formula: PRICEMAT(A1, A2, A3)where A1 = settlement date, A2 = maturity date, A3 = rate

Formula: To calculate the present value of an investment that will pay $1000 at the end of each year for 10 years, with an interest rate of 5%, the formula would be:=PV(5%, 10, 1000)The result would be: -$7,637.45

Formula: To calculate the interest rate per period for a loan of $2,000 to be paid back in 5 years with a payment of $400 per period, the formula would be:=RATE(5, -400, 2000)The result would be 0.05, indicating a 5% interest rate per period.

Formula: RECEIVED(A2,A3,A4,A5,A6)In this example, A2 is the settlement date, A3 is the maturity date, A4 is the investment amount, A5 is the discount rate, and A6 is the basis (optional).

Formula: RRI(5, 1000, 2000)This example calculates the rate of return on an investment of $1000 that will be worth $2000 after 5 periods. The result is 0.1487, or 14.87%.

Formula: SLN(1000,200,5)This example would calculate the straight-line depreciation of an asset with a cost of 1000, a salvage value of 200, and a life of 5 years. The result would be 160.

Formula: SYD(10000, 1000, 5, 3)This example returns the sum-of-years’ digits depreciation of an asset with a cost of $10,000, a salvage value of $1,000, and a useful life of 5 years for the 3rd period. The result is $2,400.

Formula: TBILLEQ(DATE(2020,1,1), DATE(2020,2,1), 0.05)This example would calculate the bond-equivalent yield of a treasury bill with a settlement date of January 1, 2020, a maturity date of February 1, 2020, and a discount rate of 0.05.

Formula: TBILLPRICE(3/1/2020, 6/1/2020, 0.05)The result of this example would be 99.876.

Formula: TBILLYIELD(0.05, “1/1/2020”, “4/1/2020”) returns 0.0541

Formula: VDB(10000, 1000, 5, 1, 3, 2, FALSE)This example will calculate the depreciation of an asset with a cost of 10,000, a salvage value of 1,000 and a life of 5 years for the first 3 periods, using a double declining balance factor and no switch.

Formula: XIRR (A1:A4, B1:B4, 0.1)A1:A4 = -1000, 500, 500, 500B1:B4 = 01/01/2020, 02/01/2020, 03/01/2020, 04/01/2020In this example, XIRR will return the internal rate of return for the series of cash flows in A1:A4 that occur at the dates in B1:B4, with an initial guess of 0.1.

Formula: XNPV(0.1, {-1000, 400, 400, 400}, {DATE(2020,1,1), DATE(2020,2,1), DATE(2020,3,1), DATE(2020,4,1)})This example would return a net present value of $912.11.

Formula: YIELD(DATE(2020,5,15),DATE(2021,5,15),5%,100,100,2,1)This example would return the yield of a security that has a settlement date of May 15th, 2020, a maturity date of May 15th, 2021, a rate of 5%, a price of 100, a redemption value of 100, a frequency of 2 (semi-annual), and a basis of 1 (US (NASD) 30/360).

Formula: YIELDDISC(“1/1/2020”, “1/1/2021”, 98, 100, 0)This example would calculate the yield on a security with a settlement date of 1/1/2020, a maturity date of 1/1/2021, a price of 98 per $100 face value, a redemption value of 100 per $100 face value, and a day count basis of 0.

Formula: YIELDMAT(A2, B2, C2, D2, E2, F2, G2, H2)In this example, A2 is the settlement date, B2 is the maturity date, C2 is the issue date, D2 is the rate, E2 is the price, F2 is the redemption value, G2 is the frequency, and H2 is the basis.

Formula: BETADIST(2,2,3,0,4)This example returns the probability that a variable, which follows a beta distribution with alpha = 2 and beta = 3, is less than or equal to 2. The lower bound of the beta distribution is 0 and the upper bound is 4.

Formula: BETAINV(0.5,2,4,0,1)This example returns 0.5, which is the inverse of the cumulative beta probability density function with a probability of 0.5, alpha of 2, beta of 4, and lower bound of 0 and upper bound of 1.

Formula: To calculate the probability of getting 3 successes in 5 trials, with a probability of success of 0.4, the formula would be:BINOMDIST(3,5,0.4,FALSE)The result would be 0.3456.

Formula: To find the probability of a chi-squared value of 10 with 5 degrees of freedom, the formula would be:=CHIDIST(10, 5)The result would be 0.0752352461465122.

Formula: To calculate the inverse of the left-tailed probability of the chi-squared distribution with a probability of 0.95 and degrees of freedom of 5, use the following Formula: CHIINV(0.95,5)The result is 11.070.

Formula: CHITEST(A2:A7, B2:B7)

Formula: CONFIDENCE(0.05,2,100)This example would calculate the 95% confidence interval for a population with a standard deviation of 2 and a sample size of 100.

Formula: COVAR(A1:A5, B1:B5)This example will calculate the covariance between the data in the range A1:A5 and the data in the range B1:B5.

Formula: CRITBINOM(10, 0.5, 0.95)This example would return the smallest value for which the cumulative binomial distribution with 10 trials and a probability of success of 0.5 is greater than or equal to 0.95. The result would be 8.

Formula: EXPONDIST(2,3,TRUE)This formula returns the cumulative exponential distribution for x=2 and lambda=3.

Formula: FDIST(2,3,4)This example returns the probability that the random variable has a value between 2 and 3 with degrees of freedom 3 and 4.

Formula: FINV(0.95, 10, 10)This example would return the inverse of the F-Distribution with a probability of 0.95, 10 degrees of freedom for the numerator, and 10 degrees of freedom for the denominator.

Formula: FTEST(A1:A10, B1:B10)

Formula: GAMMADIST(2,3,4,TRUE)This example will calculate the cumulative Gamma distribution for x = 2, alpha = 3, and beta = 4.

Formula: GAMMAINV(0.95,2,2)This example returns the inverse of the gamma cumulative distribution for a probability of 0.95, an alpha of 2, and a beta of 2.

Formula: HYPGEOMDIST(10, 5, 20, 3)This example returns the probability of 3 successes in a sample of 5 taken from a population of 20 containing 3 successes.

Formula: LOGINV(0.5, 0.5, 0.5) returns 1.29

Formula: To find the cumulative log-normal distribution for a value of x = 0.5, with a mean of 0.3 and a standard deviation of 0.2, the formula would be: LOGNORMDIST(0.5, 0.3, 0.2). The result of this formula is 0.788.

Formula: MODE(1,2,3,3,3,4,5)Result: 3

Formula: To calculate the probability of 3 failures occurring before 5 successes in a negative binomial experiment with a success probability of 0.4, the formula would be:=NEGBINOMDIST(3, 5, 0.4)The result would be 0.09216.

Formula: To find the normal cumulative distribution for a mean of 10 and a standard deviation of 2, the formula would be =NORMDIST(10, 10, 2, TRUE).

Formula: To find the inverse of the normal cumulative distribution for a probability of 0.5, a mean of 10 and a standard deviation of 2, the formula would be:=NORMINV(0.5, 10, 2)The result would be 10.

Formula: To calculate the probability that a random variable will take a value less than or equal to 0.5, the formula would be: =NORMSDIST(0.5)

Formula: To calculate the inverse of the standard normal cumulative distribution for a probability of 0.5, the formula would be:=NORMSINV(0.5)This would return a value of 0, as the inverse of the standard normal cumulative distribution for a probability of 0.5 is 0.

Formula: PERCENTILE(A1:A10,0.5)This example returns the 50th percentile of the values in cells A1 through A10.

Formula: PERCENTRANK(A2:A10, A2)This formula will return the rank of the value in cell A2 as a percentage of the values in the range A2:A10.

Formula: POISSON(4,6,FALSE)This example would calculate the probability of 4 events occurring in a fixed period of time, given the average rate of occurrence is 6. The result would be 0.1839.

Formula: QUARTILE(A2:A10,2)This example returns the second quartile of the data set in cells A2 to A10.

Formula: RANK(A2,A2:A7,1)In this example, the RANK function will return the rank of the number in cell A2 within the range of cells A2 to A7, in ascending order.

Formula: STDEV(2,4,6,8,10)The result of this example would be 2.87.

Formula: STDEVP(2, 4, 6, 8)This would return 2, which is the standard deviation of the sample set (2, 4, 6, 8).

Formula: TDIST(2,5,1)This example returns the probability associated with a Student’s t-distribution for x=2, degrees of freedom=5, and 1 tail.

Formula: TINV(0.05, 10)This example returns the critical value of the t-distribution with 10 degrees of freedom and a significance level of 0.05. The result is 1.81246.

Formula: TTEST(A2:A7, B2:B7, 2, 1)This example will calculate the probability associated with the Student’s t-test for the two arrays of data in A2:A7 and B2:B7, using two tails and type 1.

Formula: VAR(2,4,6,8)This will return 6, which is the variance of the set of values.

Formula: VARP(1,2,3,4,5)Result: 2

Formula: WEIBULL(2,3,4,TRUE)This example will calculate the Weibull cumulative distribution function with x = 2, alpha = 3, and beta = 4.

Formula:=ZTEST(A1:A50, 70, 15), calculates the one-tailed P-value of a z-test for a sample of data in cells A1 to A50 in Excel.

Formula: CUBEKPIMEMBER(“Sales Cube”, “Sales”) returns the sales KPI value from the Sales Cube.

Formula: CUBEMEMBER(“Sales Cube”, “Product[Beverages]”)

Formula: CUBEMEMBERPROPERTY(“Sales Cube”, “[Product].[Product].[Bread]”, “UniqueName”)This example returns the unique name of the cube member “[Product].[Product].[Bread]” from the “Sales Cube” cube.

Formula: To return the third highest value in the set {1,2,3,4,5}, the following formula can be used: CUBERANKEDMEMBER({1,2,3,4,5}, 3, descending)The result of the formula is 3.

Formula: CUBESET(“Sales Cube”, “[Measures].[Quantity] > 1000”, “High Quantity Sales”)

Formula: CUBESETCOUNT(“Sales Cube”)This example would return the number of sets in the cube named “Sales Cube”.

Formula: CUBEVALUE(“OLAP”, “[Sales].[Product].[Product]”, “Revenue”)

Formula: To calculate the average of the prices of all products in the database with the category of “Fruit”, the following formula can be used: DAVERAGE(A2:E7,5,”Fruit”)

Formula: DCOUNT(A1:D10,”Age”,A1:C10)This example counts the number of cells in the Age column (column 4) of the database range A1:D10 that meet the criteria in the range A1:C10.

Formula: DCOUNTA(A1:C10, 1, “>10”)This example counts the number of cells in the range A1:C10 in the first field (field 1) that contain a number greater than 10.

Formula: DGET(A1:D10, “Age”, “Name”=”John”)This example will return the age of the person named John from the database range A1:D10.

Formula: DMAX(A1:B10, “Price”, “Product = Shoes”)This example would return the maximum price from the range A1:B10 where the product is equal to “Shoes”.

Formula: DMIN(A2:C10,”Price”, “Category=Clothing”)

Formula: DPRODUCT(A1:A3,B1:B3)This example multiplies the values in cells A1, A2 and A3 with the corresponding values in cells B1, B2 and B3, and returns the sum of the products.

Formula: DSTDEV(2, 4, 6, 8, 10)Result: 2.82842712474619

Formula: To find the standard deviation of the population of people living in a certain city, the following formula could be used: =DSTDEVP(A1:A100, “population”, “city”)

Formula: DSUM(A1:C10, “Price”, “Category=Clothing”)This example will sum up all of the values in the “Price” column of the range A1:C10 that have the category “Clothing”.

Formula: To calculate the variance of the amount column in the table below, where the date is greater than or equal to 1/1/2020:DVAR(A2:C10, “Amount”, “Date>=1/1/2020”)

Formula: DVARP(2,4,6,8,10)This example would return 4.

Formula: BESSELI(1,2)This formula will return the modified Bessel function of the first kind for x = 1 and order = 2. The result will be 0.440050585744933.

Formula: BESSELJ(1,2) returns 0.440050585744933

Formula: BESSELK(2,3) returns 0.06899

Formula: BESSELY(2,3)This formula will return the Bessel function of the first kind for x = 2 and n = 3.

Formula: BIN2DEC(10101) returns 21

Formula: BIN2HEX(10101010, 8)Result: AA

Formula: BIN2OCT(10101010) returns 252

Formula: BITAND(7, 11)This will return the result of 7 (111 in binary) AND 11 (1011 in binary), which is 3 (11 in binary).

Formula: BITLSHIFT(10, 2)This example shifts the bits of the number 10 to the left by two bits. The result is 40.

Formula: BITOR(4, 5)This will return 5 as the result.

Formula: BITRSHIFT(10,2)This example shifts the bits of the number 10 two positions to the right, resulting in the value 2.

Formula: BITXOR(5,7)This will return 4, as 5 XOR 7 = 4.

Formula: COMPLEX(5,3,”i”)This will return the complex number 5+3i.

Formula: CONVERT(10, “mi”, “km”)This example would convert 10 miles to kilometers, and the result would be 16.09344 kilometers.

Formula: DEC2BIN(10, 8) returns 00001010.

Formula: DEC2HEX(255,2)Result: FF

Formula: DEC2OCT(20,2)Result: 24

Formula: DELTA(A1, B1)

Formula: ERF(-1,2)This example returns the value of 0.954499736103642, which is the area under a normal distribution curve between -1 and 2.

Formula: ERF.PRECISE(0.5)This formula will return the error function of 0.5, which is 0.5204999.

Formula: ERFC(0.5)This example returns the value 0.479500122.

Formula: To calculate the complementary error function integrated between 0 and 1, the formula would be: =ERFC.PRECISE(0,1) which returns 0.842700792949715.

Formula: GESTEP(7, 3)This will return TRUE as 7 is greater than or equal to 3.

Formula: HEX2BIN(A1, 8)In this example, the HEX2BIN function will convert the hexadecimal number in cell A1 to a binary number with 8 places.

Formula: HEX2DEC(“FF”) returns 255

Formula: HEX2OCT(A1) where A1 contains the hexadecimal number “F1” will return the octal number “361”.

Formula: IMABS(5+3i)Result: 5

Formula: IMAGINARY(2+3i)This formula will return 3, which is the imaginary coefficient of the complex number 2+3i.

Formula: IMARGUMENT(2+3i) returns 0.982793723247329

Formula: IMCONJUGATE(2+4i)Result: 2-4i

Formula: To calculate the cosine of 2 radians, use the following Formula:=IMCOS(2)The result would be -0.41614683654714.

Formula: IMCOSH(1+2i)Result: 3.76219 + 0.96623i

Formula: IMCOT(2+3i)Result: -3

Formula: IMCSC(2+3i)Result: 2-3i

Formula: To calculate the number of payments for a loan of $1000 with an interest rate of 5% and a payment period of 12 months, the following formula can be used:IMCSCHEDULE(1000, 0.05, 12, 1, 12, 0)The result of this formula will be 12, meaning that the loan will have 12 payments.

Formula: To divide the imaginary numbers 5i and 2i, the formula would be:=IMDIV(5i,2i)The result would be 2.5i.

Formula: IMEXP(“C:\mydata.txt”, “Sheet1”, 1, 1, “Delimited”, “FieldsPerRecord=4”)

Formula: IMLN(A1:B2)Where A1:B2 is a 2×2 matrix containing the values 1, 2, 3 and 4. The result of this formula would be the inverse matrix logarithm of the matrix.

Formula: IMLOG10(100)The result of this formula is 2, as the logarithm of 100 to the base 10 is 2.

Formula: IMLOG2(8)This formula will return 3, as the binary logarithm of 8 is 3.

Formula: IMPOWER(2,3)This will return 8, as 2 to the power of 3 is 8.

Formula: IMPRODUCT(2+3i,4+5i)This would return 22+22i.

Formula: IMREAL(3.14)This example would return TRUE as 3.14 is a real number.

Formula: IMSEC(“12:30:15”) returns 45015.

Formula: IMSECH(0.5)The result of this function is 1.3169578969248.

Formula: IMSINV({1,2,3;4,5,6;7,8,9}) returns the inverse of the matrix {1,2,3;4,5,6;7,8,9} as {-0.222, 0.111, 0.333; 0.167, -0.056, -0.139; 0.056, 0.167, -0.056}.

Formula: IMSINH(2) returns 1.919154444

Formula: IMSQRT(2+3i)This will return 1.5i, which is the imaginary component of the square root of 2+3i.

Formula: IMSUB(A1:A5, B1:B5)This formula will return the difference between the two arrays A1:A5 and B1:B5.

Formula: IMSUM(A1:A10, “>50”)This formula will sum all the numbers in the range A1:A10 that are greater than 50.

Formula: IMTAN(2)This example returns the value 0.2078795763507619.

Formula: OCT2BIN(11)Result: 1011

Formula: OCT2DEC(77)This example would return the decimal number 63.

Formula:OCT2HEX(11, 4)Result: 13

Formula: ENCODEURL(“www.example.com/search?q=hello world”)Result: www.example.com/search%3Fq%3Dhello%20world

Formula: FILTERXML(“Dr. Seuss”,”/book/title”)The result of this formula would be “The Cat in the Hat”.

Formula: WEBSERVICE(“https://www.example.com/data.xml”)