CITS2401 Computer Analysis and Visualisation Semester 2, 2016
Assignment 3
Set date: 12 October 2016
Due date: Friday 5:00 pm, 4 November 2016 Total marks: 50
Assignment submission instructions:
Submit 1 Excel file via the CITS2401 page on LMS (http://www.lms.uwa.edu.au).
Name the file as: YourSurname_StudentNumber, e.g. Smith_902787.xlsm and ensure that it is macro enabled file.
In case of not following the submission guidelines, your submission may be graded zero.
Plagiarism.
All work submitted should be your own. I am sure that you agree that this is for your own good!! If you do not agree, please note that we have ways to detect plagiarism in code. Incidences of plagiarism will be taken seriously and will involve follow-up with the Head of School (unit coordinator) and consequences to academic results.
Questions
Australian Bureau of Statistics (ABS) is the statistical agency of Government of Australia.
. ABS provides the
data free of charge.
An excel sheet ‘Assignment3_DataFile.xlsx’ is provided with the population of Australia by sex, states and territories from year 1901 to 2011. The data is already loaded into the excel file.
Rename the file as required in submission guidelines. Write your student ID and Name in cell B1 and D1 respectively and perform the following tasks. All tasks are required to be solved in generic manner such that if the data is changed then results are updated accordingly.
Task 1: (2 marks)
Find the male population of Australia for each year by summing the population of all states and territories and display them in the range B13 to DH13 using cell arrays.
Task 2: (2 mark)
Find the female population of Australia for each year by summing the population of all states and territories and display them in the range B24 to DH24 using cell arrays.
Task 3: (2 mark)
Find the total population of Australia for each year by summing the male and female population for each state and each year and display the result in the cell range B27 to DH35 using cell arrays.
Task 4: (3 mark)
Find the number of females per 100 males in each state and each year and display the result in the cell range B38 to DH46 using cell arrays. If the data for any state or year is not available or producing an error then empty cell should be
The ABS provides key statistics on a wide range of economic, population,
environmental and social issues, to assist and encourage informed decision making,
research and discussion within governments and the community
shown and error should not be shown. The final values must be rounded off to closest integer. Remember the same results will be used in next tasks and if you have not rounded it properly then accuracy of next tasks will be compromised.
Task 5: (1 marks)
Find the maximum state ratio of men to women from the ratios obtained in Task 4 for each year.
Task 6: (1 marks)
Find the minimum state ratio of men to women from the ratios obtained in Task 4 for each year.
Task 7: (1 marks)
Find the number of years when women population in Australia was higher than men, as per the ratios obtained in Task 4 and display the result in cell E53. Use the results obtained in Task 4.
Task 8: (1 mark)
Find the number of years when men population in Australia was higher than women, as per the ratios obtained in Task 4 and display the result in cell E54. Use the results obtained in Task 4.
Task 9: (1 mark)
Find the number of years when population of men and women in Australia are equal, as per ratios obtained in Task 4 and display the result in cell E55. Use the results obtained in Task 4.
Task 10: (8 mark)
Plot the total population of each state for all the years. Remember the guidelines mentioned in the lectures for all rules and information of the plots.
Task 11: (6 marks)
Plot the ratios obtained in Task 4 for entire Australia for all the years. Also plot the maximum and minimum ratios as obtained in Task 5 and Task 6. Remember the guidelines mentioned in lecture for all rules and information of the plots.
Task 12: (6 marks)
Plot the distribution of entire Australian population over all the states and territories of Australia for the year 2011. Also show the percentage contribution for each state’s population on the plot. Remember the guidelines mentioned in lecture for all rules and information of the plots.
Task 13: (16 marks)
Add a button on the worksheet close to range A57:D62. Write VBA code to find the maximum men to women ratios for states from the data obtained in Task 4 for all the years. Find the states and years when maximum men to women ratios are obtained and display the results starting from the cell A57. The text of the information “Highest men to women ratio years and their states” is displayed in cell A57. The text of “Highest Ratio” and maximum men to women ratio is displayed in cell A58 and A59 respectively. The headers for table are displayed in cells A59 and B59 and data in the following rows. All results of the VBA code are displayed when the button added on the worksheet is clicked.
Remember, the code should be generic and can display all the years and states for which the men to women ratio is equal to maximum value.
Note: All tasks will be graded according to the correct accomplishment of tasks.
The sample image of worksheet is provided and your solution may look like this. However, the data shown in the image is sample data and may not be the correct result.