What is a Function and Explain Different Categories of Functions in Excel?

Functions are powerful feature in MS excel application. They help to perform calculations on the numerical data easily and quickly. Functions are pre –defined formulas through which the user has to supply the arguments or parameters based on the function category. Therefore a function consist two parts namely a function name and its arguments. Functions are available in the insert menu to choose the name of the function category and provide the necessary arguments or cell references and then click o ok button. Functions are also available on the standard tool bar as function wizard with an icon fx. Thus the function computes with the given values and shows your results in the specified cell. In excel, we have various function categories such as: 1) Mathematical functions 2) Statistical functions 3) Financial functions 4) Date and time functions 5) Logical functions 6) Text functions 1) Mathematical functions:- 1. abs(  ):- abs (  ) stands or absolute it calculates the absolute value for the given number for example if you give abs (-80) then it returns the absolute value as 80. It means it returns a positive value even though when you give a negative number. 2. exp( ):- EXP stands for exponential value. For example if you give = exp(3,2) then it returns 9 which means the number 3 is raised through the power of 2. 3. int ( ) :- Int stands for integer value. It rounds the number to the nearest integer, suppose if you give      int(8.2) then it returns 8 4. MODE (  ) :- This function gives the remainder of a given value after dividing with some other number for example mode (10,3) returns 1, Mode (10,2) returns 0. 5. SQRT ( ):- SQRT stands for square root. These function calculates the square root for the given number. For example sqrt (49) return 7. 6. Round ( ): - The round function rounds  the given number to a specified number of decimal for example if you give round (18.7892) returns 18.79 7. Sum ( ) : - The sum function accumulates the total value for the given range. For example if you have given the values 5,6,7,8 and 9 in a column of cells and given the formula as = sum (A1 : A5) then it returns 35. 8. Roman ( ): - The roman function is used to convert the given numerical value into its roman digit for example = roman (3) returns III 2) Statistical functions:- 1. Max ( ) :- Max stands for maximum value. It gives the maximum value between the two number or the series of given range = max (10,20) it gives 20. 2. Min ( ) :- Min stands for minimum value. It gives the minimum value for the given range = Min (10,20) it gives 10. Note that, if you give a formula in a cel as = Max (“A”, “a”) then it returns  the ascii key of “a’ is “97”, (ascii value of “A” is 65). 3. Average ( ) :- The average function calculates the average of the given range. 4. Count ( ) : - The count function calculates the total number of cells in the given range. For example if you select  7 columns then it gives 7 as it counts the above columns. 5. Medium ( ) : The middle function gives the middle value for the given range in series. For example if you give numerical values in cells as 5,6,7,8,7 in the column of cells then it gives 9. As a middle value known as median. 3. Financial functions: - The rate function calculates the periodic interest rates at which the present investment will grow in future. 1. FV ( ) : The future value function returns the future value of an investment at a constant payment and interest rate. Syntax:- FV ( payment, interest, rate, period). 2.PV( ) :- This function calculates the present value of an investment when you give payment, period and interest rate. Syntax: - PV (payment, interest rate, period). 3. NPV ( ) : - NPV standards for net present value. It calculates the net present value of an investment based on periodic constant payments and constant interest rate. 4. Date and time ( ) : - 1. Today ( ) : It gives the current date of the system in a cell which is today’s date. 2. Now ( ) : The now function gives the current time that the system is maintaining in a cell. 3 Date ( ) : The date function should specifies a particular date in a cell in the format of year, month, date = date (2014, 08, 17). 4. Day ( ) : It gives the day of a month for the given date. 5. Month ( ) : It gives the month for the given date. 6. Year ( ) : The year function gives the year of the date. 5) Logical functions:- 1. If ( ) :- The if function is one of the most important and powerful function. It returns either a true value or false value depending upon a condition. The if function initially checks the condition and execute to provide true or false result. If the condition is satisfied then it returns true otherwise it returns false value. = if (A1 > 34), “pas” “fail”). 2. And ( ) :- The and function returns either true value or false value depending on a condition of the comparing two cells or more. If the condition  satisfies then it returns a true value otherwise it returns false value. If (A1 > 35 and B1 > 60 “true”, “false”). 3. Or ( ) :- It returns either true or false value depending upon the condition after comparing 2 cells or more. It returns true value. When either of the condition is true otherwise it returns false. = If ( or(A1<100>50), “true” false). 6. String (or) text functions:- 1. Upper ( ) : - The upper function is used to convert lower case text  into upper case text. 2. Lower ( ) : - The lower function is used to convert upper case text into lower case. 3.Sort ( ) : - Sort is a process to arrange the data in meaningful order either in ascending or descending order depending upon a key field data item. There is a short cut command available on the ¯A icon such as    to sort the data store in excel cells. This command is also available in the data menu as data sort. When you loss this command, you need to select the columns and rows containing data as a range and then apply this command. Thus you can find the data sorted from lowest to highest in ascending order by default and highest to lowest values in descending order. Note that you can sort the data not only on the text data type but also on the numeric data type as well.