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 »
    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
    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

    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
        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
                                            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 !
    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
    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
    Sum of SW Sales
    Sum of HW Sales
    Sum of SW Sales
    Sum of HW Sales
    Sum of SW Sales
    Sum of HW Sales
If you drag the name field to this area
you can see data for individual people
look here too
    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
    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)
                        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
                        But do you see the need for planning when you set up worksheets now?
Page &P
    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
    April                    Be sure to total
    Total    15,500
Page &P
    Equipment Tracking Sheet
        Amount Spent                Enter in cells B4:B7: 5000, 7800, 3200, 9750
    September                    Total these amounts
    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
    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
    Connie    42,000
    Alicia    40,000
    Thomas    40,000
    Mildred    38,000
    Burt    40,000
    Total    $300,000
    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
    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

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


Tanmoy answered on Jun 30 2021
121 Votes
    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...

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here