Functions in Excel

What are called Functions in Excel?

The term function in Excel is defined as the inbuilt function used to perform calculations based on the data requirement. Excel provides a wide variety of functions followed by specific syntax. It solves complex calculations quickly and saves the users time. It helps the user to analyze the data and retrieve the best possible solution to the problem.

Types of Functions in Excel

The various kinds of functions present in Excel are as follows,

Starting from alphabetical order, the processes are described as follows:

ABS function

The abs function returns the absolute value of the number. If a number is negative, it produces a positive number.

AND function

The AND function returns the result as TRUE if all the arguments satisfy the conditions.

ARABIC function

The ARABIC function converts Roman numbers to Arabic numbers.

ASIN function

The ASIN function returns the arcsine of the number

ASINH function

ASINH function returns the inverse hyperbolic of the number.

ATAN function

The ATAN function returns the arctangent or inverse tangent of a number.

ATAN 2 Function

ATAN 2 Function returns the arctangent or inverse tangent of the specified x- and y- coordinates.

ATANH Function

The ATANH Function returns the hyperbolic tangent of the number.

AVEDEV Function

The AVEDEV function returns the average of the absolute deviations of data points from their mean.

AVERAGE Function

The AVERAGE function returns the average of the specified arguments.

AVERAGE Function

The AVERAGE function is one of the statistical functions used to find the arguments of the specified value. The averages function calculates the numbers, text, and logical values, such as TRUE= 1 and FALSE=0, where the average process ignores this argument.

AVERAGEIF Function

The average function returns the average of the data based on the specified conditions.

AVERAGEIFS Function

BAHTTEXT Function

The Bahttext function returns the number in Thai text where "Baht" is added as a suffix.

Here ten is represented in the form of baht text format.

BASE Function

The BASE function converts the given number into text representation with the given radix.

The base function converts decimal 7 to base two and displays the result as 111.

BESSELI Function

The Besseli function returns the modified Bessel function of the given number. The modified Bessel function is equivalent to the Bessel function calculated for imaginary arguments.

BESSELJ Function

The Besselj function returns the Bessel Function

BESSELK Function

The Besselk function returns the modified Bessel function as an imaginary argument.

BESSEY Function

The Bessely function returns the Bessel function. It is called the Weber function or the Neumann function.

CHAR Function

It returns the character specified by the number. The number between 1 to 255 can be determined based on the symbols displayed.

CHOOSE Function

The choose function displays the selected number from the given list of arguments by entering the specified index number. It is given the option to choose 254 values regarding statements.

CEILING Function

The ceiling function rounds the specified number to the nearest multiple or integer.

CLEAN Function

The Clean function removes the non-printable characters in the cell.

Here the data is entered in cell A1. To use the clean function, enter the formula in cell B1 as =CLEAN (B1)

CODE Function

The code function returns the numeric code for the first character in the specified string.

COLUMN Function

The Column function is a lookup or reference function that returns the column number based on the cell reference.

COLUMNS Function

It returns the number of columns in the specified value of the array or range.

COMBIN Function

The COMBIN function is called Math and Trigonometry function. This function calculates the number of combinations without repetitions in the specified data.

COMBINA Functions

The COMBINA function returns the number of combinations with repetitions in the specified data.

CONCAT Function

The CONCAT function combines the given string in the given multiple ranges. It doesn't ignore empty arguments nor provide a delimiter.

CONCATENATE Function

The Concatenate function joins two or more specified strings into a single string.

CONVERT Function

The Convert function converts the given value from one measurement to another measurement.

COS Function

The COS function returns the cosine value of the specified number.

COSH Function

The COSH function returns the hyperbolic cosine of the number.

COT Function

The COT function returns the hyperbolic cosine of the number.

COUNT Function

As the name suggests, the count function counts the total number present in the list of arguments.

COUNTA Function

The COUNTA function counts the number of values present in the arguments.

COUNTBLANK Function

The COUNTBLANK function counts the number of blank cells present in the argument.

COUNTIF Function

The Countif function counts the number of values present in the argument based on specific criteria.

DOLLAR Function

The Dollar Function converts the number to the text where the dollar sign ($) is before the value.

EXACT Function

The EXACT function checks whether the specified value is precisely the same. It returns the result as TRUE if the fixed value is the same. It replaces the result as FALSE if the specified value is not exact.

FACT Function

As the name suggests, the fact function returns the factorial of the given number.

