Function:
Function is
predefined formula which is used for making our work easy. The function must be
defined with equal (=) sign and argument will be passed in the bracket
(Parentheses).
For
Example: =Sum(A1:P1)
- Sum is function
name
- () is
Parentheses
- A1 and P1 is
arguments
- A1:P1 is cell
range
- : is range
operator
How to use a Function in MS-Excel?
Each function has to be
used a specific manner. This is known as the syntax of the function. Let us
examine the syntax of a function that calculates the average of the value
provided to it. The syntax of the function beings with equal (=) sign and
function's name. The (=) sign conveys to excel the fact that whatever follows
it, is to be considered as a formula.
Step 1 --> =Average
this is followed by an open
parenthesis.
Step 2 --> =Average(
The inputs to the function
have to be provided after the open parenthesis inputs to a function are called
as "Arguments" of the function .If there are two or more arguments
then they are separated by range operator.
Step 3 --> =Average(A1:G1
The function ends in a
closing parenthesis.
Step 4 --> =Average(A1:G1)
Then press Enter Key to get
the result.
Step 5 --> =Average(A1:G1)
Press Enter
The answer given by the
function is called the Return Value of the function.
In a function strings always written in double quotation marks ("").
Functions are classified into following categories:-
Text functions are operates
on text related operation there are some text functions:
1. Char()-
char function returns the
character value of its given arguments (i.e., Numeric value).
Syntax:- =char(Numeric
value)
E.g. =Char(65)
Press Enter
Output A
2. Code():
Code function returns a
numeric value for the first character of any given string.
Syntax:- =Code("text")
E.g. =Char("A")
Press Enter
Output 65
Note: - Char
and code function are related to ASCII code in which A to Z
--> 65 to 90 and a to z 97 to 122
3. Concatenate ():-
Concatenate function joins
several text strings into single text string.
Syntax:- =Concatenate("Text1","Text2",......)
E.g. =Concatenate
("Ram","Singh") Press Enter
Output RamSingh
4. Exact():-
Exact function retunes true
if both the stings are same otherwise returns false. It is case sensitive
function.
Syntax:- =Exact("Text1","Text2")
E.g. =Exact("Ram","Ram")
Press Enter
Output True
E.g. =Exact("Ram","RAM")Press
Enter
Output False
5. Find ():-
Find function returns the
position number of any given character within a string. it is also case
sensitive.
Syntax:- =Find(Find
text, within text, Start number)
E.g. =Find("a","Ram",1)
Press Enter
Output 2
6. Replace ():-
Replace function replace
any number of character in a string from a given position.
Syntax:- =Replace("Old
text", Start no, No of char, New text)
E.g. =Replace("Ram
Kumar Singh", 11,5 "Shukla")
Output Ram
Kumar Shukla
7. Len():-
Len function returns the
number of characters in a text string.
Syntax:- =Len("Text")
E.g. =Len("Vishal")
Output 6
8. Upper():-
Upper function returns the
value in upper case.
Syntax:-
=Upper("Text")
E.g. =Upper("Ram")
Output RAM
9. lower():-
Lower function return the
value in Lower case.
Syntax:- =Lower("Text")
E.g. =Lower("Ram")
Output ram
10. Proper ():-
PROPER function is
categorized as String or Text function in excel. The PROPER function converts
the first character to upper case and rests to lower case. Basically, the
PROPER function in excel is used to convert your input text to proper case. It
can be used to capitalize each word in a given string.
Syntax:- =Lower("Text")
E.g. =Lower("Ram")
Output Ram
11. Left():-
Left function returns the
number of characters of any given sting from its left hand side (LHS)
Syntax:- =Left(Text,
No of char)
E.g. =Left("Vishal",4)
Output Vish
12. Right ():-
Left function returns the
number of characters of any given sting from its Right hand side (RHS)
Syntax:- =Right(Text,
No of char)
E.g. =Right("Vishal",4)
Output
shal
13. Mid ():-
Mid function returns the
number of characters of any specified position of any given string.
Syntax:-
=Mid(Text, Start no, No of char)
E.g.
=Mid("Vishal",3,2)
Output
sh
14.T ():-
T function checks whether
the given text is string or not. If yes then returns Text, else returns null
value.
Syntax:- =T(value)
E.g.
=T(“Ram”)
Output
Ram
E.g.
=T(10)
Output
NULL VALUE
E.g.
=T(“10”)
Output
10
15. Trim ():-
Trim function cuts all the
leading spaces (more than one space) of any string.
Syntax:-
=Trim(Text)
E.g.
=T(“Ram Kumar”)
Output
Ram Kumar