TABLE OF CONTENTS

Full List of Excel Functions and Formulas helps you to understand the functions with Example Formulas. There are 500+ Excel Functions available in the latest version of Excel and counting. You can go to each formula and find its description and use

Unlock the power of data analysis and manipulation with Excel functions! This comprehensive guide equips you with the knowledge to conquer spreadsheets like a pro, even if you’re a complete beginner. We’ll delve into the essential Excel functions list, categorized for easy understanding, and equip you with the skills to tackle real-world scenarios with confidence.

Why Use Excel Functions?

Excel functions are pre-built formulas that automate calculations and data manipulation, saving you time and effort. Imagine manually calculating averages or formatting dates across hundreds of cells – functions eliminate this drudgery! But the benefits go beyond efficiency:

  • Accuracy: Functions minimize errors compared to manual calculations.
  • Complexity: Functions can handle advanced tasks beyond basic formulas.
  • Consistency: Functions ensure consistent formatting and calculations throughout your spreadsheets.

List of Excel Formulas and Functions

Excel boasts a vast library of functions, but don’t be overwhelmed! We’ll explore the most essential functions grouped into intuitive categories. Here is the list of Excel Formulas and Functions. We have included Excel functions, Description, Syntax. Explained with Example Formulas.

Popular Functions

  1. IF: Evaluates a condition and returns a specific value if true, another if false. Powerful for building complex decision-making formulas.
  2. AND: Returns TRUE only if all specified conditions are true.
  3. OR: Returns TRUE if any specified condition is true.
  4. SUM: Adds values across a range of cells. A fundamental function for basic calculations.
  5. AVERAGE: Calculates the average of a set of numbers.
  6. COUNT: Counts the number of cells containing numbers, text, or logical values.
  7. CONCAT: Combines multiple text strings into a single string. Useful for merging names, addresses, etc.
  8. LEFT: Extracts a specific number of characters from the left side of a text string.
  9. RIGHT: Extracts a specific number of characters from the right side of a text string.
  10. LEN:Counts the number of characters in a text string.
  11. TODAY: Returns the current date.
  12. VLOOKUP: Searches for a specific value in a leftmost column and returns a corresponding value from another column in the same row. A workhorse for lookups across worksheets.
  13. INDEX & MATCH: A powerful combination for flexible lookups within your spreadsheets.
  14. COUNTIF: Counts the number of cells meeting a specific criteria.
  15. SUMIF: Sums the values in a range based on a specific condition.

Information Functions

Get to Know Your Spreadsheet Better with Information Functions! These tools reveal details about cells, formatting, and workbook properties.  Whether you need to check a cell’s data type (ISNUMBER, ISTEXT) or access workbook information (INFO), these functions provide valuable insights into the inner workings of your spreadsheet.

  • Reveal details about cells, formatting, and workbook properties.
  • Become a Spreadsheet Detective: Information Functions act as your eyes and ears, revealing hidden details about your data.
  • Cell Secrets Exposed: Uncover cell formatting, data types, and location with the CELL function.
  • Workbook Wisdom: Get the inside scoop on your workbook using INFO, like the number of sheets or protection status.
  • Data Type Discrimination: Distinguish between blank cells (ISBLANK), numbers (ISNUMBER), or text (ISTEXT) for cleaner formulas.
Function Description Syntax and Formula
CELL The CELL function returns information about the formatting, location, or contents of a cell. Syntax: CELL(info_type, [reference])

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

ERROR.TYPE The ERROR.TYPE function returns a number that corresponds to a specific error value in Microsoft Excel. Syntax: ERROR.TYPE(error_val)

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

INFO Description:The INFO function is an information function in Excel that returns information about the operating environment. Syntax: INFO(type)

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

ISBLANK The ISBLANK function in Excel checks if a cell is empty or not. Syntax: ISBLANK(value)

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

ISERR The ISERR function in Microsoft Excel is a logical function that returns TRUE when the value provided is an error value other than #N/A, and FALSE when the value is not an error value. Syntax: ISERR(value)

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.

ISERROR The ISERROR function is used to check if a value is an error and returns either TRUE or FALSE. Syntax: ISERROR(value)

Formula: ISERROR(A1)

ISEVEN The ISEVEN function is used to determine whether a number is even or odd. Syntax: ISEVEN(number)

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

ISFORMULA The ISFORMULA function in Excel is used to check if a cell contains a formula. It returns TRUE if the cell contains a formula and FALSE if it does not. Syntax: ISFORMULA(cell)

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

ISLOGICAL The ISLOGICAL function is used to check if a value is a logical value (TRUE or FALSE). Syntax: ISLOGICAL(value)

Formula: ISLOGICAL(TRUE) returns TRUE

ISNA The ISNA function in Excel is used to check whether a given value is the #N/A error value. Syntax: ISNA(value)

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

ISNONTEXT The ISNONTEXT function is used to check if a value is not text. It returns TRUE if the value is not text and FALSE if it is text. Syntax: ISNONTEXT(value)

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

ISNUMBER The ISNUMBER function is used to determine if a value is a number or not. Syntax: ISNUMBER(value)

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

ISODD The ISODD function is used to check if a number is odd. It returns TRUE if the number is odd and FALSE if the number is even. Syntax: ISODD(number)

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

ISOMITTED The ISOMITTED function in Excel checks whether a value is omitted from a list of values. Syntax: ISOMITTED(value)

Formula: ISOMITTED(A1)

ISREF The ISREF function checks if a cell reference is valid. Syntax: ISREF(value)

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

ISTEXT The ISTEXT function is a built-in function in Excel that checks to see if a cell contains text. It returns TRUE if the value is text and FALSE if not. Syntax: ISTEXT(value)

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

NPER The NPER function in Excel is used to calculate the number of periods for an investment based on a constant payment and a constant interest rate. Syntax: NPER(rate, pmt, pv, [fv], [type])

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

NA The NA function returns the #N/A error value, which indicates that a value is not available. Syntax: NA()

Formula: NA()

SHEET SHEET is an Excel function that returns the sheet number of a reference. Syntax: SHEET(reference)

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

SHEETS The SHEETS function returns the number of sheets in a reference. Syntax: SHEETS(reference)

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

TYPE TYPE: The TYPE function is an Excel Information function that returns a number indicating the type of value in a cell. Syntax: TYPE(value)

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.

 

Text Functions

Transform and Cleanse Your Text Data: Text data in your spreadsheets needs some TLC? Text Functions are your one-stop shop! Whether you need to combine text strings (CONCATENATE), extract specific characters (LEFT, RIGHT), or format casing (UPPER, LOWER), these functions offer unparalleled control over text manipulation and cleaning.

  • Combine text strings (CONCATENATE).
  • Extract specific characters (LEFT, RIGHT).
  • Format casing (UPPER, LOWER).
  • Clean and format customer data or extract information from text strings.
Function Description Syntax and Formula
ASC The ASC function in Microsoft Excel is used to convert a character into its corresponding ASCII code. Syntax: ASC(text)

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

ARRAYTOTEXT The ARRAYTOTEXT function in Excel converts an array of values into a text string. Syntax: ARRAYTOTEXT(array, delimiter, [text_qualifier])

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

BAHTTEXT BAHTTEXT is an Excel function that converts a number to Thai text. Syntax: BAHTTEXT(number)

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

CHAR The CHAR function returns the character specified by the number code. Syntax: CHAR(number)

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

CLEAN The CLEAN function removes all non-printable characters from a text string. Syntax: CLEAN(text)

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

CODE The CODE function is used to convert a text string into a code with a corresponding numerical value. Syntax: CODE(text)

Formula: CODE(“A”) returns 65.

CONCAT The CONCAT function in Excel is used to join two or more text strings together. Supports Ranges. Syntax: CONCAT(text1, [text2], …)

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

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

CONCATENATE The CONCATENATE function is used to join two or more text strings together. Can not handle ranges. Syntax: CONCATENATE(text1, [text2], [text3], …)

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

DBCS The DBCS function is a database function in Microsoft Excel that allows you to extract data from a database. Syntax: DBCS(database, field, criteria)

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

DOLLAR The DOLLAR function converts a number to text, using a currency format. Syntax: DOLLAR(number, [decimals])

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

EXACT The EXACT function compares two text strings and returns TRUE if the two strings are exactly the same, including case and any spaces. Syntax: EXACT(text1, text2)

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

FIXED The FIXED function in Excel is used to round a number to a specified number of decimal places and display it as a text string with a fixed number of decimal places. Syntax: FIXED(number, [decimals], [no_commas])

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

LEFT, LEFTB LEFT:The LEFT function in Excel returns the leftmost characters from a text string. And LEFTB returns the leftmost characters based on the bytes. Syntax: LEFT(text, [num_chars])text: The text string from which you want to extract the characters.num_chars: [optional] The number of characters you want to extract from the left of the text string.

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”

LEN, LENB LEN:The LEN function is used to count the number of characters in a text string. And LENB returns the number of bytes used to represent the characters in a text string. Syntax: LEN(text)

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

RIGHT, RIGHTB The Right function in Excel returns the rightmost characters from a text string. And RIGHTB returns the rightmost characters based on the bytes. Syntax: RIGHT(text, [num_chars])

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

LOWER The LOWER function in Excel converts all letters in a text string to lowercase. Syntax: LOWER(text)

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

NUMBERVALUE The NUMBERVALUE function converts a text string that represents a number to a number. Syntax: NUMBERVALUE(text, [decimal_separator], [group_separator])

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

PHONETIC The PHONETIC function is used to convert text into phonetic pronunciation. This function is useful for creating a phonetic representation of names or words, which can help differentiate between similar sounding words. Syntax: PHONETIC(text)

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

PROPER The PROPER function is used to convert a text string to proper case, which means that the first letter of each word is capitalized and all other letters are lowercase. Syntax: PROPER(text)

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

REPT The REPT function in Excel is used to repeat a text string a specified number of times. Syntax: REPT(text, number_times)

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

SUBSTITUTE The SUBSTITUTE function in Excel replaces existing text with new text in a provided text string. Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])

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

T.TEST T.TEST is a statistical function in Excel that calculates the probability associated with a Student’s t-test. It is used to determine whether two samples are likely to have come from the same two underlying populations that have the same mean. Syntax: T.TEST(array1, array2, tails, type)Array1: The first array or range of dataArray2: The second array or range of dataTails: The number of tails in the test, either 1 or 2Type: The type of t-test to be used, either “1” for a paired two-sample t-test or “2” for a two-sample t-test with unequal variances

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.

TEXT The TEXT function is used to convert a value to text in a specific number format. Syntax: TEXT(value, format_text)

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

TEXTAFTER The TEXTAFTER function returns the characters after a given character or characters in a text string. Syntax: TEXTAFTER(text, character)

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

TEXTBEFORE The TEXTBEFORE function is a text function in Microsoft Excel that returns the text before a specific character in a string. Syntax: TEXTBEFORE(text, delimiter)

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

TEXTJOIN The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that is combined. Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

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.

TEXTSPLIT The TEXTSPLIT function splits a text string into multiple parts based on a specified delimiter. Syntax: TEXTSPLIT(text, delimiter)

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

TRIM The TRIM function removes all spaces from a text string except for single spaces between words. Syntax: TRIM(text)

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

UNICHAR UNICHAR is an Excel function that returns the Unicode character that corresponds to the given numeric code. Syntax: UNICHAR(number)

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

UNICODE The UNICODE function is a built-in function in Microsoft Excel that returns the numerical value of a character or the first character in a string. Syntax: UNICODE(text)

Formula: UNICODE(“A”)Result: 65

UPPER The UPPER function in Excel converts all text in a cell to uppercase. Syntax: UPPER(text)

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

VALUE The VALUE function converts a text string that represents a number to a number. Syntax: VALUE(text)

Formula: VALUE(“2”) returns 2

VALUETOTEXT VALUETOTEXT is an Excel function that converts a numerical value into a text string. Syntax: VALUETOTEXT(value, [format_text])

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

Date and Time Functions

Manage Dates and Times with Ease: Working with dates and times in Excel can be a breeze with the dedicated Date & Time Functions. Need to know the current date (TODAY) or extract the year (YEAR) from a date? Perhaps you want to calculate the difference between two dates (DATEDIF)? These functions streamline date and time manipulation for efficient calendar calculations and time-based analyses.

  • Work with dates and times efficiently.
  • Calculate differences between dates (DATEDIF).
  • Extract specific components from dates (YEAR, MONTH, DAY).
  • Automate tasks based on current date and time (TODAY, NOW).
