Excel Logical Formulas & Functions
Explore Excel formulas with clear explanations. Use the Quick Jump panel to navigate formula categories on this page.
Logical Functions
- Excel logical formulas for condition checks, decision-making, and dynamic calculations.| Formula | Syntax | Example | Description |
|---|---|---|---|
| IF | =IF(condition, value_if_true, value_if_false) | =IF(A1>50,"Pass","Fail") | The IF function checks whether a condition is met and returns a result based on that condition. In this example, Excel tests if the value in cell A1 is greater than 50. If the condition is TRUE, Excel returns "Pass"; otherwise, it returns "Fail". This makes the IF function ideal for results, approvals, and decision-based calculations. |
| IFERROR | =IFERROR(value, value_if_error) | =IFERROR(A1/B1,0) | The IFERROR function checks whether a formula results in an error. In this example, Excel divides the value in cell A1 by B1. If the calculation causes an error such as division by zero, Excel returns 0 instead of displaying an error message. If no error occurs, the normal calculation result is returned. IFERROR is ideal for clean reports, dashboards, and user-friendly formulas. |
| AND | =AND(logical1, logical2) | =AND(A1>50,B1>50) | The AND function checks multiple conditions at the same time and returns TRUE only when all conditions are met. In this example, Excel verifies whether both A1 and B1 are greater than 50. If both conditions are TRUE, Excel returns TRUE; if any condition is FALSE, the result is FALSE. AND is commonly used in eligibility rules, validations, and advanced IF formulas. |
| OR | =OR(logical1, logical2) | =OR(A1>50,B1>50) | The OR function evaluates multiple conditions and returns TRUE if **any one condition is met**. In this example, Excel checks whether A1 or B1 is greater than 50. If at least one condition is TRUE, Excel returns TRUE; if none of the conditions are TRUE, it returns FALSE. OR is useful for flexible approvals, pass/fail logic, and decision-making formulas. |
| NOT | =NOT(logical) | =NOT(A1>50) | The NOT function reverses a logical value. If a condition is TRUE, NOT returns FALSE, and if the condition is FALSE, it returns TRUE. In this example, Excel checks whether A1 is greater than 50 and then reverses the result. NOT is commonly used to exclude conditions or invert logical tests inside IF, AND, or OR formulas. |
| IFS | =IFS(condition1, value1, condition2, value2) | =IFS(A1>80,"A",A1>60,"B",A1>40,"C") | The IFS function evaluates multiple conditions in order and returns the result for the **first TRUE condition**. In this example, Excel assigns a grade based on the value in A1. If A1 is greater than 80, it returns "A"; if the first condition is FALSE but the second is TRUE, it returns "B", and so on. IFS is a cleaner and more readable alternative to nested IF formulas. |
| IFNA | =IFNA(value, value_if_na) | =IFNA(VLOOKUP(A1,B:C,2,0),"Not Found") | The IFNA function checks whether a formula results in a #N/A error. If the formula returns #N/A, Excel outputs a specified value; otherwise, it returns the normal formula result. In this example, Excel performs a VLOOKUP in columns B and C for the value in A1. If the value is not found, it returns "Not Found". IFNA is ideal for cleaning lookup formulas and avoiding #N/A errors in reports. |
| TRUE | =TRUE() | =A1>10 | The TRUE function returns the logical value TRUE. Excel automatically evaluates conditions as TRUE when appropriate, but the TRUE function is useful in formulas that require a fixed logical value. It can be used in combination with IF, AND, OR, and other logical functions. |
| FALSE | =FALSE() | =A1<10 | The FALSE function returns the logical value FALSE. Excel automatically returns FALSE when conditions are not met, but using the FALSE function can help structure logical formulas and provide explicit logical values for calculations. |
| SWITCH | =SWITCH(expression, value1, result1, value2, result2,...) | =SWITCH(A1,1,"One",2,"Two",3,"Three") | The SWITCH function compares a value against multiple possible matches and returns the result of the first matching case. In this example, Excel checks the value in A1 and returns "One", "Two", or "Three" depending on the match. SWITCH is easier to read and maintain than multiple nested IF statements when testing one value against many options. |
| XOR | =XOR(logical1, logical2) | =XOR(A1>50,B1>50) | The XOR function returns TRUE if an odd number of conditions are TRUE. In this example, Excel checks if only one of the two conditions is TRUE. If both are TRUE or both are FALSE, Excel returns FALSE. XOR is useful for exclusive logic, validations, and advanced decision-making formulas. |
| LET | =LET(name, value, calculation) | =LET(x,A1*2,x+10) | The LET function allows you to define names for calculations or values inside a formula. In this example, Excel stores A1*2 in a variable x and then adds 10. LET improves readability, avoids repeating calculations, and enhances performance in complex formulas. |
| LAMBDA | =LAMBDA(parameters, calculation) | =LAMBDA(x,x*2)(A1) | The LAMBDA function lets you create custom reusable Excel functions without using VBA. In this example, Excel defines a function that doubles the input value. LAMBDA is ideal for advanced users who want to simplify complex formulas, reuse logic, and create custom calculations directly inside Excel. |
Lookup Functions
- Excel lookup formulas for searching, matching, and retrieving data from tables and ranges.| Formula | Syntax | Example | Description |
|---|---|---|---|
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | =VLOOKUP(A2,A:B,2,FALSE) | The VLOOKUP function searches for a value in the first column of a table and returns a corresponding value from another column in the same row. In this example, Excel looks for the value in cell A2 within column A and returns the matching value from column B. VLOOKUP is commonly used for retrieving prices, names, grades, and other related data from large tables. |
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array) | =XLOOKUP(A2,A:A,B:B) | The XLOOKUP function searches for a value in one range and returns the corresponding result from another range. In this example, Excel looks up the value in A2 from column A and returns the matching value from column B. XLOOKUP is a modern replacement for VLOOKUP and HLOOKUP, supporting left lookups, exact matches by default, and better error handling. |
| INDEX | =INDEX(array, row_num, [column_num]) | =INDEX(A1:C5,2,3) | The INDEX function returns the value of a cell based on its row and column position within a range. In this example, Excel returns the value from the second row and third column of the selected range. INDEX is powerful when combined with MATCH and is widely used in dynamic lookup formulas. |
| MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | =MATCH(A2,A:A,0) | The MATCH function searches for a value in a range and returns its position number. In this example, Excel finds the position of the value in A2 within column A. MATCH is commonly used with INDEX to create flexible and powerful lookup formulas. |
| HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | =HLOOKUP("Sales",A1:D5,2,FALSE) | The HLOOKUP function searches for a value in the first row of a table and returns a value from a specified row below. In this example, Excel searches for "Sales" in the first row and returns the value from the second row. HLOOKUP is useful for horizontally arranged data, though XLOOKUP is often preferred today. |
| LOOKUP | =LOOKUP(lookup_value, lookup_vector, [result_vector]) | =LOOKUP(A2,A:A,B:B) | The LOOKUP function searches for a value in a sorted range and returns a corresponding result. In this example, Excel looks up the value in A2 and returns the related value from column B. LOOKUP is an older function and is mainly used for approximate matches. |
| OFFSET | =OFFSET(reference, rows, cols, [height], [width]) | =OFFSET(A1,2,1) | The OFFSET function returns a cell or range that is a specified number of rows and columns away from a reference cell. In this example, Excel moves two rows down and one column to the right from A1. OFFSET is useful for dynamic ranges, dashboards, and advanced formulas, but it is volatile and should be used carefully. |
| ROW | =ROW([reference]) | =ROW(A5) | The ROW function returns the row number of a given cell reference. In this example, Excel returns 5 because cell A5 is located in row 5. ROW is commonly used in dynamic formulas, data indexing, and advanced calculations. |
| COLUMN | =COLUMN([reference]) | =COLUMN(C1) | The COLUMN function returns the column number of a specified cell reference. In this example, Excel returns 3 because column C is the third column. This function is useful for dynamic column calculations and formula automation. |
| ROWS | =ROWS(array) | =ROWS(A1:A10) | The ROWS function counts how many rows exist in a given range or array. In this example, Excel returns 10 because the range contains 10 rows. ROWS is helpful for calculating totals, dynamic ranges, and array formulas. |
| COLUMNS | =COLUMNS(array) | =COLUMNS(A1:D1) | The COLUMNS function returns the number of columns in a selected range. In this example, Excel returns 4 because the range spans columns A to D. This function is often used in dynamic tables and lookup formulas. |
| CHOOSE | =CHOOSE(index_num, value1, value2, ...) | =CHOOSE(2,"Red","Green","Blue") | The CHOOSE function selects a value from a list based on a given index number. In this example, Excel returns "Green" because the index number is 2. CHOOSE is useful for scenario analysis and controlled value selection. |
| FORMULATEXT | =FORMULATEXT(reference) | =FORMULATEXT(A1) | The FORMULATEXT function returns the actual formula stored in a cell as text. In this example, Excel displays the formula used in cell A1 instead of its calculated result. FORMULATEXT is useful for auditing formulas, teaching Excel, and documenting complex worksheets. |
| ADDRESS | =ADDRESS(row_num, column_num, [abs_num]) | =ADDRESS(5,3) | The ADDRESS function returns a cell reference as text based on specified row and column numbers. In this example, Excel returns "$C$5" because row 5 and column 3 represent cell C5. ADDRESS is commonly used with lookup and dynamic reference formulas. |
| GETPIVOTDATA | =GETPIVOTDATA(data_field, pivot_table) | =GETPIVOTDATA("Sales",$A$3) | The GETPIVOTDATA function extracts specific values from a PivotTable. In this example, Excel retrieves the Sales value from the PivotTable starting at cell A3. GETPIVOTDATA ensures accurate, structured data retrieval even when PivotTables change. |
| SORTBY | =SORTBY(array, by_array, [sort_order]) | =SORTBY(A2:A10,B2:B10,-1) | The SORTBY function sorts a range or array based on values from another range. In this example, Excel sorts the values in A2:A10 according to column B in descending order. SORTBY is ideal for dynamic sorting without modifying original data. |
| XMATCH | =XMATCH(lookup_value, lookup_array) | =XMATCH("Apple",A2:A10) | The XMATCH function returns the position of a value within a range or array. In this example, Excel returns the relative position of "Apple" in the range A2:A10. XMATCH is a modern replacement for MATCH with advanced matching capabilities. |
| AREAS | =AREAS(reference) | =AREAS((A1:A5,C1:C5)) | The AREAS function returns the number of separate ranges included in a reference. In this example, Excel returns 2 because two non-adjacent ranges are provided. AREAS is useful for advanced formulas that work with multiple ranges at the same time. |
| FIELDVALUE | =FIELDVALUE(text) | =FIELDVALUE("Revenue") | The FIELDVALUE function returns the value of a named field from linked or external data. In this example, Excel retrieves the value associated with the field name "Revenue". FIELDVALUE is mainly used with connected data types and modern Excel integrations. |
| RTD | =RTD(progID, server, topic1) | =RTD("excel.rtd",, "Price") | The RTD function retrieves real-time data from programs that support COM automation. In this example, Excel fetches live data such as prices or measurements from an external source. RTD is commonly used for financial data feeds, live dashboards, and automated reporting. |
Date Functions
- Excel date formulas for tracking, calculating, and reporting dates dynamically in your worksheets.| Formula | Syntax | Example | Description |
|---|---|---|---|
| TODAY | =TODAY() | =TODAY() | The TODAY function returns the current date based on your system clock. It automatically updates whenever the worksheet recalculates. TODAY is commonly used for tracking deadlines, reports, and date-based calculations. |
| NOW | =NOW() | =NOW() | The NOW function returns the current date and time based on your system clock. It updates automatically whenever the worksheet recalculates. NOW is useful for time-stamped reports, dashboards, and dynamic calculations. |
| DATE | =DATE(year, month, day) | =DATE(2026,1,11) | The DATE function creates a valid Excel date from year, month, and day values. In this example, Excel returns 11-Jan-2026 by combining the inputs. DATE is essential for constructing dates dynamically, performing calculations, and managing schedules. |
| DATEDIF | =DATEDIF(start_date,end_date,unit) | =DATEDIF(A1,B1,"D") | The DATEDIF function calculates the difference between two dates in days, months, or years. In this example, Excel calculates the total days between the dates in cells A1 and B1. DATEDIF is commonly used for age calculations, project timelines, and duration analysis. |
| NETWORKDAYS | =NETWORKDAYS(start_date,end_date,[holidays]) | =NETWORKDAYS(A1,B1,C1:C5) | The NETWORKDAYS function calculates the number of working days between two dates, excluding weekends and optional holidays. In this example, Excel counts workdays between A1 and B1, ignoring holidays listed in C1:C5. NETWORKDAYS is ideal for payroll, project schedules, and business reporting. |
| WORKDAY | =WORKDAY(start_date,days,[holidays]) | =WORKDAY(A1,10,C1:C5) | The WORKDAY function returns a date that is a specified number of working days from a start date, excluding weekends and optional holidays. In this example, Excel calculates the date 10 workdays after the date in A1, ignoring holidays in C1:C5. WORKDAY is useful for deadlines, project planning, and timeline calculations. |
| WEEKDAY | =WEEKDAY(serial_number,[return_type]) | =WEEKDAY(A1,1) | The WEEKDAY function returns the day of the week for a given date as a number. In this example, Excel returns 1 for Sunday, 2 for Monday, etc., based on the date in A1. WEEKDAY is useful for scheduling, reporting, and analyzing weekly patterns. |
| DAY | =DAY(serial_number) | =DAY(A1) | The DAY function extracts the day of the month from a given date. In this example, Excel returns 11 if A1 contains 11-Jan-2026. DAY is essential for date breakdowns, reporting, and dynamic calculations. |
| MONTH | =MONTH(serial_number) | =MONTH(A1) | The MONTH function extracts the month number from a given date. In this example, Excel returns 1 if A1 contains 11-Jan-2026. MONTH is widely used in reporting, dynamic calendars, and date calculations. |
| YEAR | =YEAR(serial_number) | =YEAR(A1) | The YEAR function extracts the year from a given date. In this example, Excel returns 2026 if A1 contains 11-Jan-2026. YEAR is commonly used for annual reports, timelines, and dynamic date calculations. |
| WEEKNUM | =WEEKNUM(serial_number,[return_type]) | =WEEKNUM(A1,1) | The WEEKNUM function returns the week number of the year for a given date. In this example, Excel returns 2 if A1 is 11-Jan-2026. WEEKNUM is useful for planning, weekly reporting, and analyzing yearly schedules. |
| TIME | =TIME(hour, minute, second) | =TIME(14,30,0) | The TIME function creates a valid Excel time from hour, minute, and second values. In this example, Excel returns 2:30 PM. TIME is useful for scheduling, time calculations, and dynamic dashboards. |
| HOUR | =HOUR(serial_number) | =HOUR(A1) | The HOUR function extracts the hour component from a given time or datetime. In this example, if A1 contains 14:30, Excel returns 14. HOUR is essential for time analysis, reporting, and scheduling tasks. |
| MINUTE | =MINUTE(serial_number) | =MINUTE(A1) | The MINUTE function extracts the minute component from a time or datetime value. In this example, if A1 contains 14:30, Excel returns 30. MINUTE is useful for precise time calculations, schedules, and duration analysis. |
| SECOND | =SECOND(serial_number) | =SECOND(A1) | The SECOND function extracts the second component from a time or datetime. In this example, if A1 contains 14:30:45, Excel returns 45. SECOND is essential for detailed time tracking and high-precision reporting. |
| DATEVALUE | =DATEVALUE(date_text) | =DATEVALUE("11-Jan-2026") | The DATEVALUE function converts a date stored as text into an Excel serial date number. In this example, Excel converts "11-Jan-2026" to its numeric date value. DATEVALUE is useful for calculations with imported data and text-based dates. |
| TIMEVALUE | =TIMEVALUE(time_text) | =TIMEVALUE("14:30") | The TIMEVALUE function converts a time stored as text into an Excel serial time number. In this example, Excel converts "14:30" to its numeric time value. TIMEVALUE is commonly used for calculations with text-based times and dynamic schedules. |
| DAYS | =DAYS(end_date,start_date) | =DAYS(B1,A1) | The DAYS function returns the number of days between two dates. In this example, Excel calculates the difference between the dates in B1 and A1. DAYS is essential for tracking durations, project timelines, and deadlines. |
| EDATE | =EDATE(start_date, months) | =EDATE(A1,6) | The EDATE function returns a date a specified number of months before or after a start date. In this example, Excel calculates the date 6 months after the date in A1. EDATE is widely used for due dates, subscription periods, and financial projections. |
| EOMONTH | =EOMONTH(start_date, months) | =EOMONTH(A1,3) | The EOMONTH function returns the last day of the month, a specified number of months before or after a start date. In this example, Excel calculates the last day of the month three months after the date in A1. EOMONTH is useful for financial reporting, subscription schedules, and deadline tracking. |
| YEARFRAC | =YEARFRAC(start_date, end_date,[basis]) | =YEARFRAC(A1,B1) | The YEARFRAC function calculates the fraction of a year between two dates. In this example, Excel computes the decimal year difference between A1 and B1. YEARFRAC is commonly used for interest calculations, age analysis, and project duration assessments. |
| NETWORKDAYS.INTL | =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) | =NETWORKDAYS.INTL(A1,B1,1,C1:C5) | NETWORKDAYS.INTL calculates the number of working days between two dates with custom weekend definitions. In this example, Excel counts workdays between A1 and B1 using weekend code 1, excluding holidays in C1:C5. It's ideal for flexible business calendars, payroll, and project planning. |
| WORKDAY.INTL | =WORKDAY.INTL(start_date,days,[weekend],[holidays]) | =WORKDAY.INTL(A1,10,1,C1:C5) | WORKDAY.INTL returns a date that is a specified number of workdays from a start date, with customizable weekends and optional holidays. In this example, Excel calculates the date 10 workdays after A1, considering weekend code 1 and holidays in C1:C5. It's used for advanced scheduling and project timelines. |
| DAYS360 | =DAYS360(start_date,end_date,[method]) | =DAYS360(A1,B1) | The DAYS360 function calculates the number of days between two dates based on a 360-day year (12 months of 30 days). In this example, Excel computes the days between A1 and B1 using the US method by default. DAYS360 is commonly used in financial modeling, interest calculations, and bond analysis. |
| ISOWEEKNUM | =ISOWEEKNUM(date) | =ISOWEEKNUM(A1) | The ISOWEEKNUM function returns the ISO week number of a given date. In this example, Excel calculates the ISO week number for the date in A1. ISOWEEKNUM is useful for standardized week-based reporting, project planning, and scheduling. |
| EPOCHTODATE | =EPOCHTODATE(epoch_time) | =EPOCHTODATE(1673414400) | The EPOCHTODATE function converts a Unix epoch timestamp into a readable Excel date. In this example, Excel converts 1673414400 to the corresponding date. EPOCHTODATE is ideal for handling timestamp data from APIs, databases, or external systems. |
Filter Functions
- Excel filter formulas for extracting, sorting, and manipulating data dynamically based on conditions.| Formula | Syntax | Example | Description |
|---|---|---|---|
| FILTER | =FILTER(array, include,[if_empty]) | =FILTER(A1:A10,B1:B10="Yes","No Match") | The FILTER function extracts values from a range that meet specified criteria. In this example, Excel returns all values from A1:A10 where B1:B10 equals "Yes"; otherwise, it displays "No Match". FILTER is ideal for dynamic reporting, dashboards, and extracting subsets of data based on conditions. |
| SORT | =SORT(array,[sort_index],[sort_order],[by_col]) | =SORT(A1:B10,2,1) | The SORT function arranges data in ascending or descending order based on a specified column or row. In this example, Excel sorts the range A1:B10 by the second column in ascending order. SORT is commonly used for organizing data, creating reports, and making dashboards more readable. |
| UNIQUE | =UNIQUE(array,[by_col],[exactly_once]) | =UNIQUE(A1:A10) | The UNIQUE function returns a list of distinct values from a range or array. In this example, Excel extracts all unique values from A1:A10. UNIQUE is essential for deduplicating data, generating dropdown lists, and data analysis. |
| SORTN | =SORTN(array,[n],[display_ties_mode],[sort_column],[is_ascending]) | =SORTN(A1:B10,5,0,2,1) | The SORTN function returns the top n values from a range, optionally sorting them and handling ties. In this example, Excel returns the top 5 rows from A1:B10 sorted by the second column in ascending order. SORTN is useful for top-performer lists, dashboards, and ranking data dynamically. |
Text Functions
- Excel text formulas for manipulating, formatting, and analyzing text strings dynamically.| Formula | Syntax | Example | Description |
|---|---|---|---|
| TEXT | =TEXT(value, format_text) | =TEXT(A1,"dd-mmm-yyyy") | The TEXT function converts a value to text in a specified number, date, or time format. In this example, Excel formats the date in A1 as "11-Jan-2026". TEXT is widely used for custom reporting, dynamic dashboards, and formatting numbers or dates. |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) | =TEXTJOIN(", ", TRUE, A1:A5) | The TEXTJOIN function combines text from multiple cells or ranges with a delimiter, optionally ignoring empty cells. In this example, Excel joins values in A1:A5 separated by ", ". TEXTJOIN is useful for creating lists, concatenated text, and clean reports. |
| TRIM | =TRIM(text) | =TRIM(A1) | The TRIM function removes all extra spaces from text, leaving only single spaces between words. In this example, Excel removes extra spaces from the text in A1. TRIM is essential for cleaning imported data and preparing text for analysis or comparison. |
| UPPER | =UPPER(text) | =UPPER(A1) | The UPPER function converts all letters in a text string to uppercase. In this example, Excel converts "hello" in A1 to "HELLO". UPPER is useful for standardizing text formatting, data cleaning, and comparisons. |
| LOWER | =LOWER(text) | =LOWER(A1) | The LOWER function converts all letters in a text string to lowercase. In this example, Excel converts "HELLO" in A1 to "hello". LOWER is useful for standardizing text, data processing, and cleaning imported information. |
| PROPER | =PROPER(text) | =PROPER(A1) | The PROPER function capitalizes the first letter of each word in a text string. In this example, Excel converts "hello world" in A1 to "Hello World". PROPER is commonly used for formatting names, titles, and clean presentation of text. |
| SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | =SUBSTITUTE(A1,"apple","orange") | The SUBSTITUTE function replaces specific text in a string with new text. In this example, Excel replaces "apple" with "orange" in the text from A1. SUBSTITUTE is essential for text corrections, data cleaning, and dynamic text replacement. |
| REPLACE | =REPLACE(old_text, start_num, num_chars, new_text) | =REPLACE(A1,1,5,"Hello") | The REPLACE function replaces part of a text string based on position with new text. In this example, Excel replaces the first 5 characters in A1 with "Hello". REPLACE is useful for editing text, correcting errors, and dynamic string manipulation. |
| LEFT | =LEFT(text, [num_chars]) | =LEFT(A1,5) | The LEFT function returns a specified number of characters from the start of a text string. In this example, Excel extracts the first 5 characters from the text in A1. LEFT is useful for parsing text, extracting codes, or abbreviating information. |
| RIGHT | =RIGHT(text, [num_chars]) | =RIGHT(A1,4) | The RIGHT function returns a specified number of characters from the end of a text string. In this example, Excel extracts the last 4 characters from A1. RIGHT is useful for extracting codes, suffixes, or file extensions from text. |
| MID | =MID(text, start_num, num_chars) | =MID(A1,3,5) | The MID function extracts a specific number of characters from a text string starting at any position. In this example, Excel extracts 5 characters from A1 starting at position 3. MID is useful for extracting codes, substrings, or portions of text dynamically. |
| FIND | =FIND(find_text, within_text,[start_num]) | =FIND("a",A1) | The FIND function returns the position of a substring within a text string, case-sensitive. In this example, Excel finds the position of "a" in A1. FIND is essential for locating text, parsing data, and text manipulation. |
| SEARCH | =SEARCH(find_text, within_text,[start_num]) | =SEARCH("a",A1) | The SEARCH function returns the position of a substring within a text string, case-insensitive. In this example, Excel finds the position of "a" in A1, ignoring case. SEARCH is useful for locating text, conditional formatting, and text analysis. |
| LEN | =LEN(text) | =LEN(A1) | The LEN function returns the number of characters in a text string, including spaces. In this example, Excel counts all characters in A1. LEN is useful for validation, text analysis, and dynamic formulas. |
| VALUE | =VALUE(text) | =VALUE(A1) | The VALUE function converts text that appears as a number into a numeric value. In this example, Excel converts "123" in A1 to the number 123. VALUE is essential for calculations with imported or text-formatted numbers. |
| CHAR | =CHAR(number) | =CHAR(65) | The CHAR function returns the character corresponding to a given ANSI code number. In this example, Excel returns "A" for the code 65. CHAR is useful for generating characters programmatically or creating custom text outputs. |
| CODE | =CODE(text) | =CODE("A") | The CODE function returns the numeric ANSI code of the first character in a text string. In this example, Excel returns 65 for "A". CODE is useful for character analysis, encoding, and text manipulations. |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) | =TEXTJOIN(", ", TRUE, A1:A5) | The TEXTJOIN function combines multiple text strings into one, with a specified delimiter and optional ignoring of empty cells. In this example, Excel joins A1:A5 separated by ", ". TEXTJOIN is useful for creating lists, concatenated text, and clean reporting. |
| CONCATENATE | =CONCATENATE(text1, [text2], ...) | =CONCATENATE(A1," ",B1) | The CONCATENATE function joins multiple text strings into a single string. In this example, Excel combines A1 and B1 separated by a space. CONCATENATE is useful for combining names, addresses, or any text dynamically. |
| JOIN | =JOIN(delimiter, text1, [text2], ...) | =JOIN(", ", A1:A5) | The JOIN function combines text values from multiple cells or arrays with a specified delimiter. In this example, Excel joins A1:A5 separated by ", ". JOIN is useful for creating lists, summaries, and concatenated text dynamically. |
| SPLIT | =SPLIT(text, delimiter) | =SPLIT(A1,",") | The SPLIT function divides text into multiple cells based on a delimiter. In this example, Excel splits the text in A1 at every comma. SPLIT is useful for parsing data, extracting values, and data cleaning. |
| EXACT | =EXACT(text1, text2) | =EXACT(A1,B1) | The EXACT function checks if two text strings are exactly the same, including case. In this example, Excel returns TRUE if A1 equals B1 exactly; otherwise FALSE. EXACT is useful for validations, comparisons, and ensuring case-sensitive matches. |
| T | =T(value) | =T(A1) | The T function returns the text portion of a value and ignores numbers, dates, or errors. In this example, Excel extracts only text from A1. T is useful for ensuring formulas only process text data. |
| DOLLAR | =DOLLAR(number,[decimals]) | =DOLLAR(A1,2) | The DOLLAR function converts a number to text formatted as currency. In this example, Excel converts the number in A1 to a dollar amount with 2 decimals. DOLLAR is useful for financial reporting and consistent currency formatting. |
| FIXED | =FIXED(number,[decimals],[no_commas]) | =FIXED(A1,2,TRUE) | The FIXED function rounds a number to a specified number of decimals and returns it as text. In this example, Excel rounds A1 to 2 decimals without commas. FIXED is useful for reports where numeric formatting consistency is needed. |
| REPT | =REPT(text, number_times) | =REPT("*",5) | The REPT function repeats text a specified number of times. In this example, Excel outputs "*****" by repeating "*" 5 times. REPT is useful for creating visual indicators, patterns, and repeated text. |
| ARABIC | =ARABIC(text) | =ARABIC("XIV") | The ARABIC function converts Roman numerals to Arabic numbers. In this example, Excel converts "XIV" to 14. ARABIC is useful for calculations with Roman numeral data. |
| ROMAN | =ROMAN(number,[form]) | =ROMAN(14) | The ROMAN function converts a number to a Roman numeral. In this example, Excel converts 14 to "XIV". ROMAN is useful for formatting numbers in classical or historical contexts. |
| ASC | =ASC(text) | =ASC(A1) | The ASC function converts full-width (double-byte) characters to half-width (single-byte) characters. In this example, Excel converts text in A1 to standard ASCII. ASC is useful for data imported from double-byte languages like Japanese. |
| CLEAN | =CLEAN(text) | =CLEAN(A1) | The CLEAN function removes non-printable characters from text. In this example, Excel removes hidden characters from A1. CLEAN is essential for cleaning imported data and preparing text for analysis. |
| REGEXEXTRACT | =REGEXEXTRACT(text, regular_expression) | =REGEXEXTRACT(A1,"[0-9]+") | The REGEXEXTRACT function extracts text matching a regular expression pattern. In this example, Excel extracts numbers from A1. REGEXEXTRACT is useful for pattern-based data extraction and text parsing. |
| REGEXMATCH | =REGEXMATCH(text, regular_expression) | =REGEXMATCH(A1,"[0-9]+") | The REGEXMATCH function checks if text matches a regular expression pattern. In this example, Excel returns TRUE if A1 contains numbers. REGEXMATCH is useful for validations and pattern-based conditions. |
| REGEXREPLACE | =REGEXREPLACE(text, pattern, replacement) | =REGEXREPLACE(A1,"[0-9]+","") | The REGEXREPLACE function replaces text matching a pattern with new text. In this example, Excel removes numbers from A1. REGEXREPLACE is essential for pattern-based text cleaning and transformations. |
| LEFTB | =LEFTB(text, [num_bytes]) | =LEFTB(A1,5) | The LEFTB function returns the first n bytes of a text string, useful for double-byte character languages. In this example, Excel extracts the first 5 bytes from A1. LEFTB is used for processing Asian language text or byte-specific data. |
| RIGHTB | =RIGHTB(text, [num_bytes]) | =RIGHTB(A1,4) | The RIGHTB function returns the last n bytes of a text string, useful for double-byte character languages. In this example, Excel extracts the last 4 bytes from A1. RIGHTB is used for extracting suffixes or processing Asian-language text. |
| MIDB | =MIDB(text, start_num, num_bytes) | =MIDB(A1,3,5) | The MIDB function extracts a number of bytes from a text string starting at a specified position. In this example, Excel extracts 5 bytes starting at position 3 in A1. MIDB is used for processing multi-byte text strings in Asian languages. |
| LENB | =LENB(text) | =LENB(A1) | The LENB function returns the length of a text string in bytes. In this example, Excel calculates the number of bytes in A1. LENB is essential for working with double-byte languages or byte-level text operations. |
| UNICHAR | =UNICHAR(number) | =UNICHAR(65) | The UNICHAR function returns the Unicode character corresponding to a number. In this example, Excel returns "A" for 65. UNICHAR is useful for generating symbols, special characters, and Unicode text dynamically. |
| UNICODE | =UNICODE(text) | =UNICODE("A") | The UNICODE function returns the numeric Unicode value of the first character in a text string. In this example, Excel returns 65 for "A". UNICODE is useful for character analysis, Unicode conversions, and text processing. |
Math & Trig Functions
- Excel formulas for mathematical calculations, trigonometry, rounding, and numeric analysis to simplify complex computations.| Formula | Syntax | Example | Description |
|---|---|---|---|
| SUM | =SUM(number1, [number2], ...) | =SUM(A1:A5) | The SUM function adds all numbers in a range or multiple values. In this example, Excel sums the values from A1 to A5. SUM is essential for totals, financial calculations, and general numeric analysis. |
| SUMIF | =SUMIF(range, criteria, [sum_range]) | =SUMIF(A1:A5,">50") | The SUMIF function adds values in a range that meet a specific condition. In this example, Excel sums all values in A1:A5 that are greater than 50. SUMIF is useful for conditional totals, budget analysis, and filtering numeric data dynamically. |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | =SUMIFS(B1:B10,A1:A10,">50",C1:C10,"Yes") | The SUMIFS function sums values based on multiple conditions. In this example, Excel sums values in B1:B10 where A1:A10 is greater than 50 and C1:C10 equals "Yes". SUMIFS is perfect for multi-criteria calculations and dynamic financial reports. |
| COUNTIF | =COUNTIF(range, criteria) | =COUNTIF(A1:A10,">50") | The COUNTIF function counts the number of cells in a range that meet a condition. In this example, Excel counts how many cells in A1:A10 are greater than 50. COUNTIF is useful for tracking items, filtering data, and generating dynamic counts. |
| COUNTIFS | =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) | =COUNTIFS(A1:A10,">50",B1:B10,"Yes") | The COUNTIFS function counts cells that meet multiple criteria. In this example, Excel counts how many rows have A1:A10 > 50 and B1:B10 = "Yes". COUNTIFS is useful for advanced filtering and multi-condition analysis. |
| COUNTBLANK | =COUNTBLANK(range) | =COUNTBLANK(A1:A10) | The COUNTBLANK function counts empty cells in a specified range. In this example, Excel counts how many cells in A1:A10 are blank. COUNTBLANK is essential for data validation, cleaning, and tracking missing information. |
| COUNTUNIQUE | =COUNTUNIQUE(range) | =COUNTUNIQUE(A1:A10) | The COUNTUNIQUE function counts the number of unique values in a range. In this example, Excel counts distinct values in A1:A10. COUNTUNIQUE is useful for analytics, reporting unique entries, and data summarization. |
| ROUND | =ROUND(number, num_digits) | =ROUND(A1,2) | The ROUND function rounds a number to a specified number of digits. In this example, Excel rounds the value in A1 to 2 decimal places. ROUND is useful for financial calculations, precision adjustments, and clean reports. |
| ROUNDDOWN | =ROUNDDOWN(number, num_digits) | =ROUNDDOWN(A1,0) | The ROUNDDOWN function always rounds a number down toward zero. In this example, Excel rounds A1 down to the nearest integer. ROUNDDOWN is useful for conservative estimates, budgeting, and numeric analysis. |
| ROUNDUP | =ROUNDUP(number, num_digits) | =ROUNDUP(A1,0) | The ROUNDUP function always rounds a number away from zero. In this example, Excel rounds A1 up to the nearest integer. ROUNDUP is useful for ceiling calculations, projections, and financial planning. |
| INT | =INT(number) | =INT(A1) | The INT function rounds a number down to the nearest integer. In this example, Excel converts 5.7 in A1 to 5. INT is useful for removing decimals, rounding down for calculations, and integer-only results. |
| TRUNC | =TRUNC(number, [num_digits]) | =TRUNC(A1,2) | The TRUNC function removes the decimal portion of a number without rounding. In this example, Excel truncates the value in A1 to 2 decimal places. TRUNC is useful for precision control, integer calculations, and financial reports. |
| MOD | =MOD(number, divisor) | =MOD(A1,3) | The MOD function returns the remainder after dividing a number by a divisor. In this example, Excel calculates the remainder of A1 divided by 3. MOD is useful for cycles, periodic calculations, and pattern recognition. |
| ODD | =ODD(number) | =ODD(A1) | The ODD function rounds a number up to the nearest odd integer. In this example, Excel rounds 4 in A1 to 5. ODD is useful for rounding, sequence generation, and numeric calculations requiring odd numbers. |
| EVEN | =EVEN(number) | =EVEN(A1) | The EVEN function rounds a number up to the nearest even integer. In this example, Excel rounds 5 in A1 to 6. EVEN is useful for rounding, sequence generation, and numeric calculations requiring even numbers. |
| ABS | =ABS(number) | =ABS(A1) | The ABS function returns the absolute value of a number, removing any negative sign. In this example, Excel converts -10 in A1 to 10. ABS is useful for mathematical calculations, financial models, and measuring magnitude. |
| POWER | =POWER(number, power) | =POWER(A1,3) | The POWER function raises a number to a specified exponent. In this example, Excel calculates A1 cubed. POWER is useful for exponential calculations, scientific analysis, and financial modeling. |
| PRODUCT | =PRODUCT(number1, [number2], ...) | =PRODUCT(A1:A5) | The PRODUCT function multiplies all numbers in a range or array. In this example, Excel multiplies values in A1:A5. PRODUCT is useful for totals, compound calculations, and geometric growth models. |
| QUOTIENT | =QUOTIENT(numerator, denominator) | =QUOTIENT(A1,3) | The QUOTIENT function returns the integer portion of a division. In this example, Excel divides A1 by 3 and returns only the whole number. QUOTIENT is useful for integer division, batch calculations, and stepwise processing. |
| SQRT | =SQRT(number) | =SQRT(A1) | The SQRT function calculates the square root of a number. In this example, Excel returns the square root of the value in A1. SQRT is essential for mathematical, statistical, and engineering calculations. |
| SQRTPI | =SQRTPI(number) | =SQRTPI(A1) | The SQRTPI function returns the square root of (number × π). In this example, Excel calculates SQRT(A1 × PI()). SQRTPI is useful in geometry, engineering, and trigonometric computations. |
| LOG | =LOG(number, [base]) | =LOG(A1,10) | The LOG function returns the logarithm of a number to a specified base. In this example, Excel calculates log base 10 of A1. LOG is essential for exponential analysis, data scaling, and scientific calculations. |
| LN | =LN(number) | =LN(A1) | The LN function returns the natural logarithm (base e) of a number. In this example, Excel calculates the natural log of A1. LN is useful for scientific, statistical, and financial calculations. |
| LOG10 | =LOG10(number) | =LOG10(A1) | The LOG10 function returns the base-10 logarithm of a number. In this example, Excel calculates LOG10 of A1. LOG10 is useful for scientific calculations, engineering, and large-number scaling. |
| PI | =PI() | =PI() | The PI function returns the mathematical constant π (3.14159265…). In this example, Excel returns π for calculations. PI is essential for geometry, trigonometry, and circular calculations. |
| RADIANS | =RADIANS(angle) | =RADIANS(180) | The RADIANS function converts degrees to radians. In this example, Excel converts 180° to 3.14159 radians. RADIANS is essential for trigonometric calculations and scientific formulas. |
| DEGREES | =DEGREES(angle) | =DEGREES(PI()) | The DEGREES function converts radians to degrees. In this example, Excel converts PI radians to 180°. DEGREES is useful for angle conversions and trigonometric calculations. |
| SIN | =SIN(angle) | =SIN(RADIANS(30)) | The SIN function returns the sine of an angle (in radians). In this example, Excel calculates the sine of 30°. SIN is useful for trigonometry, engineering, and geometry calculations. |
| COS | =COS(angle) | =COS(RADIANS(60)) | The COS function returns the cosine of an angle (in radians). In this example, Excel calculates the cosine of 60°. COS is useful for trigonometry, geometry, and scientific calculations. |
| TAN | =TAN(angle) | =TAN(RADIANS(45)) | The TAN function returns the tangent of an angle (in radians). In this example, Excel calculates the tangent of 45°. TAN is essential for trigonometric, engineering, and geometry calculations. |
| ATAN2 | =ATAN2(x_num, y_num) | =ATAN2(3,4) | The ATAN2 function returns the arctangent of x/y, considering the signs of both to determine the quadrant. In this example, Excel calculates ATAN2(3,4). ATAN2 is useful for vector angles, navigation, and engineering calculations. |
| ACOS | =ACOS(number) | =ACOS(0.5) | The ACOS function returns the arccosine (inverse cosine) of a number. In this example, Excel calculates ACOS(0.5) in radians. ACOS is useful in trigonometry, geometry, and scientific calculations. |
| ACOSH | =ACOSH(number) | =ACOSH(10) | The ACOSH function returns the inverse hyperbolic cosine of a number. In this example, Excel calculates ACOSH(10). ACOSH is useful for advanced mathematics, hyperbolic functions, and engineering. |
| ACOT | =ACOT(number) | =ACOT(1) | The ACOT function returns the arccotangent of a number. In this example, Excel calculates ACOT(1). ACOT is useful for trigonometry, engineering, and geometric calculations. |
| ACOTH | =ACOTH(number) | =ACOTH(2) | The ACOTH function returns the inverse hyperbolic cotangent of a number. In this example, Excel calculates ACOTH(2). ACOTH is useful in advanced math, hyperbolic calculations, and scientific analysis. |
| ASIN | =ASIN(number) | =ASIN(0.5) | The ASIN function returns the arcsine (inverse sine) of a number. In this example, Excel calculates ASIN(0.5) in radians. ASIN is useful for trigonometry, geometry, and engineering calculations. |
| ASINH | =ASINH(number) | =ASINH(1) | The ASINH function returns the inverse hyperbolic sine of a number. In this example, Excel calculates ASINH(1). ASINH is useful in advanced mathematics, hyperbolic calculations, and scientific applications. |
| ATAN | =ATAN(number) | =ATAN(1) | The ATAN function returns the arctangent of a number. In this example, Excel calculates ATAN(1) in radians. ATAN is useful for trigonometry, angles, and engineering calculations. |
| ATANH | =ATANH(number) | =ATANH(0.5) | The ATANH function returns the inverse hyperbolic tangent of a number. In this example, Excel calculates ATANH(0.5). ATANH is useful for hyperbolic functions, scientific calculations, and engineering. |
| COT | =COT(number) | =COT(RADIANS(45)) | The COT function returns the cotangent of an angle in radians. In this example, Excel calculates COT(45°). COT is useful in trigonometry, geometry, and scientific analysis. |
| COTH | =COTH(number) | =COTH(1) | The COTH function returns the hyperbolic cotangent of a number. In this example, Excel calculates COTH(1). COTH is useful for hyperbolic functions, engineering, and scientific calculations. |
| CSC | =CSC(angle) | =CSC(RADIANS(30)) | The CSC function returns the cosecant of an angle in radians. In this example, Excel calculates the cosecant of 30°. CSC is useful in trigonometry, engineering, and geometry calculations. |
| CSCH | =CSCH(number) | =CSCH(1) | The CSCH function returns the hyperbolic cosecant of a number. In this example, Excel calculates CSCH(1). CSCH is useful for hyperbolic calculations, engineering, and scientific analysis. |
| SEC | =SEC(angle) | =SEC(RADIANS(60)) | The SEC function returns the secant of an angle in radians. In this example, Excel calculates the secant of 60°. SEC is useful for trigonometry, geometry, and engineering calculations. |
| SECH | =SECH(number) | =SECH(1) | The SECH function returns the hyperbolic secant of a number. In this example, Excel calculates SECH(1). SECH is useful for hyperbolic functions, engineering, and scientific computations. |
| MROUND | =MROUND(number, multiple) | =MROUND(A1,5) | The MROUND function rounds a number to the nearest specified multiple. In this example, Excel rounds A1 to the nearest multiple of 5. MROUND is useful for financial calculations, batch processing, and rounding numeric data. |
| RAND | =RAND() | =RAND() | The RAND function generates a random decimal number between 0 and 1. In this example, Excel returns a random number like 0.734. RAND is useful for simulations, random sampling, and probabilistic models. |
| RANDBETWEEN | =RANDBETWEEN(bottom, top) | =RANDBETWEEN(1,100) | The RANDBETWEEN function generates a random integer between the specified bottom and top values. In this example, Excel returns a random number between 1 and 100. RANDBETWEEN is useful for simulations, testing, and random sampling. |
| RANDARRAY | =RANDARRAY([rows],[columns],[min],[max],[integer]) | =RANDARRAY(5,1,1,50,TRUE) | The RANDARRAY function returns an array of random numbers with optional size, min, max, and integer settings. In this example, Excel generates 5 random integers between 1 and 50. RANDARRAY is useful for simulations, randomized datasets, and dynamic calculations. |
| ISODD | =ISODD(number) | =ISODD(A1) | The ISODD function returns TRUE if a number is odd and FALSE if even. In this example, Excel checks if A1 is an odd number. ISODD is useful for validation, logical calculations, and data analysis. |
| ISEVEN | =ISEVEN(number) | =ISEVEN(A1) | The ISEVEN function returns TRUE if a number is even and FALSE if odd. In this example, Excel checks if A1 is an even number. ISEVEN is useful for validation, logic checks, and numeric analysis. |
| CEILING | =CEILING(number, significance) | =CEILING(12.3,5) | The CEILING function rounds a number up to the nearest multiple of significance. In this example, Excel rounds 12.3 to 15. CEILING is useful for rounding amounts, pricing, and numeric calculations. |
| CEILING.MATH | =CEILING.MATH(number, [significance], [mode]) | =CEILING.MATH(12.3,5) | The CEILING.MATH function rounds a number up to the nearest multiple of significance with optional negative number mode. In this example, Excel rounds 12.3 to 15. CEILING.MATH is useful for precise rounding, financial, and engineering calculations. |
| CEILING.PRECISE | =CEILING.PRECISE(number, [significance]) | =CEILING.PRECISE(12.3,5) | The CEILING.PRECISE function rounds a number up to the nearest multiple of significance, ignoring sign for negative numbers. In this example, Excel rounds 12.3 to 15. CEILING.PRECISE is useful for consistent rounding, pricing, and numeric adjustments. |
| FLOOR | =FLOOR(number, significance) | =FLOOR(12.7,5) | The FLOOR function rounds a number down to the nearest multiple of significance. In this example, Excel rounds 12.7 down to 10. FLOOR is useful for rounding, budgeting, and numeric analysis. |
| FLOOR.MATH | =FLOOR.MATH(number, [significance], [mode]) | =FLOOR.MATH(12.7,5) | The FLOOR.MATH function rounds a number down to the nearest multiple of significance with optional negative number mode. In this example, Excel rounds 12.7 down to 10. FLOOR.MATH is useful for precise rounding, engineering, and financial calculations. |
| FLOOR.PRECISE | =FLOOR.PRECISE(number, [significance]) | =FLOOR.PRECISE(12.7,5) | The FLOOR.PRECISE function rounds a number down to the nearest multiple of significance, ignoring sign for negative numbers. In this example, Excel rounds 12.7 down to 10. FLOOR.PRECISE is useful for consistent rounding, numeric adjustments, and calculations. |
| GCD | =GCD(number1, [number2], ...) | =GCD(12,18,24) | The GCD function returns the greatest common divisor of numbers. In this example, Excel calculates GCD of 12, 18, and 24 as 6. GCD is useful for fraction simplification, ratios, and number theory calculations. |
| LCM | =LCM(number1, [number2], ...) | =LCM(3,4,5) | The LCM function returns the least common multiple of numbers. In this example, Excel calculates LCM of 3, 4, and 5 as 60. LCM is useful for fractions, scheduling, and numeric analysis. |
| SEQUENCE | =SEQUENCE(rows, [columns], [start], [step]) | =SEQUENCE(5,1,1,1) | The SEQUENCE function generates an array of sequential numbers. In this example, Excel creates 5 numbers from 1 to 5. SEQUENCE is useful for generating dynamic series, table filling, and calculations. |
| SIGN | =SIGN(number) | =SIGN(-10) | The SIGN function returns the sign of a number: 1 for positive, -1 for negative, 0 for zero. In this example, Excel returns -1 for -10. SIGN is useful for logic checks, formulas, and numeric calculations. |
| EXP | =EXP(number) | =EXP(1) | The EXP function returns e raised to the power of a number. In this example, Excel calculates e^1 ≈ 2.71828. EXP is useful for growth calculations, finance, and scientific formulas. |
| TANH | =TANH(number) | =TANH(1) | The TANH function returns the hyperbolic tangent of a number. In this example, Excel calculates TANH(1) ≈ 0.76159. TANH is useful for hyperbolic functions, engineering, and mathematical calculations. |
| SINH | =SINH(number) | =SINH(1) | The SINH function returns the hyperbolic sine of a number. In this example, Excel calculates SINH(1) ≈ 1.1752. SINH is useful for hyperbolic calculations, science, and engineering applications. |
| COSH | =COSH(number) | =COSH(1) | The COSH function returns the hyperbolic cosine of a number. In this example, Excel calculates COSH(1) ≈ 1.5431. COSH is useful for hyperbolic functions, scientific, and engineering calculations. |
| SERIESSUM | =SERIESSUM(x, n, m, coefficients) | =SERIESSUM(2,1,1,{1,2,3}) | The SERIESSUM function returns the sum of a power series based on x, start n, step m, and coefficients. In this example, Excel calculates a series sum for x=2. SERIESSUM is useful in mathematical series, finance, and engineering computations. |
| GAMMALN | =GAMMALN(number) | =GAMMALN(5) | The GAMMALN function returns the natural logarithm of the gamma function. In this example, Excel calculates GAMMALN(5). GAMMALN is useful for statistics, probability, and complex mathematical functions. |
| GAMMALN.PRECISE | =GAMMALN.PRECISE(number) | =GAMMALN.PRECISE(5) | The GAMMALN.PRECISE function returns the natural logarithm of the gamma function with enhanced precision. In this example, Excel calculates GAMMALN.PRECISE(5). It is useful for statistical, engineering, and scientific calculations. |
| BASE | =BASE(number, radix, [min_length]) | =BASE(10,2) | The BASE function converts a number into a text representation in a specified base (radix). In this example, Excel converts 10 to binary "1010". BASE is useful for computer science, number systems, and encoding. |
| DECIMAL | =DECIMAL(text, radix) | =DECIMAL("1010",2) | The DECIMAL function converts a number from any base (radix) to decimal. In this example, Excel converts "1010" in base 2 to 10 in decimal. DECIMAL is useful for number conversions, programming, and engineering. |
| IMPOWER | =IMPOWER(inumber, power) | =IMPOWER("3+4i",2) | The IMPOWER function raises a complex number to a power. In this example, Excel calculates ("3+4i")². IMPOWER is useful for complex number calculations, engineering, and math modeling. |
| IMSQRT | =IMSQRT(inumber) | =IMSQRT("3+4i") | The IMSQRT function returns the square root of a complex number. In this example, Excel calculates the square root of "3+4i". IMSQRT is useful for complex number operations, scientific, and engineering applications. |
| MULTINOMIAL | =MULTINOMIAL(number1,[number2],...) | =MULTINOMIAL(2,3,4) | The MULTINOMIAL function returns the multinomial of a set of numbers. In this example, Excel calculates MULTINOMIAL(2,3,4) = 1260. MULTINOMIAL is useful for probability, combinatorics, and statistical calculations. |
| MUNIT | =MUNIT(order) | =MUNIT(3) | The MUNIT function returns an identity matrix of the specified order. In this example, Excel generates a 3x3 identity matrix. MUNIT is useful for linear algebra, matrix calculations, and engineering. |
| IMLN | =IMLN(inumber) | =IMLN("3+4i") | The IMLN function returns the natural logarithm of a complex number. In this example, Excel calculates IMLN("3+4i"). IMLN is useful for complex logarithms, engineering, and mathematical analysis. |
| ERFC | =ERFC(x) | =ERFC(1) | The ERFC function returns the complementary error function of a number. In this example, Excel calculates ERFC(1). ERFC is useful for statistics, probability, and engineering calculations. |
| ERFC.PRECISE | =ERFC.PRECISE(x) | =ERFC.PRECISE(1) | The ERFC.PRECISE function returns the complementary error function with enhanced precision. In this example, Excel calculates ERFC.PRECISE(1). It is useful for accurate statistical and engineering computations. |
| COMBIN | =COMBIN(number, number_chosen) | =COMBIN(5,3) | The COMBIN function returns the number of combinations for a given number of items. In this example, Excel calculates COMBIN(5,3) = 10. COMBIN is useful for probability, statistics, and combinatorics. |
| COMBINA | =COMBINA(number, number_chosen) | =COMBINA(5,3) | The COMBINA function returns the number of combinations with repetitions allowed. In this example, Excel calculates COMBINA(5,3) = 35. COMBINA is useful for combinatorics, probability, and scenarios with repeated items. |
| SEQUENCE | =SEQUENCE(rows, [columns], [start], [step]) | =SEQUENCE(5,1,1,1) | The SEQUENCE function generates an array of sequential numbers. In this example, Excel creates 5 numbers from 1 to 5. SEQUENCE is useful for generating dynamic series, table filling, and calculations. |
| SIGN | =SIGN(number) | =SIGN(-10) | The SIGN function returns the sign of a number: 1 for positive, -1 for negative, 0 for zero. In this example, Excel returns -1 for -10. SIGN is useful for logic checks, formulas, and numeric calculations. |
| EXP | =EXP(number) | =EXP(1) | The EXP function returns e raised to the power of a number. In this example, Excel calculates e^1 ≈ 2.71828. EXP is useful for growth calculations, finance, and scientific formulas. |
| TANH | =TANH(number) | =TANH(1) | The TANH function returns the hyperbolic tangent of a number. In this example, Excel calculates TANH(1) ≈ 0.76159. TANH is useful for hyperbolic functions, engineering, and mathematical calculations. |
| SINH | =SINH(number) | =SINH(1) | The SINH function returns the hyperbolic sine of a number. In this example, Excel calculates SINH(1) ≈ 1.1752. SINH is useful for hyperbolic calculations, science, and engineering applications. |
| COSH | =COSH(number) | =COSH(1) | The COSH function returns the hyperbolic cosine of a number. In this example, Excel calculates COSH(1) ≈ 1.5431. COSH is useful for hyperbolic functions, scientific, and engineering calculations. |
| SERIESSUM | =SERIESSUM(x, n, m, coefficients) | =SERIESSUM(2,1,1,{1,2,3}) | The SERIESSUM function returns the sum of a power series based on x, start n, step m, and coefficients. In this example, Excel calculates a series sum for x=2. SERIESSUM is useful in mathematical series, finance, and engineering computations. |
| GAMMALN | =GAMMALN(number) | =GAMMALN(5) | The GAMMALN function returns the natural logarithm of the gamma function. In this example, Excel calculates GAMMALN(5). GAMMALN is useful for statistics, probability, and complex mathematical functions. |
| GAMMALN.PRECISE | =GAMMALN.PRECISE(number) | =GAMMALN.PRECISE(5) | The GAMMALN.PRECISE function returns the natural logarithm of the gamma function with enhanced precision. In this example, Excel calculates GAMMALN.PRECISE(5). It is useful for statistical, engineering, and scientific calculations. |
| BASE | =BASE(number, radix, [min_length]) | =BASE(10,2) | The BASE function converts a number into a text representation in a specified base (radix). In this example, Excel converts 10 to binary "1010". BASE is useful for computer science, number systems, and encoding. |
| DECIMAL | =DECIMAL(text, radix) | =DECIMAL("1010",2) | The DECIMAL function converts a number from any base (radix) to decimal. In this example, Excel converts "1010" in base 2 to 10 in decimal. DECIMAL is useful for number conversions, programming, and engineering. |
| IMPOWER | =IMPOWER(inumber, power) | =IMPOWER("3+4i",2) | The IMPOWER function raises a complex number to a power. In this example, Excel calculates ("3+4i")². IMPOWER is useful for complex number calculations, engineering, and math modeling. |
| IMSQRT | =IMSQRT(inumber) | =IMSQRT("3+4i") | The IMSQRT function returns the square root of a complex number. In this example, Excel calculates the square root of "3+4i". IMSQRT is useful for complex number operations, scientific, and engineering applications. |
| MULTINOMIAL | =MULTINOMIAL(number1,[number2],...) | =MULTINOMIAL(2,3,4) | The MULTINOMIAL function returns the multinomial of a set of numbers. In this example, Excel calculates MULTINOMIAL(2,3,4) = 1260. MULTINOMIAL is useful for probability, combinatorics, and statistical calculations. |
| MUNIT | =MUNIT(order) | =MUNIT(3) | The MUNIT function returns an identity matrix of the specified order. In this example, Excel generates a 3x3 identity matrix. MUNIT is useful for linear algebra, matrix calculations, and engineering. |
| IMLN | =IMLN(inumber) | =IMLN("3+4i") | The IMLN function returns the natural logarithm of a complex number. In this example, Excel calculates IMLN("3+4i"). IMLN is useful for complex logarithms, engineering, and mathematical analysis. |
| ERFC | =ERFC(x) | =ERFC(1) | The ERFC function returns the complementary error function of a number. In this example, Excel calculates ERFC(1). ERFC is useful for statistics, probability, and engineering calculations. |
| ERFC.PRECISE | =ERFC.PRECISE(x) | =ERFC.PRECISE(1) | The ERFC.PRECISE function returns the complementary error function with enhanced precision. In this example, Excel calculates ERFC.PRECISE(1). It is useful for accurate statistical and engineering computations. |
| COMBIN | =COMBIN(number, number_chosen) | =COMBIN(5,3) | The COMBIN function returns the number of combinations for a given number of items. In this example, Excel calculates COMBIN(5,3) = 10. COMBIN is useful for probability, statistics, and combinatorics. |
| COMBINA | =COMBINA(number, number_chosen) | =COMBINA(5,3) | The COMBINA function returns the number of combinations with repetitions allowed. In this example, Excel calculates COMBINA(5,3) = 35. COMBINA is useful for combinatorics, probability, and scenarios with repeated items. |
Operator Functions
- Excel operator functions used for arithmetic calculations, comparisons, and logical evaluations.| Formula | Syntax | Example | Description |
|---|---|---|---|
| ADD | =ADD(number1, number2) | =ADD(10, 5) | The ADD function adds two numbers together and returns the total. In this example, ADD(10, 5) returns 15. It is useful for simple arithmetic operations and calculated fields. |
| CONCAT | =CONCAT(text1, text2) | =CONCAT("Excel", " Tips") | The CONCAT function joins multiple text strings into one. In this example, Excel combines "Excel" and " Tips" into "Excel Tips". Useful for creating dynamic text and reports. |
| DIVIDE | =DIVIDE(number1, number2) | =DIVIDE(20, 4) | The DIVIDE function divides one number by another. In this example, DIVIDE(20, 4) returns 5. Useful for ratio, percentage, and financial calculations. |
| EQ | =EQ(value1, value2) | =EQ(5, 5) | The EQ function checks whether two values are equal. In this example, EQ(5, 5) returns TRUE. Commonly used in logical tests and conditional formulas. |
| GT | =GT(value1, value2) | =GT(10, 5) | The GT function checks if one value is greater than another. In this example, GT(10, 5) returns TRUE. Useful for comparisons and decision-based calculations. |
| GTE | =GTE(value1, value2) | =GTE(5, 5) | The GTE function checks if a value is greater than or equal to another value. In this example, GTE(5, 5) returns TRUE. Ideal for thresholds, grading systems, and validations. |
| ISBETWEEN | =ISBETWEEN(value, min, max) | =ISBETWEEN(7, 5, 10) | The ISBETWEEN function checks whether a value lies between two numbers. In this example, ISBETWEEN(7, 5, 10) returns TRUE. Useful for ranges, scoring, and validation rules. |
| LT | =LT(value1, value2) | =LT(3, 8) | The LT function checks if one value is less than another. In this example, LT(3, 8) returns TRUE. Commonly used in logical tests and comparisons. |
| LTE | =LTE(value1, value2) | =LTE(5, 5) | The LTE function checks if a value is less than or equal to another value. In this example, LTE(5, 5) returns TRUE. Useful for limits, rules, and conditional logic. |
| MINUS | =MINUS(number1, number2) | =MINUS(10, 3) | The MINUS function subtracts one number from another and returns the result. In this example, MINUS(10, 3) returns 7. It is useful for difference calculations, balances, and variance analysis. |
| MULTIPLY | =MULTIPLY(number1, number2) | =MULTIPLY(4, 5) | The MULTIPLY function multiplies two numbers and returns the product. In this example, MULTIPLY(4, 5) returns 20. Commonly used in pricing, totals, and mathematical calculations. |
| NE | =NE(value1, value2) | =NE(5, 3) | The NE function checks whether two values are not equal. In this example, NE(5, 3) returns TRUE. It is widely used in logical tests and validation rules. |
| POW | =POW(number, power) | =POW(2, 3) | The POW function raises a number to a specified power. In this example, POW(2, 3) returns 8. Useful for exponential calculations and growth formulas. |
| UMINUS | =UMINUS(number) | =UMINUS(5) | The UMINUS function converts a positive number into a negative value. In this example, UMINUS(5) returns -5. Useful for sign control and financial adjustments. |
| UNARY_PERCENT | =UNARY_PERCENT(number) | =UNARY_PERCENT(50) | The UNARY_PERCENT function converts a number into a percentage value. In this example, UNARY_PERCENT(50) returns 50%. Helpful for percentage-based calculations and reports. |
| UNIQUE | =UNIQUE(range) | =UNIQUE(A1:A10) | The UNIQUE function extracts distinct values from a list or range. In this example, UNIQUE(A1:A10) returns only unique entries. Ideal for data cleaning, summaries, and analysis. |
| UPLUS | =UPLUS(number) | =UPLUS(-7) | The UPLUS function forces a number to be treated as positive. In this example, UPLUS(-7) returns 7. Useful for normalization and numeric consistency. |
Engineering Functions
- Excel engineering functions used for number system conversions, complex calculations, and technical analysis.| Formula | Syntax | Example | Description |
|---|---|---|---|
| BIN2DEC | =BIN2DEC(number) | =BIN2DEC(1010) | The BIN2DEC function converts a binary number into its decimal equivalent. In this example, BIN2DEC(1010) returns 10. This function is widely used in engineering, computer science, and digital logic calculations. |
| BIN2HEX | =BIN2HEX(number, [places]) | =BIN2HEX(1010) | The BIN2HEX function converts a binary number into a hexadecimal value. In this example, BIN2HEX(1010) returns A. It is useful for system-level calculations, memory addressing, and programming-related analysis. |
| BIN2OCT | =BIN2OCT(number, [places]) | =BIN2OCT(1010) | The BIN2OCT function converts a binary number into its octal equivalent. In this example, BIN2OCT(1010) returns 12. It is commonly used in engineering and computer number system conversions. |
| BITAND | =BITAND(number1, number2) | =BITAND(5, 3) | The BITAND function performs a bitwise AND operation on two numbers. In this example, BITAND(5, 3) returns 1. Useful in low-level programming, masking, and digital logic analysis. |
| BITLSHIFT | =BITLSHIFT(number, shift_amount) | =BITLSHIFT(3, 2) | The BITLSHIFT function shifts bits of a number to the left. In this example, BITLSHIFT(3, 2) returns 12. It is useful for fast multiplication and binary calculations. |
| BITOR | =BITOR(number1, number2) | =BITOR(5, 3) | The BITOR function performs a bitwise OR operation. In this example, BITOR(5, 3) returns 7. Helpful in system flags, permissions, and binary logic. |
| BITRSHIFT | =BITRSHIFT(number, shift_amount) | =BITRSHIFT(16, 2) | The BITRSHIFT function shifts bits of a number to the right. In this example, BITRSHIFT(16, 2) returns 4. Commonly used for fast division and binary manipulation. |
| BITXOR | =BITXOR(number1, number2) | =BITXOR(5, 3) | The BITXOR function performs a bitwise exclusive OR operation. In this example, BITXOR(5, 3) returns 6. Useful in encryption logic and error detection. |
| COMPLEX | =COMPLEX(real_num, i_num, [suffix]) | =COMPLEX(3, 4) | The COMPLEX function creates a complex number from real and imaginary parts. In this example, COMPLEX(3, 4) returns 3+4i. Widely used in electrical engineering and signal processing. |
| DEC2BIN | =DEC2BIN(number, [places]) | =DEC2BIN(10) | The DEC2BIN function converts a decimal number into binary format. In this example, DEC2BIN(10) returns 1010. Common in digital electronics and computing tasks. |
| DEC2HEX | =DEC2HEX(number, [places]) | =DEC2HEX(255) | The DEC2HEX function converts a decimal number into hexadecimal format. In this example, DEC2HEX(255) returns FF. Useful for memory addresses and color codes. |
| DEC2OCT | =DEC2OCT(number, [places]) | =DEC2OCT(10) | The DEC2OCT function converts a decimal number into octal format. In this example, DEC2OCT(10) returns 12. Used in engineering and legacy system calculations. |
| DELTA | =DELTA(number1, [number2]) | =DELTA(5, 5) | The DELTA function compares two numbers and returns 1 if they are equal. In this example, DELTA(5, 5) returns 1. Useful in engineering models and conditional logic. |
| ERF | =ERF(lower_limit, [upper_limit]) | =ERF(1) | The ERF function returns the error function value used in probability and statistics. In this example, ERF(1) returns a value related to the normal distribution. It is commonly used in engineering and scientific calculations. |
| ERF.PRECISE | =ERF.PRECISE(x) | =ERF.PRECISE(1) | The ERF.PRECISE function provides a more accurate error function value. It is used in advanced statistical and engineering models requiring precision. |
| GESTEP | =GESTEP(number, [step]) | =GESTEP(5, 3) | The GESTEP function tests whether a number is greater than or equal to a step value. In this example, GESTEP(5, 3) returns 1. It is useful in signal processing and logical testing. |
| HEX2BIN | =HEX2BIN(number, [places]) | =HEX2BIN("A") | The HEX2BIN function converts a hexadecimal number into binary format. In this example, HEX2BIN("A") returns 1010. Commonly used in digital electronics and system calculations. |
| HEX2DEC | =HEX2DEC(number) | =HEX2DEC("FF") | The HEX2DEC function converts a hexadecimal number into decimal. In this example, HEX2DEC("FF") returns 255. Widely used in programming and color code calculations. |
| HEX2OCT | =HEX2OCT(number, [places]) | =HEX2OCT("A") | The HEX2OCT function converts a hexadecimal number into octal format. It is useful in engineering and legacy system conversions. |
| IMABS | =IMABS(inumber) | =IMABS("3+4i") | The IMABS function returns the absolute value of a complex number. In this example, IMABS("3+4i") returns 5. Common in electrical and signal engineering. |
| IMAGINARY | =IMAGINARY(inumber) | =IMAGINARY("3+4i") | The IMAGINARY function returns the imaginary coefficient of a complex number. In this example, IMAGINARY("3+4i") returns 4. Useful for complex number analysis. |
| IMARGUMENT | =IMARGUMENT(inumber) | =IMARGUMENT("3+4i") | The IMARGUMENT function returns the argument (phase angle) of a complex number. Used in signal processing and electrical engineering. |
| IMCONJUGATE | =IMCONJUGATE(inumber) | =IMCONJUGATE("3+4i") | The IMCONJUGATE function returns the complex conjugate of a complex number. Used in AC circuit analysis and complex arithmetic. |
| IMCOS | =IMCOS(inumber) | =IMCOS("1+i") | The IMCOS function returns the cosine of a complex number. Used in advanced trigonometric and engineering calculations. |
| IMCOSH | =IMCOSH(inumber) | =IMCOSH("1+i") | The IMCOSH function returns the hyperbolic cosine of a complex number. Used in engineering and mathematical modeling. |
| IMCOT | =IMCOT(inumber) | =IMCOT("1+i") | The IMCOT function returns the cotangent of a complex number. Useful in signal analysis and complex trigonometry. |
| IMCOTH | =IMCOTH(inumber) | =IMCOTH("1+i") | The IMCOTH function returns the hyperbolic cotangent of a complex number. Common in advanced mathematical and engineering calculations. |
| IMCSC | =IMCSC(inumber) | =IMCSC("1+i") | The IMCSC function returns the cosecant of a complex number. Used in engineering and complex trigonometric analysis. |
| IMCSCH | =IMCSCH(inumber) | =IMCSCH("1+i") | The IMCSCH function returns the hyperbolic cosecant of a complex number. Useful in advanced engineering and mathematical equations. |
| IMDIV | =IMDIV(inumber1, inumber2) | =IMDIV("6+8i","2+i") | The IMDIV function divides one complex number by another and returns the result. It is commonly used in electrical engineering and complex arithmetic. |
| IMEXP | =IMEXP(inumber) | =IMEXP("1+i") | The IMEXP function returns the exponential of a complex number. Used in advanced mathematical and engineering models. |
| IMLOG | =IMLOG(inumber) | =IMLOG("1+i") | The IMLOG function returns the natural logarithm of a complex number. Useful in signal processing and complex analysis. |
| IMLOG10 | =IMLOG10(inumber) | =IMLOG10("1+i") | The IMLOG10 function returns the base-10 logarithm of a complex number. Used in engineering and scientific calculations. |
| IMLOG2 | =IMLOG2(inumber) | =IMLOG2("1+i") | The IMLOG2 function returns the base-2 logarithm of a complex number. Common in digital signal processing and binary analysis. |
| IMPRODUCT | =IMPRODUCT(inumber1, inumber2, …) | =IMPRODUCT("2+i","3+4i") | The IMPRODUCT function multiplies complex numbers together. Used in electrical engineering and signal analysis. |
| IMREAL | =IMREAL(inumber) | =IMREAL("3+4i") | The IMREAL function returns the real coefficient of a complex number. Useful for separating real and imaginary components. |
| IMSEC | =IMSEC(inumber) | =IMSEC("1+i") | The IMSEC function returns the secant of a complex number. Used in advanced trigonometric engineering calculations. |
| IMSECH | =IMSECH(inumber) | =IMSECH("1+i") | The IMSECH function returns the hyperbolic secant of a complex number. Useful in signal and mathematical modeling. |
| IMSIN | =IMSIN(inumber) | =IMSIN("1+i") | The IMSIN function returns the sine of a complex number. Used in wave and signal analysis. |
| IMSINH | =IMSINH(inumber) | =IMSINH("1+i") | The IMSINH function returns the hyperbolic sine of a complex number. Useful in advanced engineering mathematics. |
| IMSUB | =IMSUB(inumber1, inumber2) | =IMSUB("5+6i","2+i") | The IMSUB function subtracts one complex number from another. Common in complex arithmetic operations. |
| IMSUM | =IMSUM(inumber1, inumber2, …) | =IMSUM("2+3i","4+i") | The IMSUM function adds multiple complex numbers together. Widely used in engineering calculations. |
| IMTAN | =IMTAN(inumber) | =IMTAN("1+i") | The IMTAN function returns the tangent of a complex number. Used in trigonometric and signal-processing applications. |
| IMTANH | =IMTANH(inumber) | =IMTANH("1+i") | The IMTANH function returns the hyperbolic tangent of a complex number. Useful in advanced engineering and mathematical analysis. |
| OCT2BIN | =OCT2BIN(number, [places]) | =OCT2BIN(12) | The OCT2BIN function converts an octal number into binary format. Used in digital electronics and system-level calculations. |
| OCT2DEC | =OCT2DEC(number) | =OCT2DEC(12) | The OCT2DEC function converts an octal number into decimal. Helpful in engineering and computing conversions. |
| OCT2HEX | =OCT2HEX(number, [places]) | =OCT2HEX(12) | The OCT2HEX function converts an octal number into hexadecimal format. Useful in memory addressing and engineering systems. |
Financial Functions
- Excel financial formulas for loans, investments, interest calculations, and financial planning.| Formula | Syntax | Example | Description |
|---|---|---|---|
| PMT | =PMT(rate, nper, pv, [fv], [type]) | =PMT(10%/12, 60, -500000) | The PMT function calculates the fixed payment to repay a loan or investment over time with a constant interest rate. Commonly used for EMI, home loans, and financial planning. |
| NPV | =NPV(rate, value1, [value2], …) | =NPV(10%, B2:B6) | The NPV function calculates the net present value of an investment based on a discount rate and future cash flows. It is essential for evaluating project profitability and investment decisions. |
| IRR | =IRR(values, [guess]) | =IRR(B2:B6) | The IRR function calculates the internal rate of return for a series of cash flows. It is commonly used in financial analysis to assess investment returns. |
| FV | =FV(rate, nper, pmt, [pv], [type]) | =FV(5%/12, 60, -5000) | The FV function calculates the future value of an investment based on regular payments, interest rate, and periods. Useful for savings, retirement planning, and investment projections. |
| PV | =PV(rate, nper, pmt, [fv], [type]) | =PV(5%/12, 60, -5000) | The PV function calculates the present value of an investment or loan based on future payments and interest. It helps determine how much to invest today for a target future value. |
| RATE | =RATE(nper, pmt, pv, [fv], [type], [guess]) | =RATE(60, -5000, 200000) | The RATE function calculates the interest rate per period for an investment or loan based on payment, present value, and future value. It is essential for comparing loan or investment rates. |
| NPER | =NPER(rate, pmt, pv, [fv], [type]) | =NPER(5%/12, -5000, 200000) | The NPER function calculates the number of periods required to repay a loan or reach an investment goal. It is widely used in EMI planning and investment timelines. |
| IPMT | =IPMT(rate, per, nper, pv, [fv], [type]) | =IPMT(5%/12, 1, 60, 200000) | The IPMT function calculates the interest portion of a specific loan payment. Useful for amortization schedules and interest tracking. |
| PPMT | =PPMT(rate, per, nper, pv, [fv], [type]) | =PPMT(5%/12, 1, 60, 200000) | The PPMT function calculates the principal portion of a specific loan payment. Helps track how much of each payment reduces the principal balance. |
| CUMIPMT | =CUMIPMT(rate, nper, pv, start_period, end_period, type) | =CUMIPMT(5%/12, 60, 200000, 1, 12, 0) | The CUMIPMT function calculates the cumulative interest paid on a loan between two periods. Essential for financial analysis and amortization tracking. |
| CUMPRINC | =CUMPRINC(rate, nper, pv, start_period, end_period, type) | =CUMPRINC(5%/12, 60, 200000, 1, 12, 0) | The CUMPRINC function calculates the cumulative principal paid on a loan between two periods. Useful for financial planning and loan amortization tracking. |
| SLN | =SLN(cost, salvage, life) | =SLN(10000, 1000, 5) | The SLN function calculates the straight-line depreciation of an asset for each period. It is useful in accounting and financial planning to evenly allocate asset cost over its useful life. |
| DDB | =DDB(cost, salvage, life, period, [factor]) | =DDB(10000, 1000, 5, 1, 2) | The DDB function calculates the depreciation of an asset using the double-declining balance method. Ideal for accelerated depreciation in accounting and tax calculations. |
| DB | =DB(cost, salvage, life, period, [month]) | =DB(10000, 1000, 5, 1) | The DB function calculates depreciation of an asset for a specified period using the fixed-declining balance method. Useful for accounting purposes when accelerated depreciation is required. |
| XNPV | =XNPV(rate, values, dates) | =XNPV(10%, B2:B6, C2:C6) | The XNPV function calculates the net present value for a schedule of cash flows that are not necessarily periodic. Essential for accurate investment analysis and financial planning. |
| XIRR | =XIRR(values, dates, [guess]) | =XIRR(B2:B6, C2:C6) | The XIRR function calculates the internal rate of return for a series of cash flows with irregular intervals. It is widely used for investment and project analysis. |
| MIRR | =MIRR(values, finance_rate, reinvest_rate) | =MIRR(B2:B6, 10%, 12%) | The MIRR function calculates the modified internal rate of return, considering both financing and reinvestment rates. Useful for assessing project profitability more realistically. |
| PDURATION | =PDURATION(rate, pv, fv) | =PDURATION(10%, 1000, 2000) | The PDURATION function calculates the number of periods required for an investment to reach a specified future value at a constant interest rate. Useful in investment planning. |
| EFFECT | =EFFECT(nominal_rate, npery) | =EFFECT(10%, 12) | The EFFECT function calculates the effective annual interest rate based on nominal rate and compounding periods per year. Important for comparing loans and investments. |
| NOMINAL | =NOMINAL(effect_rate, npery) | =NOMINAL(10.4713%, 12) | The NOMINAL function calculates the nominal interest rate from the effective rate and number of compounding periods. Useful for loan agreements and financial analysis. |
| ACCRINT | =ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis]) | =ACCRINT("1-Jan-2026","1-Jul-2026","31-Dec-2026",5%,1000,2) | The ACCRINT function calculates the accrued interest for a security that pays periodic interest. Widely used in bond and fixed-income investment calculations. |
| ACCRINTM | =ACCRINTM(issue, settlement, rate, par, [basis]) | =ACCRINTM("1-Jan-2026","31-Dec-2026",5%,1000) | The ACCRINTM function calculates the accrued interest at maturity for a security paying interest at maturity. Useful for financial reporting and investment calculations. |
| PRICE | =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis]) | =PRICE("1-Jan-2026","1-Jan-2030",5%,6%,100,2) | The PRICE function calculates the price per $100 face value of a security paying periodic interest. Used in bond valuation and investment analysis. |
| PRICEDISC | =PRICEDISC(settlement, maturity, discount, [redemption], [basis]) | =PRICEDISC("1-Jan-2026","1-Jan-2027",5%) | The PRICEDISC function calculates the price of a discounted security. Useful for short-term bond pricing and discount yield calculations. |
| PRICEMAT | =PRICEMAT(settlement, maturity, issue, rate, yld, [basis]) | =PRICEMAT("1-Jan-2026","1-Jan-2030","1-Jan-2025",5%,6%) | The PRICEMAT function calculates the price of a security that pays interest at maturity. Useful for bond valuation and investment decisions. |
| YIELD | =YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) | =YIELD("1-Jan-2026","1-Jan-2030",5%,95,100,2) | The YIELD function calculates the yield of a security that pays periodic interest. It helps investors evaluate bond returns. |
| YIELDDISC | =YIELDDISC(settlement, maturity, pr, redemption, [basis]) | =YIELDDISC("1-Jan-2026","1-Jan-2027",95,100) | The YIELDDISC function calculates the annual yield for a discounted security. It is useful for pricing short-term bonds. |
| YIELDMAT | =YIELDMAT(settlement, maturity, issue, rate, pr, [basis]) | =YIELDMAT("1-Jan-2026","1-Jan-2030","1-Jan-2025",5%,95) | The YIELDMAT function calculates the annual yield of a security that pays interest at maturity. Useful for bond investment analysis. |
| TBILLPRICE | =TBILLPRICE(settlement, maturity, discount) | =TBILLPRICE("1-Jan-2026","1-Jan-2027",5%) | The TBILLPRICE function calculates the price of a Treasury bill based on discount rate and maturity. Essential for short-term government securities analysis. |
| TBILLYIELD | =TBILLYIELD(settlement, maturity, pr) | =TBILLYIELD("1-Jan-2026","1-Jan-2027",95) | The TBILLYIELD function calculates the yield of a Treasury bill based on its price and maturity. Useful for investors in government securities. |
| TBILLEQ | =TBILLEQ(settlement, maturity, discount) | =TBILLEQ("1-Jan-2026","1-Jan-2027",5%) | The TBILLEQ function calculates the bond-equivalent yield for a Treasury bill. It helps compare T-bills with other fixed-income securities. |
| COUPNUM | =COUPNUM(settlement, maturity, frequency, [basis]) | =COUPNUM("1-Jan-2026","1-Jan-2030",2) | The COUPNUM function calculates the number of coupons (interest payments) between settlement and maturity. Useful for bond cash flow planning. |
| COUPDAYSNC | =COUPDAYSNC(settlement, maturity, frequency, [basis]) | =COUPDAYSNC("1-Jan-2026","1-Jan-2030",2) | The COUPDAYSNC function calculates the number of days from settlement to the next coupon. Important for accurate bond interest calculations. |
| COUPDAYS | =COUPDAYS(settlement, maturity, frequency, [basis]) | =COUPDAYS("1-Jan-2026","1-Jan-2030",2) | The COUPDAYS function calculates the number of days in the coupon period that contains the settlement date. Useful in bond interest accrual calculations. |
| COUPDAYBS | =COUPDAYBS(settlement, maturity, frequency, [basis]) | =COUPDAYBS("1-Jan-2026","1-Jan-2030",2) | The COUPDAYBS function calculates the number of days from the beginning of the coupon period to the settlement date. Important for accurate accrued interest calculation. |
| COUPNCD | =COUPNCD(settlement, maturity, frequency, [basis]) | =COUPNCD("1-Jan-2026","1-Jan-2030",2) | The COUPNCD function returns the next coupon date after the settlement date. Useful for scheduling bond payments and cash flow analysis. |
| COUPPCD | =COUPPCD(settlement, maturity, frequency, [basis]) | =COUPPCD("1-Jan-2026","1-Jan-2030",2) | The COUPPCD function returns the previous coupon date before the settlement date. Useful for bond interest and cash flow tracking. |
| DURATION | =DURATION(settlement, maturity, coupon, yld, frequency, [basis]) | =DURATION("1-Jan-2026","1-Jan-2030",5%,6%,2) | The DURATION function calculates the Macauley duration of a security with periodic interest payments. It helps measure interest rate risk and bond sensitivity. |
| MDURATION | =MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) | =MDURATION("1-Jan-2026","1-Jan-2030",5%,6%,2) | The MDURATION function calculates the modified duration of a security for interest rate sensitivity analysis. Useful for bond portfolio management. |
| AMORLINC | =AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis]) | =AMORLINC(10000, "1-Jan-2026", "31-Dec-2026", 1000, 1, 10%) | The AMORLINC function calculates the depreciation for each accounting period using the French accounting method. Ideal for corporate accounting and tax planning. |
| ISPMT | =ISPMT(rate, per, nper, pv) | =ISPMT(5%, 2, 5, 1000) | The ISPMT function calculates the interest payment for a specific period of an investment. Useful for analyzing periodic interest in loans or financial planning. |
| DISC | =DISC(settlement, maturity, pr, redemption, [basis]) | =DISC("1-Jan-2026","1-Jan-2027",95,100) | The DISC function calculates the discount rate of a security. It is useful for evaluating short-term bonds and financial instruments. |
| RRI | =RRI(nper, pv, fv) | =RRI(5, 1000, 1500) | The RRI function calculates the equivalent interest rate for an investment based on the number of periods, present value, and future value. Useful for financial forecasting. |
| VDB | =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]) | =VDB(10000, 1000, 5, 1, 2, 2) | The VDB function calculates variable declining balance depreciation between two periods. Useful for accelerated depreciation in accounting. |
| FVSCHEDULE | =FVSCHEDULE(principal, schedule) | =FVSCHEDULE(1000, {10%, 5%, 12%}) | The FVSCHEDULE function calculates the future value of an initial principal after applying a series of compound interest rates. Useful for investment growth calculations. |
| DOLLARDE | =DOLLARDE(fractional_dollar, fraction) | =DOLLARDE(1.02, 16) | The DOLLARDE function converts a fractional dollar number to a decimal number. Useful for bond pricing and financial reporting. |
| DOLLARFR | =DOLLARFR(decimal_dollar, fraction) | =DOLLARFR(1.125, 16) | The DOLLARFR function converts a decimal dollar number to a fractional dollar. Useful for financial calculations in bond pricing. |
| INTRATE | =INTRATE(settlement, maturity, investment, redemption, [basis]) | =INTRATE("1-Jan-2026","1-Jan-2027",1000,1050) | The INTRATE function calculates the interest rate for a fully invested security. Useful for investment and bond analysis. |
| RECEIVED | =RECEIVED(settlement, maturity, investment, discount, [basis]) | =RECEIVED("1-Jan-2026","1-Jan-2027",1000,5%) | The RECEIVED function calculates the amount received at maturity for a fully invested security. Useful for bond and short-term investment planning. |
| SYD | =SYD(cost, salvage, life, period) | =SYD(10000, 1000, 5, 1) | The SYD function calculates the sum-of-years' digits depreciation of an asset for a specified period. Useful for accelerated depreciation in accounting and tax planning. |
Array Functions
- Excel array formulas for advanced calculations, multiple criteria evaluation, and dynamic ranges.| Formula | Syntax | Example | Description |
|---|---|---|---|
| SUMPRODUCT | =SUMPRODUCT(array1, [array2], ...) | =SUMPRODUCT(A1:A5, B1:B5) | The SUMPRODUCT function multiplies corresponding elements in the given arrays and returns the sum of those products. Useful for weighted calculations, conditional sums, and dynamic data analysis. |
| TRANSPOSE | =TRANSPOSE(array) | =TRANSPOSE(A1:B5) | The TRANSPOSE function converts rows to columns and columns to rows in a given array. Useful for reorienting data and creating dynamic reports. |
| MMULT | =MMULT(array1, array2) | =MMULT(A1:B2, C1:D2) | The MMULT function returns the matrix product of two arrays. Essential for linear algebra, financial modeling, and advanced calculations. |
| FREQUENCY | =FREQUENCY(data_array, bins_array) | =FREQUENCY(A1:A10, B1:B5) | The FREQUENCY function calculates how often values occur within a range of bins. Useful for statistical analysis and data distribution. |
| LINEST | =LINEST(known_y’s, [known_x’s], [const], [stats]) | =LINEST(A1:A10, B1:B10, TRUE, TRUE) | The LINEST function calculates the statistics for a linear regression line. Useful for trend analysis, forecasting, and predictive modeling. |
| TREND | =TREND(known_y’s, [known_x’s], [new_x’s], [const]) | =TREND(A1:A10, B1:B10, C1:C5) | The TREND function calculates values along a linear trend. Ideal for projecting data trends and predictive analytics. |
| LOGEST | =LOGEST(known_y’s, [known_x’s], [const], [stats]) | =LOGEST(A1:A10, B1:B10, TRUE, TRUE) | The LOGEST function calculates an exponential curve that fits known data points. Useful for growth trend analysis and forecasting. |
| GROWTH | =GROWTH(known_y’s, [known_x’s], [new_x’s], [const]) | =GROWTH(A1:A10, B1:B10, C1:C5) | The GROWTH function calculates predicted exponential growth values. Ideal for forecasting population, sales, or investment growth. |
| REDUCE | =REDUCE(initial_value, array, lambda) | =REDUCE(0, A1:A5, LAMBDA(a,v,a+v)) | The REDUCE function applies a LAMBDA to reduce an array to a single value. Useful for advanced custom calculations in dynamic arrays. |
| SCAN | =SCAN(initial_value, array, lambda) | =SCAN(0, A1:A5, LAMBDA(a,v,a+v)) | The SCAN function applies a LAMBDA cumulatively across an array. Useful for running totals or stepwise calculations. |
| MAP | =MAP(array, lambda) | =MAP(A1:A5, LAMBDA(x,x*2)) | The MAP function applies a LAMBDA to each element of an array. Useful for transforming data dynamically without helper columns. |
| BYROW | =BYROW(array, lambda) | =BYROW(A1:C5, LAMBDA(row,SUM(row))) | The BYROW function applies a LAMBDA to each row of an array. Useful for row-wise calculations in dynamic datasets. |
| BYCOL | =BYCOL(array, lambda) | =BYCOL(A1:C5, LAMBDA(col,SUM(col))) | The BYCOL function applies a LAMBDA to each column of an array. Useful for column-wise calculations in dynamic tables. |
| TOCOL | =TOCOL(array, [ignore], [scan_by_column]) | =TOCOL(A1:C5) | The TOCOL function converts a range or array into a single column. Useful for flattening data for further analysis. |
| TOROW | =TOROW(array, [ignore]) | =TOROW(A1:C5) | The TOROW function converts a range or array into a single row. Useful for flattening and rearranging data dynamically. |
| VSTACK | =VSTACK(array1, [array2], ...) | =VSTACK(A1:A5, B1:B5) | The VSTACK function vertically stacks multiple arrays into a single column. Useful for consolidating datasets and creating dynamic ranges. |
| HSTACK | =HSTACK(array1, [array2], ...) | =HSTACK(A1:A5, B1:B5) | The HSTACK function horizontally stacks multiple arrays into a single row. Useful for combining datasets side by side dynamically. |
| WRAPCOLS | =WRAPCOLS(array, wrap_count) | =WRAPCOLS(A1:A10, 3) | The WRAPCOLS function wraps a 1D array into multiple columns with a specified number of items per column. Useful for organizing and formatting data neatly. |
| WRAPROWS | =WRAPROWS(array, wrap_count) | =WRAPROWS(A1:A10, 3) | The WRAPROWS function wraps a 1D array into multiple rows with a specified number of items per row. Ideal for formatting lists dynamically. |
| CHOOSECOLS | =CHOOSECOLS(array, col_num1, [col_num2], ...) | =CHOOSECOLS(A1:D5, 1,3) | The CHOOSECOLS function selects specific columns from an array. Useful for extracting relevant data without modifying the original range. |
| CHOOSEROWS | =CHOOSEROWS(array, row_num1, [row_num2], ...) | =CHOOSEROWS(A1:D5, 1,3) | The CHOOSEROWS function selects specific rows from an array. Ideal for filtering or reorganizing data efficiently. |
| FLATTEN | =FLATTEN(array) | =FLATTEN(A1:C3) | The FLATTEN function converts a multi-dimensional array into a single column. Useful for simplifying data for analysis or further processing. |
| ARRAY_CONSTRAIN | =ARRAY_CONSTRAIN(array, num_rows, num_cols) | =ARRAY_CONSTRAIN(A1:D10, 5, 2) | The ARRAY_CONSTRAIN function limits the size of an array to a specific number of rows and columns. Useful for controlling output in dynamic array formulas. |
| MAKEARRAY | =MAKEARRAY(rows, cols, lambda) | =MAKEARRAY(3,3,LAMBDA(r,c,r*c)) | The MAKEARRAY function creates an array of a specified size using a LAMBDA formula. Useful for generating dynamic, calculated arrays. |
| MDETERM | =MDETERM(array) | =MDETERM(A1:C3) | The MDETERM function calculates the determinant of a square matrix. Useful in linear algebra and engineering calculations. |
| MINVERSE | =MINVERSE(array) | =MINVERSE(A1:C3) | The MINVERSE function returns the inverse of a square matrix. Useful for solving matrix equations and advanced calculations. |
| SUMX2MY2 | =SUMX2MY2(array_x, array_y) | =SUMX2MY2(A1:A5,B1:B5) | The SUMX2MY2 function calculates the sum of the differences of squares of corresponding elements in two arrays. Useful in engineering and statistical calculations. |
Database Functions
- Excel database functions for advanced data analysis, conditional calculations, and extracting information from structured lists or tables.| Formula | Syntax | Example | Description |
|---|---|---|---|
| DAVERAGE | =DAVERAGE(database, field, criteria) | =DAVERAGE(A1:D20, "Sales", F1:F2) | The DAVERAGE function calculates the average of values in a column of a database that meet specified criteria. Useful for conditional analysis and reporting within structured tables. |
| DCOUNT | =DCOUNT(database, field, criteria) | =DCOUNT(A1:D20, "Sales", F1:F2) | The DCOUNT function counts the number of numeric entries in a database column that meet specified criteria. Useful for conditional counting in structured datasets. |
| DCOUNTA | =DCOUNTA(database, field, criteria) | =DCOUNTA(A1:D20, "Region", F1:F2) | The DCOUNTA function counts all entries (numeric and text) in a database column that meet specified criteria. Useful for analyzing data completeness. |
| DGET | =DGET(database, field, criteria) | =DGET(A1:D20, "Sales", F1:F2) | The DGET function extracts a single value from a database column that meets specific criteria. Ideal for retrieving targeted data for reports or calculations. |
| DMAX | =DMAX(database, field, criteria) | =DMAX(A1:D20, "Sales", F1:F2) | The DMAX function returns the maximum value in a database column that meets specified criteria. Useful for analyzing top values conditionally. |
| DMIN | =DMIN(database, field, criteria) | =DMIN(A1:D20, "Sales", F1:F2) | The DMIN function returns the minimum value in a database column that meets specified criteria. Useful for identifying lowest entries conditionally. |
| DPRODUCT | =DPRODUCT(database, field, criteria) | =DPRODUCT(A1:D20, "Quantity", F1:F2) | The DPRODUCT function multiplies values in a database column that meet specific criteria. Useful for conditional product calculations. |
| DSTDEV | =DSTDEV(database, field, criteria) | =DSTDEV(A1:D20, "Sales", F1:F2) | The DSTDEV function estimates the standard deviation of a numeric column in a database that meets specific criteria. Useful for statistical analysis on filtered datasets. |
| DSTDEVP | =DSTDEVP(database, field, criteria) | =DSTDEVP(A1:D20, "Sales", F1:F2) | The DSTDEVP function calculates the standard deviation of an entire population in a database column that meets criteria. Useful for population-level statistical analysis. |
| DSUM | =DSUM(database, field, criteria) | =DSUM(A1:D20, "Sales", F1:F2) | The DSUM function adds values in a database column that match the specified criteria. Useful for conditional summation in structured tables. |
| DVAR | =DVAR(database, field, criteria) | =DVAR(A1:D20, "Sales", F1:F2) | The DVAR function estimates the variance of a sample in a database column that meets criteria. Useful for sample-level statistical calculations. |
| DVARP | =DVARP(database, field, criteria) | =DVARP(A1:D20, "Sales", F1:F2) | The DVARP function calculates the variance of an entire population in a database column that meets criteria. Useful for population-level statistical analysis. |
Google Functions
- Google Sheets-specific functions for dynamic arrays, formulas, and spreadsheet automation.| Formula | Syntax | Example | Description |
|---|---|---|---|
| ARRAYFORMULA | =ARRAYFORMULA(array_formula) | =ARRAYFORMULA(A1:A5 * B1:B5) | The ARRAYFORMULA function allows you to apply a formula to an entire range of cells in Google Sheets. It dynamically expands the calculation across multiple rows or columns, eliminating the need to copy formulas manually. This is especially useful for creating automated and dynamic spreadsheets. |
| DETECTLANGUAGE | =DETECTLANGUAGE(text) | =DETECTLANGUAGE("Bonjour") | The DETECTLANGUAGE function identifies the language of the input text in Google Sheets. Useful for multilingual data analysis and automation when working with diverse datasets. |
| GOOGLEFINANCE | =GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval]) | =GOOGLEFINANCE("GOOGL", "price") | The GOOGLEFINANCE function retrieves real-time or historical financial data directly into Google Sheets. Ideal for tracking stock prices, market data, and building dynamic financial models. |
| GOOGLETRANSLATE | =GOOGLETRANSLATE(text, [source_language], [target_language]) | =GOOGLETRANSLATE("Hello","en","fr") | The GOOGLETRANSLATE function translates text from one language to another in Google Sheets. Useful for international data processing and multilingual spreadsheets. |
| IMAGE | =IMAGE(url, [mode], [height], [width]) | =IMAGE("https://example.com/logo.png") | The IMAGE function inserts an image from a URL into a Google Sheet cell. Useful for dashboards, reports, and visualizing data alongside spreadsheet content. |
| QUERY | =QUERY(data, query, [headers]) | =QUERY(A1:D20,"SELECT A, B WHERE C>50") | The QUERY function runs SQL-like queries on a dataset in Google Sheets. It is powerful for filtering, summarizing, and manipulating data dynamically. |
| SPARKLINE | =SPARKLINE(data, [options]) | =SPARKLINE(B2:B10) | The SPARKLINE function creates mini charts inside a single cell. Ideal for visualizing trends, performance metrics, or changes in data compactly. |
Information Functions
- Excel and Google Sheets functions to identify cell contents, errors, and data types for advanced spreadsheet analysis.| Formula | Syntax | Example | Description |
|---|---|---|---|
| ERROR.TYPE | =ERROR.TYPE(error_val) | =ERROR.TYPE(A1) | The ERROR.TYPE function returns a number corresponding to a specific Excel error in a cell (like #DIV/0!, #N/A, #VALUE!). It helps detect and handle errors programmatically in spreadsheets, making debugging easier. |
| ISBLANK | =ISBLANK(value) | =ISBLANK(A1) | The ISBLANK function checks whether a cell is empty. It returns TRUE if the cell contains no data, and FALSE otherwise. Useful for validating data entry and conditional formulas. |
| ISDATE | =ISDATE(value) | =ISDATE(A1) | The ISDATE function checks if a value is recognized as a valid date. It returns TRUE for dates and FALSE for non-date values. Useful for data validation and date calculations. |
| ISEMAIL | =ISEMAIL(value) | =ISEMAIL(A1) | The ISEMAIL function checks if a value is a valid email address. Returns TRUE if valid, FALSE otherwise. Useful for validating email lists in spreadsheets. |
| ISERR | =ISERR(value) | =ISERR(A1) | The ISERR function checks if a value results in an error **except #N/A**. Returns TRUE for errors and FALSE otherwise. Useful for error handling in formulas. |
| ISERROR | =ISERROR(value) | =ISERROR(A1) | The ISERROR function checks if a value results in any error, including #N/A. Returns TRUE if an error exists, FALSE otherwise. Useful for conditional error handling. |
| ISFORMULA | =ISFORMULA(cell_reference) | =ISFORMULA(A1) | The ISFORMULA function checks if a cell contains a formula. Returns TRUE if the cell has a formula, FALSE otherwise. Useful for auditing and spreadsheet validation. |
| ISLOGICAL | =ISLOGICAL(value) | =ISLOGICAL(A1) | The ISLOGICAL function checks if a value is a logical TRUE or FALSE. Returns TRUE for logical values, FALSE for everything else. Useful for validating boolean inputs in formulas. |
| ISNA | =ISNA(value) | =ISNA(A1) | The ISNA function checks if a value results in the #N/A error. Returns TRUE for #N/A and FALSE for all other values. Useful in error-specific handling in spreadsheets. |
| ISNONTEXT | =ISNONTEXT(value) | =ISNONTEXT(A1) | The ISNONTEXT function checks if a value is not text. Returns TRUE for numbers, dates, errors, and logical values, and FALSE for text. Useful for data validation in mixed-type datasets. |
| ISNUMBER | =ISNUMBER(value) | =ISNUMBER(A1) | The ISNUMBER function checks if a value is a numeric value. Returns TRUE if it is a number and FALSE otherwise. Useful for validating numeric inputs or preventing formula errors. |
| ISREF | =ISREF(value) | =ISREF(A1) | The ISREF function checks if a value refers to a valid cell or range. Returns TRUE for valid references and FALSE otherwise. Useful for validating cell references dynamically. |
| ISTEXT | =ISTEXT(value) | =ISTEXT(A1) | The ISTEXT function checks if a value is text. Returns TRUE for text values and FALSE for numbers, dates, or errors. Useful for validating text inputs or conditional formatting. |
| N | =N(value) | =N(TRUE) | The N function converts non-numeric values to a numeric format: TRUE becomes 1, FALSE becomes 0, dates are converted to serial numbers, and text returns 0. Useful for calculations requiring numeric input. |
| NA | =NA() | =NA() | The NA function returns the #N/A error. Useful for indicating missing data or intentionally leaving cells empty for formulas that handle errors. |
| TYPE | =TYPE(value) | =TYPE(A1) | The TYPE function returns a number representing the type of a value: 1 for numbers, 2 for text, 4 for logical, 16 for error, and 64 for arrays. Useful for validating data types in formulas. |
| CELL | =CELL(info_type, [reference]) | =CELL("address", A1) | The CELL function returns information about the formatting, location, or contents of a cell. Useful for auditing, conditional logic, and dynamic formula building. |
Parser & Conversion Functions
- Functions in Excel and Google Sheets for parsing, extracting, and converting data between units and formats.| Formula | Syntax | Example | Description |
|---|---|---|---|
| CONVERT | =CONVERT(number, from_unit, to_unit) | =CONVERT(100, "kg", "lb") | The CONVERT function converts a number from one measurement unit to another. For example, it can convert kilograms to pounds, meters to feet, or Celsius to Fahrenheit. This function is essential for engineering, finance, and scientific spreadsheets requiring consistent units. |
| TO_DATE | =TO_DATE(value) | =TO_DATE(44927) | The TO_DATE function converts a numeric value or date-time serial number into a proper date format in Google Sheets. Useful for standardizing numeric timestamps into readable dates. |
| TO_DOLLARS | =TO_DOLLARS(value) | =TO_DOLLARS(1234.567) | The TO_DOLLARS function converts a nu |