 |
|
 |
| |
|
|
|
|
|
 |
|
 |
|
 ADVANCE LEVEL OF EXCEL |
|
|
|
|
| |
Hello students
Welcome to our Institute. We are here to provide you Computer Section in this we will give you the basic knowledge
about the Computer. In this Section we will cover the Microsoft Office Excel
according to the latest version.
EXCEL PROGRAMMING:-
Spreadsheet/Worksheet It is a tabular representation of any Mathematical, Statical and financial data.
WORKBOOK:- It is a collection of Worksheet [1 Workbook = 255 Worksheet ]
Extension:- .XLS[up to 2003] .XLSX[according
to 2007]
Functions:-Functions are built in formula`s which is used for calculation. The value supplied to the function is Known
as argument.
Rules for using functions:-
- Function must be start with an equal sign.
- Arguments are separated by Comma`s(,).
- Argument should be enclosed in small brackets.
- If an argument contain string value then it should be enclosed in Double Quotes.
Syntax = Function Name(argu1,argu2,.......)
Types Of Function:-
- Mathematical.
- Statistical.
- Logical.
- Text.
- Financial.
Mathematical:-
(1) Sum = Sum(Num1,Num2,......)
(2) Product
= Product(Num1,Num2......)
= Sum(range)
= Product(range)
Ex = Sum(2,4,6)
Ans:-12
Ex
= Product(2,4,6)
Ans:-48
(3) Sqrt = Sqrt(Number)
(4) Modulus
= Mod(Number,divisor)
Ex = Sqrt(49)
Ans:- 7
Ex
= Mod(7,2)
Ans:- 1
(5) Absolute = Abs(Number)
(6) Power
= Power(Number,power)
Ex = Abs( 5 )
Ans:- 5
Ex
= Power(2,3)
Ans:- 8
= Abs(-5)
Ans:- 5
(7) Even = Even(Number)
(8) Odd
= Odd(Number)
Ex = Even(4.67)
Ans:- 6
Ex
= Odd(3.67)
Ans:- 5
(9) Integer:- It is used to display nearest down integer value of given no.
syntax:-
= Int(Number)
= Int(5.67)
Ans 5
= Int(-5.67)
Ans -6
(10) Round = Round(Number,decimal place)
= Round( 5.65724, 3)
Ans:- 5.657
(11) Fact = Fact(Number)
(12) Log10
= Log10(Number)
Ex = Fact(3)
Ans:- 6
Ex
= Log10(2)
Ans:- 0.3010
Statistical:-
(1) Average = Average(Num1,Num2,......)
(2) Maximum
= Max(Num1,Num2......)
= Average(range)
= Max(range)
Ex
= Average(2,4,6)
Ans:-6
Ex
= Max(4,5,7)
Ans:-7
(3) Minimum = Min(Num1,Num2,......)
(4) Count
= Count(Num1,Num2......)
= Min(range)
= Count(range)
Ex
= Min(3,4,9)
Ans:-3
Ex
= Count(4,5,7,5,8)
Ans:-5
(5) CountA = CountA(value1,value2,......)
(6) CountBlank
= CountBlank(range)
= CountA(range)
= CountBlank(A1: B5)
= CountA(1,"Palak",3) Ans:- 3
Ans:- 10
(7) Countif = Countif(range,criteria)
= Countif(A1: C5, "<5")
Logical Functions:-
In these type of Functions result always comes in True or False format.
(1) AND:-In this function result comes TRUE when all the given conditions are TRUE else FALSE.
Syntax:- = AND(Cond1,Cond2,......)
=AND( 2>1, 4<5, 5+3=8 )
Ans:- TRUE
=AND( 2>1 , 4<3 )
Ans:-FALSE
(2) OR:- In this fuction Result comes false when all the given conditions are false else True
Syntax:- = OR(Cond1, Cond2,......)
= OR(4>3, 6<1)
Ans:- TRUE
= OR(4<3)
Ans:- FALSE
(3) NOT:- = NOT(Condition)
=NOT( 4>3 )
Ans:- FALSE
(4) IF
= IF(Condition, True statement, False statement)
=IF(5>3,"OK","Wrong")
Ans:- OK
Text Functions:-
(1) Length
= Len(Text)
(2) Exact = Exact(Text1,Text2,.......)
= Len("Payal")
Ans:- 5
= Exact("Ram", "RAM")
Ans:- False
(3) Left
= Left(Text, Number)
(4) Right = Right(Text, Number)
= Left("Anupriya", 3 )
Ans:- Anu
= Right("Anupriya", 5)
Ans:- Priya
(5) Upper
= Upper(Text)
(6) Lower = Lower(Text)
= Upper("raj")
Ans:- RAJ
= Lower("RAJ")
Ans:- raj
(7) Concatenate
= Concatenate(Text1,Text2,....)
= Concatenate("Raj","Atul")
Ans:- RajAtul
(8) Mid
= Mid(Text, start_num,Num_char)
= Mid("Jai Shree Ram",5,5)
Ans:- Shree
(9) Trim
= Trim(Text)
(10) Proper = Proper(Text)
= Trim("H E L L O")
Ans:- HELLO
= Proper("hello how")Hello How
(11) Find
= Find(find_text, Within_text, start_num)
= Find("boy","Akshat is a boy",1)
Ans:- 13
(12) Replace
= Replace(old_text, Start_num, num_chars, new_text)
= Replace("Hi",1,2, "bye")
Ans:- bye
Financial functions:-
Present Value(loan time amount) = PV(rate, nper, PMT, FV, Type)
Payment = PMT(rate, nper, PV , FV,Type)
Future Value(rate with PV) = FV(rate, nper, PMT, PV, Type)
Rate:-
| Yearly | rate |
| Half Yearly | rate/2 |
| Quaterly | rate/4 |
| Monthly | rate/12 |
NPER:- No Per Installment
Type:- Boolean Type
0 Begining of the Period
1 Ending of the Period
| A | B |
| Rate | 8% |
| NPER | 10 |
| Present Value | 10,000 |
| Payment | =PMT(B1/12, B2, B3, 0, 0) |
| Future Value | =FV(B1/12, B2, B4, 0, 0) |
| Present Value | =PV(B1/12, B2, B4, 0, 0) |
Cell Referencing:-
Cell coordinate in a formula is Known as Cell referencing.
(A) Relative Reference
Ex:- = Sum (A1:A3)
(B) Absolute Reference
Ex:- = Sum($A$1 : $A$3)
(C) Mixed Reference
Ex:- = Sum(A$1 : A3)
|
|
 |
|
 |
|
|
|
|
 |
|
 |
|