Function Description Syntax and Formula
DATE The DATE function is used to create a date value from individual year, month, and day components. Syntax: DATE(year, month, day)

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

DATEDIF The DATEDIF function is a built-in function in Microsoft Excel that calculates the number of days, months, or years between two dates. Syntax: DATEDIF(start_date, end_date, unit)

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

DATEVALUE The DATEVALUE function in Microsoft Excel converts a date that is stored as text to a serial number that Excel recognizes as a date. Syntax: DATEVALUE(date_text)

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

DAY SThe DAYS function returns the number of days between two dates. Syntax: DAYS(end_date, start_date)

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.

DAYS The DAYS function returns the number of days between two dates. Syntax: DAYS(end_date, start_date)

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

DAYS360 The DAYS360 function is used to calculate the number of days between two dates based on a 360-day year. Syntax: DAYS360(start_date, end_date, [method])

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

EDATE EDATE: The EDATE function returns a date that is a specified number of months before or after a given date. Syntax: EDATE(start_date, months)

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

EOMONTH The EOMONTH function returns the last day of the month that is a specified number of months before or after a given date. Syntax: EOMONTH(start_date, months)

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.

HOUR The HOUR function returns the hour part of a given time, as a number between 0 and 23. Syntax: HOUR(serial_number)

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

ISOWEEKNUM The ISOWEEKNUM function returns the ISO week number of a given date. Syntax: ISOWEEKNUM(date)

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

MINUTE The MINUTE function in Excel returns the minute component of a given time. Syntax: MINUTE(serial_number)

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

MONTH The MONTH function in Microsoft Excel is used to return the month number from a given date. Syntax: MONTH(serial_number)

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

NETWORKDAYS NETWORKDAYS Function:The NETWORKDAYS function calculates the number of whole working days between two dates. It excludes weekends and holidays from the calculation. Syntax: NETWORKDAYS(start_date, end_date, [holidays])

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

NETWORKDAYS.INTL NETWORKDAYS.INTL is an Excel function that calculates the number of workdays between two dates, excluding weekends and holidays. Syntax: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

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.

NOW NOW: This function returns the current system date and time. Syntax: NOW()

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

SECOND The SECOND function returns the second part of a time value. Syntax: SECOND(time_value)

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

TIME The TIME function is used to construct a time value from hour, minute, and second values. Syntax: TIME(hour, minute, second)

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

TIMEVALUE The TIMEVALUE function is used to convert a time expressed as text into a serial number that Excel recognizes as time. Syntax: TIMEVALUE(time_text)

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

TODAY The TODAY function returns the current date. Syntax: TODAY()

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

WEEKDAY The WEEKDAY function in Excel returns a number from 1-7 corresponding to the day of the week for a given date. Syntax: WEEKDAY(serial_number, [return_type])

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.

WEEKNUM The WEEKNUM function returns the week number of a given date in the year. Syntax: WEEKNUM(serial_number, [return_type])

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).

WORKDAY The Excel WORKDAY function returns a date that is a given number of workdays away from a start date. Syntax: WORKDAY(start_date, days, [holidays])

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

WORKDAY.INTL The WORKDAY.INTL function calculates the date after a specified number of workdays, taking into account weekends and holidays. Syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays])

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.

YEAR The YEAR function returns a four-digit year (a number from 1900 to 9999) given a date value. Syntax: YEAR(serial_number)

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.

YEARFRAC The YEARFRAC function is used to calculate the fraction of the year represented by the number of days between two dates. Syntax: YEARFRAC(start_date, end_date, [basis])

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.

Logical Functions

Make Informed Decisions Based on Conditions: Ever need your spreadsheet to make decisions based on certain conditions? Enter the world of Logical Functions! These powerful tools evaluate conditions and return TRUE or FALSE, allowing you to build complex formulas with branching logic and control the flow of your calculations.

  • Evaluate conditions and return TRUE or FALSE.
  • Useful for building complex formulas with decision-making capabilities.
  • Control the flow of calculations based on specific criteria.
  • Examples: IF, AND, OR, NOT, XOR
Function Description Syntax and Formula
AND The AND function is a logical function in Excel that returns TRUE if all of the conditions that are specified are true, and returns FALSE if any of the conditions are false. Syntax: AND(logical1, [logical2], …)
Formula: AND(A1>5,B1<10)
BYCOL UMNBYCOLUMN is a function in Microsoft Excel that allows users to sort a range of data by column. Syntax: BYCOLUMN(array, [col], [ascending])

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

BYROW BYROW is an Excel function that returns an array of numbers from a given range of cells. It takes two arguments: array and row_num. Array is a range of cells that contains numbers, and row_num is a number that indicates which row should be returned. Syntax: BYROW(array, row_num)

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}.

FALSE The FALSE function in Excel is a logical function that returns the value FALSE. Syntax: FALSE()

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

IF The IF function in Excel is a logical function that allows you to make a logical comparison between two values and returns a value based on the result of that comparison. Syntax: IF(logical_test, value_if_true, value_if_false)

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

IFERROR The IFERROR function is used to catch and handle errors in a formula. It tests a value for an error and returns a different value if an error is found. Syntax: IFERROR(value, value_if_error)

Formula: IFERROR(A1/B1, 0)

IFNA IFNA: This function returns the value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. Syntax: IFNA(value_if_error, value_if_no_error)

Formula: IFNA(0,A1/A2)

IFS IFS is an Excel function that allows you to test multiple conditions at the same time and returns a value that corresponds to the first TRUE result. Syntax: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

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.

LAMBDA The LAMBDA function in Excel is used to create custom functions. It allows users to create their own functions that can be used in formulas. It is a powerful tool that can be used to create complex formulas. Syntax: LAMBDA(argument1, argument2, …, argument_n, expression)Arguments:argument1, argument2, …, argument_n: These are the arguments that will be used in the expression.expression: This is the expression that will be evaluated.

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

MAKEARRAY The MAKEARRAY function creates a single-column array from multiple values. Syntax: MAKEARRAY(value1, [value2], [value3], …)

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

MAP MAP is an Excel function that allows you to map one set of values to another set of values. It takes three arguments: lookup_value, from_array, and to_array. Syntax: MAP(lookup_value, from_array, to_array)

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

NOT The NOT function is a logical function in Excel that returns the opposite of a given logical value. It takes a single argument that is either TRUE or FALSE and returns the opposite. Syntax: NOT(logical)

Formula: NOT(TRUE) returns FALSE.

OR The OR function is a logical function in Excel that returns either TRUE or FALSE based on one or more conditions. Syntax: OR(logical1, [logical2], …)

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

REDUCE The REDUCE function is an Excel function that reduces a range of values by applying a given function. It is a dynamic array function that can take multiple values and return a single result. Syntax: REDUCE(function, range[, initial])

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.

SCAN The SCAN function searches for specific characters within a text string and returns the text string from the start position to the character found. Syntax: SCAN(text, character_set, [start_num], [num_chars])

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.

SWITCH The SWITCH function is a logical function in Microsoft Excel that evaluates an expression against multiple conditions and returns a result corresponding to the first matching condition. Syntax: SWITCH (expression, value_1, result_1, [value_2, result_2], … [default])

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

TRUE The TRUE function returns the logical value TRUE. Syntax: TRUE()

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

XOR The XOR function in Excel returns a logical exclusive OR of all arguments. It returns TRUE if an odd number of arguments are TRUE, and FALSE if an even number of arguments are TRUE. Syntax: XOR(logical1, [logical2], …)

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

 

Lookup and Reference Functions

Find What You Need Across Your Spreadsheets: Lost in a sea of data across different worksheets? Lookup & Reference Functions come to the rescue! These champions help you retrieve specific data from any location within your workbook. VLOOKUP and HLOOKUP are masters at searching for values and returning corresponding information, while INDEX and MATCH offer ultimate flexibility for customized lookups.

  • Retrieve data from different parts of your workbook.
  • Search for specific values and return corresponding information (VLOOKUP, HLOOKUP).
  • Use a combination of INDEX and MATCH for flexible lookups.
  • Reference cells across worksheets for consolidated reports.

 

Function Description Syntax and Formula
ADDRESS The ADDRESS function returns a cell address as text, based on a given row and column number. Syntax: ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

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.

AREAS The AREAS function in Microsoft Excel returns the number of areas in a reference. Syntax: AREAS(reference)

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

CHOOSE The CHOOSE function in Excel is used to select one of a number of values based on an index number. Syntax: CHOOSE(index_num, value1, [value2], …)

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

CHOOSECOLS The CHOOSECOLS function allows you to select columns from a range of cells. It returns an array of the selected column numbers. Syntax: CHOOSECOLS(range, [criteria])

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

CHOOSEROWS The CHOOSEROWS function returns an array of rows from a list based on a criteria. Syntax: CHOOSEROWS(list, criteria)

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

COLUMN The COLUMN function is an Excel function that returns the column number of a given cell reference. Syntax: COLUMN(reference)

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

COLUMNS COLUMNS is a function in Microsoft Excel that returns the number of columns in a given array or reference. Syntax: COLUMNS(array)

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

DROPDOWN The DROPDOWN function creates a dropdown list in a cell. Syntax: DROPDOWN(list, [selected], [input_title], [input_message])

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

EXPAND The EXPAND function is used to expand a range of cells from a single cell. Syntax: EXPAND(reference, row_num, column_num)

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

FILTER The FILTER function is used to filter a range of data based on supplied criteria. It returns a subset of data that meets the criteria. Syntax: FILTER(array, include, [if_empty])

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”.

FORMULATEXT The FORMULATEXT function is used to return the formula in a cell as text. Syntax: FORMULATEXT(reference)

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

GETPIVOTDATA The GETPIVOTDATA function in Excel is used to retrieve data stored in a PivotTable. It is used to reference data stored in a PivotTable by using a standard cell reference. Syntax: GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)

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

HLOOKUP HLOOKUP is an Excel function used to look up and retrieve data from a row in a table. It searches for a value in the top row of the table and returns the corresponding value in the same column from another row. Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

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.

HSTACK HSTACK is an Excel function that allows users to combine two or more arrays into one single array. Syntax: HSTACK(array1, array2, array3, …)

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

HYPERLINK The HYPERLINK function creates a shortcut that jumps to a specific location in the current workbook, opens a document stored on a network server, or opens a web page on the Internet. Syntax: HYPERLINK(link_location, [friendly_name])

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

INDEX The INDEX function returns a value or reference of the cell at the intersection of a particular row and column within a range. Syntax: INDEX(array, row_num, [column_num])

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.

INDIRECT The INDIRECT function returns a reference to a range. It is useful when you want to convert a text string into a valid reference. Syntax: INDIRECT(ref_text, [a1])

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

LOOKUP The LOOKUP function is used to look up a value in a row or column of data. It can be used to look up a value either vertically (in a column) or horizontally (in a row). Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])

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

MATCH MATCH is a function in Excel that looks for a specified item in a range of cells and returns the relative position of that item in the range. Syntax: MATCH(lookup_value, lookup_array, [match_type])lookup_value: The value to be found in the lookup_array.lookup_array: The range of cells being searched.match_type: [optional] The number -1, 0, or 1. The match_type specifies how Excel matches lookup_value with values in lookup_array.

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.

OFFSET The OFFSET function returns a reference to a range that is a specified number of rows and columns from a given reference cell. Syntax: OFFSET(reference, rows, cols, [height], [width])

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.

ROW The ROW function returns the row number of a cell reference. Syntax: ROW([reference])

Formula: ROW(A1) returns 1.

ROWS The ROWS function returns the number of rows in a given array or range. Syntax: ROWS(array)

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

RTD The RTD function in Excel is used to retrieve real-time data from a program that supports COM automation. Syntax: RTD(progID, server, topic1, [topic2], …)

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

SORT The SORT function in Excel allows users to sort a range of data by one or more columns or rows in either ascending or descending order. Syntax: SORT(array, [sort_index], [sort_order], [by_col])

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.

