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 (firstname.lastname@truman.edu)
Yossi Banai Yossi Banai Yossi.Banai@gmail.com
Josh Barnhill Josh Barnhill Josh.Barnhill@gmail.com
Stephanie Bourne Stephanie Bourne Stephanie.Bourne@gmail.com
Aidan Delaney Aidan Delaney Aidan.Delaney@gmail.com
Luca Dellamore Luca Dellamore Luca.Dellamore@gmail.com
Jenny Giest Jenny Giest Jenny.Giest@gmail.com
Jim Gottfried Jim Gottfried Jim.Gottfried@gmail.com
David Hamilton David Hamilton David.Hamilton@gmail.com
Kari Hensien Kari Hensien Kari.Hensien@gmail.com
Mor Hezi Mor Hezi Mor.Hezi@gmail.com
Cassie Hicks Cassie Hicks Cassie.Hicks@gmail.com
Helge Hoeing Helge Hoeing Helge.Hoeing@gmail.com
Nicole Holliday Nicole Holliday Nicole.Holliday@gmail.com
Srinivas Iragavarapu Srinivas Iragavarapu Srinivas.Iragavarapu@gmail.com
John Kane John Kane John.Kane@gmail.com
Kevin Kenneth Kevin Kenneth Kevin.Kenneth@gmail.com
Rebecca Laszlo Rebecca Laszlo Rebecca.Laszlo@gmail.com
Susan Metters Susan Metters Susan.Metters@gmail.com
Bobby Moore Bobby Moore Bobby.Moore@gmail.com
Barbara Moreland Barbara Moreland Barbara.Moreland@gmail.com
Stuart Munson Stuart Munson Stuart.Munson@gmail.com
Carole Poland Carole Poland Carole.Poland@gmail.com
Darya Popkova Darya Popkova Darya.Popkova@gmail.com
Juha-Pekka Posti Juha-Pekka Posti Juha-Pekka.Posti@gmail.com
Jean Trenary Jean Trenary Jean.Trenary@gmail.com
Rachel Valdez Rachel Valdez Rachel.Valdez@gmail.com
Jian Wang Jian Wang Jian.Wang@gmail.com
Mark Henry Mark Henry Mark.Henry@gmail.com
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.