Monday, 29 February 2016


SPACE COMPUTER INSTITUTE, VIKHROLI, MUMBAI , MO 9920574163 ©

condition
if avg>35  result is pass
if avg<35  result is fail

IF
SR NAME M1 M2 M3 M4 M5 M6 TOTAL AVG RESULT
1002 sanju 70 70 70 70 70 69
1003 dada 25 25 26 25 25 25
1004 asmita 89 58 58 89 98 98
1005 kajal 59 95 59 78 35 65
1006 sanju 59 78 56 48 89 98
1007 mangesh 58 48 98 49 52 68
1008 lina 58 49 58 45 89 68
1009 dada 58 78 27 78 68 56
1010 sanju 65 96 59 89 45 58


NESTED IF
IF(K27>89,"A",IF(K27>=80,"B",IF(K27>=70,"C",IF(K27>=60,"D","F"))))
>89 A
>=80 B
>=70 C
>=60 D
<60 E


SR NAME M1 M2 M3 M4 M5 M6 TOTAL AVG GRADE
1002 sanju 70 70 70 70 70 69
1003 dada 45 89 99 56 65 98
1004 asmita 89 58 58 89 98 98
1005 kajal 59 95 59 78 35 65
1006 sanju 59 78 56 48 89 98
1007 mangesh 58 48 98 49 52 68








AND IF
IF  MARKS >=35 in all subject then result is pass or result is fail
=IF(AND(M1>=35,M2>=35,M3>=35,M4>=35,M5>=35,M6>=35),"PASS","FAIL"))
SRNAMEM1M2M3M4M5M6TOTALAVGGRADERESULT
1002sanju707070703469
1003dada458999566598
1004asmita895858899898
1005kajal599559783565
1006sanju597856488998
1007mangesh584898495268
1008lina584958458968
1009dada587827786856
1010sanju659659894558



AND,OR,NOT
IF DEPT="SALES" AND REGION="NORTH" TAX IS 1000, ELSE TAX IS 500

