Data Analysis and Visualization using Excel: A Practical Guide

Desklib provides solved assignments and past papers to help students succeed.

Using Numeracy, Data and IT
Assessment

Contents
Part 1................................................................................................................................................3
Question 1....................................................................................................................................3
Question 2....................................................................................................................................5
Part 2..............................................................................................................................................10
References......................................................................................................................................22
List of figures
Figure 1: Question 1........................................................................................................................3
Figure 2: Question 2........................................................................................................................5
Figure 3: Data Tab...........................................................................................................................6
Figure 4: Shorting option from A-Z and Z-A..................................................................................6
Figure 5: GGG Consulting spreadsheet after shorting from A-Z....................................................6
Figure 6: List to display the First name of the employee who worked less than 35 hours in a
week.................................................................................................................................................7
Figure 7: 3-D Column graph for the Net Amount...........................................................................8
Figure 8: List of replications............................................................................................................8
Figure 9: Spreadsheet for students spending.................................................................................10
Figure 10: Developed excel sheet for students monthly spending................................................10
Figure 11: Developed excel sheet for students monthly spending and total spending on different
sections..........................................................................................................................................11
Figure 12: Excel sheet showing calculated Mean..........................................................................12
Figure 13: Excel sheet showing calculated Median......................................................................13
Figure 14: Excel sheet showing calculated Standard Deviation....................................................14
Figure 15: 3-D Column graph for the expenses over Food...........................................................15
Figure 16: Column graph for the expenses over Clothing.............................................................15
Figure 17: Column graph for the expenses over Entertainmente..................................................16
Figure 18: Column graph for the expenses over Charitable Giving..............................................17

Figure 19: Sheet for Scatter diagram.............................................................................................18
Figure 20: Scatter diagram for Food..............................................................................................19
Figure 21: Scatter diagram for Clothing........................................................................................19
Figure 22: Scatter diagram for Entertainment...............................................................................20
Figure 23: Scatter diagram for Charitable Giving.........................................................................21

Part 1
Question 1
Solution: Give,
Total no. of students = 20
No. of students having 14 marks = 2
No. of students having 15 marks = 5
No. of students having 16 marks = 4
No. of students having 17 marks = 4
No. of students having 18 marks = 5
Hence, the marks scored by 20 students = 14, 14, 15, 15, 15, 15, 15, 16, 16, 16, 16, 17, 17, 17,
17, 18, 18, 18, 18, 18
Now,
We know that, Mean = Total ∑ of data
Total number of data ------------------equation (i)
i.e. Mean = Sum of all the data/ Total no. of data (Helmenstine, 2019).
Figure 1: Question 1

So, calculating the mean of total marks scored by 20 students using equation (i), we have;
Mean of marks = The total ∑ of scores
Total number of students
= ( 14∗2 )+ ( 15∗5 )+ (16∗4 ) + ( 17∗4 ) +(18∗5)
20
= 28+75+64+ 68+90
20
= 325
20 = 16.25
From, the above calculation, mean of all the marks of 20 students is 16.25
Thus, marks less than 16.25 = 14, 15, 16
Hence, No of students with marks less than 16.25 = 2+5+4
= 11
Hence, Total no. of students who will retake the test is 11 as there marks is less than 16.25 (mean
of marks).

Question 2
Solution:
1 (a): Steps to short given spreadsheet in alphabetical and ascending order of the First name are
as follow:
Step I: Open the spreadsheet or excel sheet on your computer.
Step II: Make the top header row of the spreadsheet different from others such as making it bold,
putting the text with a different colour so that it will become different from other data which
needs to be sorted in alphabetical order.
Step III: Remove blank cell if any present in the column “First Name” from the spreadsheet.
Step IV: Click on the top header row of the column named “First Name” in the spreadsheet to
select the complete column of “First Name”.
Step V: Click on “Data” from the option showing on top of the spreadsheet to open “Data Tab”.
Step VI: Search for the short & filter options from the “Data Tab” opened.
Figure 2: Question 2

