Log in  \/ 
x
or
x
x
Register  \/ 
x

or

Excel Functions Guide | ExtendOffice


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]

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