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 world all will be well :-)
Name Base Salary Performance Rating from Mgr. Years of Service Salary Increase Percentage New Salary
Anderson, C. 50,000.00 4 2
Carlson, A. 35,000.00 3 5
Dugan, J. 28,000.00 2 4
Evans, M. 45,000.00 4 6
Warden, S 38,000.00 2 4
Smith, J 67,000.00 4 7
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 more 2.50%
Performance >=3 4.50%
PLANNING Checkpoints:
How many IF statements? Warden 2.5% =if(AND(C2>=3),4.5%,IF(D2>=4,2.5%,""))
That is the key Carlson 4.5%
TIP: plan that first IF statement carefully
Belle Sales
Beautiful Belle Sales Commissions Commission 4.25% 25000 0r ove
Southwest Te
itory--2002
Last First Q1 Sales Q1 Commission Q2 Sales Q2 Commission Q3 Sales Q3 Commission Q4 Sales Q4 Commission Total Sales
Hefferin Jacob 28,365.21 29,961.47 29,865.56 33,258.41 121,450.65
Benner David 26,549.31 30,247.62 27,951.25 21,325.66 106,073.84
Michener John 37,518.24 40,341.11 34,217.33 22,364.89 134,441.57
Franklin Ralph 29,651.84 33,264.85 29,652.21 34,325.44 126,894.34
O'Keefe Elizabeth 32,146.93 31,694.55 33,284.29 21,345.87 118,471.64
Sykes Jennifer 15,577.21 28,691.33 32,103.86 29,365.21 105,737.61
Hamilton Linda 23,421.94 27,481.15 22,083.10 18,651.75 91,637.94
Villeneuve Jean 23,514.88 37,541.02 19,073.80 24,557.64 104,687.34
Saunders He
ert 35,214.36 31,247.58 36,743.23 36,211.97 139,417.14
Della Penna Albert 24,782.51 33,216.54 15,436.80 26,359.92 99,795.77
Worthit Hart Lee 15,000.00 20,000.00 20,000.00 15,000.00 70,000.00
291,742.43 - 0 343,687.22 - 0 300,411.43 - 0 282,766.76 - 0 1,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 Sales Pivot Tables are wonderful - instead of creating a
2001 Sales Database multitude of charts, graphs, you can quickly play with
data for answers. They are easy to do!
Record Name Years Employed Position Region State SW Sales HW Sales BUT, it may take some a little longer to really visualize
1 Smith, Bob 3 Sales Rep Western CA 200,000 180,000 how you want the data to be seen. This just takes
2 Hu
s, Daniel 4 Telemarketer Eastern FL 340,000 230,000 practice,more practice and more practice.
3 Smith, Michael 5 Telemarketer Eastern MA 123,000 230,000
4 Watson, Tom 8 Sales Rep Eastern MA 230,000 340,000 You Create pivot tables from databases
5 William, Michael 3 Sales Rep Eastern FL 120,000 340,000 Pivot tables let you see data in variety of ways
6 Martinez, Carlos 4 Senior Sales Rep Eastern FL 450,000 450,000 Pivot tables automatically summarize the data
7 Wilson, Bernie 1 Sales Rep Central IL 120,000 170,000
8 Thomas, Bill 2 Sales Rep Central IL 230,000 120,000 How to do:
9 Cain, Mary 5 Senior Sales Rep Central IL 234,000 560,000 Click anywhere in database
10 Zain, Beth 7 Senior Sales Rep Central IL 340,000 800,000 DATA MENU-PIVOT TABLE
11 Alviso, Alex 9 Senior Sales Rep Western CA 450,000 340,000 Dialog boxes appear - STEP 3 is the KEY ONE
12 Brown, Bill 3 Telemarketer Western CA 546,000 120,000 Look at the bottom: layout and options
13 Richards, Paul 4 Telemarketer Western WA 234,000 546,000 Look at options and you see all the functions: sum,
14 Cray, Zip 6 Telemarketer Western WA 900,000 780,000 average 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 Steps lists the regions with a
ow headings on the left side
Pivot Table on Pivot Table1 tab summarizes the sales for each region as data
NOW, choose existing worksheet and click A20
See the pivot table, play with the a
ows
Note the pivot table toolba
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
Region Data Total Click on the down a
owheads and see how you
Central Sum of SW Sales 924000 can show information just for certain areas.
Sum of HW Sales 1650000
Eastern Sum of SW Sales 1263000
Sum of HW Sales 1590000
Western Sum of SW Sales 2330000
Sum of HW Sales 1966000
Total Sum of SW Sales 4517000
Total Sum of HW Sales 5206000
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 a
owhead 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
Region Total
Central 1650000
Eastern 1590000
Western 1966000
Grand Total 5206000
PivotTable2
Name (All)
Region Data Total
Central Sum of SW Sales 924000
Sum of HW Sales 1650000
Eastern Sum of SW Sales 1263000
Sum of HW Sales 1590000
Western Sum of SW Sales 2330000
Sum of HW Sales 1966000
Total Sum of SW Sales 4517000
Total Sum of HW Sales 5206000
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 College Working with multiple sheets:
XXXXXXXXXXFederal Grant and Budget Tracking This is just too, too cool!
Why do I want to know this?
Grant Amount $300,000 WELL, what if you had to have a summary sheet of data fo
Today's Date 10/9/07 individual worksheets? This is the way to go!
Look to the left - what you see there is a summary setup
Category Budget
Allocation Year-to-Date
Spent Available
Balance Look at the items under category - these would be individual
Postage 5,000 worksheets with data
Equipment 40,000 The KEY: set up the worksheets co
ectly:
Instructional Materials 50,000 summary sheet here is called the maste
Mileage 5,000 we have two of the category worksheets - postage and equipment
Salaries Complete the Postage and Equip worksheets first
Connie 42,000 (these are ongoing worksheets - meaning we would use monthly totals
Alicia 40,000 each month to fill in the data on these sheets.)
Thomas 40,000 Do this now
Mildred 38,000 NOW, let's coordinate the data between the master,
Burt 40,000 postage 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 ba
Click on postage tab (formula bar shows postage!
(This means the formula went to the postage worksheet)
Click on Cell B16 (postage sheet total)
Ente
Check it out - the result went to the maste
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 Spent Enter in cells B4:B7: 3000, 2500, 4000, 1000 and total these numbers
September 3,000
October 2,500
November 4,000
December 1,000
January 5,000
Fe
uary
March
April Be sure to total
May
June
July
August
Total 15,500
&A
Page &P
Equip
Equipment Tracking Sheet
Amount Spent Enter in cells B4:B7: 5000, 7800, 3200, 9750
September Total these amounts
Octobe
Novembe
Decembe
January
Fe
uary
March
April
May
June
July
August
Total
Move_Copy
Folsom Technical College
XXXXXXXXXXFederal Grant and Budget Tracking
What if you wanted to copy or move a spreadsheet
Grant Amount $300,000 to another or new work book?
Today's Date 10/9/07
Ho, ho, ho….too, too, easy
Category Budget
Allocation Year-to-Date
Spent Available
Balance
Postage 5,000 Right Click on the sheet tab you want to move
Equipment 40,000 or copy. Then follow the dialog boxes.
Instructional Materials 50,000 If you select a new workbook, a new work will appear.
Mileage 5,000
Salaries
Connie 42,000
Alicia 40,000
Thomas 40,000
Mildred 38,000
Burt 40,000
Total $300,000
Homework1
Product Month Region Revenue Create a pivot table that will answer the following questions:
House Blend April Pacific North West 36,580 what is the total revenue for Columbian coffee?
House Blend May Pacific North West 45,715 what is the total revenue for Dark Roast coffee?
House Blend June Pacific North West 24,587 what is the total revenue for Decaffeinataed coffee?
House Blend April Mid-West 65,241 what is the total revenue for House Blend coffee?
House Blend May Mid-West 36,547 what is the april revenue for each
and of coffee?
House Blend June Mid-West 85,241
House Blend April Southwest 22,145
House Blend May Southwest 35,548
House Blend June Southwest 36,578
House Blend April Rocky Mtn. States 54,012
House Blend May Rocky Mtn. States 56,784
House Blend June Rocky Mtn. States 32,564
Dark Roast April Pacific North West 58,874 TIP: pivot tables take planning
Dark Roast May Pacific North West 36,524 You can use the PAGE area in setup - think big category
Dark Roast June Pacific North West 25,413 You want to set this up so that you can easily access monthly information
Dark Roast April Mid-West 66,325 You can copy and paste the information
Dark Roast May Mid-West 60,024
Dark Roast June Mid-West 51,220
Dark Roast April Southwest 21,547
Dark Roast May Southwest 55,362
Dark Roast June Southwest 54,785
Dark Roast April Rocky Mtn. States 35,604
Dark Roast May Rocky Mtn. States 35,874
Dark Roast June Rocky Mtn. States 65,241
Columbian April Pacific North West 25,487
Columbian May Pacific North West 66,321
Columbian June Pacific North West 65,012
Columbian April Mid-West 32,548
Columbian May Mid-West 62,234
Columbian June Mid-West 35,568
Columbian April Southwest 62,105
Columbian May Southwest 26,425
Columbian June Southwest 26,854
Columbian April Rocky Mtn. States 36,987
Columbian May Rocky Mtn. States 65,842
Columbian June Rocky Mtn. States 45,210
Decaffineated April Pacific North West 55,146
Decaffineated May Pacific North West 36,650
Decaffineated June Pacific North West 25,456
Decaffineated April Mid-West 65,012
Decaffineated May Mid-West 65,840
Decaffineated June Mid-West 29,998
Decaffineated April Southwest 22,048
Decaffineated May Southwest 62,304
Decaffineated June Southwest 55,021
Decaffineated April Rocky Mtn. States 25,045
Decaffineated May Rocky Mtn. States 65,354
Decaffineated June Rocky Mtn. States 55,460
Homework2
Computer Sales, SouthWest
Sales Comparison
For years 2002 and 2003
Last Name First Name Date of Hire Gender Position Sales Te
itory 2003 Sales 2002 Sales % Increase
Anderson Bruce 8/12/90 M Telemarketing AZ 1,256,874 1,358,705 8.1%
Baker Jane 9/17/97 F Outside Sales CA 998,564 1,048,057 5.0%
Campbell William 10/22/93 M Outside Sales AZ 1,053,687 1,255,648 19.2%
Downing Mary 11/28/96 F Telemarketing CA 1,574,865 1,595,846 1.3%
Eachin John 4/10/00 M e-commerce CA 984,248 1,024,875 4.1%
Franklin Anne 8/6/99 F Outside Sales AZ 1,002,548 1,125,087 12.2%
Gregg Je
y 9/20/95 M Outside Sales CA 874,510 950,465 8.7%
Highland Cheryl 10/17/94 F e-commerce AZ 1,005,487 1,154,870 14.9%
Create pivot tables to determine the effectiveness of the sales methods (positions tell you the method)
Show all te
itories 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 Name First Name Street City State ZIP Tele. No. Soc. Sec. No.
Coffey Janet 3857 So. Dressel Drive Palo Alto CA 94333 XXXXXXXXXX XXXXXXXXXX
Martin Jeffrey 2306 N.W. Brynwood Lane Redwood City CA 94332 XXXXXXXXXX XXXXXXXXXX
Yeuan Charng 3857 Maple Lane Menlo Park CA 94312 XXXXXXXXXX XXXXXXXXXX
Karvandi Farideh 8585 No. Perez Road Palo Alto CA 94333 XXXXXXXXXX XXXXXXXXXX
Edmondson Kathy 36500 Elm Lane Menlo Park CA 94311 XXXXXXXXXX XXXXXXXXXX
Pe
y David 10233 Forest Hill Drive Palo Alto CA 94333 XXXXXXXXXX XXXXXXXXXX
Eisman Joan 546 To
eyville Drive Redwood City CA 94332 XXXXXXXXXX XXXXXXXXXX
Eisman Destiny 2983 Alder Circle Redwood City CA 94332 XXXXXXXXXX XXXXXXXXXX
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
Smith Kim 775 Bristol Rd Denver CO 81223 XXXXXXXXXX XXXXXXXXXX
Smith Kim 895 Grant Avenue Phoenix AZ 85303 XXXXXXXXXX XXXXXXXXXX
Smith Kim 5873 River Road Denver CO 81234 XXXXXXXXXX XXXXXXXXXX
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