FACTDOUBLE Function

The Fact double function returns the double factorial of the number.

FALSE Function

The False function returns the logical value called False.

FIXED Function

The Fixed Function rounds the number to a particular decimal point specified in the formula.

FLOOR Function

The Floor Function rounds down the specified number to the nearest multiple.

FISHER Transformation

The Fisher Function returns the Fisher transformation.

GAMMA Function

The Gamma function returns the gamma function value.

GCD Function

The GCD function is called the Greatest Common Divisor, which returns the greatest common divisor of the specified numbers.

The greatest common divisor of 7 and 4 is 1.

GEOMEAN Function

The Geomean Function is called the Geometric mean function, which returns the specified data's geometric mean.

GESTEP Function

The GESTEP function returns the result as one if the value is greater than the specified value. The function returns zero if the given value exceeds the fixed value.

HARMAN Function

The HARMAN function returns the harmonic mean of the specified range.

ISEVEN Function

The ISEVEN function returns the result as TRUE if the given number is even.

ISBLANK Function

ISBLANK function refers to the empty cell in the specified data. It returns the result as TRUE if the selected cell is open, or it returns the result as FALSE if the cell contains data.

ISERR Function

The ISERR function indicates the cell containing an error value except for #N/A

As the specified calculation contains an error, it returns the value as TRUE; if it doesn't have a mistake, the result will be FALSE.

ISERROR Function

The ISERROR Function refers to the error value such as #VALUE!, #N/A, #REF!, #NUM!, #NAME?, #NULL!, #DIV/0! etc.

IS LOGICAL Function

The ISLOGICAL Function checks whether the given value is logical.

ISNA Function

The ISNA function refers to the #N/A Error value, Value Not Available.

ISNONTEXT Function

The ISNONTEXT function refers to the cell which does not contain any text. If the cell is blank, it returns the result as TRUE.

As cell C1 contains a text value, the result is FALSE. The data is addressed in the formula by cell reference.

ISNUMBER Function

As the name suggests, the ISNUMBER function returns the result as TRUE if the given value is numeric. If the value input is other than numeric, the result is FALSE.

ISTEXT Function

As the name suggests, the ISTEXT function returns the result as TRUE if the input is a string. If the information of the value is other than string, the result is FALSE.

ISREF Function

The ISREF function returns the result as TRUE if the cell is referred. The result will be FALSE if the cell is not referred.

IMABS Function

The IMABS function returns the absolute value of a complex number.

IMCONJUGATE Function

The IMCONJUGATE function returns the complex conjugate of the complex number.

IMDIV Function

The IMDIV function returns the quotient of two complex numbers.

IMEXP Function

The IMEXP function returns the exponential of the complex number.

IMLN Function

The IMLN function returns the natural logarithm of a complex number.

IMLOG10 Function

The IMLOG10 function returns the base-10 algorithms of a complex number.

IMLOG2 Function

The IMLOG2 Function returns the base-2 algorithms of a complex number.

IMPOWER Function

The IMPOWER function returns a complex number raised to an integer power.

IMPRODUCT Function

The PRODUCT function returns the product of complex numbers.

KURT Function

The Excel KURT function calculates the kurtosis of a supplied set of values.

LARGE Function

The Large Function returns the K-the most significant number in the specified data. This function returns the desired most significant digit in the data, such as first, second, third, etc.

LCM Function

As the name suggests, LCM is called Least Common Multiple which returns the least common multiple of all integers.

LEFT and LEFTB Function

The Left function returns the character in the text string based on the number of characters specified.

The LEFTB function returns the character in the text string based on the number of bytes specified.

LEN and LENB Functions

The LEN function returns the number of characters in the text string.

The LENB function returns the number of bytes used to represent the character in the string.

LN Function

The LN function returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).

LOG Function

The log function calculates the logarithms with any base. If the base value is not entered in the formula, it is set to a default value of 10.

LOG10 Functions

The LOG10 Function returns the base-10 logarithm of a number.

LOGEST Function

The Excel LOGEST function returns statistical information on the exponential curve of best fit through a supplied set of x- and y- values.

LOWER Function

The lower function converts the text to lowercase.

MAX Function

The MAX function returns the maximum value in the list of arguments.

MEDIAN Function

As the name suggests, the MEDIAN function returns the median of the given number.

MID and MIDB Function

The MID function returns the number of characters in the text string in a specified starting position.

