a) To insert a row after the 10 rows you need to select row 14.
b) To select row 14 keep your mouse pointer on “14” and you will see that the mouse pointer has changed to its color to solid black.
c) Right click to select the row 14 and select the “Insert” option from the menu.
d) Select the Cell A14 and write “Average Salary” .
e) Select the cell F14 i.e. the cell under all the salary entries. This is the cell which would display the average salary.
f) Hit the “=” key which tells excel that the selected cell will have a formula which does some computation.
g) After the = write “AVERAGE (”. (You will see that as soon as you type A MS excel will give you all the available command starting with A. So instead of writing the whole command you can use your arrow keys to select the appropriate formula and hit the TAB key to select that formula along with the starting bracket.)
h) To calculate the average the range of cells having the salaries should be mentioned as the arguments of the Average command.
i) To give the range of cells, keep the cell F14 selected and select the cell F4 which contains the first salary entry and keeping the left mouse button pressed drag down to cell F13 and you will see that the average formula now has F4:F13 after the opening bracket.
j) Close the brackets and hit enter.
k) The average salary is computed in cell F14.
a) Insert a row after the row with the average salary.
b) In cell F15 i.e. the cell after the one which computes the average salary we will compute the median salary.
c) Select cell F15 and hit “=” key.
d) The command to compute the median of a range of numbers is MEDIAN in excel so write MEDIAN after the = sign in F15.
e) Now the ranges of cells containing the numbers need to be specified to the MEDIAN command.
f) Select cells F4 to F13, close the brackets and hit enter.
g) The median salary will be displayed in cell F15.
a) Select the cells F3 to F14.
b) Get to the Data tab in excel and click the sort button.
c) Select the “Expand this selection” option in the pop up and click “Sort”.
d) In the second pop up that comes up ensure that you select “Gender” (or the column label you have given for gender) as the “Sort by” value and click OK.
e) The whole data set will be sorted according to the gender.
a) Insert a row after the row which is showing the median salary and name give a label “Average salary for men”.
b) Write the command to calculate the average salary and select the range of cells which displays the salary information for men i.e. cells F9 to F13 in the sample excel sheet provided.
Repeat the above steps to calculate the median salary for men, average salary for women and median salary for women.