SORTBY The SORTBY function sorts a range of data based on one or more columns. Syntax: SORTBY(array, sort_column1, [sort_order1], [sort_column2], [sort_order2], …)

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.

TAKE TAKE is an Excel function that returns a specified number of characters from the start of a text string. Syntax: TAKE(text,num_chars)

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

TOCOL The TOCOL function returns the column number of a given cell reference. Syntax: TOCOL(cell_reference)

Formula: TOCOL(“A1”) returns 1

TOROW The TOROW function returns the row number of a reference. Syntax: TOROW(reference)

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

TRANSPOSE The TRANSPOSE function in Excel is used to transpose a range of cells from vertical to horizontal or vice versa. Syntax: TRANSPOSE(array)

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.

UNIQUE The UNIQUE function in Excel is used to return a list of unique values from a list or range. Syntax: UNIQUE(array, [by_col], [exactly_once])Array: The array or range of cells from which to extract unique values.By_col: (optional) A logical value that specifies whether to return unique rows or columns. If set to TRUE, the function will return unique rows; if set to FALSE, the function will return unique columns. The default value is FALSE.Exactly_once: (optional) A logical value that specifies whether to return only values that appear exactly once in the list or range. The default value is FALSE.

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

VLOOKUP VLOOKUP is an Excel function used to look up and retrieve data from a specific column in a table. It searches for a value in the leftmost column of a table and returns a value in the same row from another column. Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

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.

VSTACK The VSTACK function in Excel is used to stack two or more vertical ranges of data on top of each other. Syntax: VSTACK(array1, array2, [array3], …)

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

WRAPCOLS The WRAPCOLS function in Microsoft Excel is used to wrap the contents of a cell across multiple columns. Syntax: WRAPCOLS(cell_reference, number_of_columns)

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

WRAPROWS WRAPROWS is a Microsoft Excel function that allows you to wrap text in a cell to multiple lines. This is useful when you have a long text string that needs to be displayed in a cell. Syntax: WRAPROWS(cell_reference)

Formula: WRAPROWS(A1)

XLOOKUP XLOOKUP is an Excel function that looks for a value in the first column of a table array and returns the corresponding value from another column in the same row. Syntax: XLOOKUP(lookup_value, lookup_array, return_column_number, [not_found], [match_mode], [search_mode])

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.

XMATCH XMATCH: This function returns the position of a value in a given array that matches a specified value in a specified order. Syntax: XMATCH(lookup_value, lookup_array, [match_type])

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

 

Math and Trigonometry Functions

Unleash the Power of Mathematical Calculations with Excel’s Math & Trigonometry Functions. From basic arithmetic like SUM and AVERAGE to advanced calculations like square roots (SQRT) and trigonometric functions (SIN, COS, TAN), these functions equip you to tackle any numerical analysis or scientific computation with ease.

  • Perform basic arithmetic operations (SUM, AVERAGE, COUNT).
  • Tackle advanced calculations (SQRT, SIN, COS, TAN).
  • Analyze scientific data and create financial models.
  • Calculate loan payments and interest rates.

 

Function Description Syntax and Formula
ABS The ABS function in Excel returns the absolute value of a number. Syntax: ABS(number)

Formula: ABS(-5)Result: 5

ACOS The ACOS function returns the arccosine (in radians) of a number. Syntax: ACOS(number)

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

ACOSH The ACOSH function returns the inverse hyperbolic cosine of a number. Syntax: ACOSH(number)

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

ACOT The ACOT function returns the inverse cotangent (arccotangent) of a given number. Syntax: ACOT(number)

Formula: ACOT(0.5)This formula returns 1.10714871779409.

ACOTH The ACOTH function is an Excel function that returns the inverse hyperbolic cotangent of a given number. Syntax: ACOTH(number)

Formula: ACOTH(2)This formula returns 0.5493061443340548.

AGGREGATE The AGGREGATE function is a built-in function in Excel that performs calculations such as sum, count, average, max, min, product, etc. on a range of data. Syntax: AGGREGATE(function_num, options, ref1, [ref2], …)Function_num: This is a number that specifies the type of function to be used.Options: This is a number that specifies the options to be used.Ref1, [ref2], …: This is the range of cells that will be used for the calculation.

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

ARABIC The ARABIC function is used to convert Roman numerals to Arabic numerals. Syntax: ARABIC(text)

Formula: ARABIC(“XVII”) returns 17

ASIN The ASIN function returns the arcsine of a given number. Syntax: ASIN(number)

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

ASINH The ASINH function returns the inverse hyperbolic sine of a given number. Syntax: ASINH(number)

Formula: ASINH(2)This formula returns 1.4436354751788.

ATAN The ATAN function in Excel returns the arctangent of a given number, which is the angle in radians between the x-axis and a line from the origin to the given number. Syntax: ATAN(number)

Formula: ATAN(1) returns 0.785398163397448

ATAN2 The ATAN2 function returns the arctangent of two numbers, which is the angle between the x-axis and a line from the origin to a point in the Cartesian plane. Syntax: ATAN2(x_num, y_num)

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

ATANH The ATANH function is an Excel function that returns the inverse hyperbolic tangent of a number. Syntax: ATANH(number)

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

BASE BASE is an Excel function that converts a number from one number base to another. It is used to convert a number from one number system to another, such as from binary to decimal or from hexadecimal to octal. Syntax: BASE(number, radix, min_length)

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.

CEILING CEILING: The CEILING function rounds a number up to the nearest multiple of a specified number. Syntax: CEILING(number, significance)

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

CEILING.MATH The CEILING.MATH function rounds a number up to the nearest integer or to the nearest multiple of significance. Syntax: CEILING.MATH(number, significance, [mode])

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.

CEILING.PRECISE The CEILING.PRECISE function rounds a number up to the nearest integer or to the nearest multiple of significance. Syntax: CEILING.PRECISE(number, significance)

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

COMBIN COMBIN is an Excel function that calculates the number of combinations of a given number of objects from a set of objects. Syntax: COMBIN(number, number_chosen)

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.

COMBINA COMBINA: The COMBINA function returns the number of combinations for a given number of items. Syntax: COMBINA(number, number_chosen)

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.

COS The COS function returns the cosine of an angle given in radians. Syntax: COS(number)

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

COSH The COSH function in Excel returns the hyperbolic cosine of a given number. Syntax: COSH(number)

Formula: COSH(2) returns 3.7621956910836

COT The COT function is used to calculate the cotangent of an angle given in radians. Syntax: COT(number)

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

COTH The COTH function returns the hyperbolic cotangent of a given number. Syntax: COTH(number)

Formula: COTH(0.5) returns 1.31

CSC CSC (Complementary Sine) is an Excel function that returns the complementary sine of a given number. The syntax for the CSC function is CSC(angle), where angle is the angle in radians for which you want to find the complementary sine. Syntax: CSC(number)

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

CSCH The CSCH function returns the hyperbolic cosecant of a given number. Syntax: CSCH(number)

Formula: CSCH(2)Result: 0.275720564771759

DECIMAL The DECIMAL function is used to convert a number from any other base to decimal. Syntax: DECIMAL(number, radix)

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

DEGREES The DEGREES function converts an angle in radians to degrees. Syntax: DEGREES(angle)

Formula: DEGREES(3.14159265358979)Result: 180

EVEN The EVEN function rounds a number up to the nearest even integer. Syntax: EVEN(number)

Formula: EVEN(3.2)Result: 4

EXP The EXP function in Excel returns the result of the mathematical constant e raised to the power of a given number. Syntax: EXP(number)

Formula: EXP(2)Result: 7.38905609893065

FACT FACT: The FACT function returns the factorial of a number. Syntax: FACT(number)

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

FACTDOUBLE The FACTDOUBLE function returns the double factorial of a given number. The double factorial is the product of all the integers from 1 up to the given number, but skipping every other number. Syntax: FACTDOUBLE(number)

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.

FLOOR The FLOOR function in Excel rounds a number down to the nearest multiple of a specified value. Syntax: FLOOR(number, significance)

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

FLOOR.MATH The FLOOR.MATH function rounds a number down to the nearest multiple of a specified significance. Syntax: FLOOR.MATH(number, significance, [mode])

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

FLOOR.PRECISE The FLOOR.PRECISE function rounds a number down to the nearest multiple of significance. Syntax: FLOOR.PRECISE(number, significance)

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

GCD GCD stands for Greatest Common Divisor. It is used to calculate the greatest common divisor of two or more integers. Syntax: GCD(number1, [number2], …)

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

INT The INT function in Excel returns the integer part of a number by rounding down to the nearest integer. Syntax: INT(number)

Formula: INT(3.14)Result: 3

ISO.CEILING ISO.CEILING is an Excel function that rounds a number to the nearest integer or multiple of significance. Syntax: ISO.CEILING(number,significance)

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

LCM LCM stands for Least Common Multiple. It is a function that is used to determine the smallest number that two or more numbers have in common. Syntax: LCM(number1, [number2], …)

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.

LET The LET function is a new function in Excel that allows you to assign a name to a value or expression. It is useful for making formulas easier to read and understand. Syntax: LET(name, expression)

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

LN The LN function returns the natural logarithm of a number. The natural logarithm is the logarithm to the base e. Syntax: LN(number)

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

LOG The LOG function in Microsoft Excel returns the logarithm of a number to a specified base. Syntax: LOG(number, [base])

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

LOG10 The LOG10 function returns the logarithm of a number to the base 10. Syntax: LOG10(number)

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

MDETERM MDETERM is an Excel function used to calculate the determinant of a given matrix. Syntax: MDETERM(array)

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

MINVERSE The MINVERSE function returns the inverse matrix for a given matrix. Syntax: MINVERSE(array)

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

MMULT The MMULT function returns the matrix product of two arrays. Syntax: MMULT(array1, array2)

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

MOD The MOD function returns the remainder after a number is divided by a divisor. Syntax: MOD(number, divisor)

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

MROUND The MROUND function rounds a number to the nearest multiple of another number. Syntax: MROUND(number, multiple)

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

MULTINOMIAL The MULTINOMIAL function returns the multinomial coefficient of a set of numbers. Syntax: MULTINOMIAL(number1, number2, …)

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

MUNIT MUNIT is an Excel function that returns the unit of measure associated with a given number. Syntax: MUNIT(number)

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

ODD The ODD function is used to round a number up to the nearest odd integer. Syntax: ODD(number)

Formula: ODD(8.2)Result: 9

PI The PI function in Excel returns the value of pi (p), which is the ratio of the circumference of a circle to its diameter. Syntax: PI()

Formula: PI()Result: 3.14159265358979

POWER Description:The POWER function is a mathematical function that returns the result of a number raised to a specified power. Syntax: POWER(number, power)

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

PRODUCT The PRODUCT function multiplies all the numbers given as arguments and returns the product. Syntax: PRODUCT(number1, [number2], …)

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

QUOTIENT The QUOTIENT function returns the integer portion of a division. Syntax: QUOTIENT(numerator, denominator)

Formula: QUOTIENT(10,3) returns 3

RADIANS The RADIANS function is used to convert angles from degrees to radians. Syntax: RADIANS(angle)

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

RAND The RAND function in Excel is used to generate a random number between 0 and 1. Syntax: RAND()

Formula: RAND()Result: 0.838592582

RANDARRAY The RANDARRAY function is an Excel function that generates an array of random numbers between two specified numbers. Syntax: RANDARRAY(rows, columns, min, max)

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

RANDBETWEEN The RANDBETWEEN function is used to generate a random number between two specified numbers. Syntax: RANDBETWEEN(bottom, top)

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

ROMAN ROMAN is an Excel function that converts a number to a Roman numeral. Syntax: ROMAN(number, [form])number: The number to be converted to a Roman numeral.form: [optional] An integer that specifies the type of Roman numeral you want.

Formula: ROMAN(10)Result: X

ROUND The ROUND function rounds a number to a specified number of digits. Syntax: ROUND(number, num_digits)

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

ROUNDDOWN The ROUNDDOWN function rounds a number down to the specified number of decimal places. Syntax: ROUNDDOWN(number, num_digits)

Formula: ROUNDDOWN(2.567,2)Result: 2.56

ROUNDUP The ROUNDUP function rounds a number up to a specified number of decimal places. Syntax: ROUNDUP(number, num_digits)

Formula: ROUNDUP(3.14159, 2) returns 3.15