IF(AND(DEPT="SALES",REGION="NORTH"),1000,500)
IF DEPT="SALES" OR  DEPT="ADMIN" ADD TAX IS 500, ELSE TAX IS 100
IF(OR(DEPT="SALES",REGION="ADMIN"),5001,00)
IF DEPT IS  OTHER THAN "SALES" THEN BONUS IS 5000 
=IF(NOT(DEPT="SALES"),"
emp code last name dept region branch hire date salary   tax   ADD   TAX BONUS
1001 raja  SALES NORTH delhi 01/01/2000 10000
1002 sanju ADMIN NORTH madras 01/02/2000 65000
1003 dada SALES SOUTH mumbai 01/03/2000 78000
1004 asmita SALES EAST cal 01/04/2000 91000
1005 kajal SALES NORTH cal 01/05/2000 104000
1006 sanju SALES NORTH mumbai 01/06/2000 117000
1007 mangesh SALES NORTH madras 01/07/2000 130000
1008 lina SALES NORTH delhi 01/08/2000 143000
1009 dada SALES NORTH mumbai 01/09/2000 156000
1010 sanju SALES NORTH cal 01/10/2000 169000




LOOKUP
USING LOOK UP FIND GRADE FOR BELOW TABLE
USING IF FIND GRADE FOR BELOW TABLE

AVG GRADE
25 F
30 D
35 D+
40 D++
45 C
50 C+
55 C++
60 B
65 B+
70 B++
75 A
80 A+
90 A++
SRNAMEM1M2M3M4M5M6TOTALAVGGRADE
1002sanju757878787890
1003dada458999566598
1004asmita895858899898
1005kajal599559783565
1006sanju597856488998
1007mangesh584898495268
1008lina584958458968
1009dada587827786856
1010sanju659659894558















sub total: 


  1. find the total salary departntmentt wise
  2. find total of region wise
  3. find the avg salary departntmentt wise
  4. find the avg  of region wise
  5. find the maximum  salary departntmentt wise
  6. find total of region wise
  7. find the avg salary departntmentt wise
  8. find the avg  of region wise
  9. find the total salary departntmentt wise
  10. find total salary of region wise
  11. find the avg salary departntmentt wise
  12. find thecount  of salary region wise
  13. find the total count salary departntmentt wise
  14. find minimum of salaryregion wise
  15. find the minimum salary departntmentt wise
emp code f nam last name dept region salary
112 chandan kambale admin east 11600
132 sonali shinde admin east 24600
122 jack johnson admin south 18100
127 ali khan admin west 21350
107 tom pawar admin west 8350
125 rajesh pawar class iv north 20050
110 mack johnson class iv south 10300
128 john johnson hr east 22000
108 sanjiv shinde hr east 9000
113 rakesh pawar hr north 12250
103 khali khan hr west 5800
123 guru singh hr west 18750
117 satpal singh labour north 14850
137 sandeep pawar labour north 27850
102 sanjiv shinde labour south 5000
129 indrajeet singh mkt north 22650
134 santa johnson mkt south 25900
114 sanjiv shinde mkt south 12900
124 suresh kambale personal east 19400
104 jack johnson personal east 6400
109 salman khan personal north 9650
119 sangita pawar personal west 16150
120 sanjiv shinde production east 16800
105 guru singh production north 7050
130 madan kambale production south 23300
115 ali khan production west 13550
135 teja singh production west 26550
133 alibaba khan r& d north 25250
118 rahul kambale r& d south 15500
138 sanjiv shinde r& d south 28500
136 sadan kambale sales east 27200
116 chery johnson sales east 14200
121 shahrukh khan sales north 17450
101 rohan pawar sales north 4500
106 kuthalahi kambale sales south 7700
126 sanjiv shinde sales south 20700
111 sanjay singh sales west 10950
131 manisha pawar sales west 23950





MAKE PIVOT TABLE FOR ABOVE DATA: 

Sum of salary Column Labels
Row Labels east north south west Grand Total
ali 34900 34900
alibaba 25250 25250
chandan 11600 11600
chery 14200 14200
guru 7050 18750 25800
indrajeet 22650 22650
jack 6400 18100 24500
john 22000 22000
khali 5800 5800
kuthalahi 7700 7700
mack 10300 10300
madan 23300 23300
manisha 23950 23950
rahul 15500 15500
rajesh 20050 20050
rakesh 12250 12250
rohan 4500 4500
sadan 27200 27200
salman 9650 9650
sandeep 27850 27850
sangita 16150 16150
sanjay 10950 10950
sanjiv 25800 67100 92900
santa 25900 25900
satpal 14850 14850
shahrukh 17450 17450
sonali 24600 24600
suresh 19400 19400
teja 26550 26550
tom 8350 8350
Grand Total 151200 161550 167900 145400 626050




region (Multiple Items)
Sum of salary Column Labels
Row Labels admin hr personal production sales Grand Total
ali 21350 13550 34900
chandan 11600 11600
chery 14200 14200
guru 18750 18750
jack 6400 6400
john 22000 22000
khali 5800 5800
manisha 23950 23950
sadan 27200 27200
sangita 16150 16150
sanjay 10950 10950
sanjiv 9000 16800 25800
sonali 24600 24600
suresh 19400 19400
teja 26550 26550
tom 8350 8350
Grand Total 65900 55550 41950 56900 76300 296600



MAKE PIVOT CHART  FOR ABOVE DATA: 


USE CONDITIONAL FORMATTING FOR FOLLOWING 

emp codef namlast namedeptregionsalary
112chandankambaleadmineast11600
132sonalishindeadmineast24600
122jackjohnsonadminsouth18100
127alikhanadminwest21350
107tompawaradminwest8350
125rajeshpawarclass ivnorth20050
110mackjohnsonclass ivsouth10300
128johnjohnsonhreast22000
108sanjivshindehreast9000
113rakeshpawarhrnorth12250
103khalikhanhrwest5800
123gurusinghhrwest18750
117satpalsinghlabournorth14850
137sandeeppawarlabournorth27850
102sanjivshindelaboursouth5000
129indrajeetsinghmktnorth22650
134santajohnsonmktsouth25900
114sanjivshindemktsouth12900
124sureshkambalepersonaleast19400
104jackjohnsonpersonaleast6400
109salmankhanpersonalnorth9650
119sangitapawarpersonalwest16150
120sanjivshindeproductioneast16800
105gurusinghproductionnorth7050
130madankambaleproductionsouth23300
115alikhanproductionwest13550
135tejasinghproductionwest26550
133alibabakhanr& dnorth25250
118rahulkambaler& dsouth15500
138sanjivshinder& dsouth28500
136sadankambalesaleseast27200
116cheryjohnsonsaleseast14200
121shahrukhkhansalesnorth17450
101rohanpawarsalesnorth4500
106kuthalahikambalesalessouth7700
126sanjivshindesalessouth20700
111sanjaysinghsaleswest10950
131manishapawarsaleswest23950




CONDITION

  1. if  dept  sales  make font color  blue  
  2. if  dept  hr  make font color  red  
  3. if  dept  personal  make font color  pink  
  4. if  dept  admin  make font color  yelow  
  5. if  region   east  make font color  blue  
  6. if  region   west  make font color  red  
  7. if  region   south  make font color  pink  
  8. if  region   north  make font color  yelow  
  9. if  salary  greater than  15000  make font color  blue
  10. if  salary  less than  5000  make font color  red
  11. make  salary  top three    make font color  yellow
  12. if date  this   month  make date  back  
  13. if  this  today    green  
  14. if this  year    pink  
  15. if(salary>15000,1,0)
    if(and(salary>100000,saalary<500000),1,0)
    if(year(date)<2011,1,0)
    if(and(year(date)<2011,year(date)>2005),1,0)


USING PMT





  loan amount 200000 pmt
rate of intrest
(PER MONTH)		 10%
TOTAL MONTH 36
emi(pmt)

USING FV 
amt  -2000 fv
rate of intrest  10% PER MONTH
month 60
future vale:------


USING PMT





loan amount200000pmt
rate of intrest10%PER ANUM
month36
emi(pmt)

USING FV 

amt-2000fv
rate of intrest10% PER month
DURATION60
future vale


 Goal seek               

loan amount

200000

pmt

rate of intrest

10%

month

21.96962129

emi(pmt)

-Rs. 10,000.00

  1.  Using Goal Seek  If emi Is-10000 find what is required month?
  2.  Using Goal Seek  If emi Is-8000 find what is required month?
  3.  Using Goal Seek  If emi Is-5000 find what is required month?


amt -1937.056707
rate of intrest 10%
month 60
future vale Rs. 150,000.00
  1. Using Goal Seek if future valu300000 find what is required amount?
  2.  Using Goal Seek if future valu500000 find what is required amount?
  3.  Using Goal Seek if future valu100000 find what is required amount?
  4.  Using Goal Seek if future valu150000 find what is required amount?


custom list 

jack
chery
sonali
sanjiv
sanjiv
salman
alibaba
shahrukh
satpal
indrajeet

 Drag jack and find what is the next

Create DropDown Controls

Create a list of employees 
Type in the following information in Column A
Bill Smith
Kaylee Wild
Helen Pulaski
Corey Haas
Angelique Riol
Select the data and name the range.  In the Name Box type: Employees
Name the sheet: Employees

Create a list of locations on another spreadsheet in the same workbook
Enter the following Locations in Column A and sort them A-Z
Ann Arbor
Brighton
Lansing
Flint
Detroit
Grand Rapids
Pontiac
Make following list 


  • jan-feb.......
  • January-February----
  • 1-2-3....100
  • 2-4-6...10
  • 1995-2000.....2025
count if

rder ID
Product
Unit Price
10248
Queso Cabrales
14
10248
Singaporean Hokkien Fried Mee
9.8
10248
Mozzarella di Giovanni
34.8
10249
Tofu
18.6
10249
Manjimup Dried Apples
42.4
10250
Jack's New England Clam Chowder
7.7
10250
Manjimup Dried Apples
42.4
10250
Louisiana Fiery Hot Pepper Sauce
16.8
10251
Gustaf's Knäckebröd
16.8
10251
Ravioli Angelo
15.6

 que1



 1. sum the unit price products with Order ID=10250 and their quantities are greater than 30 and less than 70
2.     sum the unit price products with their unit prices > 40 and their quantities >30
 3.Calculate the average of unit prices of products with Order ID =10250
4.     Count the products with Order ID=10250
5.     Count the products with Order ID=10250 and their quantities are greater than 30 and less than 70
6.     Count the products with their unit prices > 40 and their quantities >30
7.     Calculate the average of unit prices of products with Order ID =10250
4.     Count the products with Order ID=10250
5.     Count the products with Order ID=10250 and their quantities are greater than 30 and less than 70
6.     Count the products with their unit prices > 40 and their quantities >30
7.     Calculate the average of unit prices of products with Order ID =10250

Working with Equations

Enter the labels and format the labels big, bold, and centered
·         In Cell A1 type: First Name
·         In Cell B1 type: Last Name
·         In Cell C1 type: Department
·         In Cell D1 type: Salary

Add the data

Type at least five sample records. For example:

A
B
C
D
1
First Name
Last Name
Department
Salary
2
Deeter
Poohbah
Training
$34,000

 

Format the Columns

Select column D and use the Currency tool

Creating Equations

Select Cell D7 and use AutoSum to add up the SUM of the Salaries in the D Column.
Select Cell D8 and use Insert Function to calculate the AVERAGE of the Salaries.

Using IF functions
This activity compares the employee’s salary with the Average in Cell D8.
Insert the label "Compare" into cell E1
In E2, insert the IF function.
            In the Wizard, enter the following information
                        Logical Test: D2>D8
                        Value_if_true: "Above"
                        Value_if_false: "Below"
Use the Insert Function wizard to put the correct formula for the remaining cells.

Save the spreadsheet and name it: Excel Intermediate Practice 1
Intermediate Microsoft® Excel: Practice 2
Objectives:
The Learner will be able to:
1.  Explain what labels are
2.  Sort Excel data by using the labels in the header row
3. Create a Custom Sort
4. Modify the Custom Sort Order
5. Change Page Orientation

6.  Create Custom headers and footers

7.  Save the spreadsheet

Sort Data

Work with Sample Data

Open the sample Excel list, Sales.XLS
When prompted, SAVE to your Documents folder

Review the Data
A
B
C
D
E
F
1
Month
Client
Category
Service
Class
Date
2
January
Rick Towner
Private
Training
Access
1/12/2004
3
January
Darlene Davis
Private
Training
Access
1/15/2004
4
January
Hometown Community College
Educational
Training
Word
1/15/2004
5
February
Hometown Community College
Educational
Training
PowerPoint
02/05/2004
6
February
Harmony Kitchen And Bath
Corporate
Training
Excel
02/07/2004
7
February
Database Consultants
Corporate
Training
Access
02/10/2004
8
February
Bay County
Government
Training
Outlook
02/12/2004

Sort the Data

Select the entire spreadsheet and Sort the data by Month
            Did the Months sort as expected or did they sort alphabetically?
            Try the Sort again: use the CUSTOM SORT and change the Order to Custom List

Modify the Page Layout
Format the following Page Layout Options:
            Make the orientation "Landscape"
            Create a Custom Header and type a sample company a name in the center
            Create a Custom Footer with the current date on the right

Save the spreadsheet and name it: Excel Intermediate Practice 2
Intermediate Microsoft® Excel: Practice 3

Objectives:

The Learner will be able to:

1.  Enter data into a Spreadsheet

2.  Use AutoFill with labels, data and formulas

3.  Format Cell Borders and Contents

4.  Calculate the total across the rows

5.  Calculate the total for each column

6.  Use Conditional Formatting

Create a Time Sheet


A
B
C
D
E
F
G
1
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Total
2
8
8
8
8
8
8
48
3
4
5
6
7
8
8
8
8
8
8
48
8
Overtime
8

Enter the Labels in the first row
            In Cell A1 type: Monday
            Use the AutoFill handle to add Tuesday through Saturday

Calculate the Total
            In Cell G1 type: Total
            In Cell G2 create the equation: =Sum(A2:F2)
            Use the AutoFill handle to fill down that equation to G6

Calculate the Daily Total
            Enter sample data in cell A2 through F2
            Select Cell A7 and AutoSum the total
            Use the AutoFill handle to add this equation to Cells B7 through G7

Format the cells
            Make the Labels Bold
            Align all of the text Centered, in the middle of the cells

Calculate the overtime in Cell G8
            The equation in cell G8 would be: =G7-40
            Use Conditional Formatting on Cell G8

Save the spreadsheet and name it: Excel Intermediate Practice 3
Intermediate Microsoft® Excel: Practice 4
Objectives:
The Learner will be able to:
1. Enter data into an Excel Spreadsheet at least 75% of the time
2. Use Data Validation to create a DropDown Control at least 75% of the time
3.  Rename a sheet in an Excel workbook at least 75% of the time
4.  Apply Conditional Formatting at least 75% of the time
5.  Use the Fill Down command
6.  Enter data using Drop Down Controls at least 75% of the time


Select the names and name the range.  In the Name Box type: Location
Name the sheet: Location

Create a schedule on another spreadsheet in the same workbook
Rename Sheet3: Schedule
In Cell A1 Type: Employee Name
In Cell A2, use Data Validation to create a Drop Down control using the "Employees" source.
            Include an Input Message that says: "Select an employee from the list"
            Fill down Five rows
In Cell B1 Type: Location
In Cell B2, use Data Validation to create a Drop Down control using "Location" as the source
            Include and Input Message that says: "Select a Location from the list."
            Fill Down five rows

Apply Conditional Formatting
            Fill in 3 rows of Employees with locations. 
            If the Location is Pontiac, format the text to be GREEN.         Does Pontiac show up Green?  ;-)

Save the spreadsheet and name it: Excel Intermediate Practice 4
Posted by at No comments:
Subscribe to: Posts (Atom)