Paper, Order, or Assignment Requirements

Staff Data

Start with the Excel workbook (spreadsheet) Staff Data – start.xlsx.

Create a binary encoding in column I for any male employee over 50. The column label in I1 for this encoding should be titled “Males > 50”. Create a function in I2 that can be copied down throughout the range that returns a 1 if both are of these conditions (i.e. Gender is M and Age is greater than 50 years of age) are true. If both of these conditions are not true, then return a 0. Though this can be performed various ways, utilize the IF and AND functions.

Create a binary encoding in column J for any female who is 45 or under. The column label in J1 for this encoding should be titled “Female and Age <= 45”. Create a 1 if both are true and a 0 otherwise. Though this can be performed various ways, utilize the IF and AND functions.

Create a binary encoding in column K if an employee is either a male that is over the age of 50 or is a female who is 45 or under. The column heading for K1 should be “Male and Age > 50” or “Female and Age <= 45.” Hint: Utilize the information that you have already encoded in columns I and J along with the logical functions (i.e. IF and OR).

In M1:O7 there is a preformatted range. In cells N2 and O2, calculate the average age and salary for the entire staff shown in the range A1:G359. In cells N3 and O3, calculate the average age and salary for just the males shown in the range A1:G359. In cells N4 and O4, calculate the average age and salary for just the females shown in the range A1:G359. In cells N5 and O5, calculate the average age and salary for the first binary encoded column in column I. In cells N6 and O6, calculate the average age and salary for the second binary encoded column in column J. Finally, in cells N7 and O7, calculate the average age and salary for the third binary encoded column in column K. Format the range N2:N7 so it shows only one decimal. Format range O2:O7 as an accounting format showing two decimals.