SEC The SEC function returns the secant of an angle, which is the reciprocal of the cosine of that angle. Syntax: SEC(angle)

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

SECH The SECH function returns the hyperbolic secant of a given number. Syntax: SECH(number)

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

SERIESSUM The SERIESSUM function is an Excel function that calculates the sum of a series of terms in a power series. Syntax: SERIESSUM(x, n, m, coefficients)x: The input value for the power series.n: The power of the first term in the series.m: The power of the last term in the series.coefficients: The coefficients of the terms in the series.

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.

SEQUENCE The SEQUENCE function generates a list of sequential numbers in an array, based on user-defined parameters. Syntax: SEQUENCE(rows, columns, [start], [step])

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

SIGN The SIGN function returns the sign of a number. It returns 1 if the number is positive, 0 if the number is 0, and -1 if the number is negative. Syntax: SIGN(number)

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

SIN The SIN function returns the sine of a given angle. Syntax: SIN(angle)

Formula: SIN(45)Result: 0.70710678118

SINH The SINH function returns the hyperbolic sine of a given number. Syntax: SINH(number)

Formula: SINH(2) returns 3.626860407847019

SQRT The SQRT function in Excel returns the square root of a given number. Syntax: SQRT(number)

Formula: SQRT(9)Result: 3

SQRTPI The SQRTPI function returns the square root of a number multiplied by pi. Syntax: SQRTPI(number)

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

SUBTOTAL The SUBTOTAL function is used to calculate a subtotal in a list or database. It can perform calculations such as sum, average, count, etc. Syntax: SUBTOTAL(function_num, range1, range2,…)Function_num: This is a number that specifies the type of calculation to perform.Range1, Range2,…: This is a range of cells or array of values to perform the calculation on.

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

SUM The SUM function is an Excel function used to add up a range of values. Syntax: SUM(number1, [number2], …)

Formula: SUM(A1:A5)

SUMIF The SUMIF function in Excel is used to sum values that meet a certain criteria. Syntax: SUMIF(range, criteria, [sum_range])

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.

SUMIFS The SUMIFS function is an Excel function used to sum values in a range that meet multiple criteria. Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

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″)

SUMPRODUCT The SUMPRODUCT function multiplies corresponding components in the given arrays and returns the sum of those products. Syntax: SUMPRODUCT(array1, [array2], [array3], …)

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

SUMSQ The SUMSQ function is an Excel function that returns the sum of the squares of a set of numbers. Syntax: SUMSQ(number1, [number2], …)

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

SUMX2MY2 The SUMX2MY2 function in Microsoft Excel calculates the sum of the squares of the differences between two arrays of numbers. Syntax: SUMX2MY2(array_x, array_y)

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.

SUMX2PY2 The SUMX2PY2 function adds the sum of the squares of two arrays and then returns the square root of the result. Syntax: SUMX2PY2(array1, array2)

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.

SUMXMY2 The SUMXMY2 function returns the sum of the difference of two arrays, or ranges, of numbers. Syntax: SUMXMY2(array1, array2)

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

TAN The TAN function returns the tangent of a given angle. Syntax: TAN(angle)

Formula: TAN(45) returns the value 1.

TANH The TANH function returns the hyperbolic tangent of a given number. Syntax: TANH(number)

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

TRUNC The TRUNC function truncates a number to an integer by removing the decimal values. Syntax: TRUNC(number, [num_digits])

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

 

Statistical Functions

Dive Deep into Your Data: Get into the heart of your data with Statistical Functions! Analyze trends, central tendencies, and data dispersion with functions like AVERAGE, MEDIAN, and STDEV. Explore the distribution of your data with COUNTIF and SUMIF, or identify the minimum and maximum values (MIN, MAX) to gain valuable insights from your datasets.

  • Analyze trends, central tendencies, and data dispersion.
  • Calculate common statistics like average (AVERAGE), median (MEDIAN), and standard deviation (STDEV).
  • Identify minimum and maximum values (MIN, MAX).
  • Count data meeting specific criteria (COUNTIF, SUMIF).
Function Description Syntax and Formula
AVEDEV AVEDEV: The AVEDEV function in Excel returns the average of the absolute deviations of data points from their mean. Syntax: AVEDEV(number1, [number2], …)

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.

AVERAGE The AVERAGE function in Excel calculates the arithmetic mean of a given set of values. Syntax: AVERAGE(number1, [number2], …)

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

AVERAGEA The AVERAGEA function is an Excel function that calculates the average of all numbers, including text and logical values. Syntax: AVERAGEA(value1, [value2], …)

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

AVERAGEIF The AVERAGEIF function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria. Syntax: AVERAGEIF(range, criteria, [average_range])

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.

AVERAGEIFS The AVERAGEIFS function is an Excel function that calculates the average of a range of cells that meet multiple criteria. Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

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”)

BETA.DIST BETA.DIST is an Excel function used to calculate the cumulative beta probability density function (PDF) for a given set of parameters. Syntax: BETA.DIST(x, alpha, beta, cumulative, [A], [B])

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.

BETA.INV BETA.INV is an Excel function that returns the inverse of the cumulative distribution function for a specified beta distribution. Syntax: BETA.INV(probability, alpha, beta, [A], [B])

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.

BINOM.DIST The BINOM.DIST function in Excel returns the individual term binomial distribution probability. It calculates the probability of a certain number of successes in a given number of independent trials, each with the same probability of success. Syntax: BINOM.DIST(number_s, trials, probability_s, cumulative)

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.

BINOM.DIST.RANGE BINOM.DIST.RANGE is an Excel function that calculates the probability of a certain number of successes in a sequence of independent Bernoulli trials, given a probability of success in each trial. Syntax: BINOM.DIST.RANGE(trials, probability_s, number_s, number_s2)

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)

BINOM.INV The BINOM.INV function is an Excel statistical function that returns the inverse of the cumulative binomial distribution. This function is useful in determining the number of successes given a probability and a number of trials. Syntax: BINOM.INV(probability, number_trials, alpha)

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.

CHISQ.DIST The CHISQ.DIST function returns the cumulative probability of a chi-squared distribution. Syntax: CHISQ.DIST(x,deg_freedom,cumulative)

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.

CHISQ.DIST.RT The CHISQ.DIST.RT function returns the right-tailed probability of the chi-squared distribution. Syntax: CHISQ.DIST.RT(x,deg_freedom)

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.

CHISQ.INV The CHISQ.INV function in Excel returns the inverse of the left-tailed probability of the chi-squared distribution. Syntax: CHISQ.INV(probability,deg_freedom)

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.

CHISQ.INV.RT The CHISQ.INV.RT function returns the inverse of the right-tailed probability of the chi-squared distribution. Syntax: CHISQ.INV.RT(probability,deg_freedom)

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.

CHISQ.TEST CHISQ.TEST is an Excel function used to perform a chi-square test of independence. It is used to determine whether there is a significant association between two categorical variables. Syntax: CHISQ.TEST(observed_range, expected_range)

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

CONFIDENCE.NORM The CONFIDENCE.NORM function returns the confidence interval for a population mean, using a normal distribution. This function uses a confidence level to calculate the margin of error that is added and subtracted from the sample mean to create the confidence interval. Syntax: CONFIDENCE.NORM(alpha,standard_dev,size)alpha: The significance level used to compute the confidence level.standard_dev: The standard deviation for the data range.size: The number of observations in the data range.

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.

CONFIDENCE.T The CONFIDENCE.T function is used to calculate the confidence interval for a population mean, based on a sample mean and standard deviation. Syntax: CONFIDENCE.T(alpha, standard_dev, size)

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.

CORREL CORREL is an Excel function that returns the correlation coefficient of two sets of values. It is used to measure the linear relationship between two variables. Syntax: CORREL(array1, array2)

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

COUNT The COUNT function is used to count the number of cells that contain numbers within a specified range. Syntax: COUNT(value1, [value2], …)

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.

COUNTA The COUNTA function in Microsoft Excel counts the number of cells that contain data within a range. Syntax: COUNTA(value1, [value2], …)

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

COUNTBLANK The COUNTBLANK function counts the number of empty cells in a range of cells. Syntax: COUNTBLANK(range)

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

COUNTIF COUNTIF is an Excel function used to count the number of cells within a range that meet a specified criteria. Syntax: COUNTIF(range, criteria)

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

COUNTIFS The COUNTIFS function is a statistical function used to count the number of cells in a range that meet multiple criteria. Syntax: COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2], …)

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.

COVARIANCE.P The COVARIANCE.P function calculates the population covariance of two sets of values. Syntax: COVARIANCE.P(array1, array2)

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.

COVARIANCE.S The COVARIANCE.S function is an Excel function used to calculate the sample covariance of two sets of values. Syntax: COVARIANCE.S(array1,array2)

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.

DEVSQ DEVSQ is an Excel function that calculates the sum of squares of deviations of data points from their sample mean. Syntax: DEVSQ(number1, [number2], …)

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.

EXPON.DIST The EXPON.DIST function returns the exponential distribution. It returns the probability that a random variable, which follows an exponential distribution, is less than or equal to a given value. Syntax: EXPON.DIST(x,lambda,cumulative)x: The value at which you want to evaluate the distribution.lambda: The rate parameter of the distribution.cumulative: A logical value that determines the form of the function.

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.

F.DIST F.DIST is an Excel function that returns the F probability distribution. It calculates the probability of a value occurring given a certain number of degrees of freedom. Syntax: F.DIST(x,deg_freedom1,deg_freedom2)

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

F.DIST.RT The F.DIST.RT function returns the right-tailed F probability distribution. This function is used to calculate the probability that the observed variance in a sample is greater than the variance in the entire population. Syntax: F.DIST.RT(x,deg_freedom1,deg_freedom2)

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.

F.INV F.INV is an Excel function used to calculate the inverse of the F-distribution. It returns the probability associated with a given x-value and degrees of freedom. Syntax: F.INV(probability, degrees_freedom1, degrees_freedom2)

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.

F.INV.RT F.INV.RT is an Excel function that returns the inverse of the F probability distribution. It returns the value of the inverse cumulative distribution function (inverse of the cumulative probability) for a given probability. Syntax: F.INV.RT(probability,deg_freedom1,deg_freedom2)

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.

F.TEST F.TEST is an Excel function used to calculate the probability of two samples having the same variance. It takes two sets of data as its arguments and returns a probability value. Syntax: F.TEST(array1, array2)

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.

FISHER FISHER is an Excel function that returns the Fisher transformation at x-value. The Fisher transformation is a way to normalize data that may not be normally distributed. Syntax: FISHER(x)

Formula: FISHER(0.5) returns 0.279415498198926

FISHERINV The FISHERINV function returns the inverse of the Fisher transformation at a specified value. Syntax: FISHERINV(x)

Formula: FISHERINV(0.9) returns 0.47140452079103

FORECAST The FORECAST function is used to calculate a future value based on existing values provided. It uses linear regression to calculate the value. Syntax: FORECAST(x, known_y’s, known_x’s)

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.

FORECAST.ETS The FORECAST.ETS function is an Excel function that predicts future values based on existing values. It uses the Exponential Triple Smoothing (ETS) algorithm to predict future values. Syntax: FORECAST.ETS(known_y’s, [known_x’s], new_x’s, [seasonality], [trend], [confidence])

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%.

FORECAST.ETS.CONFINT The FORECAST.ETS.CONFINT function is an Excel function that returns a confidence interval for a forecast generated by the FORECAST.ETS function. Syntax: FORECAST.ETS.CONFINT(known_y’s, [known_x’s], [confidence_level], [forecast_type], [seasonality], [data_completion], [aggregation])

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.

FORECAST.ETS.SEASONALITY The FORECAST.ETS.SEASONALITY function in Microsoft Excel is used to predict future values based on existing values that have a seasonal pattern. Syntax: FORECAST.ETS.SEASONALITY(x, known_y’s, [seasonality], [data_completion], [aggregation])

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.

FORECAST.ETS.STAT The FORECAST.ETS.STAT function in Microsoft Excel is used to calculate a future value based on existing values using the Exponential Triple Smoothing (ETS) algorithm. Syntax: FORECAST.ETS.STAT(known_y’s, [known_x’s], [new_x’s], [stat_type], [seasonality], [aggregation])

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

