Functions

Function

In Microsoft Excel, a function is a pre-built formula that performs a specific calculation or task. Functions are designed to simplify and automate common calculations, saving you time and reducing the chances of errors in your spreadsheets.

Excel provides a wide range of functions to perform various tasks. Categories as below:

  • Information Function
  • Logical Function
  • Date & Time Function
  • Text Function
  • Statistical Function
  • Math & Trigonometry Function
  • Lookup & Reference Function
  • Financial Function
  • Engineering Function
  • Cube Function
  • Compatibility Function
  • Web Function
  • Database Function
  • Add-in and Automation Function

Functions in Excel typically follow a specific syntax and consist of the function name, arguments enclosed in parentheses, and sometimes additional parameters or options.

Here are some common examples of Excel functions:

  1. SUM: Adds up a range of numbers.
    Example: =SUM(A1:A5) adds the values in cells A1 through A5.
  2. AVERAGE: Calculates the average of a range of numbers.
    Example: =AVERAGE(B1:B10) calculates the average of values in cells B1 through B10.
  3. CONCATENATE: Combines multiple text strings into one.
    Example: =CONCATENATE(“Hello, “, “world!”) combines the two text strings into “Hello, world!”
  4. IF: Performs a conditional test and returns different values based on the test result.
    Example: =IF(A1 > 10, “Yes”, “No”) checks if the value in cell A1 is greater than 10 and returns “Yes” or “No” accordingly.
  5. TODAY: Returns the current date.
    Example: =TODAY() returns the current date.
  6. VLOOKUP: Searches for a value in a table and returns a corresponding value from another column.
    Example: =VLOOKUP(A1, B1:C10, 2, FALSE) searches for the value in cell A1 in the first column of the table B1:C10 and returns the value from the second column.

You can access Excel’s functions through the “Formulas” tab on the ribbon or by typing them directly into a cell. Excel also provides a Function Wizard to help you select and enter functions correctly.

By using Excel functions, you can perform complex calculations and data manipulations efficiently and accurately in your spreadsheets.

Difference between Function and Formula in Excel

In Microsoft Excel, functions and formulas are related but distinct concepts. They both involve performing calculations or operations on data within a spreadsheet, but they have key differences. For details, see the menu of Formulas.

List of some frequently used functions

Note:
Version indicate the version of Excel a function was introduced. These functions aren’t available in earlier versions.
Square brackets (“[ ]”) – it show the optional argument within the function. Means argument mention within square brackets is not compulsory. 

Information Functions

FunctionVersionDescriptionSyntax
CELLReturns information about the formatting, location, or contents of the first cell, according to the sheet's reading order, in a reference.

Info_type = address, col, color, contents, filename, format, parentheses, prefix, protect, row, type, width.
=CELL(info_type,[reference])
INFOReturns information about the current operating environment
The INFO function is not available in Excel Web App.

type_text =
"directory": Path of the current directory or folder.
"numfile": Number of active worksheets in the open workbooks.
"osversion": Current operating system version, as text.
"recalc": Current recalculation mode; returns "Automatic" or "Manual".
"release": Version of Microsoft Excel, as text.
"system": Name of the operating environment:Macintosh = "mac", Windows = "pcdos"
"origin": Returns the absolute cell reference of the top and leftmost cell visible in the window, based on the current scrolling position, as text prepended with "$A:". This value is intended for for Lotus 1-2-3 release 3.x compatibility. The actual value returned depends on the current reference style setting. Using D9 as an example, the return value would be: A1 reference style:"$A:$D$9". R1C1 reference style:"$A:R9C4"
=INFO(type_text)
ERROR.TYPEReturns a number corresponding to an error type.

