Engineering
★ 44 · ◉ 27 · All versions
=HEX2OCT()
Converts a hexadecimal number to octal.
=HEX2OCT(number, [places])
Scientific & Unit Conversion
Engineering
★ 44 · ◉ 20 · All versions
=IMSQRT()
Returns the square root of a complex number in x + yi or x + yj text format.
=IMSQRT(inumber)
Scientific & Unit Conversion
Text
★ 43 · ◉ 30 · All versions
=MIDB()
Returns a portion of text starting at a specified position based on bytes
=MIDB(text, start_num, num_bytes)
Text Manipulation
Engineering
★ 43 · ◉ 23 · All versions
=BESSELK()
Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments.
=BESSELK(X, N)
Scientific & Unit Conversion
Engineering
★ 43 · ◉ 23 · All versions
=BIN2OCT()
Converts a binary number to octal.
=BIN2OCT(number, [places])
Scientific & Unit Conversion
Text
★ 42 · ◉ 20 · All versions
=RIGHTB()
Returns a specified number of characters from the end of text based on bytes
=RIGHTB(text, [num_bytes])
Text Manipulation
Math & Trig
★ 41 · ◉ 46 · All versions
=SERIESSUM()
Many functions can be approximated by a power series expansion. Returns the sum of a power series based on the formula:
=SERIESSUM(x, n, m, coefficients)
Rounding & Numeric
Text
★ 41 · ◉ 38 · All versions
=REPLACEB()
Replaces part of text by bytes (DBCS)
=REPLACEB(old_text, start_num, num_bytes, new_text)
Text Manipulation
Engineering
★ 41 · ◉ 28 · All versions
=ERF()
Returns the error function integrated between lower_limit and upper_limit.
=ERF(lower_limit,[upper_limit])
Scientific & Unit Conversion
Math & Trig
★ 40 · ◉ 48 · Excel 2013
=CEILING.MATH()
The CEILING.MATH function rounds a number up to the nearest integer or, optionally, to the nearest multiple of significance.
=CEILING.MATH(number, [significance], [mode])
Rounding & Numeric
Math & Trig
★ 40 · ◉ 47 · All versions
=SQRT()
Returns a positive square root.
=SQRT(number)
Rounding & Numeric
Scientific & Unit Conversion
Lookup & Reference
★ 40 · ◉ 43 · All versions
=ROWS()
Returns the number of rows in a reference or array.
=ROWS(array)
Numbering & Sample Data
Worksheet Info & Navigation
Statistical
★ 40 · ◉ 38 · Excel 2010
=PERCENTILE.INC()
PERCENTILE.INC returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. You can use this function to establish a threshold of acceptance.
=PERCENTILE.INC(array,k)
Ranking & Top N
Descriptive Statistics
Date & Time
★ 40 · ◉ 38 · All versions
=TODAY()
The TODAY function returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals. For example, if you know that someone was born in 1963, you might use the following formula to find that person's age as of this year's birthday: = YEAR( TODAY())-1963 This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person's age.
=TODAY()
Daily Tasks
Conditional Formatting & Validation
Lookup & Reference
★ 40 · ◉ 34 · Excel 365
=DROP()
DROP function
=DROP(array, rows,[columns])
Sort, Filter & Dedupe
Reshape Tables
Engineering
★ 40 · ◉ 33 · All versions
=COMPLEX()
Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.
=COMPLEX(real_num, i_num, [suffix])
Scientific & Unit Conversion
Math & Trig
★ 40 · ◉ 33 · All versions
=MROUND()
MROUND returns a number rounded to the desired multiple. MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple. The Number and Multiple arguments must have the same sign. If not, a #NUM error is returned.
=MROUND(number, multiple)
Rounding & Numeric
Statistical
★ 40 · ◉ 30 · Excel 2010
=STDEV.P()
Calculates standard deviation based on the entire population given as arguments (ignores logical values and text).
=STDEV.P(number1,[number2],...)
Descriptive Statistics
Text
★ 40 · ◉ 29 · Excel 365
=REGEXEXTRACT()
This article shows you how REGEXEXTRACT function allows you to extract text from a string based on a supplied regular expression.
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
Data Cleaning
Text Manipulation
Statistical
★ 40 · ◉ 26 · Excel 2007
=AVERAGEIF()
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
=AVERAGEIF(range, criteria, [average_range])
Conditional Aggregation
Financial
★ 40 · ◉ 24 · All versions
=SLN()
Returns the straight-line depreciation of an asset for one period.
=SLN(cost, salvage, life)
Financial Calculations
Depreciation
Date & Time
★ 40 · ◉ 22 · All versions
=TIME()
Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
=TIME(hour, minute, second)
Date & Time Calculations
Financial
★ 40 · ◉ 22 · All versions
=XNPV()
Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
=XNPV(rate, values, dates)
Financial Calculations
Investment Appraisal
Lookup & Reference
★ 40 · ◉ 21 · All versions
=HLOOKUP()
Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The H in HLOOKUP stands for "Horizontal."
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Lookup & Retrieval
Lookup & Reference
★ 40 · ◉ 19 · Excel 365
=CHOOSEROWS()
CHOOSEROWS function
=CHOOSEROWS(array,row_num1,[row_num2],…)
Lookup & Retrieval
Sort, Filter & Dedupe
Lookup & Reference
★ 40 · ◉ 16 · All versions
=LOOKUP()
How to use the LOOKUP function in Excel to search in a single row or column and find a value from the same position in a second row or column.
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Lookup & Retrieval
Text
★ 39 · ◉ 35 · All versions
=FIXED()
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
=FIXED(number, [decimals], [no_commas])
Text Manipulation
Engineering
★ 39 · ◉ 34 · All versions
=IMAGINARY()
Returns the imaginary coefficient of a complex number in x + yi or x + yj text format.
=IMAGINARY(inumber)
Scientific & Unit Conversion
Math & Trig
★ 38 · ◉ 60 · All versions
=ATAN2()
Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi.
=ATAN2(x_num, y_num)
Rounding & Numeric
Engineering
★ 38 · ◉ 18 · All versions
=IMDIV()
Returns the quotient of two complex numbers in x + yi or x + yj text format.
=IMDIV(inumber1, inumber2)
Scientific & Unit Conversion
Engineering
★ 38 · ◉ 15 · All versions
=IMTAN()
Returns the tangent of a complex number in x+yi or x+yj text format.
=IMTAN(inumber)
Scientific & Unit Conversion
Engineering
★ 37 · ◉ 36 · All versions
=ERFC()
Returns the complementary ERF function integrated between x and infinity.
=ERFC(x)
Scientific & Unit Conversion
Engineering
★ 37 · ◉ 32 · All versions
=IMPRODUCT()
Returns the product of 1 to 255 complex numbers in x + yi or x + yj text format.
=IMPRODUCT(inumber1, [inumber2], ...)
Scientific & Unit Conversion
Engineering
★ 37 · ◉ 30 · All versions
=BESSELJ()
Returns the Bessel function.
=BESSELJ(X, N)
Scientific & Unit Conversion
Lookup & Reference
★ 37 · ◉ 23 · All versions
=RTD()
Retrieves real-time data from a program that supports COM automation.
=RTD(ProgID, server, topic1, [topic2], ...)
Daily Tasks
Math & Trig
★ 36 · ◉ 34 · All versions
=SUMX2PY2()
Returns the sum of the sum of squares of corresponding values in two arrays. The sum of the sum of squares is a common term in many statistical calculations.
=SUMX2PY2(array_x, array_y)
Rounding & Numeric
Text
★ 36 · ◉ 28 · All versions
=FINDB()
Returns the position of a text in another text in bytes (DBCS, for double-byte languages) — like FIND but byte-based
=FINDB(find_text, within_text, [start_num])
Text Manipulation
Math & Trig
★ 36 · ◉ 24 · All versions
=SQRTPI()
Returns the square root of (number * pi).
=SQRTPI(number)
Rounding & Numeric
Engineering
★ 36 · ◉ 23 · All versions
=IMABS()
Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format.
=IMABS(inumber)
Scientific & Unit Conversion
Text
★ 35 · ◉ 38 · All versions
=ASC()
For Double-byte character set (DBCS) languages, the function changes full-width (double-byte) characters to half-width (single-byte) characters.
=ASC(text)
Text Manipulation
Text
★ 35 · ◉ 37 · Excel 365
=REGEXREPLACE()
REGEXREPLACE Function
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
Data Cleaning
Text Manipulation
Date & Time
★ 35 · ◉ 37 · All versions
=YEARFRAC()
YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.
=YEARFRAC(start_date, end_date, [basis])
Date & Time Calculations
Workdays & Due Dates
Compatibility
★ 35 · ◉ 36 · All versions
=FORECAST()
Predicts a future value based on a linear trend (the legacy FORECAST.LINEAR)
=FORECAST(x, known_y's, known_x's)
Forecasting & Trends
Math & Trig
★ 35 · ◉ 36 · All versions
=QUOTIENT()
Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
=QUOTIENT(numerator, denominator)
Rounding & Numeric
Lookup & Reference
★ 35 · ◉ 35 · All versions
=COLUMNS()
Returns the number of columns in an array or reference.
=COLUMNS(array)
Numbering & Sample Data
Worksheet Info & Navigation
Engineering
★ 35 · ◉ 35 · All versions
=CONVERT()
units for the result. CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.
=CONVERT(number,from_unit,to_unit)
Scientific & Unit Conversion
Math & Trig
★ 35 · ◉ 35 · Excel 2013
=FLOOR.MATH()
Round a number down to the nearest integer or to the nearest multiple of significance.
=FLOOR.MATH(number, significance, mode)
Rounding & Numeric
Text
★ 35 · ◉ 31 · All versions
=CHAR()
Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.
=CHAR(number)
Data Cleaning
Text Manipulation
Date & Time
★ 35 · ◉ 31 · All versions
=DATEVALUE()
The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations. To view a date serial number as a date, you must apply a date format to the cell. Find links to more information about displaying numbers as dates in the See Also section.
=DATEVALUE(date_text)
Data Cleaning
Date & Time Calculations
Lookup & Reference
★ 35 · ◉ 31 · Excel 365
=TRIMRANGE()
The TRIMRANGE function excludes all empty rows and/or columns from the outer edges of a range or array.
=TRIMRANGE(range,[trim_rows],[trim_cols])
Data Cleaning
Dynamic Ranges & Dashboards
Cube
★ 35 · ◉ 30 · Excel 2007
=CUBEVALUE()
Returns an aggregated value from the cube.
=CUBEVALUE(connection, [member_expression1], [member_expression2], …)
Reporting
Dynamic Ranges & Dashboards
Financial
★ 35 · ◉ 30 · All versions
=EFFECT()
Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
=EFFECT(nominal_rate, npery)
Financial Calculations
Loans & Installments
Statistical
★ 35 · ◉ 30 · All versions
=FREQUENCY()
The FREQUENCY function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores.
=FREQUENCY(data_array, bins_array)
Conditional Aggregation
Reporting
Compatibility
★ 35 · ◉ 30 · All versions
=MODE()
The MODE function returns the most frequently occurring, or repetitive, value in an array or range of data.
=MODE(number1,[number2],...)
Descriptive Statistics
Statistical
★ 35 · ◉ 29 · Excel 2010
=QUARTILE.INC()
QUARTLE.INC returns the quartile of a data set, based on percentile values from 0..1, inclusive. Quartiles often are used in sales and survey data to divide populations into groups.
=QUARTILE.INC(array,quart)
Ranking & Top N
Descriptive Statistics
Logical
★ 35 · ◉ 27 · Excel 365
=BYROW()
Applies a LAMBDA function to each row and returns an array of the results
=BYROW(array, lambda(row))
Advanced Formulas
Statistical
★ 35 · ◉ 24 · Excel 2016
=FORECAST.ETS()
Calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends. This function requires the timeline to be organized with a constant step between the different points.
=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
Forecasting & Trends
Engineering
★ 35 · ◉ 24 · All versions
=IMSINH()
The IMSINH function returns the hyperbolic sine of a complex number in x+yi or x+yj text format.
=IMSINH(inumber)
Scientific & Unit Conversion
Date & Time
★ 35 · ◉ 23 · All versions
=HOUR()
Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).
=HOUR(serial_number)
Date & Time Calculations
Engineering
★ 35 · ◉ 22 · Excel 2013
=BITXOR()
Returns a bitwise 'XOR' of two numbers.
=BITXOR(number1, number2)
Scientific & Unit Conversion