Excel files
HR_NestedIF This set of Worksheets covers: One problem Assignments due: HR Nested If Don't be puzzled - take your time and This is a typical HR type worksheet you could be asked to do in the business worldall will be well :-) NameBase SalaryPerformance Rating from Mgr.Years of ServiceSalary Increase PercentageNew Salary Anderson, C.50,000.0042 Carlson, A.35,000.0035 Dugan, J.28,000.0024 Evans, M.45,000.0046 Warden, S38,000.0024 Smith, J67,000.0047 Human Resources must prepare a report showing the following: It is being recommend that anyone with 4 years or more of service gets a 2.5% salary increase and anyone who gets a performance rating of 3 or over gets a 4.5% salary increase The maximum salary increase is limited to 4.5% 4 yrs or more2.50% Performance >=34.50% PLANNINGCheckpoints: How many IF statements?Warden 2.5%=if(AND(C2>=3),4.5%,IF(D2>=4,2.5%,"")) That is the keyCarlson 4.5% TIP: plan that first IF statement carefully Belle Sales Beautiful Belle Sales CommissionsCommission4.25%25000 0r over Southwest Territory--2002 LastFirstQ1 SalesQ1 CommissionQ2 SalesQ2 CommissionQ3 SalesQ3 CommissionQ4 SalesQ4 CommissionTotal Sales HefferinJacob28,365.2129,961.4729,865.5633,258.41121,450.65 BennerDavid26,549.3130,247.6227,951.2521,325.66106,073.84 MichenerJohn37,518.2440,341.1134,217.3322,364.89134,441.57 FranklinRalph29,651.8433,264.8529,652.2134,325.44126,894.34 O'KeefeElizabeth32,146.9331,694.5533,284.2921,345.87118,471.64 SykesJennifer15,577.2128,691.3332,103.8629,365.21105,737.61 HamiltonLinda23,421.9427,481.1522,083.1018,651.7591,637.94 VilleneuveJean23,514.8837,541.0219,073.8024,557.64104,687.34 SaundersHerbert35,214.3631,247.5836,743.2336,211.97139,417.14 Della PennaAlbert24,782.5133,216.5415,436.8026,359.9299,795.77 WorthitHart Lee15,000.0020,000.0020,000.0015,000.0070,000.00 291,742.43- 0343,687.22- 0300,411.43- 0282,766.76- 01,218,607.84 Total Yrly Sales:1,218,607.84 Total Yrly Comm:0 Sheet3 Agenda This set of Worksheets covers: Pivot Tables Working with Master Sheets Copying spreadsheets Assignments due: Homework only Back to Power users Back to Power users PivotTables National Computing SalesPivot Tables are wonderful - instead of creating a 2001 Sales Databasemultitude of charts, graphs, you can quickly play with data for answers. They are easy to do! RecordNameYears EmployedPositionRegionStateSW SalesHW SalesBUT, it may take some a little longer to really visualize 1Smith, Bob3Sales RepWesternCA200,000180,000how you want the data to be seen. This just takes 2Hubbs, Daniel4TelemarketerEasternFL340,000230,000practice,more practice and more practice. 3Smith, Michael5TelemarketerEasternMA123,000230,000 4Watson, Tom8Sales RepEasternMA230,000340,000You Create pivot tables from databases 5William, Michael3Sales RepEasternFL120,000340,000Pivot tables let you see data in variety of ways 6Martinez, Carlos4Senior Sales RepEasternFL450,000450,000Pivot tables automatically summarize the data 7Wilson, Bernie1Sales RepCentralIL120,000170,000 8Thomas, Bill2Sales RepCentralIL230,000120,000How to do: 9Cain, Mary5Senior Sales RepCentralIL234,000560,000Click anywhere in database 10Zain, Beth7Senior Sales RepCentralIL340,000800,000DATA MENU-PIVOT TABLE 11Alviso, Alex9Senior Sales RepWesternCA450,000340,000Dialog boxes appear - STEP 3 is the KEY ONE 12Brown, Bill3TelemarketerWesternCA546,000120,000Look at the bottom: layout and options 13Richards, Paul4TelemarketerWesternWA234,000546,000Look at options and you see all the functions: sum, 14Cray, Zip6TelemarketerWesternWA900,000780,000average etc. We will stay with SUM for now. Layout- click on this is where we lay out a pivot table To the right you see field names - the middle is the design area Drag REGION to Row Drag HW SALES and SW SALES to middle area - under the word COLUMN What this does: Pictures of the Stepslists the regions with arrow headings on the left side Pivot Table on Pivot Table1 tabsummarizes the sales for each region as data NOW, choose existing worksheet and click A20 See the pivot table, play with the arrows Note the pivot table toolbar not there - view toolbars-pivot table Let’s modify this table click somewhere in table Click on wizard - 3rd icon from left Drag STATE to column heading and Finish Check out toolbar - especially refresh ! PivotTable1 RegionDataTotalClick on the down arrowheads and see how you CentralSum of SW Sales924000can show information just for certain areas. Sum of HW Sales1650000 EasternSum of SW Sales1263000 Sum of HW Sales1590000 WesternSum of SW Sales2330000 Sum of HW Sales1966000 Total Sum of SW Sales4517000 Total Sum of HW Sales5206000 Note - if you should select just the sum of HW Sales, you can not go back and select anything else - What to do? Easy - use the Pivot Table toolbar - first, click on a data number in the total column then go to the pivot table toolbar, click on the down arrowhead by the words Pivot Table and select Wizard you will see you are on Step 3, select layout and drag the SW Sales to the Data Area Sum of HW Sales RegionTotal Central1650000 Eastern1590000 Western1966000 Grand Total5206000 PivotTable2 Name(All) RegionDataTotal CentralSum of SW Sales924000 Sum of HW Sales1650000 EasternSum of SW Sales1263000 Sum of HW Sales1590000 WesternSum of SW Sales2330000 Sum of HW Sales1966000 Total Sum of SW Sales4517000 Total Sum of HW Sales5206000 PivotChart Sum of SW Sales Central Sum of HW Sales Sum of SW Sales Eastern Sum of HW Sales Sum of SW Sales Western Sum of HW Sales If you drag the name field to this area you can see data for individual people look here too Total 924000 1650000 1263000 1590000 2330000 1966000 MASTER Folsom Technical CollegeWorking with multiple sheets: 1997 - 1998 Federal Grant and Budget TrackingThis is just too, too cool! Why do I want to know this? Grant Amount$300,000WELL, what if you had to have a summary sheet of data for Today's Date10/9/07individual worksheets? This is the way to go! Look to the left - what you see there is a summary setup CategoryBudget AllocationYear-to-Date SpentAvailable BalanceLook at the items under category - these would be individual Postage5,000worksheets with data Equipment40,000The KEY: set up the worksheets correctly: Instructional Materials50,000summary sheet here is called the master Mileage5,000we have two of the category worksheets - postage and equipment SalariesComplete the Postage and Equip worksheets first Connie42,000(these are ongoing worksheets - meaning we would use monthly totals Alicia40,000each month to fill in the data on these sheets.) Thomas40,000Do this now Mildred38,000NOW, let's coordinate the data between the master, Burt40,000postage and equipment worksheets Total$300,000 EASY: You may want to print this sheet out to follow the steps below Click in cell C8 - master sheet Type = in the formula bar Click on postage tab (formula bar shows postage! (This means the formula went to the postage worksheet) Click on Cell B16 (postage sheet total) Enter Check it out - the result went to the master Add another number to the postage sheet and see what the master does See - this automatically updates the master - COOL! More practice - work with Equipment sheet Complete the equipment sheet Click in cell 9 - master sheet Type = in the formula bar, click on equipment tab, click on cell B16 COOL!!!! But do you see the need for planning when you set up worksheets now? &A Page &P Postage Postage Tracking Sheet Amount SpentEnter in cells B4:B7: 3000, 2500, 4000, 1000 and total these numbers September3,000 October2,500 November4,000 December1,000 January5,000 February March AprilBe sure to total May June July August Total15,500 &A Page &P Equip Equipment Tracking Sheet Amount SpentEnter in cells B4:B7: 5000, 7800, 3200, 9750 SeptemberTotal these amounts October November December January February March April May June July August Total Move_Copy Folsom Technical College 1997 - 1998 Federal Grant and Budget Tracking What if you wanted to copy or move a spreadsheet Grant Amount$300,000to another or new work book? Today's Date10/9/07 Ho, ho, ho….too, too, easy CategoryBudget AllocationYear-to-Date SpentAvailable Balance Postage5,000Right Click on the sheet tab you want to move Equipment40,000or copy. Then follow the dialog boxes. Instructional Materials50,000If you select a new workbook, a new work will appear. Mileage5,000 Salaries Connie42,000 Alicia40,000 Thomas40,000 Mildred38,000 Burt40,000 Total$300,000 Homework1 ProductMonthRegionRevenueCreate a pivot table that will answer the following questions: House BlendAprilPacific North West36,580what is the total revenue for Columbian coffee? House BlendMayPacific North West45,715what is the total revenue for Dark Roast coffee? House BlendJunePacific North West24,587what is the total revenue for Decaffeinataed coffee? House BlendAprilMid-West65,241what is the total revenue for House Blend coffee? House BlendMayMid-West36,547what is the april revenue for each brand of coffee? House BlendJuneMid-West85,241 House BlendAprilSouthwest22,145 House BlendMaySouthwest35,548 House BlendJuneSouthwest36,578 House BlendAprilRocky Mtn. States54,012 House BlendMayRocky Mtn. States56,784 House BlendJuneRocky Mtn. States32,564 Dark RoastAprilPacific North West58,874TIP: pivot tables take planning Dark RoastMayPacific North West36,524You can use the PAGE area in setup - think big category Dark RoastJunePacific North West25,413You want to set this up so that you can easily access monthly information Dark RoastAprilMid-West66,325You can copy and paste the information Dark RoastMayMid-West60,024 Dark RoastJuneMid-West51,220 Dark RoastAprilSouthwest21,547 Dark RoastMaySouthwest55,362 Dark RoastJuneSouthwest54,785 Dark RoastAprilRocky Mtn. States35,604 Dark RoastMayRocky Mtn. States35,874 Dark RoastJuneRocky Mtn. States65,241 ColumbianAprilPacific North West25,487 ColumbianMayPacific North West66,321 ColumbianJunePacific North West65,012 ColumbianAprilMid-West32,548 ColumbianMayMid-West62,234 ColumbianJuneMid-West35,568 ColumbianAprilSouthwest62,105 ColumbianMaySouthwest26,425 ColumbianJuneSouthwest26,854 ColumbianAprilRocky Mtn. States36,987 ColumbianMayRocky Mtn. States65,842 ColumbianJuneRocky Mtn. States45,210 DecaffineatedAprilPacific North West55,146 DecaffineatedMayPacific North West36,650 DecaffineatedJunePacific North West25,456 DecaffineatedAprilMid-West65,012 DecaffineatedMayMid-West65,840 DecaffineatedJuneMid-West29,998 DecaffineatedAprilSouthwest22,048 DecaffineatedMaySouthwest62,304 DecaffineatedJuneSouthwest55,021 DecaffineatedAprilRocky Mtn. States25,045 DecaffineatedMayRocky Mtn. States65,354 DecaffineatedJuneRocky Mtn. States55,460 Homework2 Computer Sales, SouthWest Sales Comparison For years 2002 and 2003 Last NameFirst NameDate of HireGenderPositionSales Territory2003 Sales2002 Sales% Increase AndersonBruce8/12/90MTelemarketingAZ1,256,8741,358,7058.1% BakerJane9/17/97FOutside SalesCA998,5641,048,0575.0% CampbellWilliam10/22/93MOutside SalesAZ1,053,6871,255,64819.2% DowningMary11/28/96FTelemarketingCA1,574,8651,595,8461.3% EachinJohn4/10/00Me-commerceCA984,2481,024,8754.1% FranklinAnne8/6/99FOutside SalesAZ1,002,5481,125,08712.2% GreggJerry9/20/95MOutside SalesCA874,510950,4658.7% HighlandCheryl10/17/94Fe-commerceAZ1,005,4871,154,87014.9% Create pivot tables to determine the effectiveness of the sales methods (positions tell you the method) Show all territories for the position of Internet sales Agenda This set of Worksheets covers: Review of Sorting and Filtering data Creating databases This is where we have fun! When you do the Macro, you will really say - COOL! Back to Power http://westpro.com/devel/Karina/powerUsers.html ReviewSort Last NameFirst NameStreetCityStateZIPTele. No.Soc. Sec. No. CoffeyJanet3857 So. Dressel DrivePalo AltoCA94333555-5837111-22-3333 MartinJeffrey2306 N.W. Brynwood LaneRedwood CityCA94332555-9287444-55-6666 YeuanCharng3857 Maple LaneMenlo ParkCA94312555-3859222-33-4444 KarvandiFarideh8585 No. Perez RoadPalo AltoCA94333555-8283777-88-9999 EdmondsonKathy36500 Elm LaneMenlo ParkCA94311555-1728111-00-2222 PerryDavid10233 Forest Hill DrivePalo AltoCA94333555-1827333-44-5555 EismanJoan546 Torreyville DriveRedwood CityCA94332555-0928666-77-8888 EismanDestiny2983 Alder CircleRedwood CityCA94332555-8774999-00-1111 Sorting: Click in any cell Data Menu - Sort and sort by last name UNDO - either ctrl + Z, Undo Icon or Edit menu - undo Add these names to the list: let's do it the easy way, copy and paste Select the information, do a copy and paste at cell A11 SmithKim775 Bristol RdDenverCO81223555-1162123-55-6789 SmithKim895 Grant AvenuePhoenixAZ85303555-1038456-32-9876 SmithKim5873 River RoadDenverCO81234555-1829612-23-4567 Look at the last names, you have several people with the same last name and the same first name and so on Sorting when you have identical information - First sort by last name, when last names are identical, THEN sort by first name AND when last and first