Error_val = #NULL!-1; #DIV/0!-2; #VALUE!-3; #REF!-4; #NAME?!-5; #NUM!-6; #N/A-7; #GETTING_DATA-8
=ERROR.TYPE(error_val)
ISBLANKReturns TRUE if the value is blank. Check whether a reference is to an empty cell.=ISBLANK(value)
ISERRReturns TRUE if the value is any error value except #N/A.
Check whether a value is an error (#value!, #ref!, #div/0!, #num!, #name?! or #null!) excluding #n/a.
=ISERR((value)
ISERRORReturns TRUE if the value is any error value,
Check whether a value is an error (#value!, #ref!, #div/0!, #num!, #name?! or #null!, #n/a).
=ISERROR(value)
ISEVENReturns TRUE if the number is even=ISEVEN(number)
ISFORMULA2013checks whether a reference is to a cell containing a formula, and returns TRUE or FALSE=ISFORMULA(reference)
ISLOGICALReturns TRUE if the value is a logical value (TRUE or FALSE)=ISLOGICAL(value)
ISNAReturns TRUE if the value is the #N/A error value=ISNA(value)
ISNONTEXTReturns TRUE if the value is not text (Blank cells are not text)=ISNONTEXT(vauue)
ISNUMBERReturns TRUE if the value is a number=ISNUMBER(value)
ISODDReturns TRUE if the number is odd=ISODD(number)
ISREFReturns TRUE if the value is a reference=ISREF(value)
ISTEXTReturns TRUE if the value is text=ISTEXT(value)
NReturns a value converted to a number=N(value)
NAReturns the error value #N/A (Value not available)=NA()
SHEET2013Description Returns the sheet number of the reference sheet=SHEET([value])
SHEETS2013Returns the number of sheets in a reference.=SHEETS([reference])
TYPEReturns an integar representing the data type of a value;
number=1; text-2; logical value=4; error value=16; array=64.
=TYPE(value)

Logical Functions

FunctionVersionDescriptionSyntax
ANDChecks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE (255 logics can be added).=AND(logical1, [logical2], [logical3],………)
ORChecks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returs FALSE only if all arguments are FALSE. (255 logics can be added)=OR(logical1, [logical2], [logical3],………)
XORExcel
2013
Returns a logical exclusive OR of all arguments. 1 to 254 conditions you want to test that can be either TRUE or FALSE, and can be logical values, arrays, or references.=XOR(logical1, [logical2],…)
TRUEReturns the logical value TRUE. You can enter the value TRUE directly into cells and formulas without using this function=TRUE()
FALSEReturns the logical value FALSE. You can also type the word FALSE directly onto the worksheet or into the formula, and Microsoft Excel interprets it as the logical value FALSE.=FALSE()
IFSpecifies a logical test to perform. Checks whether a condition is met, and return one value if TRUE, and another value if FALSE.=IF(logical_test, [value_if_true], [value_if_false])
IFSExcel
2019
The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.
Please note that the IFS function allows you to test up to 127 different conditions.
=IFS([Something is True1, Value if True1, Something is True2, Value if True2, Something is True3, Value if True3)
IFERRORYou can use the IFERROR function to trap and handle errors in a formula.
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.
The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
=IFERROR(value, value_if_error)
IFNAExcel
2013
Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression.=IFNA(value, value_if_na)
LETExcel -
365
The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
The function can process up to 126 name/value pairs.
=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
NOTReverses the logic of its argument, Changes FALSE to TRUE or TRUE to FALSE=NOT(logical)
SWITCHExcel
2016
The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.=SWITCH(Value to switch, Value to match1...[2-126], Value to return if there's a match1...[2-126], Value to return if there's no match)

Date and Time Functions

FunctionVersionDescriptionSyntax
DATEReturns the serial number
of a particular date
=Date(year, Month, Day)
DATEVALUEConverts a date in the form of text to a serial number=DATEVALUE(date_text)
DAYS360Calculates the number of days between two dates based on a 360-day year=DAYS360( start_date,
end_date, [method])
EDATEReturns the serial number of the date that is the indicated number of months before or after the start date=EDATE(start_date, months)
EOMONTHReturns the serial number of the last day of the month before or after a specified number of months=EOMONTH(start_date, months)
HOURConverts a serial number to an hour, Returns the hours as a number from 0 (12:00 A.M.) to 23 (23:00 P.M.)=HOUR(serial_number)
MINUTEConverts a serial number to a minute. Returns the minute, a number from 0 to 59=MINUTE(serial_number)
SECONDConverts a serial number to a second. Returns the second, a number from 0 to 59=SECOND(serial_number)
TIMEReturns the serial number of a particular time. Converts hours, minutes & seconds given as a number to an excel serial number, formatted with a time format.=TIME(hour, minute, second)
TIMEVALUEConverts a time in the form of text to a serial number=TIMEVALUE(time_text)
DAYConverts a serial number to a day of the month=DAY(serial_number)
DAYS2013Returns the number of days between two dates.=DAYS((end_date, start_date)
MONTHConverts a serial number to a month. Returns the month, a number from 1 (January) to 12 (December)=MONTH(serial_number)
YEARConverts a serial number to a year. Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.=YEAR( serial_number
i.e. 42766)
YEARFRACReturns the year fraction representing the number of whole days between start_date and end_date=YEARFRAC( start_date,
end_date, [basis])
DATEDIF2016Calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.
Intro in 2016 VER but work in 2013 (no hind) also. (formation Option >>"Y"=year, "YM"=month, "MD" = day)
=DATEDIF( date1, date2,
Format Option)
NOWReturns the serial number of the current date and time. Returns the serial number of the current date and time. If the cell format was General before the function was entered, Excel changes the cell format so that it matches the date and time format of your regional settings.=NOW()
TODAYReturns the serial number of today's date. Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 31, 2017 is serial number 42766 because it is 42,766 days after January 1, 1900=TODAY()
NETWORKDAYSReturns the number of whole workdays between two dates. Working days exclude weekends and any dates identified in holidays.=NETWORKDAYS( start_date,
end_date, [holidays])
NETWORKDAYS
.INTL
2010Returns the number of whole workdays between two dates with custom weekend parameter.
Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.
Weekend number values indicate the following weekend days:

Weekend number Weekend days
1 or omitted - Saturday, Sunday
2 - Sunday, Monday
3 - Monday, Tuesday
4 - Tuesday, Wednesday
5 - Wednesday, Thursday
6 - Thursday, Friday
7 - Friday, Saturday
11 - Sunday only
12 - Monday only
13 - Tuesday only
14 - Wednesday only
15 - Thursday only
16 - Friday only
17 - Saturday only
=NETWORKDAYS
.INTL( start_date, end_date,
[weekend], [holidays])
WEEKDAYConverts a serial number to a day of the week. 1 to 7 identifying th day of the week of the date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.=WEEKDAY( serial_number,
[Return_type])
WEEKNUMConverts a serial number to a number representing where the week falls numerically with a year.
week number in the year. Return_type (Optional): A number that determines on which day the week begins. The default is 1.
Return_type Week begins on System
1 or omitted Sunday 1
2 Monday 1
11 Monday 1
12 Tuesday 1
13 Wednesday 1
14 Thursday 1
15 Friday 1
16 Saturday 1
17 Sunday 1
21 Monday 2
=WEEKNUM( serial_number,
[Return_type])
ISOWEEKNUM2013Returns number of ISO week number of the year for a given date.=ISOWEEKNUM(date)
WORKDAYReturns the serial number of the date before or after a specified number of workdays=WORKDAY( start_date,
days, [holidays])
WORKDAY.INTL2010Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.
Weekend number values indicate the following weekend days:

Weekend number Weekend days
1 or omitted - Saturday, Sunday
2 - Sunday, Monday
3 - Monday, Tuesday
4 - Tuesday, Wednesday
5 - Wednesday, Thursday
6 - Thursday, Friday
7 - Friday, Saturday
11 - Sunday only
12 - Monday only
13 - Tuesday only
14 - Wednesday only
15 - Thursday only
16 - Friday only
17 - Saturday only
=WORKDAY.INTL( start_date,
days, [weekend], [holidays])

Text Functions

FunctionVersionDescriptionSyntax
BAHTTEXTConverts a number to Thai text and adds a suffix of "Baht", using the ß (baht) currency format. You can change the Baht format to a different style in the Excel desktop application by using Regional and Language Options from control pannel.=BAHTTEXT((number)
ASCChanges full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters=ASC (text)
CHARReturns the character specified by the code number from the character set for your computer.
The CHAR function handles 256 characters but nothing more.
=CHAR(number)
CODEReturns a numeric code for the first character in a text string, in the character set used by your computer.=CODE(text)
UNICHARExcel
2013
Returns the Unicode character that is references by the given numeric value. The CHAR function handles more than 256 characters.=UNICHAR(number)
UNICODEExcel
2013
Returns the number (code point) that corresponds to the first character of the text=UNICODE(text)
CLEANRemoves all nonprintable characters from text=CLEAN(text)
CONCATExcel
2016
Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. It replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel.=CONCAT(text1, [text2],…)
CONCATENATEJoins several text items into one text item=CONCATENATE(text1,text2,…..)
DOLLARConverts a number to text, using the $ (dollar) currency format=DOLLAR(number,[decimal])
EXACTChecks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive.=EXACT(text1,text2)
FIND, FINDBReturns the starting position of one text string within another text string, FIND is case-sensitive.=FIND(find_text,within_text,[start_num])
SEARCH, SEARCHBReturns the number of the character at which a specific character or text string is first found, reading left to right, (not case-sensitive)=SEARCH(find_text,within_text,[start_num])
FIXEDRound number to the specified number of decimal and returns the result as text with or without commas.=FIXED(number,[decimal],[no_commas])
LEFT, LEFTBReturns the specified number of characters from the start of a text-strings.=LEFT(text,[num_chars])
RIGHT, RIGHTBReturns the specified number of characters from the end of a text-strings.=RIGHT(text,[num_chars])
MID, MIDBReturns the characters from the middle of a text string, given a starting position and length.=MID(text,srart_num,num_chars)
LEN, LENBReturns the number of characters in a text string=LEN(text)
LOWERConverts all letters in a text string to lowercase.=LOWER(text)
UPPERConverts a text string to all uppercase letters.=UPPER(text)
PROPERConverts a text string to proper case; the first letter in each word in uppercase and all other letters to lowercase.=PROPER(text)
REPTRepeats text a given number of times=REPT(Text, number_times)
REPLACE, REPLACEBReplace part of a text string with a different text string.=REPLACE(old_text, start_num, num_chars, new_text)
SUBSTITUTEReplace existing text with new text in a text string.=SUBSTITUTE(text, old_text, new_text, [instance_num])
TChecks whether a value is text, and returs the text if it is, or returns double quotes (empty text) if it is not.=T(value)
TEXTConverts a value to text in a specific number format.=TEXT(value, format_text)
TEXTAFTERExcel -
365
Returns text that occurs after given character or string.=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
TEXTBEFOREExcel -
365
Returns text that occurs before a given character or string.=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
TEXTJOINExcel
2019
Combines the text from multiple ranges and/or strings.=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
TEXTSPLITExcel -
365
Splits text strings by using column and row delimiters.
col_delimiter: The text that marks the point where to spill the text across columns.
row_delimiter: The text that marks the point where to spill the text down rows. Optional.
ignore_empty: Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.
match_mode: Specify 1 to perform a case-insensitive match. Defaults to 0, which does a case-sensitive match. Optional.
pad_with: The value with which to pad the result. The default is #N/A.
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
ARRAYTOTEXTExcel -
365
Returns an array of text values from any specified range.
Format: Optional. It can be one of two values:
0 Default. The text returned will be the same as the text rendered in a cell that has general formatting applied.
1 Strict format that includes escape characters and row delimiters. Encapsulates returned strings in quotes except for Booleans, Numbers and Errors.
=ARRAYTOTEXT(array, [format])
TRIMRemoves all spaces from a text string except for single spaces between words.=TRIM(text)
VALUEConverts a text string that represents a number to a number=VALUE(text)
VALUETOTEXTExcel -
365
Returns text from any specified value.
Format: Optional. It can be one of two values:
0 Default. The text returned will be the same as the text rendered in a cell that has general formatting applied.
1 Strict format that includes escape characters and row delimiters. Encapsulates returned strings in quotes except for Booleans, Numbers and Errors.
=VALUETOTEXT(value, [format])
NUMBERVALUEExcel
2013
Converts text to number in a locale-independent manner=NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])

Statistical Functions

FunctionVersionDescriptionSyntax
AVEDEVReturns the average of the absolute deviations of data points from their mean=AVEDEV(number1,[number2],….)
AVERAGEReturns the average of its arguments. Arguments can be numbers, names, arrays, or references that contain numbers.=AVERAGE(number1,[number2],…..)
AVERAGEAReturns the average of its arguments, including numbers, text, and logical values=AVERAGEA(value1,[value2],…..)
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteria=AVERAGEIF(range, criteria, [average-range])
AVERAGEIFSExcel
2019
Returns the average (arithmetic mean) of all cells that meet multiple criteria.=AVERAGEIFS(average_range, criteria_range1, criteria1,[criteria_range2, criteria2],…..)
COUNTCounts how many numbers are in the list of arguments. only numbers & Date in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. 255 arguments can be added.=COUNT(value1, [value2], [value3],…….)
COUNTACounts the number of cells that are not empty.=COUNTA(value1,[value2],…….)
COUNTBLANKCounts the number of empty cells in a specified range of cells.=COUNTBLANK(range)
COUNTIFCounts the number of nonblank cells within a range that meet the given criteria=COUNTIF(range, criteria)
COUNTIFSExcel
2019
Counts the number of cells specified by a given set of conditions or criteria.=COUNTIFS(criteria_range1, criteria1,[criteria_range2, criteria2],……..)
FREQUENCYReturns a frequency distribution as a vertical array. Calculate how often values occur within a range of values and then returns a vertical array of numbers of having one more element than bins_array.=FREQUENCY(data_array, bins_array)
LARGEReturns the k-th largest value in a data set=LARGE(array, k)
SMALLReturns the k-th smallest value in a data set=SMALL(array, k)
MAXReturns the maximum value in a list of arguments. It ignores logical value and text.=MAX(number1, [number2], ……..)
MAXAReturns the maximum value in a list of arguments, including numbers, text, and logical values. Does not ignore logical value and text.=MAXA(value1, [value2], …….)
MAXIFSExcel
2019
Returns the maximum value among cells specified by a given set of conditions or criteria=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MINReturns the minimum value in a list of arguments. It ignores logical value and text.=MIN(number1, [number2], ……..)
MINAReturns the smallest value in a list of arguments, including numbers, text, and logical values. Does not ignore logical value and text.=MINA(value1, [value2], …….)
MINIFSExcel
2019
Returns the minimum value among cells specified by a given set of conditions or criteria.=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MEDIANReturns the median, or the number in the middle of the set of given numbers.=MEDIAN(number1, [number2], ……..)
MODEReturns the most frequently occuring or repetitive value in an array or range of data. This function is available for compatibility with excel 2007 and earlier.=MODE(number1, [number2], ……..)
MODE.SNGLExcel
2010
Returns the most frequently occuring or repetitive value in an array or range of data. Arguments 2 to 254 for which you want to calculate the mode. You can also use a single array or a reference to an array instead of arguments separated by commas.=MODE.SNGL(number1, [number2],……..)
MODE.MULTExcel
2010
Returns the vertical array of the most frequently occurring, or repetitive value in an array or range of data. For a horizontal array, =TRANSPOSE(MODE.MULT(number1,number2,.....)).=MODE.MULT(number1, [number2],……..)
FORECASTReturns a value along a linear trend .
Note: In Excel 2016, this function is replaced with FORECAST.LINEAR as part of the new Forecasting functions, but it's still available for compatibility with earlier versions.
=FORECAST(x, known_y's, known_x's)
FORECAST.LINEARExcel
2016
Returns a future value based on existing values=FORECAST.LINEAR(x, known_y's, known_x's)
GROWTHReturns values along an exponential trend=GROWTH(known_y's, [known_x's], [new_x's], [const])
RANKReturns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Nonnumeric values in ref are ignored.
Important: This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. This function is available for compatibility with excel 2007 and earlier.
=RANK(number,ref,[order])
RANK.AVGExcel
2010
Returns the rank of a number in a list of numbers.
If more than one value has the same rank, the average rank is returned.
Order is 0 (zero) or omitted = Descending order
Order is any nonzero value = Ascending order
=RANK.AVG(number,ref,[order])
RANK.EQExcel
2010
Returns the rank of a number in a list of numbers.
If more than one value has the same rank, the top rank of that set of values is returned.
Order is 0 (zero) or omitted = Descending order
Order is any nonzero value = Ascending order
=RANK.EQ(number,ref,[order])

Math Functions

FunctionVersionDescriptionSyntax
ABSReturns the absolute value of a number, a number without its sign.=ABS(number)
CEILINGRounds a number to the nearest integer or to the nearest multiple of significance.=CEILING(number, significance)
CEILING.MATHExcel
2013
Rounds a number up, to the nearest integer or to the nearest multiple of significance.=CEILING.MATH(number, [significance], [mode])
DEGREESConverts radians to degrees=DEGREES(angle)
RADIANSConverts degrees to radians=RADIANS(angle)
EVENRound the positive number up and negative number down to the nearest even integar.=EVEN(number)
ODDRound the positive number up and negative number down to the nearest odd integar.=ODD(number)
EXPReturns e raised to the power of a given number=EXP(number)
FACTReturns the factorial of a number, equal to 1*2*3*numbers.=FACT(number)
INTRounds a number down to the nearest integer=INT(number)
MODReturns the remainder (after a number is divided by a divisor) from division.=MOD(number, divisor)
POWERReturns the result of a number raised to a power=POWER(number, power)
PRODUCTMultiplies its arguments. 255 num can be added.=PRODUCT(number1, [number2],……..)
QUOTIENTReturns the integer portion of a division=QUOTIENT(numerator, denominator)
RANDReturns a random number greater than or equal to 0 and less than 1=RAND()
RANDBETWEENReturns a random number between the numbers you specify=RANDBETWEEN(bottom, top)
RANDARRAYExcel
365
Returns an array of random numbers between 0 and 1. However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.=RANDARRAY([rows],[columns],[min],[max],[whole_number])
ROMANConverts an arabic numeral to roman, as text.
form1 = 0-classic; 1-more concise; 2-more concise; 3-more concise; 4-simplified
=ROMAN(number,[form1])
FLOORExcel
2013
Rounds number down, toward zero, to the nearest multiple of significance.=FLOOR(number, significance)
ROUNDRounds a number to a specified number of digits=ROUND(number, num_digits)
ROUNDUPRounds a number up, away from zero=ROUNDUP(number, num_digits)
ROUNDDOWNRounds a number down, toward zero=ROUNDDOWN(number, num_digits)
SUBTOTALReturns a subtotal in a list or database.
Function num =
1-AVERAGE; 2-COUNT; 3-COUNTA; 4-MAX; 5-MIN; 6-PRODUCT; 9-SUM;
101-AVERAGE;102-COUNT; 103-COUNTA; 104-MAX; 105-MIN; 106-PRODUCT; 109-SUM.
(101 to 111 is work on only visible cells while 1 to 9 work on invisible cell too.)
254 ref can be added.
=SUBTOTAL(function_num, ref1, [ref2], ……..)
SUMAdds all the numbers in the range of cells. 255 num can be added.=SUM(number1, [number2],………)
SUMIFAdds the cells specified by a given condition or criteria=SUMIF(range, criteria, [sum_range])
SUMIFSExcel
2019
Adds the cells in a range that meet multiple criteria. (127 criterias can be added).=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],………)
SUMPRODUCTReturns the sum of the products of corresponding ranges or array components. (255 arrays can be added)=SUMPRODUCT(array1, [array2], [array3],……..)
TRUNCTruncates a number to an integer by removing the decimal, or fractional, part of the number.=TRUNC(number, [num_digits])

Lookup & Reference Functions

FunctionVersionDescriptionSyntax
AREASReturns the number of areas in a reference. An area is an continuous range of cells or a single cell.=AREAS(reference)
ADDRESSCreates a cell reference as text to a single cell in a worksheet, given specified row and column numbers.=ADDRESS(row_num, column_num, [abs_num1], [a1], [sheet_text])
COLUMNReturns the column number of a reference=COLUMN([reference])
COLUMNSReturns the number of columns in an array or reference=COLUMNS(array)
ROWReturns the row number of a reference=ROW([reference])
ROWSReturns the number of rows in an array or reference=ROWS(array)
GETPIVOTDATAReturns/extracts data stored in a PivotTable=GETPIVOTDATA(data_field, pivot_table, [field1, item1],……..)
HYPERLINKCreates a shortcut or jump that opens a document stored on your hard drive, on a network server, an intranet, or the Internet=HYPERLINK(link_location, [friendly_name])
INDIRECTReturns a reference indicated by a text value=INDIRECT(ref_text, [a1])
LOOKUPLooks up values in a vector or array (lookup_vector = lookup_array)=LOOKUP(lookup_value, array) & LOOKUP(lookup_value, lookup_vector, [result_vector])
XLOOKUPExcel -
365
Use the XLOOKUP function to find things in a table or range by row. With XLOOKUP, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on.
Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
[match_mode] : 0 - Exact match. If none found, return #N/A. This is the default.
-1 - Exact match. If none found, return the next smaller item.
1 - Exact match. If none found, return the next larger item.
2 - A wildcard match where *, ?, and ~ have special meaning.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
HLOOKUPLooks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify.=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
VLOOKUPLooks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be stored in an ascending order.=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
CHOOSEChoose a value or action to perform from a list of values, based on an index number. 254 values can be added.=CHOOSE(index_num, value1,[value2],…….)
CHOOSECOLSExcel -
365
Returns the specified columns from an array.=CHOOSECOLS(array, col_num1, [col_num2],…)
CHOOSEROWSExcel -
365
Returns the specified rows from an array.=CHOOSEROWS(array, row_num1, [row_num2],…)
INDEXReturns a value or reference of the cell at a intersection of a particular row and column, in a given range. Uses an index to choose a value from a reference or array=INDEX(array, row_num, [column_num]) or =INDEX(reference, row_num, [column_num], [area_num])
MATCHReturns the relative position of an item in array that match a specified values in specified order. Looks up values in a reference or array.=MATCH(lookup_value, lookup_array, [match_type {1=less than, 0=Exact match, -1=Greater than}])
XMATCHExcel -
365
The XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.
Match_mode- specifies which match type to use:

0 or omitted (default) - exact match
-1 - exact match or the next smallest value
1 - exact match or the next largest value
2 - wildcard match (*, ?)
Search_mode- specifies the search direction and algorithm:

1 or omitted (default) - search from first to last.
-1 - search in reverse order from last to first.
2 - binary search ascending. Requires lookup_array to be sorted in ascending order.
-2 - binary search descending. Requires lookup_array to be sorted in descending order.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
OFFSETReturns a reference of a range that is a given number of rows and columns from a given reference.=OFFSET(reference, rows, cols, [height], [width])
TRANSPOSEConverts a vertical range of cells to a horizontal range, or vice versa.=TRANSPOSE(array)
RTDRetrieves real-time data from a program that supports COM automation (Automation: A way to work with an application's objects from another application or development tool. Formerly called OLE Automation, Automation is an industry standard and a feature of the Component Object Model (COM).)
FORMULATEXTExcel
2013
Returns the formula at the given reference as text/string.=FORMULATEXT(reference)
DROPExcel -
365
Excludes a specified number of rows or columns from the start or end of an array. You might find this function useful to remove headers and footers in an Excel report to return only the data.=DROP(array, rows, [columns])
EXPANDExcel -
365
Expands or pads an array to specified row and column dimensions.=Expand(array, rows, [columns], [pad_with])
FILTERExcel -
365
Filters a range of data based on criteria you specify.=FILTER(array, include, [if_empty])
SORTExcel -
365
Sorts the contents of a range or array. [sort_order]: 1 for ascending order (default), -1 for descending order.
[by_col]: A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column.
=SORT(array, [sort_index], [sort_order], [by_col])
SORTBYExcel -
365
Sorts the contents of a range or array based on the values in a corresponding range or array.
by_array1: The array or range to sort on
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
TAKEExcel -
365
Returns a specified number of contiguous rows or columns from the start or end of an array.=TAKE(array, rows, [columns])
TOCOLExcel -
365
Returns the array in a single column.
Ignore: Whether to ignore certain types of values. By default, no values are ignored. Specify one of the following:
0 Keep all values (default)
1 Ignore blanks
2 Ignore errors
3 Ignore blanks and errors
scan_by_column: If it is omitted or FALSE, the array is scanned by row; If TRUE, the array is scanned by column.
=TOCOL(array, [ignore], [scan_by_column])
TOROWExcel -
365
Returns the array in a single row. Arguments are same as TOCOL.=TOROW(array, [ignore], [scan_by_column])
UNIQUEExcel -
365
Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.
The data to filter by unique entries.
by_column: Whether to filter the data by columns or by rows. By default, this is FALSE.
exactly_once: Whether to return only entries with no duplicates. By default, this is FALSE.
=UNIQUE(range, [by_column], [exactly_once])

Database Functions

FunctionVersionDescriptionSyntax
DAVERAGEReturns the average of selected database entries=DAVERAGE(database, field, criteria)
DCOUNTCounts the cells that contain numbers in a database=DCOUNT(database, field, criteria)
DCOUNTACounts nonblank cells in a database=DCOUNTA(database, field, criteria)
DGETExtracts from a database a single record that matches the specified criteria=DGET(database, field, citeria)
DMAXReturns the maximum value from selected database entries=DMAX(database, field, criteria)
DMINReturns the minimum value from selected database entries=DMIN(database, field, criteria)
DPRODUCTMultiplies the values in a particular field of records that match the criteria in a database=DPRODUCT(database, field, criteria)
DSUMAdds the numbers in the field column of records in the database that match the criteria=DSUM(database, field, criteria)
DSTDEVEstimates the standard deviation based on a sample of selected database entries=DSTDEV(database, field, criteria)
DSTDEVPCalculates the standard deviation based on the entire population of selected database entries=DSTDEVP(database, field, criteria)
DVAREstimates variance based on a sample from selected database entries=DVAR(database, field, criteria)
DVARPCalculates variance based on the entire population of selected database entries=DVARP(database, field, criteria)
Exit mobile version