FORECAST.LINEAR The FORECAST.LINEAR function is an Excel function that predicts a value based on existing values. It uses a linear regression algorithm to calculate the best fit line for the existing values and predict a value for the specified x value. Syntax: FORECAST.LINEAR(x, known_y’s, known_x’s)

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.

FREQUENCY The FREQUENCY function in Excel returns a frequency distribution, which is a summary table that shows the frequency of values within a range. Syntax: FREQUENCY(data_array, bins_array)

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.

GAMMA The GAMMA function is used to calculate the gamma value of a given number. Gamma is a mathematical function that is used to calculate the probability of a random variable taking on a value less than or equal to a certain number. Syntax: GAMMA(number)

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

GAMMA.DIST The GAMMA.DIST function in Excel returns the gamma distribution, which is a type of probability distribution. It is used to calculate the probability that a random variable is less than or equal to a given value. Syntax: GAMMA.DIST(x,alpha,beta,cumulative)

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.

GAMMA.INV The GAMMA.INV function returns the inverse of the gamma cumulative distribution. Syntax: GAMMA.INV(probability,alpha,beta,cumulative)

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.

GAMMALN GAMMALN: The GAMMALN function returns the natural logarithm of the gamma function, G(x). Syntax: GAMMALN(x)

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

GAMMALN.PRECISE The GAMMALN.PRECISE function is an Excel function that returns the natural logarithm of the gamma function, G(x), to a high degree of precision. Syntax: GAMMALN.PRECISE(x)

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.

GAUSS The GAUSS function returns the cumulative distribution function (CDF) of a standard normal distribution. Syntax: GAUSS(x)

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

GEOMEAN The GEOMEAN function calculates the geometric mean of a set of numbers. The geometric mean is the average of a set of numbers multiplied together and then taking the nth root, where n is the number of numbers in the set. Syntax: GEOMEAN(number1, [number2], …)

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).

GROWTH The GROWTH function returns an array of y-values for a series of new x-values that are linearly interpolated from a given set of x-values and y-values. Syntax: GROWTH(known_y’s, [known_x’s], [new_x’s], [const])

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

HARMEAN The HARMEAN function is used to calculate the harmonic mean of a set of numbers. The harmonic mean is the reciprocal of the arithmetic mean of the reciprocals of the given numbers. Syntax: HARMEAN(number1, [number2], …)

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

HYPGEOM.DIST HYPGEOM.DIST is an Excel function that calculates the probability of a given number of successes in a sample, without replacement, from a population of a given size. Syntax: HYPGEOM.DIST(sample_s, number_sample, population_s, successes, cumulative)

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).

INTERCEPT The INTERCEPT function in Excel returns the y-intercept of a given linear regression line. It is used to calculate the point at which a line crosses the y-axis. Syntax: INTERCEPT(known_y’s, known_x’s)

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.

KURT KURT: The KURT function in Excel returns the kurtosis of a data set. Kurtosis is a measure of the “peakedness” of a distribution. Syntax: KURT(number1, [number2],…)

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

LARGE The LARGE function returns the nth largest value from a range of values. Syntax: LARGE(array, nth)

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

LINEST The LINEST function is a statistical function in Excel that returns the parameters of a linear trend. It can be used to calculate the slope and y-intercept of a line, as well as the correlation coefficient, standard error of the estimate, and other statistics. Syntax: LINEST(known_y’s, [known_x’s], [const], [stats])

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

LOGEST The LOGEST function is used to calculate an exponential curve that best fits a set of data points. It returns an array of values that describe the exponential curve, including the y-intercept, the slope, and the correlation coefficient. Syntax: LOGEST(known_y’s, [known_x’s], [const], [stats])

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.

LOGNORM.DIST The LOGNORM.DIST function returns the cumulative lognormal distribution of x, given parameters for the distribution’s mean and standard deviation. Syntax:LOGNORM.DIST(x, mean, standard_dev, cumulative)

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.

LOGNORM.INV The LOGNORM.INV function returns the inverse of the lognormal cumulative distribution for a specified value, mean and standard deviation. Syntax: LOGNORM.INV(probability, mean, standard_dev)

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.

MAX The MAX function in Excel returns the largest value from a range of values. Syntax: MAX(number1, [number2], …)

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

MAXA The MAXA function is an Excel function that returns the maximum value in a range of values, including logical values and text. Syntax: MAXA(value1, [value2], …)

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

MAXIFS The MAXIFS function returns the maximum value in a range, based on multiple criteria. Syntax: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

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.

MEDIAN The MEDIAN function returns the median of the given numbers. The median is the middle number in a set of numbers; it is calculated by arranging all the numbers from lowest to highest value and picking the middle number. Syntax: MEDIAN(number1, [number2], …)

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

MIN The MIN function returns the smallest value from a given set of numbers. Syntax: MIN(number1, [number2], …)

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

MINA The MINA function is used to find the minimum value in a given array of values, including text and logical values. Syntax: MINA(value1, [value2], …)

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

MINIFS The MINIFS function is used to find the minimum value from a range of cells that meet multiple criteria. Syntax: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

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.

MODE.MULT MODE.MULT is an Excel function that returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data. Syntax: MODE.MULT(array)

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

MODE.SNGL MODE.SNGL is an Excel function that returns the most frequently occurring value in a set of numbers. Syntax: MODE.SNGL(number1,[number2],…)

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.

NEGBINOM.DIST NEGBINOM.DIST is an Excel function that calculates the negative binomial distribution. The negative binomial distribution is a probability distribution that is used to represent the number of successes in a sequence of independent Bernoulli trials before a specified number of failures occur. Syntax: NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) Where:number_f = the number of failuresnumber_s = the number of successesprobability_s = the probability of successcumulative = a logical value that determines the form of the function (TRUE for cumulative distribution function and FALSE for probability mass function)

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.

NORM.DIST NORM.DIST is an Excel function that returns the cumulative normal distribution for a given mean and standard deviation. It is used to calculate the probability that a random variable is less than or equal to a certain value. Syntax: NORM.DIST(x, mean, standard_dev, cumulative)Where:x = The value for which you want to calculate the distribution.mean = The mean of the distribution.standard_dev = The standard deviation of the distribution.cumulative = A logical value that determines the form of the function. If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

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.

NORM.INV NORM.INV is an Excel function that returns the inverse of the standard normal cumulative distribution. The syntax is NORM.INV(probability, mean, standard_dev). Syntax: NORM.INV(probability, mean, standard_dev)

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.

NORM.S.DIST NORM.S.DIST is an Excel statistical function that returns the standard normal cumulative distribution function. It gives the probability that a random variable with a normal distribution will be less than or equal to a specified value. Syntax: NORM.S.DIST(x, cumulative)x: The value for which you want the distribution.Cumulative: A logical value that determines 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.

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.

NORM.S.INV NORM.S.INV is an Excel function that returns the inverse of the standard normal cumulative distribution. This function is used to calculate the probability of a value being greater than or less than a certain number. Syntax: NORM.S.INV(probability)

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.

PEARSON The PEARSON function returns the Pearson product moment correlation coefficient, which is a measure of the linear correlation between two variables. Syntax: PEARSON(array1, array2)

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

PERCENTILE.EXC The PERCENTILE.EXC function is an Excel function that returns the k-th percentile of values in a range. Syntax: PERCENTILE.EXC(array, k)

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

PERCENTILE.INC The PERCENTILE.INC function in Excel returns the k-th percentile of values in a range. Syntax: PERCENTILE.INC(array, k)

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

PERCENTRANK.EXC The PERCENTRANK.EXC function returns the rank of a value in a data set as a percentage of the data set. Syntax: PERCENTRANK.EXC(array, x, [significance])

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.

PERCENTRANK.INC The PERCENTRANK.INC function returns the rank of a value in a data set as a percentage of the data set. Syntax: PERCENTRANK.INC(array, x, [significance])

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.

PERMUT The PERMUT function is used to calculate the number of permutations for a given number of objects. A permutation is the arrangement of a set of objects in a specific order. Syntax: PERMUT(number, number_chosen)

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.

PERMUTATIONA The PERMUTATIONA function returns the number of permutations for a given number of objects that can be selected from number objects. Syntax: PERMUTATIONA(number, number_chosen)

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

PHI PHI is a statistical function in Excel that calculates the value of the Phi coefficient, which is a measure of association between two binary variables. It is often used to measure the strength of a linear relationship between two variables. Syntax: PHI(x, y)

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

POISSON.DIST The POISSON.DIST function in Excel returns the probability of a given number of events occurring in a fixed period of time when the rate of events is known. Syntax: POISSON.DIST(x,mean,cumulative)

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.

PROB The PROB function in Microsoft Excel is used to calculate the probability of a given event. It returns the probability that values in a range are between two limits. Syntax: PROB(x_range,prob_range,lower_limit,upper_limit)

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

QUARTILE.EXC The QUARTILE.EXC function returns the quartile of a dataset, which is a type of statistical measure. Syntax: QUARTILE.EXC(array, quart)

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

QUARTILE.INC The QUARTILE.INC function returns the quartile of a given data set, where quartiles divide a ranked set of data into four equal parts. Syntax: QUARTILE.INC(array, quart)Array: The array or range of cells containing numerical data for which you want the quartile value.Quart: A number specifying which quartile value to return.

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

RANK.AVG The RANK.AVG function returns the rank of a number in a list of numbers. It assigns the same rank to numbers with the same value. Syntax: RANK.AVG(number,ref,[order])

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.

