Last Name First Name Salary Years Job Title Bonus

Last Name First Name Salary Years Job Title Bonus
Banai Yossi $ 292,800 17 Physician $ 1,941

Barnhill Josh $ 30,000 7 Billing Clerk $ 972
Bourne Stephanie $ 246,000 15 Physician $ 3,776
Da Silva Sergio $ 83,000 9 Physician Assistant $ 1,720
Delaney Aidan $ 33,675 17 Receptionist $ 4,831
Dellamore Luca $ 41,000 17 Medical Assistant $ 1,183
Giest Jenny $ 55,000 13 Office Manager $ 9,789
Gottfried Jim $ 36,695 7 Receptionist $ 5,735
Hamilton David $ 28,000 11 Medical Assistant $ 8,197
Hensien Kari $ 24,500 1 File Clerk $ 2,808
Hezi Mor $ 352,475 24 Physician $ 8,987
Hicks Cassie $ 481,750 32 Physician $ 5,779
Hoeing Helge $ 42,000 18 Medical Assistant $ 4,270
Holliday Nicole $ 360,000 25 Physician $ 4,212
Iragavarapu Srinivas $ 324,500 21 Physician $ 8,786
Kane John $ 63,350 17 Registered Nurse $ 2,502
Kenneth Kevin $ 26,000 1 File Clerk $ 3,781
Laszlo Rebecca $ 522,875 18 Physician $ 4,899
Metters Susan $ 32,000 10 Billing Clerk $ 5,521
Moore Bobby $ 32,000 20 File Clerk $ 7,754
Moreland Barbara $ 49,000 5 Billing Clerk $ 1,856
Munson Stuart $ 32,500 16 Referral Specialist $ 6,175
Murray Billie Jo $ 29,000 15 Medical Assistant $ 6,249
Poland Carole $ 95,000 32 Nurse Practitioner $ 4,494
Popkova Darya $ 66,650 19 Registered Nurse $ 646
Posti Juha-Pekka $ 90,000 21 Registered Nurse $ 2,375
Trenary Jean $ 68,125 20 Registered Nurse $ 6,930
Valdez Rachel $ 32,475 5 Receptionist $ 11,576
Wang Jian $ 33,000 27 Referral Specialist $ 7,206
Wilson Dan $ 402,000 27 Physician $ 8,580

Use a SumIf function to find the Use countif to determine how many
total sum amount of bonuses paid employees have the certain job titles
to certain job titles below below.
Job Title Bonus Title # of Employees
Physician $ 46,960.00 Physician 8
Billing Clerk $ 8,349.00 Billing Clerk 3
Physician Assistant $ 1,720.00 Physician Assistant 1
Receptionist $ 22,142.00 Receptionist 3
Medical Assistant $ 19,899.00 Medical Assistant 4
Office Manager $ 9,789.00 Office Manager 1
File Clerk $ 14,343.00 File Clerk 3
Registered Nurse $ 12,453.00 Registered Nurse 4
Referral Specialist $ 13,381.00 Referral Specialist 2
Nurse Practitioner $ 4,494.00 Nurse Practitioner 1
How much bonus was paid to

physicians who made over Count the number of physicians who
$400,000 in salary? made over $400,000 in salary
$ 19,258.00 3
Count the number of employees

