Great Deal! Get Instant \$10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

# 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...

1 answer below Â»
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
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
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
Answered Same Day Jun 28, 2021

## Solution

Tanmoy answered on Jun 30 2021
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    2.50%    51250
Carlson, A.    35,000.00    3    5    4.50%    36575
Dugan, J.    28,000.00    2    4    2.50%    28700
Evans, M.    45,000.00    4    6    4.50%    47025
Warden, S    38,000.00    2    4    2.50%    38950
Smith, J    67,000.00    4    7    4.50%    70015
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...
SOLUTION.PDF