RANK.EQ The RANK.EQ function returns the rank of a number in a list of numbers. Syntax: RANK.EQ (number, reference, [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.

RSQ RSQ is a statistical function in Microsoft Excel that returns the square of the Pearson product-moment correlation coefficient (R-squared) of two supplied sets of values. It is used to measure how closely two sets of data points fit a given regression line. Syntax: RSQ(known_y’s, known_x’s)

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

SKEW SKEW: The SKEW function returns the skewness of a distribution based on a supplied set of values. Skewness is a measure of asymmetry in a probability distribution. Syntax: SKEW(number1,[number2],…)

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

SKEW.P SKEW.P is a statistical function in Microsoft Excel that calculates the skewness of a distribution based on a population of numerical data. Syntax: SKEW.P(number1,[number2],…)

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

SLOPE The SLOPE function calculates the slope of a regression line based on the given data points. It is used to measure the rate of change in a data set. Syntax: SLOPE (known_y’s, known_x’s)

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

SMALL The SMALL function returns the nth smallest value from a given set of values. Syntax: SMALL(array, nth_smallest)

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

STANDARDIZE The STANDARDIZE function returns a normalized value from a distribution characterized by a mean and standard deviation. Syntax: STANDARDIZE(x, mean, standard_dev)

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.

STDEV.P STDEV.P is an Excel function that calculates the standard deviation of a population based on a sample of numeric values. Syntax: STDEV.P(number1, [number2], …)

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

STDEV.S STDEV.S is an Excel function that calculates the standard deviation of a sample based on a supplied set of values. Syntax: STDEV.S(number1, [number2], …)

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

STDEVA STDEVA is an Excel function that returns the standard deviation of a population based on a sample of numeric values. Syntax: STDEVA(number1, [number2], …)

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

STDEVPA STDEVPA is an Excel function that calculates the standard deviation of a population based on a sample of numbers. It takes the following syntax:STDEVPA(number1, [number2], …)Where number1, number2, etc. are the numeric values for which you want to calculate the standard deviation. Syntax: STDEVPA(value1, [value2], …)

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

STEYX STEYX is a statistical function in Microsoft Excel that calculates the standard error of the predicted y-value for each x in the regression. Syntax: STEYX(known_y’s, known_x’s)

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

T.DIST T.DIST is an Excel function that returns the probability associated with a Student’s t-distribution. Syntax: T.DIST(x,deg_freedom,cumulative)

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.

T.DIST.2T The T.DIST.2T function returns the probability associated with a Student’s t-distribution. It can be used to determine the probability of a given range of values. Syntax: T.DIST.2T(x,deg_freedom,cumulative)

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.

T.DIST.RT The T.DIST.RT function returns the right-tailed Student’s t-distribution. The function is used to calculate probabilities for a given value of the t-distribution. Syntax: T.DIST.RT(x,deg_freedom,cumulative)

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

T.INV T.INV is an Excel function that returns the t-value of the Student’s t-distribution, given the probability and the degrees of freedom. Syntax: T.INV(probability, 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.

T.INV.2T The T.INV.2T function returns the two-tailed probability of the Student’s t-distribution. Syntax: T.INV.2T(probability,deg_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.

T.TEST T.TEST is an Excel statistical function used to calculate the probability associated with the Student’s t-test. The syntax for the T.TEST function is:T.TEST(array1, array2, tails, type)Where:• array1 – The first array or range of data.• array2 – The second array or range of data.• tails – The number of tails in the t-test. This can either be 1 (for one-tailed) or 2 (for two-tailed).• type – The type of t-test to be performed. This can either be 1 (for paired) or 2 (for two-sample equal variance). Syntax: T.TEST(array1,array2,tails,type)

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.

TREND The TREND function is used to calculate linear trend line to a given set of data points and extend it to new data points. Syntax: TREND(known_y’s, [known_x’s], [new_x’s], [const])

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.

TRIMMEAN The TRIMMEAN function is an Excel function used to calculate the mean of a dataset after a certain percentage of the data points have been excluded from the calculation. Syntax: TRIMMEAN(array, percent)

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.

VAR.P VAR.P is a statistical function in Excel that calculates the population variance of a given set of values. Syntax: VAR.P(value1, [value2], …)

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).

VAR.S VAR.S is an Excel function that calculates the sample variance of a population based on a sample of numbers. Syntax: VAR.S(number1, [number2], …)

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.

VARA The VARA function in Excel returns the variance of an array or range of numbers. Syntax: VARA(number1, [number2], …)

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

VARPA VARPA is an Excel function that calculates the variance of an entire population based on a set of values. Syntax: VARPA(value1, [value2], …)

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

WEIBULL.DIST WEIBULL.DIST is an Excel function used to calculate the probability that a value is less than or equal to a given value in a Weibull distribution. Syntax: WEIBULL.DIST(x,alpha,beta,cumulative)x: The value for which you want to calculate the distribution.alpha: The shape parameter of the distribution.beta: The scale parameter of the distribution.cumulative: A logical value that determines the form of the function. If cumulative is TRUE, WEIBULL.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

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.

Z.TEST The Z.TEST function in Excel is used to calculate the one-tailed or two-tailed probability-value of a z-test. Syntax: Z.TEST(array, x, sigma)

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.

 

Financial Functions

Become a Financial Whiz: Excel empowers you to become a financial whiz with its dedicated Financial Functions. From calculating loan payments (PMT) to analyzing investment returns (IRR, NPV, XIRR), these functions provide the tools needed for financial modeling, cash flow forecasting, and informed investment decisions.

  • Perform financial calculations like loan payments (PMT) and interest rates (IRR).
  • Analyze investment returns (NPV, XIRR).
  • Create financial models and cash flow forecasts.
Function Description Syntax and Formula
ACCRINT The ACCRINT function in Microsoft Excel calculates the accrued interest for a security that pays periodic interest. Syntax: ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

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.

ACCRINTM The ACCRINTM function returns the amount of interest that has been accrued on a security that pays periodic interest, such as a bond, between a start and end date. Syntax: ACCRINTM(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

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.

AMORDEGRC The AMORDEGRC function returns the depreciation of an asset for a specified period using a depreciation coefficient from a table of depreciation coefficients. Syntax: AMORDEGRC(cost, date_purchased, first_period, salvage, period, [month])

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.

AMORLINC The AMORLINC function is used to calculate the depreciation of an asset for a specified period using the linear depreciation method. Syntax: AMORLINC(cost, date_purchased, first_period, salvage, period, [rate])

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)

COUPDAYBS The COUPDAYBS function returns the number of days from the beginning of the coupon period to the settlement date. Syntax: COUPDAYBS(settlement, maturity, frequency, [basis])

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.

COUPDAYS The COUPDAYS function returns the number of days in the coupon period that contains the settlement date. Syntax: COUPDAYS(settlement, maturity, frequency, [basis])

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.

COUPDAYSNC The COUPDAYSNC function returns the number of days in the coupon period that contains the settlement date for a security with a periodic interest payment. Syntax: COUPDAYSNC(settlement, maturity, frequency, [basis])

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.

COUPNCD The COUPNCD function returns the next coupon date after the settlement date for a security with a specified frequency of coupon payments. Syntax: COUPNCD(settlement, maturity, frequency, [basis])

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).

COUPNUM COUPNUM is an Excel function that calculates the number of coupon payments between two dates. It is used to calculate the number of times an investor will receive a coupon payment between two dates. Syntax: COUPNUM(settlement, maturity, frequency, [basis])

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.

COUPPCD The COUPPCD function returns the next coupon payment date after the settlement date. Syntax: COUPPCD(settlement, maturity, frequency, [basis])

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).

CUMIPMT The CUMIPMT function in Excel calculates the cumulative interest paid on a loan between a start period and an end period. Syntax: CUMIPMT(rate, nper, pv, start_period, end_period, type)

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.

CUMPRINC The CUMPRINC function in Excel is used to calculate the cumulative principal paid on a loan between a start period and an end period. Syntax: CUMPRINC(rate, nper, pv, start_period, end_period, type)

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.

DB DB: The DB function is a financial function that returns the depreciation of an asset for a specified period using the fixed-declining balance method. Syntax: DB(cost, salvage, life, period, [month])

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.

DDB The DDB function is used to calculate the depreciation of an asset for a specified period using the double-declining balance method. Syntax: DDB(cost, salvage, life, period, [factor])

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.

DISC The DISC function returns the discount rate for a security. Syntax: DISC(settlement, maturity, pr, redemption, [basis])

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.

DOLLARDE DOLLARDE is an Excel function that converts a dollar price expressed as a fraction into a dollar price expressed as a decimal. Syntax: DOLLARDE(fractional_dollar, fraction)

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

DOLLARFR DOLLARFR is an Excel function that converts a number to text in a currency format. Syntax: DOLLARFR(number,decimals)

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

DURATION The DURATION function in Excel calculates the number of days between two given dates. Syntax: DURATION(settlement, maturity, frequency, [basis])

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.

EFFECT The EFFECT function is an Excel function that calculates the effective annual interest rate. It takes two arguments, the nominal interest rate and the number of compounding periods per year. Syntax: EFFECT(nominal_rate, npery)

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.

FV FV: The FV function in Excel is used to calculate the future value of an investment. It takes three arguments: rate, nper, and pmt. Syntax: FV(rate, nper, pmt)

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%.

FVSCHEDULE FVSCHEDULE is an Excel function that returns the future value of an investment based on a series of periodic payments and a constant interest rate. Syntax: FVSCHEDULE(principal, schedule)

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.

INTRATE INTRATE calculates the interest rate for a fully invested security. Syntax: INTRATE(settlement, maturity, investment, redemption, [basis])

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.

IPMT IPMT: The IPMT function in Excel is used to calculate the interest payment for a given period of an investment or loan. Syntax: IPMT(rate, period, number_of_periods, present_value, [future_value], [type])

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.

IRR The IRR function in Excel is a financial function that returns the internal rate of return for a series of cash flows. The internal rate of return is the discount rate that sets the net present value of the cash flows to zero. Syntax: IRR(values, [guess])

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.

ISPMT The ISPMT function calculates the interest paid during a specific period of an investment. Syntax: ISPMT(rate, period, number_of_periods, present_value)

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.

MDURATION The MDURATION function returns the Macauley duration of a security with an assumed par value of $100. Syntax: MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

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.

MIRR MIRR: The MIRR function is an Excel financial function that returns the modified internal rate of return for a series of periodic cash flows. Syntax: MIRR(values, finance_rate, reinvest_rate)

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.

NOMINAL NOMINAL: This function is used to calculate the nominal interest rate given the effective rate and the number of compounding periods per year. Syntax: NOMINAL(effect_rate, npery)

Formula: NOMINAL(0.1, 4)Result: 0.0943

NPER The NPER function returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Syntax: NPER(rate, pmt, pv, [fv], [type])

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.

NPV NPV is a financial function in Excel used to calculate the net present value of an investment. It takes into account the initial investment, the rate of return, and the number of periods in the investment. Syntax: NPV(rate, value1, value2, …)

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.

ODDFPRICE ODDFPRICE is an Excel function that calculates the price per $100 face value of a security with an odd first period. Syntax: ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])

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.

ODDFYIELD The ODDFYIELD function returns the odd yield of a security with an odd first period. Syntax: ODDFYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis])

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).

ODDLPRICE ODDLPRICE is an Excel function that returns the price per unit of an odd lot, which is an order for a quantity of stock that is less than the standard trading unit. Syntax: ODDLPRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

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.

ODDLYIELD The ODDLYIELD function is used to calculate the yield of a security that pays odd interest payments. Syntax: ODDLYIELD(settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [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.

PDURATION PDURATION is a financial function in Excel that calculates the number of periods required to pay off a loan or make an investment given the present value, periodic payment, and future value. Syntax: PDURATION(present_value, payment, future_value)

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.

PMT PMT is an Excel function used to calculate the periodic payment for a loan. The syntax is PMT(rate, nper, pv, [fv], [type]).Rate is the interest rate per period.Nper is the total number of payments for the loan.Pv is the present value (or principal) of the loan.Fv is the future value (or balance) of the loan after the last payment.Type is the number 0 or 1 and indicates when payments are due. Syntax: PMT(rate, nper, pv, [fv], [type])

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.

PPMT The PPMT function in Excel calculates the principal payment for a given period for an investment based on a constant periodic payment and a constant interest rate. Syntax: PPMT(rate, period, number_of_periods, present_value, future_value, type)

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.

PRICE The PRICE function is used to calculate the price per $100 face value of a security that pays periodic interest. Syntax: PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

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.

PRICEDISC PRICEDISC is an Excel function that calculates the price of a discounted security. It takes three arguments: the settlement date, the maturity date, and the discount rate. Syntax: PRICEDISC(settlement, maturity, discount)

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.

PRICEMAT PRICEMAT is an Excel function that calculates the price of a security based on its yield. It takes three arguments: settlement, maturity, and rate. Syntax: PRICEMAT(settlement, maturity, rate)

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

PV The PV function calculates the present value of an investment, which is the total amount that a series of future payments is worth now. Syntax: PV(rate, nper, pmt, [fv], [type])

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

RATE The RATE function is used to calculate the interest rate per period of an annuity. Syntax: RATE(nper, pmt, pv, [fv], [type], [guess])

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.

RECEIVED The RECEIVED function is a financial function in Microsoft Excel that returns the amount received at maturity for a fully invested security. Syntax: RECEIVED(settlement, maturity, investment, discount, [basis])

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).

RRI RRI: The RRI function is used to calculate the rate of return on an investment over a period of time. Syntax: RRI(nper, pv, fv)nper: The total number of periods over which the investment is made.pv: The present value of the investment.fv: The future value of the investment.

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%.

SLN SLN: The SLN function is used to calculate the straight-line depreciation of an asset for a single period. Syntax: SLN(cost, salvage, life)

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.

SYD The SYD function returns the sum-of-years’ digits depreciation of an asset for a specified period. Syntax: SYD(cost, salvage, life, period)

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.

TBILLEQ The TBILLEQ function is used to calculate the bond-equivalent yield of a treasury bill. Syntax: TBILLEQ(settlement, maturity, discount)

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.

TBILLPRICE TBILLPRICE is an Excel function that calculates the price of a Treasury bill based on the rate, issue date, and maturity date. Syntax: TBILLPRICE(settlement, maturity, discount)

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

TBILLYIELD The TBILLYIELD function returns the yield for a Treasury bill, given the discount rate, settlement date, and maturity date. Syntax: TBILLYIELD(discount, settlement, maturity)

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

VDB VDB stands for Variable Declining Balance. It is a financial function that is used to calculate the depreciation of an asset over a period of time. Syntax: VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])

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.

XIRR XIRR is an Excel function that calculates the internal rate of return for a series of cash flows that occur at irregular intervals. It is used to calculate the rate of return for investments that don’t have a fixed schedule of payments, such as investments in stocks or mutual funds. Syntax: XIRR (values, dates, [guess])

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.

XNPV The XNPV function calculates the net present value of an investment based on a discount rate and a series of cash flows. Syntax: XNPV(rate, values, dates)

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.