The MIDB function returns the number of characters in the text string based on the bytes specified.

MIN Function

The MIN function returns the minimum value of the given data.

MINIIFS Function

The MINIFS function returns the minimum value in the given argument based on specific criteria.

MINA Function

The MINA function returns the smallest value in the argument, including logical values, text, and characters.

MINUTE Function

The Minute function returns the minute in time value. The minute value ranges from 0 to 59.

MOD Function

The MOD function returns the remainder of the division value.

MODE Function

The mode function returns the frequently repeated value in the array or given data range.

MROUND Function

The ROUND function rounds the given number to a specified number of multiple.

MAXA Function

The MAXA function returns the maximum value in the list of arguments in the given range or data, including numbers, text, and logical values.

N Function

The N Function returns a value converted to a number.

If the cell contains a number, it returns the same number.

If the cell contains logical value TRUE, it returns the value 1

If the cell has the logical value FALSE, it returns the value 0

If the cell has an error, it returns the result as an error value

Other than the above data, it returns the result as a zero value.

NA Function

NA function is called Not Available. Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. When a formula refers to a cell containing #N/A, the procedure returns the #N/A error value. The NA function has no arguments in the formula.

NETWORKDAYS Function

The NETWORKDAYS function returns the workdays between two given dates.

The workdays exclude weekends and holidays present between the two dates.

NOT Function

The NOT function reverses the logic of the arguments.

NOW Function

The Now () function returns the current date and time.

OCT2BIN Function

The OCT2BIN function converts the octal number to a binary value.

OCT2BIN returns the #NUM! error value, If the number is not a valid octal number.

OCT2BIN returns the #VALUE! error value, if places are non-numeric

OCT2BIN returns the #NUM! Error value if places are negative.

OCT2DEC Function

The OCT2DEC function converts the octal number to decimal.

OCT2HEX Function

The OCT2HEX function converts the octal number to a Hexadecimal function.

ODD Function

The odd function rounds the given number to the nearest odd integer.

OR Function

The OR function returns the result as TRUE if any arguments are true. It displays the result as False if both statements are evaluated as false.

PI Function

The PI function returns the value of pi.

RAND Function

The RAND function returns the number between 0 and 1.

RANDBETWEEN Function

The RANDBETWEEN function returns the random number between the numbers specified.

RANK Function

The RANK function returns the rank of the number in the given array or range. It is called Statistical Range.

SECOND Function

The Second function returns the second of a time value. The second is entered in the range between 0 to 59 value.

SIGN Function

The sign function returns the sign of a given number.

T Function

The T function converts the arguments into text.

If the entered value is text, it returns the text

If the entered value is a number, it produces a zero

The empty text is replaced if the entered value is logical ().

TAN Function

The TAN function returns the tangent of the specified angle.

TRUE Function

The TRUE function returns the logical value TRUE.

TRUNC Function

The TRUNC function displays the integer part of the fractional number.

TYPE Function

The Type function returns the type of the value.

If the entered value is numeric, the type function returns the result as 1.

If the entered value is text, the type function returns the result as 2.

If the entered value is logical, the type function returns the result as 4.

If the entered value is an error type, the type function returns the result as 16

If the entered value is an array, the type function returns the result as 64.

If the entered value is compound data, the type function returns the result as 128.

UPPER Function

The upper function converts the specified text into uppercase.

VALUETOTEXT Function

The Valuetotext function returns the text from the given value. It converts the non-text value to text which is unchanged.

If the value zero is entered in the formula, it returns the text in the concise format. It displays similar text present in the selected cell.

If the value one is entered in the formula, it returns the text in double quotes except for the value such as Booleans, Numbers, and Errors.

WEEKDAY Function

The WEEKDAY function converts the given serial number into a day of the week.

By default, the serial number is entered between 1(Sunday) and 7(Saturday).

XOR Function

The XOR function returns the logical Exclusive OR for all the arguments.

The XOR function returns the result as TRUE if any of the arguments is considered true.

It returns FALSE if both arguments are evaluated to be true.

YEAR Function

The year function returns the year of the given date.

ZTEST Function

The ZTEST function returns the one-tailed probability value of the z-test function.

Summary

Excel functions help perform several calculations in data. The various parts are explained in this tutorial. Using Excel functions saves the user's time and efficiency, providing enormous advantages such as reusability, scalability, flexibility, accuracy, etc.