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 |