Figure 3: Data Tab
Step VII: Click on A Z button in order to short all the details in alphabetical order from A to Z
and in the same way click on Z A in order to short all the data from Z to A (Cheusheva, 2019).
Figure 4: Shorting option from A-Z and Z-A
Figure 5: GGG Consulting spreadsheet after shorting from A-Z
1 (b): Steps to create the list to display the First name of the employee who worked less than 35
hours in a week are shown below:

Step I: Open the spreadsheet first.
Step II: Select the table which needs to be filtered.
Step III: Then Click on “Data” to open the data tab.
Step IV: Then click on the “Filter” option to enable the filter on the selected table.
Step V: Go on the “Hr Worked” column and uncheck “Select All” option.
Step VI: Then check the box for “Less than 35” from the options.
Step VII: Final list of details which worked less than 35 hours a week will be shown as below
(Benlcollins, 2019).
Figure 6: List to display the First name of the employee who worked less than 35 hours in a week
1 (c): In order to calculate the total number of people who earned above the given Average
amount, we have to use count formula.
i.e. “=COUNTIF(H4:H18,">530.80")”
This will calculate the result = 9
Thus, a total of 9 people earned above the given Average amount.
1 (d): The Net Amount can be shown better using 3-D Column graph. It is shown below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
£0.00
£100.00
£200.00
£300.00
£400.00
£500.00
£600.00
£700.00
£800.00
£382.50
£652.80
£374.00£382.50
£433.50
£624.75£637.50
£612.00
£765.00
£595.00£595.00
£765.00
£309.40
£547.40
£285.60
Net Amount
Figure 7: 3-D Column graph for the Net Amount
1 (e): From the above-given spreadsheet, replication has been used in column C, D, E, F, G and
H.
Figure 8: List of replications
1 (f): In the cell H20, Grand Total has been shown for the Net Amount which can be calculated
using the following formula: =SUM(H4: H18)

Question 2:
2 (a): In order to count a total number of females (F), we can use the following formula:
=COUNTIF(C4:C18,"F")
= 8
Thus, the total number of females is 8.
2 (b): In order to count a total number of records with an hourly rate less than £25, we can use
the following formula:
=COUNTIF(D4:D18, "<25")
= 11
Thus, 11 records have an hourly rate of less than £25.
2 (c): In order to count a total number of surnames with the letter ‘p’ in them, we can use the
following formula (Azbel, 2018):
=COUNTIF(B4:B18,"*p*")
= 3
Thus, 3 people have surnames with the letter ‘p’ in them.
2 (d): In order to calculate the sum of Net Amount for the females who earned a Net Amount
greater than or equal to £495, we have to follow below-given steps:
Step I: Apply the filter to select females first.
Step: Apply the filter to select the females with Net Amount greater than or equal to £495.
Step III: Use the formula to calculate the total sum of the shorted list (Gogia, 2019):
i.e. =SUM(H9,H13,H14)
= £1,814.75
2 (e): In order to find ‘Messi’ and corresponding Deduction, we will use the following formula:

i.e. =VLOOKUP("Messi", B4:H18, 6, 0) = £76.50
Part 2
Solution: The excel sheet developed for the above spreadsheet has been shown below:
Figure 10: Developed an excel sheet for students monthly spending
Figure 9: Spreadsheet for students spending

From above sheet, monthly income of different students has been including all the details of their
expenses such as Food, Clothing, Entertainment and Charitable Giving along with their marital
status and location or campus where they live. It has been done in order to calculate the average
expense of students on different activities. It helps in the detailed analysis of different activities
which students performed as well as areas where they expense more in comparison with others
with the help of graphs as well as tables. All these help in the development of Managerial report
in order to check the trends followed by students for expenses over the different section as well
as manage future activities and expenses. The managerial report has been used by multiple
companies and industries in order to look over the records and their analysis in a better way for
future progress (Dunn, 2018).
Detailed analysis of the above monthly expense of students has been given below:
(a): From the above table it can be calculated as,
i. Total
Total expense on Food = £2895
Total expense on Clothing = £1950
Total expense on Entertainment = £980
Total expense on Charitable Giving = £150