YIELD YIELD: The YIELD function returns the yield on a security that pays periodic interest. Syntax: YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

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).

YIELDDISC YIELDDISC is an Excel function used to calculate the yield on a discounted security. It takes the following arguments:• Settlement: The security’s settlement date.• Maturity: The security’s maturity date.• Price: The security’s price per $100 face value.• Redemption: The security’s redemption value per $100 face value.• Basis: The type of day count basis to use. Syntax: YIELDDISC(settlement, maturity, price, redemption, basis)

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.

YIELDMAT YIELDMAT is an Excel function used to calculate the yield of a security that pays periodic interest. It is used to calculate the yield of a bond or other security on a given date. Syntax: YIELDMAT(settlement, maturity, issue, rate, pr, redemption, frequency, [basis])

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.

 

Compatibility Functions

Working with Older Excel Versions: Compatibility functions act as bridges between spreadsheets created in different Excel versions.  These functions offer alternative names for functions that have been renamed in newer versions.  Using them guarantees your formulas work correctly even when opened in older Excel, ensuring seamless collaboration and data exchange.

    • Compatibility functions are provided for functions that have been renamed in newer versions of Excel.
    • Using these functions ensures your spreadsheets work correctly if opened in older Excel versions.
    • If backward compatibility is not essential, it’s recommended to use the newer functions for improved accuracy and clarity.
Function Description Syntax and Formula
BETADIST The BETADIST function in Excel returns the cumulative beta probability density function or the probability that a variable, which follows a beta distribution, is less than or equal to a specified value. Syntax: BETADIST(x,alpha,beta,A,B)x: A value at which the probability density function is to be evaluated.alpha: The alpha parameter of the beta distribution.beta: The beta parameter of the beta distribution.A: Optional. The lower bound of the beta distribution.B: Optional. The upper bound of the beta distribution.

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.

BETAINV The BETAINV function returns the inverse of the cumulative beta probability density function (BETA.DIST). Syntax: BETAINV(probability, alpha, beta, [A], [B])

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.

BINOMDIST The BINOMDIST function in Excel calculates the individual term binomial distribution probability. Syntax: BINOMDIST(number_s,trials,probability_s,cumulative)

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.

CHIDIST CHIDIST is an Excel function that returns the one-tailed probability of the chi-squared distribution. The syntax for CHIDIST is:CHIDIST(x,deg_freedom)Where x is the value at which to evaluate the distribution, and deg_freedom is the number of degrees of freedom. Syntax: CHIDIST(x,deg_freedom)

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.

CHIINV The Excel CHIINV function returns the inverse of the left-tailed probability of the chi-squared distribution. Syntax: CHIINV(probability,deg_freedom)

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.

CHITEST The CHITEST function in Microsoft Excel is used to calculate the test for independence of two ranges of categorical data. Syntax: CHITEST(actual_range, expected_range)

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

CONFIDENCE CONFIDENCE is an Excel function that calculates the confidence interval for a population mean, using a normal distribution. It takes three arguments: alpha, standard_dev, and size.Alpha is the significance level used to compute the confidence level. It is a number between 0 and 1.Standard_dev is the standard deviation for the population.Size is the sample size. Syntax: CONFIDENCE(alpha,standard_dev,size)

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.

COVAR COVAR is an Excel function that calculates the covariance between two sets of data. It is used to measure the linear relationship between two variables. Syntax: COVAR(array1, array2)

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.

CRITBINOM The CRITBINOM function returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. Syntax: CRITBINOM(trials, probability_s, alpha)

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.

EXPONDIST The EXPONDIST function returns the exponential distribution for a specified value, lambda, and cumulative. Syntax: EXPONDIST(x,lambda,cumulative)

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

FDIST The FDIST function in Excel is a statistical function that returns the F probability distribution. It returns the probability that the random variable has a value between two specified values. Syntax: FDIST(x,deg_freedom1,deg_freedom2)

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.

FINV FINV is an Excel function used to calculate the inverse of the F-Distribution. Syntax: FINV(probability, degrees_freedom1, degrees_freedom2)

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.

FTEST The FTEST function returns the result of an F-test. An F-test is a type of statistical test that is used to compare the variances of two data sets. Syntax: FTEST(array1, array2)

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

GAMMADIST GAMMADIST is an Excel function that calculates the Gamma probability density function or the cumulative Gamma distribution. Syntax: GAMMADIST(x,alpha,beta,cumulative)

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

GAMMAINV The GAMMAINV function returns the inverse of the gamma cumulative distribution. Syntax: GAMMAINV(probability,alpha,beta)

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.

HYPGEOMDIST The HYPGEOMDIST function returns the hypergeometric distribution. It is used to calculate the probability of a given number of successes in a sample of a given size, taken from a population of a given size containing a certain number of successes. Syntax: HYPGEOMDIST(sample_s, number_sample, population_s, number_successes)

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.

LOGINV LOGINV is an Excel function that returns the inverse of the lognormal cumulative distribution. Syntax: LOGINV(probability, mean, standard_dev)

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

LOGNORMDIST The LOGNORMDIST function in Microsoft Excel returns the cumulative log-normal distribution for a given set of parameters. Syntax: LOGNORMDIST(x, mean, standard_dev)

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.

MODE MODE: The MODE function returns the most frequently occurring, or repetitive, value in an array or range of data. Syntax: MODE(number1, [number2], …)

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

NEGBINOMDIST The NEGBINOMDIST function returns the probability of a given number of failures occurring before a specified number of successes in a negative binomial experiment. Syntax: NEGBINOMDIST(number_f, number_s, probability_s)

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.

NORMDIST NORMDIST is a statistical function in Microsoft Excel which returns the normal cumulative distribution for a specified mean and standard deviation. Syntax: NORMDIST(x, mean, standard_dev, cumulative)

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).

NORMINV The NORMINV function returns the inverse of the normal cumulative distribution for a given mean and standard deviation. Syntax: NORMINV(probability, mean, standard_dev)

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.

NORMSDIST The NORMSDIST function returns the cumulative normal distribution for a given value. It is used to calculate the probability that a random variable will take a value less than or equal to a given value. Syntax: NORMSDIST(x)

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)

NORMSINV NORMSINV is an Excel function that calculates the inverse of the standard normal cumulative distribution. It returns the value of the inverse standard normal distribution for a given probability. Syntax: NORMSINV(probability)

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.

PERCENTILE The PERCENTILE function returns the k-th percentile of values in a range. Syntax: PERCENTILE(array,k)

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

PERCENTRANK The PERCENTRANK function in Excel returns the rank of a value in a data set as a percentage of the data set. Syntax: PERCENTRANK(array, x, [significance])

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.

POISSON The POISSON function in Excel calculates the probability of a given number of events occurring in a fixed period of time, given the average rate of occurrence. Syntax: POISSON(x, mean, cumulative)x = The number of eventsmean = The expected mean number of eventscumulative = A logical value that determines the form of the function (TRUE for cumulative, FALSE for probability)

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.

QUARTILE The QUARTILE function returns the quartile of a given set of data. Quartiles divide a set of data into four equal parts. Syntax: QUARTILE(array, quart)

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

RANK The RANK function in Excel is used to return the rank of a number within a set of numbers. Syntax: RANK(number,ref,[order])

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.

STDEV STDEV is an Excel function that calculates the standard deviation of a set of numbers. It is used to measure the amount of variation or dispersion from the average of a set of values. Syntax: STDEV(number1, [number2], …)

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

STDEVP STDEVP is a statistical function in Microsoft Excel that calculates the standard deviation of a population based on a sample of numbers. Syntax: STDEVP(number1, [number2], …)

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

TDIST The TDIST function returns the probability associated with a Student’s t-distribution. Syntax: TDIST(x,deg_freedom,tails)

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.

TINV TINV is an Excel function used to calculate the inverse of the t-distribution. It is used to calculate the critical value of the t-distribution, given a certain significance level. Syntax: TINV(probability, degrees_of_freedom)

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.

TTEST TTEST Function:The TTEST function calculates the probability associated with the Student’s t-test. The Student’s t-test is used to determine whether two samples are likely to have come from the same two underlying populations that have the same mean. Syntax: TTEST(array1, array2, tails, type)Array1: The first array of data.Array2: The second array of data.Tails: The number of tails in the t-test. The default value is 2.Type: The type of t-test. The default value is 1.

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.

VAR The VAR function in Excel is used to calculate the variance of a set of values. Syntax: VAR(number1, [number2], …)

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

VARP VARP is an Excel function that calculates the variance of a population based on a set of values. Syntax: VARP(value1, [value2], …)

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

WEIBULL The WEIBULL function in Excel calculates the Weibull probability density function or the Weibull cumulative distribution function for a supplied set of parameters. Syntax: WEIBULL(x,alpha,beta,cumulative)

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

ZTEST The ZTEST function in Excel calculates the one-tailed probability-value (P-value) of a z-test. Syntax: ZTEST(array, x, [sigma])

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.

 

Cube Functions

Unlock the Secrets of Multidimensional Data Analysis with Cube Functions, specifically designed for PivotTables. Explore data relationships, interact with different dimensions, and create dynamic reports to gain deeper insights from your complex datasets. (Note: Requires knowledge of PivotTables)

  • Specifically designed for PivotTables.
  • Explore data relationships and interact with different dimensions.
  • Create dynamic reports for deeper insights from complex datasets.
Function Description Syntax and Formula
CUBEKPIMEMBER CUBEKPIMEMBER is an Excel function that returns a key performance indicator (KPI) value from an OLAP cube. Syntax: CUBEKPIMEMBER(cube_name, kpi_name)

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

CUBEMEMBER The CUBEMEMBER function returns information about a member or tuple within a cube. Syntax: CUBEMEMBER(cube_name, member_name)

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

CUBEMEMBERPROPERTY The CUBEMEMBERPROPERTY function returns a property value for a given cube member. Syntax: CUBEMEMBERPROPERTY(cube_name, member_name, property_name)

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.

CUBERANKEDMEMBER The CUBERANKEDMEMBER function is a Microsoft Excel function that returns the nth item in a set, based on its rank in the set. Syntax: CUBERANKEDMEMBER(set, rank [, [ascending | descending]])

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.

CUBESET The CUBESET function is used to create a set of members or tuples from a cube. Syntax: CUBESET(cube_name, set_expression, [name])

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

CUBESETCOUNT The CUBESETCOUNT function in Excel returns the number of sets in a cube. Syntax: CUBESETCOUNT(cube_name)

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

CUBEVALUE The CUBEVALUE function is a Microsoft Excel function that returns a value from an OLAP cube. Syntax: CUBEVALUE(connection, cube_ref, field1, field2, …)

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

 

Database Functions

Bridge the Gap Between Excel and External Databases. These advanced tools allow you to retrieve, manipulate, and aggregate data directly from database tables, streamlining data integration and analysis for power users.

  • Retrieve, manipulate, and aggregate data directly from database tables.
  • Requires a basic understanding of database structures and functionalities.
Function Description Syntax and Formula
DAVERAGE DAVERAGE is an Excel function that calculates the average of selected database entries that meet certain criteria. Syntax: DAVERAGE(database, field, criteria)

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”)

DCOUNT DCOUNT: The DCOUNT function counts the cells that contain numbers in a database. Syntax: DCOUNT(database, field, criteria)Database: This is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields.Field: This is the column in the database that you want to count the numbers in.Criteria: This is an optional range of cells that contains criteria.

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.

DCOUNTA DCOUNTA is an Excel function that counts the number of cells in a database that contain numbers and meets a given set of criteria. Syntax: DCOUNTA(database, field, criteria)

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.

DGET The DGET function is used to extract a single value from a specified database record (row) that matches the given criteria. Syntax: DGET(database, field, criteria)

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

DMAX DMAX is an Excel function that returns the maximum value from a specified database column that meets a given criteria. Syntax: DMAX(database, field, criteria)

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”.

DMIN The DMIN function returns the smallest number in a set of values from a database. Syntax: DMIN(database, field, criteria)

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

DPRODUCT The DPRODUCT function multiplies the corresponding values in two or more arrays and returns the sum of the products. Syntax: DPRODUCT(array1, array2, [array3], …)

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.

