MS Excel - Formulas Tab
Formulas Tab
The formula
in Ms Excel always starts with the symbol "=". Like - = 5+5, this is
the formula to add two numbers whose result will be 10.
Through Formula, we can do Addition, Subtraction,
Multiplication, Division of more than one number. Formula has the following
elements like - = Equal, Cell Range, Operators, Constant.
Cell
Address – Select the cells to which we have to
apply Addition, Subtraction, Multiplication, Division or other arithmetic
operation. Like - A1+B1+C1.
Operators – Operators
are symbols that are used to perform various mathematical or logical
calculations. Like - = (A1+B1+C1) * 10.
Constants – Constants are those numbers
which we can enter in direct formula. Like - =10+10, A3
Ms Excel Function
The
pre-made formula in MS Excel is called Function. These are predefined formulas
which have been made to do a predefined work. MS Excel already provides us the
facility of inbuilt function by which we can easily do Mathematical, Logical,
Text and other types of calculations.
Types of Function
Ø Logical Function
Ø Database function
Ø Text function
Ø Financial Function
Ø Date & Time Function
Ø Lookup & Reerence
Function
Ø Math & Trig Functi
Logical Function –
Logical functions
are those functions through which we can compare two or more facts or logic.
1.
AND ( ) Function - In the AND Function, two logic means the arguments that are
given, we can also give two or more arguments, we get the result as True and
False.
We will get
the result only if both the given conditions or all the given conditions are
true in AND Function.
Syntax : AND
(Logic1, Logic2, Logic3,……..LogicN)
Example –
=AND(10>5, 5<10) Result – True
2.
OR( ) Function - In the OR Function, two logic ie the logic or condition
which is given, we can also give two or more arguments, we get the result as
True and False.
We will get
the result only if any one condition or logic is correct from the given condition
or all the condition given in OR Function.
Syntax : OR
(Logic1, Logic2, Logic3,……..LogicN)
Example – =OR(10>5,
5<10) Result – True
3.
NOT( ) Function - Only one condition logic is given in this function, which
if false, we will get True result.
Syntax : NOT(Logic)
Example – =NOT(5>10) Result – True
|
Name |
Marks |
Result |
|
A |
50 |
pass |
|
B |
20 |
Fail |
4.
IF( ) Function – Through IF Function, we can get the message displayed
according to our logic being True or False.
Syntax : IF (Logic
Test, Value if true, value if false)
Example – =IF(Total
Marks (Cell Address) >33, “Pass”, “Fail”)
5.
TRUE( ) Function – Only one condition is given in True Function and we get the
result only when that condition is true.
Syntax : TRUE
(Logic Test, Value if true, value if false)
Example – =TRUE(10>5) Result – True
Text function -
1.
Upper( ) Function – This
function is used to convert the Microsoft Excel typed text to Upper Letter,
that is, to convert the text written in small letters to capital letters.
Syntax :
=UPPER(Text or Cell Address)
|
parvati-nandan
study center |
PARVATI-NANDAN
STUDY CENTER |
2.
Lower( ) Function – This
function is used to convert the Microsoft Excel typed text to Lower Letter,
that is, to convert the text written in capital letters to small letters.
Syntax : =LOWER(Text
or Cell Address)
|
PARVATI-NANDAN
STUDY CENTER |
parvati-nandan
study center |
3.
Left( ) Function – Left()
function is used in MS Excel to display the text based on the number of
characters on the left side of the given text.
Syntax : =LEFT(Text
or Cell Address, Numbers)
Example : =Left(“Parvati-Nandan Study Center”,
14)
Result : Parvati-Nandan
4.
Right( ) Function – The Right ()
function is used in MS Excel to display the text based on the number of
characters on the right side of the given text.
Syntax : =RIGHT(Text
or Cell Address, Numbers)
Example : =RIGHT(“Parvati-Nandan Study Center”,
12)
Result : Study Center
5.
Len( ) Function – Len function
is used to find the length of a given text or string in MS Excel.
Syntax : =LEN(Text
or Cell Address)
Example : =LEN(“Parvati-Nandan Study Center”) Result : 26
6. Concatenate(
) Function – Concatenate() This function is used in
Microsoft Excel to join one or more text or string together.
Syntax : = Concatenate(Text1, Text2,………..)
Example : = Conatenate(“Formulas”, “Excel”)
Result : Formulas Excel
7.
Trim( ) Function – Trim() This
function is used in Microsoft Excel to remove extra space from a given text
string, that is, more than one space space can be removed through this
function.
Date & Time Function -
1.
Today( ) Function – This
function is used to display the current date in MS Excel.
Syntax : = Today()
Example : = Today() Result : 15/03/2021
2.
Now( ) Function – This
function is used to display the current date & time in MS Excel.
Syntax : = Now()
Example : = Now() Result : 15/03/2021, 01:15
Lookup & Reference Function
1.
Syntax : = Lookup(Lookup
Value, Table Range)
Example : = Lookup(A2, A2:A4)
Result : Amit Kumar
2.
Hlookup( ) Function – This
function is used to search data related to any data in MS Excel database. Like
from the database given below, if we can search the data on the basis of the
value of a heading and its row index.
Syntax : = HLOOKUP(Lookup
Value, Table Range, row number, 0)
Example : = HLOOKUP(B1,A1:B4,2,0) Result : 21
3.
Vlookup( ) Function – This
function is used to search data related to any data in MS Excel database. Like
from the database given below, if we can search the data on the basis of the
value of a name and its column index.
Syntax : = VLOOKUP(Lookup
Value, Table Range, Column number, 0)
Example : = VLOOKUP(A3,A1:B4,2,0) Result : 23
Comments
Post a Comment