Cube
|
Description
|
Arguments
|
CUBEKPIMEMBER |
Returns the key performance indicator (KPI) property |
connection, kpi_name, kpi_property, [caption] |
CUBEMEMBER |
Returns a member or tuple from a cube |
connection, member_expression, [caption] |
CUBESET |
Returns a calculated set of members or tuples |
connection, set_expression, [caption], [sort_order], [sort_by] |
CUBESETCOUNT |
Returns the number of items in a set |
set |
CUBEVALUE |
Returns an aggregated value from a cube filtered by multiple member_expression arguments |
connection, [member_expression1], [member_expression2], … |
Database
|
Description
|
Arguments
|
DAVERAGE |
Returns average that matches the specific criteria |
database, field, criteria |
DCOUNT |
Counts the cells that contain numbers in a field of records in a databased which match the specified criteria |
database, field, criteria |
DCOUNTA |
Counts the cells that in a field of records in a databased which match the specified criteria |
database, field, criteria |
DGET |
Returns a single value in the given field of the database that matches the criteria |
database, field, criteria |
DMAX |
Returns max that matches the specific criteria |
database, field, criteria |
DMIN |
Returns min that matches the specific criteria |
database, field, criteria |
DPRODUCT |
Returns product that matches the specific criteria |
database, field, criteria |
DSTDEV |
Returns the estimated value of a population’s standard deviation based on a sample by using the values from the sample database that match the given criteria you specify |
database, field, criteria |
DSTDEVP |
Returns the standard deviation of a population by using the numbers from the entire database that match the given criteria you specify |
database, field, criteria |
DSUM |
Returns the sum of numbers from a database that match the given criteria |
database, field, criteria |
DVAR |
Estimates the variance of a sample retrieved from a field that matches the given conditions |
database, field, criteria |
DVARP |
Returns the variance for an entire population retrieved from a field that matches the given conditions |
database, field, criteria |
Date and Time
|
Description
|
Arguments
|
DATE |
Creates date with year, month and day |
year, month, day |
DATEDIF |
Returns the years, months or days between two dates |
start_date, end_date, unit |
DATEVALUE |
Converts a date stored as text format to a valid date |
date_text |
DAY |
Gets the day as a number (1 to 31) from a date |
date |
DAYS |
Gets the number of days between two dates |
start_date, end_date |
DAYS360 |
Gets the number of days between 2 dates in a 360-day year |
start_date, end_date, [method] |
EDATE |
Adds n months for a date |
start_date, months |
EOMONTH |
Gets the last day of month n months in future or past |
start_date, months |
ISOWEEKNUM |
Gets ISO week number from a given date |
date |
HOUR |
Gets the hour as a number (0 to 23) from a date time |
serial_number |
MINUTE |
Returns the minute as number (0 to 59) from time |
serial_number |
MONTH |
Gets the month as integer number (1 to 12) from date |
serial_number |
NETWORKDAYS |
Gets the number of working days between two dates |
start_date, end_date, [holidays] |
NETWORKDAYS.INTL |
Gets workdays between two dates |
start_date, end_date, [weekend], [holidays] |
NOW |
Gets the current date and time |
// |
SECOND |
Returns the seconds as number (0 to 59) from time |
serial_number |
TIME |
Creates time with hours, minutes and seconds |
hour, minute, second |
TIMEVALUE |
Gets time from a text string |
time_text |
TODAY |
Gets the current date |
// |
WEEKDAY |
Gets day of the week as a number (1 to 7) from date |
serial_number, [return_type] |
WEEKNUM |
Returns the week number of the given date in a year |
serial_number, [return_type] |
WORKDAY |
Adds workdays to the given start date and returns a workday |
start_date, days, [holidays] |
WORKDAY.INTL |
Adds workdays to the given start date and returns a workday excluding custom weekend and holidays |
start_date, days, [weekend], [holidays] |
YEAR |
Returns the year based on the given date in a 4-digit serial number format |
serial_number |
YEARFRAC |
Calculates the fractional year in a decimal format between given dates |
start_date, end_date, [basis] |
Engineering
|
Description
|
Arguments
|
BESSELI |
Calculates the modified Bessel function |
x, n |
BESSELJ |
Returns the Bessel function, for a specified value x and order |
x, n |
BESSELK |
Returns the modified Bessel functio |
x, n |
BESSELY |
Returns the Bessel function |
x, n |
BIN2DEC |
Converts a binary number to a decimal number |
number |
BIN2HEX |
Converts a binary number to a hexadecimal number |
number, [places] |
BIN2OCT |
Converts a binary number to an octal number |
number, [places] |
BITAND |
Returns a decimal number representing the bitwise 'AND' of two supplied numbers |
number1, number2 |
BITLSHIFT |
Returns a decimal number shifted left by a specified number of bits |
number, shift_amount |
BITOR |
Returns the bitwise ‘OR’ of two given numbers |
number1, number2 |
BITRSHIFT |
Returns the given number shifted right by the specified number of bits |
number, shift_amount |
BITXOR |
Returns the bitwise ‘XOR’ of two given numbers |
number1, number2 |
COMPLEX |
Converts real and imaginary coefficients to a complex number |
real_num, i_num, [suffix] |
CONVERT |
Converts a given number from one measurement system to another |
number, from_unit, to_unit |
DEC2BIN |
Converts a decimal number to a binary number |
number, [places] |
DEC2HEX |
Converts a decimal number to a hexadecimal number |
number, [places] |
DEC2OCT |
Converts a decimal number to an octal number |
number, [places] |
DELTA |
Compares two numeric values, and checks if they are equal |
number1, number2 |
ERF |
Returns the error function integrated between lower_limit and upper_limit |
lower_limit, [upper_limit] |
ERF.PRECISE |
Returns the error function integrated between zero (0) and a limit |
x |
ERFC |
Returns the complementary Error function integrated between a lower limit and infinity |
x |
ERFC.PRECISE |
Returns the complementary Error function integrated between a limit and infinity |
x |
GESTEP |
Checks whether the given number is greater than or equal to the given step value and returns 1 if TRUE and 0 if FALSE |
number, [step] |
HEX2BIN |
Converts a hexadecimal number to a binary number |
number, [places] |
HEX2DEC |
Converts a hexadecimal number to a binary number |
number |
HEX2OCT |
Converts a hexadecimal number to a binary number |
number, [places] |
IMABS |
Returns the absolute value of a complex number |
inumber |
IMAGINARY |
Returns the imaginary coefficient of a given complex number |
inumber |
IMARGUMENT |
Returns the angle expressed in radians of a given complex number |
inumber |
IMCONJUGATE |
Returns the complex conjugate of a given complex number |
inumber |
IMCOS |
Returns the cosine of a given complex number |
inumber |
IMCOSH |
Returns the hyperbolic cosine of a given complex number |
inumber |
IMCOT |
Returns the cotangent of a given complex number |
inumber |
IMCSC |
Returns the cosecant of a given complex number |
inumber |
IMCSCH |
Returns the hyperbolic cosecant of a given complex number |
inumber |
IMDIV |
Calculates the quotient of two given complex numbers |
inumber1, inumber2 |
IMEXP |
Returns the exponential of a given complex number |
inumber |
IMLN |
Returns the natural logarithm of a given complex number |
inumber |
IMLOG2 |
Returns the base-2 logarithm of a given complex number |
inumber |
IMLOG10 |
Returns the common (base 10) logarithm of a given complex number |
inumber |
IMPOWER |
Returns a complex number raised to a given power |
inumber |
IMPRODUCT |
Calculates the product of one or more complex numbers |
inumber1, [inumber2], ... |
IMREAL |
Returns the real coefficient of a given complex number |
inumber |
IMSEC |
Returns the secant of a complex number |
inumber |
IMSECH |
Returns the hyperbolic secant of a complex number |
inumber |
IMSIN |
Returns the sine of a complex number |
inumber |
IMSINH |
Returns the hyperbolic sine of a complex number |
inumber |
IMSQRT |
Returns the square root of a complex number |
inumber |
IMTAN |
Returns the tangent of a given complex number |
inumber |
IMSUB |
Returns the difference between two complex numbers |
inumber1, inumber2 |
IMSUM |
Calculates the sum of two or more complex numbers |
inumber1, [inumber2], ... |
OCT2BIN |
Converts an octal number to a binary number |
number, [places] |
OCT2DEC |
Converts an octal number to a decimal number |
number |
OCT2HEX |
Converts an octal number to a hexadecimal number |
number, [places] |
Financial
|
Description
|
Arguments
|
ACCRINT |
Returns accrued interest periodic |
issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method] |
ACCRINTM |
Returns accrued interest at maturity |
issue, settlement, rate, par, [basis] |
AMORDEGRC |
Returns the linear depreciation of an asset for each accounting period by applying a depreciation coefficient based on the lifetime of the assets |
cost, date_purchased, first_period, salvage, period, rate, [basis] |
AMORLINC |
Returns the linear depreciation of an asset for each accounting period |
cost, date_purchased, first_period, salvage, period, rate, [basis] |
COUPDAYBS |
Returns the number of days between the beginning of the coupon period and its settlement date |
settlement, maturity, frequency, [basis] |
COUPDAYS |
Returns the number of days in the coupon period including the settlement date |
settlement, maturity, frequency, [basis] |
COUPDAYSNC |
Returns the calculated number of days from the settlement date to the next coupon date |
settlement, maturity, frequency, [basis] |
COUPNCD |
Returns the next coupon date after the settlement date |
settlement, maturity, frequency, [basis] |
COUPNUM |
Returns number of coupons payable between settlement date and maturity date |
settlement, maturity, frequency, [basis] |
COUPPCD |
Returns previous coupon date before settlement date |
settlement, maturity, frequency, [basis] |
CUMIPMT |
Returns the cumulative interest paid on a load between the start period and end period |
rate, nper, pv, start_period, end_period, type |
CUMPRINC |
Calculates the cumulative principal paid on a load between the start period and end period |
rate, nper, pv, start_period, end_period, type |
DB |
Returns the depreciation of an asset for a specified period by using a fixed-declining balance method |
cost, salvage, life, period, [month] |
DDB |
Returns the depreciation of an asset for a specified period by using a double-declining balance method or other specific method |
cost, salvage, life, period, [factor] |
DISC |
Returns the discount rate of a security |
settlement, maturity, pr, redemption, [basis] |
DOLLARDE |
Converts a dollar value expressed in fractional notation to decimal |
fractional_dollar, fraction |
DOLLARFR |
Converts a dollar value expressed in decimal to fractional notation |
decimal_dollar, fraction |
DURATION |
Calculates the duration of a security that pays interest on a periodic basis |
settlement, maturity, coupon, yld, frequency,[basis] |
EFFECT |
Calculates the effective annual interest rate |
nominal_rate, npery |
FV |
Figures out the future value of an investment |
rate, nper, pmt, pv, type |
FVSCHEDULE |
Figures out future value of a lump sum investment with adjustable interest rates |
principal, schedule |
INTRATE |
Figures out the interest rate for a fully invested security |
settlement, maturity, investment, redemption, [basis] |
IPMT |
Figures out the interest payment for the specified period for an investment or load |
rate, per, nper, pv, [fv], [type] |
IRR |
Figures out the internal rate of return for a series of cash flows that occur at regular intervals |
values, [guess] |
ISPMT |
Figures out interest payment of given period of an investment or loan |
rate, per, nper, pv |
MDURATION |
Figures out the modified Macauley duration for a security |
settlement, maturity, coupon, yld, frequency, [basis] |
MIRR |
Figures out modified internal rate of return for a series of cash flows |
values, finance_rate, reinvest_rate |
NOMINAL |
Calculates the nominal annual interest rate |
effect_rate, npery |
NPER |
Calculates the number of periods for investment or loan |
rate, pmt, pv, [fv], [type] |
NPV |
Calculates the net present value of investment |
rate, value1, [value2], ... |
ODDFPRICE |
Returns price per $100 face value with odd first period |
settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis] |
ODDFYIELD |
Returns yield of security with odd first period |
settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, [basis] |
ODDLPRICE |
Returns price per $100 face value with odd last period |
settlement, maturity, last_interest, rate, yld, redemption, frequency, [basis] |
ODDLYIELD |
Returns yield of security with odd last period |
settlement, maturity, last_interest, rate, pr, redemption, frequency, [basis] |
PDURATION |
Returns the required number of periods for an investment to reach a specific value |
rate, pv, fv |
PMT |
Returns the periodic payment required to pay off a loan |
rate, nper, pv, [fv], [type] |
PPMT |
Returns the principal portion of a given loan payment |
rate, per, nper, pv, [fv], [type] |
PRICE |
Returns the price of a bond per $100 face value that pays periodic interest |
settlement, maturity, rate, yld, redemption, frequency, [basis] |
PRICEDISC |
Returns the price per $100 face value of a discounted security |
settlement, maturity, discount, redemption, [basis] |
PRICEMAT |
Returns the price per $100 face value of a security that pays interests at maturity |
settlement, maturity, issue, rate, yld, [basis] |
PV |
Returns the present value of a loan or an investment based on a constant interest rate |
rate, nper, pmt, [fv], [type] |
RATE |
Returns the interest rate per period of an annuity |
nper, pmt, pv, [fv], [type], [guess] |
RECEIVED |
Returns the amount received at maturity for a fully invested security |
settlement, maturity, investment, discount, [basis] |
RRI |
Returns a numeric value. You can format the result to a percentage format |
nper, pv, fv |
SLN |
Returns straight-line depreciation of asset for one period |
cost, salvage, life |
SYD |
Returns sum-of-years’ depreciation of asset for given period |
cost, salvage, life, per |
TBILLEQ |
Returns bond-equivalent yield for Treasury bill |
settlement, maturity, discount |
TBILLPRICE |
Returns price per $100 for Treasury bill |
settlement, maturity, discount |
TBILLYIELD |
Returns yield for Treasury bill |
settlement, maturity, pr |
VDB |
Calculates depreciation using the double-declining balance method |
cost, salvage, life, start_period, end_period, [factor], [no_switch] |
XIRR |
Calculates the internal rate of return for irregular cash flows |
values, dates, [guess] |
XNPV |
Calculates the net present value for irregular cash flows |
rate, values, dates |
YIELD |
Calculates the yield on a security that pays periodic interest |
settlement, maturity, rate, pr, redemption, frequency, [basis] |
YIELDDISC |
Returns the annual yield for a discounted security |
settlement, maturity, pr, redemption, [basis] |
YIELDMAT |
Returns the annual yield of a security that pays interest at maturity |
settlement, maturity, issue, rate, pr, [basis] |
Information
|
Description
|
Arguments
|
CELL |
Returns the requested information about a specified cell |
info_type, [reference] |
ERROR.TYPE |
Returns a number that corresponds to a specific error value |
error_val |
INFO |
Returns details about the current operating environment |
type_text |
ISBLANK |
Returns TRUE if a cell is empty or blank and FALSE otherwise |
value |
ISERR |
Returns TRUE for any error type (except #N/A) and FALSE otherwise |
value |
ISERROR |
Tests if an initial supplied expression or value returns an Excel error |
value |
ISEVEN |
Tests if a supplied number (or numeric expression) is even |
number |
ISFORMULA |
Tests if a cell contains formula |
reference |
ISLOGICAL |
Tests if a supplied value or expression returns a logical value (TRUE and FALSE) |
value |
ISNA |
Tests if a value or expression returns Excel #N/A error |
value |
ISNONTEXT |
Returns TRUE if a given value is non-text |
value |
ISNUMBER |
Returns TRUE if a given value is a number |
value |
ISODD |
Returns TRUE if a given value is an odd number |
value |
ISOMITTED |
Checks if the value is omitted in the LAMBDA function |
argument |
ISREF |
Returns TRUE if a given value is a reference |
value |
ISTEXT |
Returns TRUE if a given value is a text |
value |
N |
Converts a value to a number |
value |
NA |
Returns the #N/A error value |
// |
SHEET |
Returns the index number of a reference sheet in Excel |
[value] |
SHEETS |
Returns the number of sheets in a given reference |
[reference] |
TYPE |
Returns the type of the given value |
value |
Logical
|
Description
|
Arguments
|
AND |
Tests multiple conditions to return True or False |
logical1, [logical2], ... |
BYCOL |
Applies a LAMBDA function to each column in a given array and returns the result per column as a single array |
array, lambda(column) |
BYROW |
Applies a LAMBDA function to each row in a given array and returns the result per row as a single array |
array, lambda(row) |
FALSE |
Generates the logical FALSE value |
// |
IF |
Tests for a specific condition |
logical_test, [value_if_true], [value_if_false] |
IFERROR |
Traps and deals with errors |
value, value_if_error |
IFNA |
Traps and deals with #N/A errors |
value, value_if_na |
IFS |
Tests multiple conditions to return the first match |
logical_test1, value_if_true1, [logical_test2, value_if_true2], ... |
LAMBDA |
Creates custom functions that can be reused throughout a workbook |
[parameter1, parameter2, …], calculation |
LET |
Assigns names to calculation results |
name1, name1_value, [name2/name2_value], ...,calculation |
MAKEARRAY |
Returns a calculated array based on the given number of rows and columns |
rows, columns, lambda(r,c,calculation) |
MAP |
Returns an array formed by mapping each value in the supplied array(s) to a new value |
array1, [array2], ..., lambda |
NOT |
Reverses the arguments or results |
logical |
OR |
Tests multiple conditions with OR |
logical1, [logical2], ... |
REDUCE |
Returns the total value in the accumulator, reducing the array to an accumulated value |
[initial_value], array, lambda(accumulator, value) |
SCAN |
Returns an array that contains the intermediate values while scanning the array |
[initial_value], array, lambda(accumulator, value) |
SWITCH |
Matches multiple values, but returns the first match |
expression, value1, result1, [value2, result2], ..., [default] |
TRUE |
Generates the logical TRUE value |
// |
XOR |
Performs the exclusive OR function |
logical1, [logical2], ... |
Lookup and Reference
|
Description
|
Arguments
|
ADDRESS |
Returns the cell address reference by column number and row number |
row_num, column_num, [abs_num], [a1], [sheet_text] |
AREAS |
Returns the number of areas that make up the reference |
reference |
CHOOSE |
Returns a value from the list of value argument by the given index number |
index_num, value1, [value2], ... |
CHOOSECOLS |
Returns specified columns in an array or a range |
array, col_num1, [col_num2],... |
CHOOSEROWS |
Returns specified rows in an array or a range |
array, row_num1, [row_num2],... |
COLUMN |
Returns the number of column which formula appears or the column number of given reference |
reference |
COLUMNS |
Returns the total number of columns in a given array or reference |
array |
DROP |
Returns the specific rows or columns from an array based on the given number |
array, rows, [columns] |
EXPAND |
Expands or pads an array to a specified number of rows and columns dimensions |
array, rows, [columns], [pad_with] |
FIELDVALUE |
Retrieves field data from linked data types like Stocks or Geography data types |
value, field_name |
FILTER |
Filters a range of data and dynamically returns the matching results |
array, include, [if_empty] |
FORMULATEXT |
Returns a formula as a text string from a given reference |
reference |
GETPIVOTDATA |
Returns data based on the pivot table structure |
data_field, pivot_table, [field1, item1], ... |
HLOOKUP |
Looks a value in table by matching first row |
value, table, row_index, [range_lookup] |
HSTACK |
Combines multiplearrays horizontally into one large array |
array1,[array2],... |
HYPERLINK |
Creates a hyperlink which links to a given web page, a cell reference |
link_location, [friendly_name] |
INDEX |
Returns the displayed value based on a given position from a range or an array |
array, row_num, [col_num], [area_num] |
INDIRECT |
Converts a text string to a valid reference |
ref_text, [a1] |
LOOKUP |
Finds certain value in a one-column range |
lookup_value, lookup_vector, [result_vector] |
MATCH |
Gets the position of an item in an array |
lookup_value, lookup_array, [match_type] |
OFFSET |
Returns a reference offset from a starting point |
reference, rows, cols, [height], [width] |
ROW |
Returns row number of a reference |
[reference] |
ROWS |
Returns the number of rows in a reference or an array |
array |
SORT |
Sorts the contents of a range or array in ascending or descending order |
array, [sort_index], [sort_order], [by_col] |
SORTBY |
Sorts the contents of one range or array based on the values in a corresponding range or array |
array, by_array1, [sort_order1], [by_array2, sort_order2],... |
TAKE |
Returns a specified number of contiguous rows or columns from the start or end of a given array |
array, rows,[columns] |
TOCOL |
Transforms an array or range to a single column |
array, [ignore], [scan_by_column] |
TOROW |
Transforms an array or range to a single row |
array, [ignore], [scan_by_column] |
TRANSPOSE |
Rotates the orientation of a range or array |
array |
UNIQUE |
Extracts unique values from a range of data |
array, [by_col], [exactly_once] |
VLOOKUP |
Looks a value in a table by matching on the first column and return value from a certain column |
lookup_value, Table_array, col_index, [range_lookup] |
VSTACK |
Combines multiple arrays vertically into one large array |
array1,[array2],... |
WRAPROWS |
Converts a single row or column into multiple rows by specifying the number of values in each row. |
avector, wrap_count, [pad_with] |
WRAPCOLS |
Converts a single row or column into multiple columns by specifying the number of values in each column. |
vector, wrap_count, [pad_with] |
XMATCH |
Returns relative position of a specified value in a vertical or horizontal array or range |
lookup_value, lookup_array, [match_mode], [search_mode] |
Math and Trigonometry
|
Description
|
Arguments
|
ABS |
Returns the absolute value of number |
number |
ACOS |
Returns the arccosine (inverse cosine) of number |
number |
ACOSH |
Returns the inverse hyperbolic cosine of a number |
number |
ACOT |
Returns the arccotangent (inverse cotangent) of number |
number |
ACOTH |
Returns the inverse hyperbolic cotangent of a number |
number |
AGGREGATE |
Returns based on the specific used function |
function_num, options, ref1, [ref2] |
ARABIC |
Returns an arabic number by the given roman number |
text |
ASIN |
Returns an angle of triangle in radians |
number |
ASINH |
Returns the inverse hyperbolic sine of a number |
number |
ATAN |
Returns the arctangent (inverse tangent) of a number |
number |
ATAN2 |
Returns the arctangent (inverse tangent) of specified x- and y-coordinates |
x_num, y_num |
ATANH |
Returns the inverse hyperbolic tangent of a number |
number |
BASE |
Returns the text representation of the converted number to another base |
number, radix, [min_length] |
CEILING |
Rounds number up to nearest multiple |
number, multiple |
CEILING.MATH |
Rounds number up to nearest multiple or nearest integer |
number, [significance], [mode] |
CEILING.PRECISE |
Returns a number that is rounded up to the nearest integer or to the nearest multiple of specified significance |
number, [significance] |
COMBIN |
Returns the number of combinations for a given number of items |
number, number_chosen |
COMBINA |
Returns the number of combinations for a specified number of items with repetitions allowed |
number, number_chosen |
COS |
Returns the cosine of an angle given in radians |
number |
COSH |
Returns the hyperbolic cosine of a given number |
number |
COT |
Calculate the cotangent of an angle in radians |
number |
COTH |
Returns the hyperbolic cotangent of a hyperbolic angle in radians |
number |
CSC |
Returns the cosecant of an angle provided in radians |
array, number |
CSCH |
Returns the hyperbolic cosecant of an angle provided in radians |
array, number |
DECIMAL |
Returns decimal number |
text, radix |
DEGREES |
Returns an angle in degrees |
angle |
EVEN |
Rounds numbers away from zero to the nearest even integer |
number |
EXP |
Returns the result of the constant e raised to the nth power |
number |
FACT |
Returns the factorial of a given number |
number |
FACTDOUBLE |
Returns the double factorial of a given number |
number |
FLOOR |
Rounds a given number down to the nearest multiple of the specified significance |
number, significance |
FLOOR.MATH |
Rounds a given number down to the nearest integer or nearest multiple of the specified significance |
number, [significance], [mode] |
FLOOR.PRECISE |
Rounds a given number down to the nearest integer or nearest multiple of the specified significance |
number, [significance] |
GCD |
Returns the greatest common divisor of two or more integers |
number1, [number2], ... |
INT |
Returns the integer part of a number by rounding it down to the nearest integer |
number |
ISO.CEILING |
Rounds up the nearest integer or the nearest multiple of significance |
number, [significance] |
LCM |
Returns the least common multiple of integers |
number1, [number2], ... |
LN |
Returns the natural logarithm of a given number |
number |
LOG |
Returns the logarithm of a number using a specified base |
number, [base] |
LOG10 |
Returns the base-10 logarithm of a given number |
number |
MDETERM |
Returns the matrix determinant of an array |
array |
MINVERSE |
Returns the inverse matrix of a given array |
array |
MMULT |
Returns the matrix products of two arrays |
array1, array2 |
MOD |
Returns the remainder after division |
number, divisor |
MROUND |
Returns a number rounded to the nearest specified multiple |
number, multiple |
MULTINOMIAL |
Returns the ratio of the factorial of a sum of supplied values to the product of factorials of those values |
number1, [number2], ... |
MUNIT |
Returns a unit matrix for specified dimension |
dimension |
NEGBINOM.DIST |
Returns returns the negative binomial distribution |
number_f, number_s, probability_s, cumulative |
ODD |
Rounds a number up to the nearest odd integer |
number |
PI |
Returns the number 3.14159265358979 of the mathematical constant called pi |
// |
POWER |
Returns the result of a number raised to a given power |
number, power |
PRODUCT |
Calculates the product of cell values provides as arguments |
number1, [number2], ... |
QUOTIENT |
Returns only the integer portion of a division |
numerator, denominator |
RADIANS |
Converts degrees to radians |
angle |
RAND |
Returns a random real number between 0 and 1 |
// |
RANDBETWEEN |
Returns a random integer number between two given numbers |
bottom, top |
ROMAN |
Converts an Arabic number to a Roman numeral as text |
number, [form] |
ROUND |
Rounds a number to a specified number of digits |
number, num_digits |
ROUNDDOWN |
Rounds a number up (toward zero) to a specified number of digits |
number, num_digits |
ROUNDUP |
Rounds a number up (away from zero) to a specified number of digits |
number, num_digits |
SEC |
Returns the secant of an angle provided in radians |
number |
SECH |
Returns hyperbolic secant of an angle in radians |
number |
SERIESSUM |
Returns sum of a power series |
x, n, m, coefficients |
SIGN |
Returns sign of a number |
number |
SIN |
Returns sine of an angle in radians |
number |
SINH |
Returns hyperbolic sine of a number |
number |
SQRT |
Returns square root of a number |
number |
SQRTPI |
Returns square root of a supplied number multiplied by pi |
number |
SUBTOTAL |
Returns subtotal in a list or database |
function_num, ref1, [ref2], ... |
SUM |
Returns sum of numbers |
number1, [number2], … |
SUMIF |
Returns the summation for a list of numbers based on the specific criteria |
range, criteria, sum_range |
SUMIFS |
Returns the summation for a list of numbers that meet all specific criteria |
sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... |
SUMPRODUCT |
Returns the result of multiplied and summed arrays |
array1, [array2], ... |
SUMSQ |
Returns sum of squares of values |
number1, [number2], … |
SUMX2MY2 |
Returns the sum of the difference of squares of corresponding values in two given arrays |
array_x, array_y |
SUMX2PY2 |
Returns the sum of squares of corresponding values in two given arrays |
array_x, array_y |
SUMXMY2 |
Returns the sum of squares of the differences of corresponding values in two given arrays |
array_x, array_y |
TAN |
Returns the tangent value of an angle |
number |
TANH |
Returns the hyperbolic tangent of a supplied number. |
number |
TRUNC |
Returns a truncated number based on a given precision |
number, num_digits |
Statistical
|
Description
|
Arguments
|
AVEDEV |
Returns the average of the absolute deviations of the numbers provided from their mean |
number1, [number2], ... |
AVERAGE |
Returns the average (arithmetic mean) of the given numbers |
number1, [number2], ... |
AVERAGEA |
Returns the average (arithmetic mean) of the supplied values |
value1, [value2], ... |
AVERAGEIF |
Returns the average (arithmetic mean) of the numbers in a range that meet the given criteria |
range, criteria, [average_range] |
AVERAGEIFS |
Returns the average (arithmetic mean) of the numbers in a range that meet one or more supplied criteria |
average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... |
BETA.DIST |
Returns the beta distribution which is usually used to study the percentage variation of something across samples. |
x, alpha, beta, cumulative, [A], [B] |
BETA.INV |
Returns the inverse of the cumulative beta probability density function for a specified beta distribution |
probability, alpha, beta, [A], [B] |
BETADIST |
Rturns the cumulative beta probability density function |
x, alpha, beta, [A], [B] |
BETAINV |
Rturns the inverse of the cumulative beta probability density function |
probability, alpha, beta, [A], [B] |
BINOM.DIST |
Returns the individual term binominal distribution probability |
number_s, trials, probability_s, cumulative |
BINOMDIST |
Returns the individual term binominal distribution probability |
number_s, trials, probability_s, cumulative |
BINOM.DIST.RANGE |
Returns the binomial distribution probability for the number of successes from a specified number of trials falling into a specified range |
trials, probability_s, number_s, [number_s2] |
BINOM.INV |
Returns the minimum value for which the cumulative binomial distribution is greater than or equal to a criterion |
trials, probability_s, alpha |
CRITBINOM |
Returns the minimum value for which the cumulative binomial distribution is greater than or equal to a criterion |
trials, probability_s, alpha |
CHISQ.DIST |
Returns the left-tailed probability of the chi-squared distribution |
x, deg_freedom, cumulative |
CHISQ.DIST.RT |
Returns the right-tailed probability of the chi-squared distribution |
x, deg_freedom |
CHISQ.INV |
Returns the inverse of the left-tailed probability of the chi-squared distribution |
probability, deg_freedom |
CHISQ.INV.RT |
Returns the inverse of the right-tailed probability of the chi-squared distribution |
probability, deg_freedom |
CHISQ.TEST |
Returns the chi-squared distribution of two provided data sets |
actual_range, expected_range |
CONFIDENCE.NORM |
Uses a normal distribution to calculate the confidence interval for a population mean |
alpha, standard_dev, size |
CONFIDENCE.T |
Uses a student’s distribution to calculate the confidence interval for a population mean |
alpha, standard_dev, size |
CORREL |
Returns the correlation coefficient of two cell ranges |
array1, array2 |
COUNT |
Returns the number of cells contain numbers |
value1, [value2] |
COUNTA |
Returns the number of cells excluding empty cells |
value1, [value2] |
COUNTBLANK |
Returns the number of empty cells |
range |
COUNTIF |
Returns the number of cells that meet acriterion |
range, criteria |
COUNTIFS |
Returns the number of cells that meet multiple critera |
criteria_range1, criteria1, [criteria_range2], [criteria2], ... |
COVARIANCE.P |
Returns the population variance of two data sets |
array1, array2 |
COVARIANCE.S |
Returns the sample variance of two data sets |
array1, array2 |
COVAR |
Returns the variance of two data sets |
array1, array2 |
DEVSQ |
Returns the sum of squares of the deviations from the sample mean |
number1, [number2], ... |
EXPON.DIST |
Returns the exponential distribution based on the given x value and the parameter of the distribution |
array1, arry2, cumulative |
EXPONDIST |
Returns the exponential distribution based on the given x value and the parameter of the distribution |
array1, arry2, cumulative |
F.DIST |
Returns the F probability distribution |
x, deg_freedom1, deg_freedom2, cumulative |
FDIST |
Returns the (right-tailed) F probability distribution |
x, deg_freedom1, deg_freedom2 |
F.DIST.RT |
Calculates the (right-tailed) F probability distribution |
x, deg_freedom1, deg_freedom2 |
F.INV |
Returns the inverse of F probability distribution |
probability, deg_freedom1, deg_freedom2 |
FINV |
Returns the inverse of the (right-tailed) F probability distribution |
probability, deg_freedom1, deg_freedom2 |
FISHER |
Returns the Fisher transformation of a supplied value (x) |
x |
FISHERINV |
Returns the inverse of Fisher transformation of a supplied value (y) |
y |
F.INV.RT |
Calculates the inverse of the (right-tailed) F probability distribution |
probability, deg_freedom1, deg_freedom2 |
FORECAST |
Predicts a value with a linear trend |
x, known_y's, known_x's |
FORECAST.ETS |
Predicts a value with a seasonal trend |
target_date, values, timeline, [seasonality], [data_completion], [aggregation] |
FORECAST.ETS.CONFINT |
Calculates the confidence interval for the forecast value at the specified target date |
target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation] |
FORECAST.ETS.SEASONALITY |
Returns the length of a seasonal pattern based on existing values and a timeline |
values, timeline, [data_completion], [aggregation] |
FORECAST.ETS.STAT |
Returns a specified statistical value as a result of time series forecasting |
values, timeline, statistic_type, [seasonality], [data_completion], [aggregation] |
FORECAST.LINEAR |
Predicts a value with a linear trend |
x, known_y's, known_x's |
FREQUENCY |
Returns a frequency distribution |
data_array, bins_array |
F.TEST |
Returns the result of an F-test for two given arrays or ranges |
array1, array2 |
FTEST |
Returns the result of an F-test for two given arrays or ranges |
array1, array2 |
GAMMA |
Returns the value of the gamma function for a specified number |
x |
GAMMA.DIST |
Returns the gamma distribution |
x, alpha, beta, cumulative |
GAMMADIST |
Returns the gamma distribution |
x, alpha, beta, cumulative |
GAMMA.INV |
Returns the inverse of the gamma cumulative distribution |
probability, alpha, beta |
GAMMAINV |
Returns the inverse of the gamma cumulative distribution |
probability, alpha, beta |
GAMMALN |
Returns the natural logarithm of the gamma function, Γ(n) |
x |
GAMMALN.PRECISE |
Returns the natural logarithm of the gamma function, Γ(n) |
x |
GAUSS |
Calculates the probability that a member of a standard normal population falls between the mean and z standard deviation from the mean |
z |
GEOMEAN |
Returns geometric mean of a range of positive numbers |
number1, [number2], ... |
GROWTH |
Returns the predicted exponential GROWTH based on a given set of data |
known_y’s, [known_x’s], [new_x’s], [const] |
HARMEAN |
Returns harmonic mean of a range of positive numbers |
number1, [number2], ... |
HYPGEOM.DIST |
Returns the value of the hypergeometric distribution |
sample_s, number_sample, population_s, number_pop, cumulative |
HYPGEOMDIST |
Returns the value of the hypergeometric distribution |
sample_s, number_sample, population_s, number_pop |
INTERCEPT |
Calculates the point at which a linear regression line will intersect the y-axis by using the given x-values and y-values |
known_ys, known_xs |
KURT |
Returns the kurtosis of a supplied data set |
number1, [number2], ... |
LARGE |
Returns the k-th largest number in array |
array, k |
LINEST |
Returns the statistic for a best fit straight line based on the supplied set of x value and y value by using the “least squares” method |
known_ys, known_xs, [const], [stats] |
LOGEST |
Returns an exponential curve that best fits a supplied set of y- and x- values and returns an array of values that describes the curve |
known_y’s, [known_x’s], [const], [stats] |
LOGNORM.DIST |
Calculates the lognormal distribution for a given value of x |
x, mean, standard_dev, cumulative |
LOGNORMDIST |
Calculates the lognormal distribution for a given value of x |
x, mean, standard_dev |
LOGNORM.INV |
Returns the inverse lognormal distribution for a given value of x |
probability, mean, standard_dev |
LOGINV |
Returns the inverse lognormal distribution for a given value of x |
probability, mean, standard_dev |
MAX |
Returns the largest value in the supplied set of values, ignores logical values |
number1, [number2], ... |
MAXA |
Returns the largest value in the supplied set of values |
value1, [value2], ... |
MAXIFS |
Returns the largest value of a range of values that specified set of criteria |
max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... |
MEDIAN |
Returns the middle number of the supplied numbers |
number1, [number2], ... |
MIN |
Returns the smallest number from the data provided |
number1, [number2], ... |
MINA |
Returns the smallest numeric value from a set of values provided |
value1, [value2], ... |
MINIFS |
Returns the smallest numeric value in a range that meet one or more supplied criteria |
min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... |
MODE |
Returns the most frequently occurring number in a set of numbers |
number1, [number2], ... |
MODE.MULT |
Returns a vertical array of the most frequently occurring numbers in a set of numbers |
number1, [number2], ... |
MODE.SNGL |
Returns the most frequently occurring number in a set of numbers |
number1, [number2], ... |
NORM.DIST |
Returns normal cumulative distribution function or probability density function |
x, mean, standard_dev, cumulative |
NORMDIST |
Returns normal cumulative distribution function or probability density function |
x, mean, standard_dev, cumulative |
NORM.INV |
Returns inverse of the normal cumulative distribution |
probability, mean, standard_dev |
NORMINV |
Returns inverse of the normal cumulative distribution |
probability, mean, standard_dev |
NORM.S.DIST |
Returns standard normal cumulative distribution function or probability density function |
z, cumulative |
NORMSDIST |
Returns standard normal cumulative distribution function |
z |
NORM.S.INV |
Returns inverse of the standard normal cumulative distribution |
probability |
NORMSINV |
Returns inverse of the standard normal cumulative distribution |
probability |
PEARSON |
Returns the Pearson product-moment correlation coefficient |
array1, array2 |
PERCENTILE |
Returns k-th percentile, k is from 0 to 1 including |
array, k |
PERCENTILE.EXC |
Returns k-th percentile, k is from 0 to 1 excluding |
array, k |
PERCENTILE.INC |
Returns k-th percentile, k is from 0 to 1 including |
array, k |
PERCENTRANK |
Returns the rank of a value in a data set as a percentage of the data set |
array, x, [significance] |
PERCENTRANK.EXC |
Returns the rank of a value in a data set as a percentage (excludes 0 and 1) of the data set |
array, x, [significance] |
PERCENTRANK.INC |
Returns the rank of a value in a data set as a percentage (includes 0 and 1) of the data set |
array, x, [significance] |
PERMUT |
Returns the number of permutations of a given number of objects from a set of objects (not allow repetitions) |
number, number_chosen |
PERMUTATIONA |
Returns the number of permutations of a given number of objects from a set of objects (allows repetitions) |
number, number_chosen |
PHI |
Returns the value of density distribution for a standard normal distribution for a given number |
x |
POISSON |
Returns the Poisson distribution which is used to predict the number of events occurring over a specific time |
x, mean, cumulative |
POISSON.DIST |
Returns the Poisson distribution which is used to predict the number of events occurring over a specific time |
x, mean, cumulative |
PROB |
Returns the probability that values in a range are between two limits |
x_range, prob_range, [lower_limit], [uppeer_limit] |
QUARTILE |
Returns the quartile for a data set |
array, quart |
QUARTILE.EXC |
Returns the quartile for a data set based on a percentile range of 0 to 1 exclusive |
array, quart |
QUARTILE.INC |
Returns the quartile for a data set based on a percentile range of 0 to 1 inclusive |
array, quart |
RANDARRAY |
Returns an array of random numbers |
[rows], [columns], [min], [max], [integer] |
RANK |
Returns the rank of a number against numbers in the same list |
number, ref, [order] |
RANK.AVG |
Returns the rank of a number against numbers in the same list |
number, ref, [order] |
RANK.EQ |
Returns the rank of a number against numbers in the same list |
number, ref, [order] |
RSQ |
Returns the square of the Pearson product-moment correlation coefficient |
known_ys, known_xs |
SEQUENCE |
Allows creating a list of sequential numbers in an array |
rows, [columns], [start], [step] |
SKEW |
Returns the skewness of a distribution of a set of values |
number1, [number2], ... |
SKEW.P |
Returns the skewness of a distribution of data that represents an entire population |
number1, [number2], ... |
SLOPE |
Returns the slope of a regression line based on the supplied y values and x values |
known_y's, known_x's |
SMALL |
Returns the kth smallest value from a set of numeric values |
array, k |
STANDARDIZE |
Returns a normalized value (z-score) from a distribution based on the mean and standard deviation of a data set |
x, mean, standard_dev |
STDEV |
Returns standard deviation based on a given sample of data |
number1, [number2], ... |
STDEV.P |
Returns standard deviation based on the entire population |
value1, [value2], ... |
STDEV.S |
Returns standard deviation based on a given sample of data |
number1, [number2], ... |
STDEVA |
Returns standard deviation based on a sample of population |
number1, [number2], ... |
STDEVP |
Returns standard deviation based on the entire population |
value1, [value2}, ... |
STDEVPA |
Calculates standard deviation based on the given entire population |
value1, [value2], ... |
STEYX |
Returns the standard error of the predicted y value for each x in a linear regression |
known_ys, known_xs |
T.DIST |
Returns the Student's left-tailed t-distribution |
x, deg_freedom, cumulative |
T.DIST.2T |
Returns the Student's two-tailed t-distribution |
x, deg_freedom |
T.DIST.RT |
Returns the Student's right-tailed t-distribution |
x, deg_freedom |
TDIST |
Returns the Student's t-distribution |
x, deg_freedom, tails |
T.INV |
Returns the inverse of Student's left-tailed t-distribution |
probability, deg_freedom |
TINV |
Returns the two-tailed inverse of the Student's t-distribution |
probability, deg_freedom |
T.INV.2T |
Returns the inverse of Student's two-tailed-distribution |
probability, deg_freedom |
TREND |
Predicts values along a linear trend |
known_y's, [known_x's], [new_x's], [const] |
TRIMMEAN |
Returns the mean of the interior of a data set |
array, percent |
T.TEST |
Returns the probability that is associated with a Student's t-test |
array1, array2, tails, type |
TTEST |
Returns the probability that is associated with a Student's t-test |
array1, array2, tails, type |
VAR |
Returns the variance based on a given sample |
number1, [number2], ... |
VAR.P |
Returns the variance based on the entire population |
number1, [number2], ... |
VAR.S |
Returns the variance based on a given sample |
number1, [number2], ... |
VARA |
Returns the variance based on a given sample |
value1, [value2], ... |
VARP |
Returns the variance based on the entire population |
number1, [number2], ... |
VARPA |
Returns the variance based on the entire population |
value1, [value2], ... |
WEIBULL |
Returns the Weibull probability density function or Weibull cumulative distribution |
x_range, alpha, betta, cumulative |
WEIBULL.DIST |
Returns the Weibull probability density function or Weibull cumulative distribution |
x_range, alpha, betta, cumulative |
ZTEST |
Returns the one-tailed P-value of a z-test which is useful for various analyses |
array, x, [sigma] |
Z.TEST |
Returns the one-tailed P-value of a z-test which is useful for various analyses |
array, x, [sigma] |
Text
|
Description
|
Arguments
|
ARRAYTOTEXT |
Converts an array or range into a text string |
array, [format] |
ASC |
Returns the ASCII code for the first character of a string |
text |
BAHTTEXT |
Converts a number to Thai text with a suffix of “Baht” (Thai currency บาทถ้วน) |
number |
CHAR |
Returns the character specified by a number |
number |
CLEAN |
Removes all non-printable characters from the given text |
text |
CODE |
Returns a numeric code of the first character in a given text string |
text |
CONCAT |
Joins texts from multiple columns, rows or ranges together |
text1, [text2], ... |
CONCATENATE |
Joins two or more texts items from multiple cells into one |
text1, [text2], ... |
DBCS |
Converts half-width (single-byte) characters into full-width (double-byte) characters in a text string |
text |
DOLLAR |
Converts a number to text in the currency formatting |
number, decimals |
EXACT |
Returns TRUE if two compared strings are exactly same, or returns FALSE |
text1, text2 |
FIND |
Returns the starting position of a string inside another one |
find_text, within_text, [start_num] |
FINDB |
Returns the starting position of a string inside another one in bytes |
find_text, within_text, [start_num] |
FIXED |
Returns a number formatted as decimals and represented as text |
number, [decimal_places], [no_commas] |
LEFT |
Extracts string from left of a text string |
text, [num_chars] |
LEFTB |
Extracts specified bytes of string from left of a text string |
text, [num_bytes] |
LEN |
Counts the length of a string |
text |
LENB |
Counts the length of a string in bytes |
text |
LOWER |
Lowercases letters in the text string |
text |
MID |
Returns the specific characters from the middle of text string |
text, start_num, num_chars |
MIDB |
Returns specific bytes of characters from the position you specify from a text string |
text, start_num, num_bytes |
NUMBERVALUE |
Returns the real number from number is stored as text |
text, [decimal_separator], [group_separator] |
PHONETIC |
Extracts the phonetic (furigana) characters from a text string |
reference |
PROPER |
Converts text strings to proper case |
text |
REPLACE |
Finds and replaces characters based on given location from text string with a new text |
old_text, start_num, num_chars, new_text |
REPLACEB |
replaces part of a text string with a new text string based on the number of bytes you specify |
old_text, start_num, num_bytes, new_text |
REPT |
Returns the repeated text by specific number of times |
text, number_times |
RIGHT |
Extracts the text from right of a text string |
text, [num_chars] |
RIGHTB |
Extracts specified bytes of string from right of a text string |
text, [num_bytes] |
SEARCH |
Returns the location of the specific character or text from the given text string |
find_text, within_text, [start_num] |
SEARCHB |
Returns the starting position of a string inside another one in bytes |
find_text, within_text, [start_num] |
SUBSTITUTE |
Replaces text based on given text |
text, new_text, old_text, [instance_num] |
T |
Returns the text referred to by a value |
value |
TEXT |
Converts number to text with a specific format |
text, format_text |
TEXTAFTER |
Extracts and returns the text that occurs after a given substring or delimiter |
text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] |
TEXTBEFORE |
Extracts and returns the text that occurs before a given substring or delimiter |
text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] |
TEXTJOIN |
Joins multiple values with specific delimiter |
delimiter, ignore_empty, text1, [text2], ... |
TEXTSPLIT |
Splits text strings by a given delimiter |
text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with] |
TRIM |
Removes extra spaces from text string |
text |
UNICHAR |
Returns the Unicode character based on given number |
number |
UNICODE |
Returns the number based on the first character of given text |
text |
UPPER |
Converts all letters of a given text to uppercase |
text |
VALUE |
Converts text to a number |
text |
VALUETOTEXT |
Converts any specified value to text |
value, [format] |
Web
|
Description
|
Arguments
|
ENCODEURL |
Converts a text to a URL-encoded string |
text |
FILTERXML |
Returns specific values from XML text by using the given XPath |
xml, xpath |
WEBSERVICE |
Returns data from a web service |
url |