with greater than or equal to 15 Count the number of employees with
years of experience less than 10 years of experience
20 7
Referral Specialist
Find the average bonus amount for Find the average salary for job title
people with more than 15 years of specified in N30 and for people with
experience greater than 10 years of experience
$ 5,086.11 $ 32,750.00
Help - USE N30 as an absolute reference in your function
ferral Specialist
Name First Name Last Name Email ([email protected])
Yossi Banai Yossi Banai [email protected]
Josh Barnhill Josh Barnhill [email protected]
Stephanie Bourne Stephanie Bourne [email protected]
Aidan Delaney Aidan Delaney [email protected]
Luca Dellamore Luca Dellamore [email protected]
Jenny Giest Jenny Giest [email protected]
Jim Gottfried Jim Gottfried [email protected]
David Hamilton David Hamilton [email protected]
Kari Hensien Kari Hensien [email protected]
Mor Hezi Mor Hezi [email protected]
Cassie Hicks Cassie Hicks [email protected]
Helge Hoeing Helge Hoeing [email protected]
Nicole Holliday Nicole Holliday [email protected]
Srinivas Iragavarapu Srinivas Iragavarapu [email protected]
John Kane John Kane [email protected]
Kevin Kenneth Kevin Kenneth [email protected]
Rebecca Laszlo Rebecca Laszlo [email protected]
Susan Metters Susan Metters [email protected]
Bobby Moore Bobby Moore [email protected]
Barbara Moreland Barbara Moreland [email protected]
Stuart Munson Stuart Munson [email protected]
Carole Poland Carole Poland [email protected]
Darya Popkova Darya Popkova [email protected]
Juha-Pekka Posti Juha-Pekka Posti [email protected]
Jean Trenary Jean Trenary [email protected]
Rachel Valdez Rachel Valdez [email protected]
Jian Wang Jian Wang [email protected]
Mark Henry Mark Henry [email protected]
Last Name, First Name
Banai, Yossi 1. Using text to columns, break up first and last name.
Barnhill, Josh 2. Using concatenate, create a gmail for each person
Bourne, Stephanie 3. Using concatenate, make each name in Last Name, First Name format
Delaney, Aidan
Dellamore, Luca
Giest, Jenny
Gottfried, Jim
Hamilton, David
Hensien, Kari
Hezi, Mor
Hicks, Cassie
Hoeing, Helge
Holliday, Nicole
Iragavarapu, Srinivas
Kane, John
Kenneth, Kevin
Laszlo, Rebecca
Metters, Susan
Moore, Bobby
Moreland, Barbara
Munson, Stuart
Poland, Carole
Popkova, Darya
Posti, Juha-Pekka
Trenary, Jean
Valdez, Rachel
Wang, Jian
Henry, Mark
Name format
UEFA Champions League Top Goal Scorers
More Goals
than
Player Country Goals Appearances Ratio Average?
Alessandro Del Pie Italy 42 89 0.47 1
Alfredo Di Stéfano Argentina 49 58 0.84 1
Amancio Spain 21 52 0.40 0
Andriy Shevchenko Ukraine 48 90 0.53 1
Arjen Robben Netherlands 29 107 0.32 0
Claudio Pizarro Peru 21 73 0.29 0
Cristiano Ronaldo Portugal 120 154 0.78 1
David Trezeguet France 29 58 0.50 0
Didier Drogba Ivory Coast 44 92 0.48 1
Edinson Cavani Uruguay 32 53 0.60 0
Eusébio Portugal 46 63 0.73 1
Ferenc Puskás Hungary 35 41 0.85 0
Fernando Morientes Spain 30 88 0.34 0
Filippo Inzaghi Itlay 46 81 0.57 1
Francisco Gento Spain 32 88 0.36 0
Frank Lampard England 23 105 0.22 0
Gerd Müller Germany 34 35 0.97 0
Giovane Élber Brazil 25 69 0.36 0
Gonzalo Higuaín Argentina 22 75 0.29 0
Hernán Crespo Argentina 25 65 0.38 0
Jari Litmanen Finland 23 57 0.50 0
Jean-Pierre Papin France 28 37 0.76 0
José Altafini Brazil 24 28 0.86 0
José Torres Portugal 21 33 0.64 0
Kaká Brazil 30 96 0.31 0
Karim Benzema France 56 105 0.53 1
Lionel Messi Argentina 103 126 0.82 1
Luís Figo Portugal 24 103 0.23 0
Luiz Adriano Brazil 21 47 0.45 0
Marco Simone Italy 24 46 0.52 0
Mario Gómez Germany 26 44 0.59 0
Mário Jardel Brazil 25 46 0.54 0
Neymar Brazil 27 48 0.56 0
Patrick Kluivert Netherlands 29 71 0.41 0
Paul Scholes England 24 124 0.19 0
Raúl Spain 71 142 0.50 1
Rivaldo Brazil 27 73 0.37 0
Robert Lewandowski Poland 46 73 0.63 1
Robin van Persie Netherlands 25 59 0.42 0
Roy Makaay Netherlands 29 61 0.48 0
Ruud van Nistelrooy Netherlands 56 73 0.77 1
Ryan Giggs Wales 28 141 0.20 0
Samuel Eto'o Cameroon 60 78 0.77 1
Santillana Spain 22 46 0.48 0
Sergio Agüero Argentina 31 62 0.50 0
Steven Gerrard England 21 73 0.29 0
Thierry Henry France 50 112 0.45 1
Thomas Müller Germany 42 100 0.42 1
Wayne Rooney England 40 85 0.47 1
Zlatan Ibrahimović Sweden 48 120 0.40 1
Average Goals 36.68
Player Country Goals

Lionel Messi Argentina 103
Neymar Brazil 27
Wayne Rooney England 40
Cristiano Ronaldo Portugal 120
1. Using the average goals in J1, create an If Statement saying whether or not
each player scored more than the aveage number of goals
2. Use Vlookups to complete the table above.

Last Name First Name Salary Years Job Title Bonus

Uploaded by

Copyright:

Available Formats

Last Name First Name Salary Years Job Title Bonus

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Last Name First Name Salary Years Job Title Bonus

Uploaded by

Copyright:

Available Formats

Last Name First Name Salary Years Job Title Bonus

Banai Yossi $ 292,800 17 Physician $ 1,941

Wilson Dan $ 402,000 27 Physician $ 8,580

How much bonus was paid to

Count the number of employees

Player Country Goals

You might also like