In Microsoft Excel, functions are of great use to reduce the time taken for calculating and analyzing data. Excel Function is nothing but a predefined program, to perform a particular task. In Microsoft Excel 2003 we can work with different types of functions that will help in doing quick mathematics like math and trigonometry functions, text functions, date and time functions, logical functions and so on.
Entering of Functions
You can enter a function as a formula entry in a cell. However you should ensure that
- The function begins with an equal = sign.
- The arguments are enclosed in brackets.
- The arguments are separated by commas (,)
- If any argument contains a text value that includes space, enclose the value in quotes (” “)
1. = ABS() :- This returns the absolute value of a number or given number. Syntax is ABS(no) For example ABS(-1234) returns a result 1234.
2. Average :– It returns the average of set of arguments. Syntax is Average(no1, no2, no3,…) For example, Average(1,2,3,4,5,6) results in answer 3
3. =MAX :- It returns the highest value in a set of arguments. Syntax is = Max (no1, no2, no3,…..) Example:- =Max(5,7,6,3,4) answer is 7.
4. =MIN :- It returns the smallest value in a set of arguments. Syntax is = Min (no1, no2, no3,…..) Example:- =MIN(5,7,6,3,4) answer is 3.
5. =MOD :- It returns the remainder after divided by the divisor. Syntax is =MOB(no, divisor) Example:- =MOD(10,3) answer is 1.
6. =Fact :- This function returns the factorial of a number. Syntax =Fact(no) Example =Fact(5) answer is 120.
7. =Product :- It returns the product of set of arguments. Syntax =Prodcut(no1, no2, no3,…….) Example:- =Product(2,3,2) answer is 12.
8. =Count :- It returns number of arguments. Syntax =Count(no1, no2, no3,…….) Example:- =Count(6,7,8,1,5) returns an answer 5.
9. Power :- It returns the product of a number raised to power value. Syntax =Power(no, power value) Example:- =Power(2,5) answer is 32.
10. =Round :- It rounds a real number to a specific decimal precession. Syntax =Round(no, no.of digits) Example:– =Round(2355.78934621, 2) answer is 2355.79 Another example:- =Round(2344, -1) answer is 2350.
11. =Today :- It returns the system present data or current date. Syntax =Today()
12. =Now :- It returns the system present data and time. Syntax =Now()
13. =CONCATENATE() :- It joins several strings into a single string. Syntax = CONCATENATE(“Text 1”, “Text 2”, “Text 3”) Example:- Concatenate(“Wonderful”,”Tech”,”Stuff”) returns WonderfulTechStuff.
14. =CHAR :- It returns the character of the specified ASCII code number. Syntax =CHAR(Ascii no) Example:- =CHAR(65) returns A.
15. =Find :- It finds a string within another string and it returns starting position of that string. Syntax =Find(finding string, within string)
Example:- =Find(“is”, “Rose is Red”) answer is 6.
16. =LEN :– It returns the length of the string. Syntax =LEN(string) Example:- =LEN(“wonderful”) answer is 9.
When we want to change the text in a cell to upper case or lower case, we use functions UPPER and LOWER.
17. =UPPER :- It converts the string into uppercase. Syntax =UPPER(“string”)
Syntax: UPPER(text)
In the syntax, the text argument in parentheses refers to the text you want to convert to uppercase. Example:- =UPPER(srija) returns SRIJA
18. =LOWER :- It converts the string into lowercase. Syntax =LOWER(“string”)
Syntax: LOWER(text)
In the syntax, the text argument in parentheses refers to the text you want to convert to lowercase.
Example:- =LOWER(SRUTHI) answer is sruthi.
Note: LOWER and UPPER does not change characters in text that are not letters.
19. =LEFT :- It returns the left most character in a string.
Syntax =LEFT(“string”, no-of characters)
Example:- LEFT(“Sai Mahitha”, 3) answer is Sai
20. =Right :- It returns the right most characters in a string.
Syntax =RIGHT(“string”, no-of characters)
Example:- =RIGHT(“Sai Mahitha”, 7) answer is Mahitha
21. =MID :- It returns the string within another string. Here we should specify starting position and number of character. Syntax =MID(“string”, starting position, no-of characters)
Example:- =MID(“Sai Mahitha”, 5, 4) answer is Mahi (from 5thposition 4 characters)
22. =Proper :– It converts the starting letter in a word into capital letter and the remaining letters into small. Syntax =Proper(string) | Example:- Proper(SAI MAHITHA) answer is Sai Mahitha
23. =TRIM :- It removes the unnecessary spaces between word to word except a single space. Syntax =TRIM(string)
24. Logical Functions:- These always returns either true or false status.
=AND :- It returns true when all the given condition are true otherwise it returns false. =AND(condition1, condition2, condition3,……)
Example:- =AND(30>20, 4050) returns False.
25. =OR :- It returns true status when any condition is true in a given set of conditions. =OR(condition1, condition2, condition3)
Example:- OR(30>50, 40>20, 60>80) returns answer as True.
26. =IF :- It returns true statement when the given condition is true otherwise it returns false statement. Syntax =IF(given condition, “true statement”, “false statement”) Example:- =IF(30>20, “Hello”, “Bye”) returns Hello. This functions used in combination with =AND function is especially useful to find progress report result pass or fail. Syntax is =IF(AND(c2>=35, d2>=35, e2>=35, f2>=35, g2>=35, h2>=35), “Pass”, “Fail”)
AutoSUM (Σ) Function in Microsoft Excel 2003
One of the most frequently used functions in the SUM() function that calculates the total of a set of numeric values. Thus, a toolbar button Σ has been provided to invoke the SUM function. You can use this Auto Sum function symbol Σ to calculate the total of a group of cells without typing the formula in the destination cells. When we use Auto Sum function it asks for a specified range where data is present on which Auto Sum is to be applied and we may need to keep the cursor in the cell where we want sum to appear. By default, the Auto SUM button Σ will total the values above the destination cells or in the row to the left of the destination cells. The steps to Auto Sum using functions are:
- First select the destination cell by place the cursor at the end of data which need to be worked using Auto SUM function.
- Click on the AutoSUM button Σ
- We see a box that automatically selects the range of the cells where data is present.
- Press Enter to sum the data and it will display the result in the cell where we’ve placed the cursor.