Database
|
Description
|
Arguments
|
DAVERAGE |
Return average that match the specific criteria. |
Database, Field, Criteria |
Date and Time
|
Description
|
Arguments
|
DATE |
Create date with year, month and day |
year, month, day |
DATEDIF |
Return the years, months or days between two dates |
start_date, end_date, unit |
DATEVALUE |
Convert a date stored as text format to a valid date |
date_text |
DAY |
Get the day as a number (1 to 31) from a date |
date |
DAYS |
Get the number of days between two dates |
start_date, end_date |
DAYS360 |
Get the number of days between 2 dates in a 360-day year |
start_date, end_date, method |
EDATE |
Add n months for a date |
start_date, months |
EOMONTH |
Get the last day of month n months in future or past |
start_date, months |
ISOWEEKNUM |
Get ISO week number from a given date |
date |
HOUR |
Get the hour as a number (0 to 23) from a date time |
Serial_number |
MINUTE |
Return the minute as number (0 to 59) from time |
serial_number |
MONTH |
Get the month as integer number (1 to 12) from date |
serial_number |
NETWORKDAYS |
Get the number of working days between two dates |
Start_date, End_date, Holidays |
NETWORKDAYS.INTL |
Get workdays between two dates |
Start_date, End_date, Weekend, Holidays |
NOW |
Get the current date and time |
|
SECOND |
Return the seconds as number (0 to 59) from time |
serial_number |
TIME |
Create time with hours, minutes and seconds |
Hour, Minute, Second |
TIMEVALUE |
Get time from a text string |
time_text |
TODAY |
Get the current date |
|
WEEKDAY |
Get 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_daye, End_date, [Basis] |
Lookup and Reference
|
Description
|
Arguments
|
ADDRESS |
Return the cell address reference by column number and row number. |
Row_num, Column_num, Abs_num, A1, Sheet_text |
AREAS |
Return the number of areas that make up the reference |
Reference |
CHOOSE |
Return a value from the list of value argument by the given index number |
Index_num, Value1, [Value2] |
COLUMN |
Return the number of column which formula appears or the column number of given reference |
Reference |
COLUMNS |
Return the total number of columns in a given array or reference |
Array |
FORMULATEXT |
Return a formula as a text string from a given reference |
Reference |
GETPIVOTDATA |
Return data based on the pivot table structure |
data_field, pivot_table, [field1, item1] |
HLOOKUP |
Look up a value in table by matching first row |
Value, Table, Row_index, [Range_lookup] |
HYPERLINK |
Create 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 |
Convert a text string to a valid reference |
ref_text, a1 |
LOOKUP |
Find certain value in a one-column range |
lookup_value, lookup_vector, [result_vector] |
MATCH |
Get the position of an item in an array |
lookup_value, lookup_array, [match_type] |
MMULT |
Return the matrix products of two arrays |
Array1, Array2 |
OFFSET |
Return a reference offset from a starting point |
Reference, Rows, Cols, [height], [width] |
ROW |
Return row number of a reference |
[reference] |
ROWS |
Return the number of rows in a reference or an array |
Array |
TRANSPOSE |
Rotate the orientation of a range or array |
Array |
VLOOKUP |
Lookup 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] |
Financial
|
Description
|
Arguments
|
FV |
Figure out the future value of an investment |
rate, nper, pmt, pv, type |
FVSCHEDULE |
Figure out future value of a lump sum investment with adjustable interest rates |
principal, Schedule |
INTRATE |
Figure out the interest rate for a fully invested security |
Settlement, Maturity, Investment, Redemption, Basis |
IRR |
Figure out the internal rate of return for a series of cash flows that occur at regular intervals |
Values, Guess |
ISPMT |
Figure out interest payment of given period of an investment or loan |
rate, Per, nper, pv |
MDURATION |
Figure out the modified Macauley duration for a security |
settlement, Maturity, Coupon, Yld, Frequency, Basis |
MIRR |
Figure out modified internal rate of return for a series of cash flows |
Values, Finance_rate, Reinvest_rate |
IPMT |
Figure out the interest payment for the specified period for an investment or load |
rate, per, Nper, fv, type |
Math
|
Description
|
Arguments
|
ABS |
Return the absolute value of number. |
Number |
AGGREGATE |
Return based on the specific used function. |
Function_num, Options, Ref1, [Ref2] |
ARABIC |
Return an arabic number by the given roman number. |
Text |
ASIN |
Return an angle of triangle in radians. |
Number |
CEILING |
Round number up to nearest multiple |
Number, Multiple |
CEILING.MATH |
Round number up to nearest multiple or nearest integer |
Number, [Significance], [Mode] |
CEILING.PRECISE |
Round number up to nearest multiple or nearest integer ignoring sign of number |
Number, [Significance] |
COS |
Return the cosine of an angle given in radians. |
Number |
DECIMAL |
Return decimal number. |
Text, Radix |
DEGREES |
Return an angle in degrees. |
Angle |
SUMIF |
Return the summation for a list of numbers based on the specific criteria. |
Range, Criteria, Sum_range |
SUMIFS |
Return the summation for a list of numbers that meet all specific criteria. |
Sum_range, Range1, Criteria1, ... |
SUMPRODUCT |
Return the result of multiplied and summed arrays. |
Array1, Array2, ... |
TAN |
Return the tangent value of an angle. |
Number |
TRUNC |
Return a truncated number based on a given precision. |
Number, Mum_digits |
Logical
|
Description
|
Arguments
|
AND |
Test multiple conditions to return True or False. |
Logical1, Logical2, ... |
FALSE |
Generate the logical FALSE value. |
|
IF |
Test for a specific condition. |
logical_test, value_if_true, value_if_false |
IFERROR |
Trap and deal with errors. |
value, value_if_error |
IFNA |
Trap and deal with #N/A errors. |
value, value_if_na |
IFS |
Test multiple conditions to return the first match. |
test1, value1, test2,value2, ... |
NOT |
Reverse the arguments or results. |
logical |
OR |
Test multiple conditions with OR. |
logical1, logical2, ... |
SWITCH |
Match multiple values, but return the first match. |
expression, value1,result1, value2,result2, ..., default |
TRUE |
Generate the logical TRUE value |
|
XOR |
Perform the exclusive OR function. |
logical1, logical2, ... |
Statistical
|
Description
|
Arguments
|
COUNT |
Return the number of cells contain numbers. |
Value1, [Value2] |
COUNTA |
Return the number of cells excluding empty cells. |
Value1, [Value2] |
COUNTBLANK |
Return the number of empty cells. |
Range |
COUNTIF |
Return the number of cells that meet a criterion. |
Range, Criteria |
COUNTIFS |
Return the number of cells that meet multiple critera. |
Criteria_range1, Criteria1, ... |
Text
|
Description
|
Arguments
|
CHAR |
Return the character specified by a number. |
Number |
CLEAN |
Remove all non-printable characters from the given text. |
Text |
CODE |
Return a numeric code of the first character in a given text string. |
Text |
CONCAT |
Join texts from multiple columns, rows or ranges together. |
Text1, Text2, ... |
CONCATENATE |
Join two or more texts items from multiple cells into one. |
Text1, Text2, ... |
DOLLAR |
Convert 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 |
FIXED |
Returns a number formatted as decimals and represented as text. |
Number, Decimal_places, No_commas |
LEFT |
Extracts string from left of the text string. |
Text, Num_chars |
LEN |
Count the number of characters. |
Text |
LOWER |
Lowercase letters in the text string. |
Text |
SUBSTITUTE |
Replace text based on given text. |
text, new_text, old_text, [instance_num] |
TEXT |
Convert number to text with a specific format. |
text, format_text |
TEXTJOIN |
Join multiple values with specific delimiter |
delimiter, ignore_empty, text1, [text2]... |
TRIM |
Remove extra spaces from text string |
text |
UNICHAR |
Return the Unicode character based on given number. |
number |
UNICODE |
Return the number based on the first character of given text. |
text |
UPPER |
Convert all letters of a given text to uppercase. |
text |
VALUE |
Convert text to a number. |
text |
MID |
Return the specific characters from the middle of text string. |
text, start_num, num_chars |
NUMBERVALUE |
Return the real number from number is stored as text. |
text, decimal_separator, group_separator |
PROPER |
Convert text strings to proper case. |
text |
REPLACE |
Find and replace characters based on given location from text string with a new text. |
old_text, start_num, num_chars, new_text |
REPT |
Return the repeated text by specific number of times. |
text, number_times |
RIGHT |
Extract the text from right of the text string. |
text, num_chars |
SEARCH |
Return the location of the specific character or text from the given text string. |
find_text, within_text, start_num |