EXCEL – PRACTICE SESSION

@Sum( )

• @ Sum (CellAddress1 . CellAddress2)

• Total : @ Sum(CellAddress1 . CellAddress2)

Note: [CellAddress1->Starting cell . CellAddress2 -> Ending cell]

@Average( ) 

• @ Average (CellAddress1 . CellAddress2)

• Total : @Sum (CellAddress1 . CellAddress2)
• Average : @Average (CellAddress1 . CellAddress2)

Note[Cell Address1-> Starting cell . CellAddress2 -> Ending cell]

@Product( )

• @ Product (CellAddress1 . CellAddress2)

• Product : @ Product (CellAddress1 . CellAddress2)

Note[Cell Address1-> Starting cell . CellAddress2 -> Ending cell]

Find Simple Interest [Using Formula]

• Simple Interest :
  • = Principle amount * Rate * Time/100

Note[Cell Address of Principle amount*Cell Address of Rate*Cell address of Time should be given]

Find Amount (Using Formula)

• Amount :
  • = Quantity * Price

Note[Cell Address of Quantity*Cell Address of Price should be given]

Find Area & Perimeter of Rectangle (Using Formula)

• Area :
  • = Length * Base  of Rectangle
• Perimeter :
  • = 2*(Length+Base)

Note[Cell Address of Length and Cell Address of base should be given]

Find Radius, Area & Circumference of Circle (Using Formula)

• Radius :
  • = Diameter/2
• Area :
  • = 3.14*Radius^2
• Perimeter :
  • = 2*3.14*Radius

Note[Cell Address of Diameter, Cell Address of Radius should be given]

Find Radius, Volume, TSA & CSA of Cylinder (Using Formula)

• Radius :
  • = Diameter/2
• Volume :
  • = 3.14*Radius^2*Height
• TSA :
  • = 2*3.14*Radius*(Radius+Height)
• CSA :
  • = 2*3.14*Radius*Height

Note[Cell Address of Diameter, Cell Address of Radius and Cell Address of Height should be given]

@if( )

Find Eligible for vote or not

• CONDITION =
  • If Age is greater than or equal to 18 then Eligible for Vote.
• @if(age>=18,”ELIGIBLE”,”NOT ELIGIBLE”)

NOTE : age -> cell address of age should be given.

Find Profit & Loss / Write No profit & No Loss

• CONDITION =
  • If Sale Price is greater than Cost Price then Profit.
  • If Sale Price is less than Cost Price then Loss.
• PROFIT =>
  • @IF(Sale Price>Cost Price, Sale Price-Cost Price, “No Profit”)
• LOSS =>
  • @IF(Sale Price

NOTE : [Cell address of Sale Price and Cost Price should be given].

Learn to Format Excel data

Find Biggest Number / Smallest Number

• CONDITION =
  • If Number1 is greater than Number2 Then Biggest number is Number 1.
• Biggest Number =>
  • @IF(Number1>Number2, Number1,Number2)
• Smallest Number =>
  • @IF(Number1<Number2, Number1,Number2)

NOTE : [Cell address of Number1 and Number2 should be given].

RESULT (Creation 1)

Enter Name,Class, Marks of Five Subjects find Total Marks, Percentage and Division.

Condition for Division ->

1. If Percentage is Greater & Equal to 60, then First Division.
2. If Percentage is Greater & Equal to 45, then Second Division.
3. If Percentage is Greater & Equal to 33, then Third Division.
4. If Percentage is Less Than 33, then Third Division. [Percentage<33,”Fail”]  

Note –> Cell address of Percentage should be given

• Total Marks ->
  • Sum of All subject
• Percentage ->
  • Total Marks * 100/500  or Total Marks / 5

Note -> (Each Subjects Full marks is 100, so divided by 500)

• Division ->
  • @if(Percentage>=60, “First Division”, If(Percentage>=45, “Second Division”, If (Percentage>=33, “Third Division”, “Fail”)))

Note -> cell address of Percentage should be given.

RESULT (Creation 2 – Using 2 conditions) 

@if( or( ) )

Enter Name,Class, Marks of Five Subjects find Total Marks, Percentage and Division.

Condition1 :

1. If Fail in Any subject (marks<33) then fail. or

Condition2 :

1. 1. If Percentage is Greater & Equal to 60, then First Division.
2. 2. If Percentage is Greater & of Equal to 45, then Second Division.
3. 3. If Percentage is Greater & of Equal to 33, then Third Division.
4. 4. If Percentage is Less Than 33, then Third Division. [Percentage<33,”Fail”]  

Note –> Cell address of Percentage should be given

• Total Marks ->
  • Sum of All subject
• Percentage ->
  • Total Marks * 100/500  or Total Marks / 5
• Division ->
  • @If(OR(Math<33, Physics<33, Chemistry<33, Biology<33, English<33), “Fail”, if(Percentage>=60, “First Division”, If(Percentage>=45, “Second Division”, If (Percentage>=33, “Third Division”, “Fail”)))
•  

Note –> Cell address of all subjects and Cell address of Percentage should be given.

Result Creation 3 with Grade :

Enter Name,Class, Marks of Five Subjects find Total Marks, Percentage and Grade.

Condition for Grade ->

1. 1. If Percentage is Greater & Equal to 80%, then Grade A+.
2. 2. If Percentage is Greater & Equal to 60%, then Grade A.
3. 3. If Percentage is Greater & Equal to 45%, then Grade B+.
4. 4. If Percentage is Greater & Equal to 33%, then Grade B.
5. 5. If Percentage is Less Than 33, then Grade C. 

Note –> Cell address of Percentage should be given

• Total Marks ->
  • Sum of All subject
• Percentage ->
  • Total Marks * 100/500  or Total Marks / 5
• Grade->
  • @If(Percentage>=80, “Grade A+”, If(Percentage>=60, “Grade A”, If(Percentage>=45, “Grade B+”, If(Percentage>=33, “Grade B”, “, “Grade C”))))

Note –> Cell address of Percentage should be given.

Result Creation 4 with Rank :

@ RANK ( )

Enter Name,Class, Marks of Five Subjects find Total Marks, Percentage and Grade.

• Total Marks ->
  • Sum of All subject
• Percentage ->
  • Total Marks * 100/500  or Total Marks / 5
• Grade->
  • @If(Percentage>=80, “Grade A+”, If(Percentage>=60, “Grade A”, If(Percentage>=45, “Grade B+”, If(Percentage>=33, “Grade B”, “, “Grade C”))))
• Rank->
  • @RANK (Total Marks, Range, 0)
    
    • Lock the Range using F4 -> $
    • 0 – Descending order, 1-Ascending order

Salary Calculation 1 :

Store Name, City, Basic Salary. Find TA, DA, HRA and Gross Salary with following condition.

Condition ->

1. If Salary is Greater & Equal to 45000, then TA 20%, DA 18%, HRA 16%.
2. If Salary is Greater & Equal to 25000, then TA 17%, DA 16%, HRA 15%.
3. If Salary is Less than 25000, then TA 15%, DA 12%, HRA 8%.

• TA (Traveling Allowance) ->
  • @if(BS>=45000,
• Percentage ->
  • Total Marks * 100/500  or Total Marks / 5
• Grade->
  • @If(Percentage>=80, “Grade A+”, If(Percentage>=60, “Grade A”, If(Percentage>=45, “Grade B+”, If(Percentage>=33, “Grade B”, “, “Grade C”))))

Note –> Cell address of Percentage should be given.