DSTDEV DSTDEV is an Excel statistical function that calculates the standard deviation of a population based on a sample of numbers. Syntax: DSTDEV(number1, [number2], …)

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

DSTDEVP DSTDEVP is a statistical function in Microsoft Excel that returns the standard deviation of a population based on a sample of data. Syntax: DSTDEVP(database, field, criteria)

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”)

DSUM DSUM is a function in Microsoft Excel that allows users to sum up values in a list that meet certain criteria. Syntax: DSUM(database, field, criteria)

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”.

DVAR The DVAR function in Excel calculates the variance of a sample set of data. Syntax: DVAR(database, field, criteria)

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”)

DVARP The DVARP function in Excel is used to calculate the variance of a population based on a sample of data. Syntax: DVARP(number1,[number2],…)

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

 

Engineering Functions

Delve into Specialized Engineering Calculations with Excel’s Engineering Functions. From converting between degrees and radians (DEGREES, RADIANS) to complex unit conversions (CONVERT), these functions cater to specific engineering disciplines and provide solutions for advanced technical calculations.

  • Cater to specific engineering disciplines.
  • Perform complex unit conversions and engineering calculations.
Function Description Syntax and Formula
BESSELI BESSELI is an Excel function that returns the modified Bessel function In(x). It is used to calculate the modified Bessel function of the first kind. Syntax: BESSELI(x,n)x – The value for which to calculate the modified Bessel function.n – The order of the modified Bessel function.

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.

BESSELJ The BESSELJ function is an Excel function that returns the Bessel function of the first kind, Jn, of an order n and argument x. Syntax: BESSELJ(x,n)

Formula: BESSELJ(1,2) returns 0.440050585744933

BESSELK The BESSELK function is an Excel function that returns the modified Bessel function K of the specified order and argument. Syntax: BESSELK(x,n)

Formula: BESSELK(2,3) returns 0.06899

BESSELY The BESSELY function is an Excel function that calculates the Bessel function of the first kind, which is a mathematical function used to solve a variety of problems in physics, engineering, and mathematics. Syntax: BESSELY(x,n)

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

BIN2DEC BIN2DEC is an Excel function that converts a binary number to a decimal number. Syntax: BIN2DEC(number)

Formula: BIN2DEC(10101) returns 21

BIN2HEX The BIN2HEX function is used to convert a binary number to a hexadecimal number. Syntax: BIN2HEX(number, [places])

Formula: BIN2HEX(10101010, 8)Result: AA

BIN2OCT BIN2OCT is an Excel function that converts a binary number to an octal number. Syntax: BIN2OCT(number)

Formula: BIN2OCT(10101010) returns 252

BITAND BITAND is a logical function in Excel that performs a bitwise AND operation on two numbers. The syntax for the BITAND function is: BITAND(number1, number2). Syntax: BITAND( number1, number2)

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).

BITLSHIFT BITLSHIFT is an Excel function that shifts the bits of a number to the left or right. It takes two arguments: the number to be shifted, and the number of bits to shift it. Syntax: BITLSHIFT(number, shift_amount)

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

BITOR BITOR is an Excel logical function that returns a bitwise OR of two numbers. The syntax for the BITOR function is: BITOR(number1, number2). Syntax: BITOR(number1, number2)

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

BITRSHIFT BITRSHIFT is an Excel function that shifts the bits of a number to the right. It takes two arguments: the number to be shifted and the number of positions to shift. Syntax: BITRSHIFT(number, shift_amount)

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

BITXOR The BITXOR function returns a bitwise ‘exclusive or’ of two numbers. Syntax: BITXOR(number1, number2)

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

COMPLEX The COMPLEX function is used to convert real and imaginary coefficients into a complex number. Syntax: COMPLEX(real_num, i_num, [suffix])

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

CONVERT The CONVERT function is used to convert a number from one unit of measurement to another. Syntax: CONVERT(number, from_unit, to_unit)

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

DEC2BIN DEC2BIN is an Excel function that converts a decimal (base 10) number to a binary (base 2) number. Syntax: DEC2BIN(number, [places])

Formula: DEC2BIN(10, 8) returns 00001010.

DEC2HEX DEC2HEX is an Excel function that converts a decimal number to a hexadecimal number. Syntax: DEC2HEX(number, [places])

Formula: DEC2HEX(255,2)Result: FF

DEC2OCT The DEC2OCT function is a mathematical function in Microsoft Excel that converts a decimal number to an octal number. Syntax: DEC2OCT(number, places)

Formula: DEC2OCT(20,2)Result: 24

DELTA The DELTA function in Excel returns 1 if two values are equal and 0 if they are not equal. Syntax: DELTA(number1, number2)

Formula: DELTA(A1, B1)

ERF The ERF function returns the error function integrated between two supplied limits. The error function is a special function used in probability and statistics to measure the area under a normal distribution curve. Syntax: ERF(lower_limit, upper_limit)

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.

ERF.PRECISE The ERF.PRECISE function is an Excel function that calculates the error function of a given value. The error function is a mathematical function used to calculate the probability that a normally distributed random variable will be within a certain range. Syntax: ERF.PRECISE(x)

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

ERFC ERFC is an Excel function that returns the complementary error function of a given number. Syntax: ERFC(number)

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

ERFC.PRECISE The ERFC.PRECISE function is an Excel statistical function that returns the complementary error function integrated between lower_limit and upper_limit. The complementary error function is a special function used in statistics and probability theory. Syntax: ERFC.PRECISE(lower_limit, upper_limit)

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

GESTEP The GESTEP function returns a logical value of TRUE or FALSE depending on whether a number is greater than or equal to a given step value. Syntax: GESTEP(number, step)

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

HEX2BIN The HEX2BIN function in Excel is used to convert a hexadecimal number to a binary number. Syntax: HEX2BIN(number, [places])

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.

HEX2DEC HEX2DEC is an Excel function that converts a hexadecimal number to its decimal equivalent. Syntax: HEX2DEC(number)

Formula: HEX2DEC(“FF”) returns 255

HEX2OCT The HEX2OCT function in Excel is used to convert a hexadecimal number to an octal number. Syntax: HEX2OCT(number)

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

IMABS The IMABS function is an Excel function that returns the absolute value of a complex number. Syntax: IMABS(inumber)

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

IMAGINARY The IMAGINARY function returns the imaginary coefficient of a complex number in Excel. Syntax: IMAGINARY(inumber)

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

IMARGUMENT The IMARGUMENT function returns the argument theta, which is the angle associated with the complex number in x + iy (x and y are real numbers). Syntax: IMARGUMENT(inumber)

Formula: IMARGUMENT(2+3i) returns 0.982793723247329

IMCONJUGATE The IMCONJUGATE function returns the complex conjugate of a complex number. Syntax: IMCONJUGATE(inumber)

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

IMCOS The IMCOS function returns the cosine of an angle given in radians. Syntax: IMCOS(number)

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

IMCOSH The IMCOSH function returns the hyperbolic cosine of a complex number in x + yi or x + yj text format. Syntax: IMCOSH(inumber)

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

IMCOT IMCOT is an Excel function that returns the imaginary part of a complex number. Syntax: IMCOT(inumber)

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

IMCSC The IMCSC function is used to calculate the complex conjugate of a complex number in Microsoft Excel. Syntax: IMCSC(inumber)

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

IMCSCH EDULEThe IMCSCHEDULE function in Microsoft Excel is used to calculate the number of payments for a loan or an annuity. Syntax: IMCSCHEDULE(principal, interest, payments, start_period, end_period, type)

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.

IMDIV The IMDIV function returns the result of a complex number division in the imaginary number format. Syntax: IMDIV(inumber1, inumber2)

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

IMEXP The IMEXP function is an Excel function that imports data from a text file into a worksheet. It is a useful tool for importing data from other sources into Excel. Syntax: IMEXP(filename, [worksheet], [start_row], [start_column], [import_type], [import_options])

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

IMLN IMLN: The IMLN function returns the inverse matrix logarithm of a matrix. Syntax: IMLN(matrix)

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.

IMLOG10 The IMLOG10 function returns the logarithm of a number to the base 10. Syntax: IMLOG10(number)

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

IMLOG2 IMLOG2 is an Excel function used to calculate the binary logarithm of a number. The function takes a single argument, which is the number for which the binary logarithm is to be calculated. Syntax: IMLOG2(number)

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

IMPOWER The IMPOWER function is an Excel function that returns the given number raised to the power of another number. Syntax: IMPOWER(number, power)

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

IMPRODUCT The IMPRODUCT function returns the product of two or more complex numbers. Syntax: IMPRODUCT(inumber1,inumber2,…)

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

IMREAL IMREAL is an Excel function that returns TRUE if a number is a real number and FALSE if it is not. Syntax: IMREAL(number)

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

IMSEC The IMSEC function returns the number of seconds in a given time. Syntax: IMSEC(time)

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

IMSECH The IMSECH function returns the inverse hyperbolic secant of a given number. Syntax: IMSECH(number)

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

IMSIN VThe IMSINV function returns the inverse of the matrix which is provided as an argument. Syntax: IMSINV(array)

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}.

IMSINH The IMSINH function returns the hyperbolic sine of a given number. Syntax: IMSINH(number)

Formula: IMSINH(2) returns 1.919154444

IMSQRT IMSQRT is an Excel function that returns the imaginary component of the square root of a complex number. Syntax: IMSQRT(inumber)inumber – A complex number for which you want to find the imaginary component of the square root.

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

IMSUB The IMSUB function returns the difference between two arrays of any size. Syntax: IMSUB(array1, array2)

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

IMSUM The IMSUM function calculates the sum of an array of numbers based on a given set of conditions. Syntax: IMSUM(array, criteria)

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

IMTAN The IMTAN function returns the modified Bessel function of the first kind, which is a mathematical function used to solve differential equations. Syntax: IMTAN(x)

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

OCT2BIN OCT2BIN is an Excel function that converts an octal number to a binary number. Syntax: OCT2BIN(number, [places])number: The octal number that you want to convert to a binary number.places: (optional) The number of characters to use.

Formula: OCT2BIN(11)Result: 1011

OCT2DEC The OCT2DEC function converts an octal number to a decimal number in Microsoft Excel. Syntax: OCT2DEC(number)

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

OCT2HEX The OCT2HEX function converts an octal number to a hexadecimal number. Syntax:OCT2HEX(number, [places])

Formula:OCT2HEX(11, 4)Result: 13

 

Web Functions

Bring the Power of the Web Directly into Your Spreadsheet with Web Functions!  These tools allow you to retrieve data from web pages in real-time, keeping your spreadsheets dynamic and up-to-date with the latest information. (Requires internet connection and may be affected by website availability)

  • Retrieve data from web pages in real-time.
  • Requires internet connection and may be affected by website availability.
Function Description Syntax and Formula
ENCODEURL ENCODEURL is an Excel function that encodes a string of text into a URL-safe format. This is useful when creating a hyperlink in a cell. Syntax: ENCODEURL(text)

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

FILTERXML The FILTERXML function in Excel is used to extract data from an XML string, based on an XPath expression. Syntax: FILTERXML(xml, xpath)

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

WEBSERVICE The WEBSERVICE function is used to retrieve data from a web service in an Excel worksheet. Syntax: WEBSERVICE(url)

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

Conclusion: Mastering the Art of Excel Functions

Congratulations! You’ve embarked on a journey to conquer the vast landscape of Excel functions. This guide has equipped you with a foundational understanding of the core categories and their key functionalities.

Remember, this is just the beginning! As you delve deeper into Excel, you’ll discover a treasure trove of even more specialized functions catering to specific needs. Here are some tips to solidify your Excel mastery:

  • Practice Makes Perfect: Experiment with the functions covered in this guide. Create sample spreadsheets and play around with different formulas to solidify your understanding.
  • Explore Online Resources: The internet is brimming with tutorials, examples, and cheat sheets for Excel functions. Utilize these resources to expand your knowledge and tackle more complex tasks.
  • Don’t Be Afraid to Experiment: The beauty of Excel lies in its versatility. Don’t be afraid to combine functions and explore creative solutions to your data analysis challenges.

With dedication and practice, you’ll transform from a spreadsheet novice to a data analysis guru. So, fire up Excel, unleash the power of functions, and unlock the hidden insights within your data!

Excel Functions and Formulas

Share This Story, Choose Your Platform!