1 Finding Bonuses
Explication de la t âche .
It is the end of the year and you were tasked with bonuses for all the employees that work at your office.
Your boss informed you that you have 100,000 dollars to give out but he is unsure the best way to distribute it.
He has given you some data and would like you to run some tests to see if some of his bonus ideas are reasonable or some would cost just too much.
Here is the bonus structures he would like you to test:
Each member of the full time staff would get 500$ and the rest nothing
Everyone who has a job rating of 4 or 5 would get 400$ Bonus and the rest 0$
People hired after 2010 get 2% of their salaries as bonuses and before 1.5% of their salaries as bonuses
🌶 Défi épicé . 🌶
Comment faire.
This exercise will require you to build new formulas (3) each in a new column. Each formula is a simple IF statement that will have to follow this structure:
=IF(Test, what happens if it’s true,what happens if it’s false)
Note that the commas are critical
Tests can look like this:
C2 = “Full time”
F2 > 4
The symbols you can use go as follow:
= (Equals)
> (Greater than)
< (Less than)
>= (Greater than or equals too)
<= (Less than or equals too)
<> (Not equals)
In the “What happens if X” arguments can look like this
400
G1
H2*2%
Knowing this if you still need help here is how you get started! First, give the title of the new column “Bonus 1” and go and type your if statements in the first cell after the title as follows
=IF(C2=”full time”,500,0)
After hit ENTER on your keyboard. You can populate this down really quickly by going on the fill handle and double clicking.
The second formulas and third formulas go as follows if you wish to see them :
(note that this isn't the only way to type these formulas)
Everyone who has a job rating of 4 or 5 would get 400$ Bonus and the rest 0$
=IF(F2>=4,400,0)
People hired after 2010 get 2% of their salaries as bonuses and before 1.5% of their salaries as bonuses
=IF(E2>40179,G2*2%,G2*1.5%)
This one is a little weird because we have to know what serial number is 1/1/2010 - To do that you type your date in cell following your computers date format (look at the bottom right of you computer) and change it's number format to "NUMBER" from "DATE"
Once you have these 3 collumns I'd like to figure out the total for each collumn so create a small dashboard of information on the side which would contain the totals for each collumn
To do this type
Bonus 1
Bonus 2
Bonus 3
And select the cell next to them and type =SUM(H:H) to get the total for the H collumn - Repeat this for each collumn of bonuses
