Numeracy, Data & IT: Excel Formulas and Analysis

This document provides a comprehensive guide on numeracy, data analysis, and IT skills. It covers topics such as fractions, percentages, averages, ratios, and excel formulas. The document also includes insights into analyzing Olympic Games medals data and highlights the countries that performed the best. It is a valuable resource for individuals looking to enhance their IT skills and excel in data analysis.

USING NUMERACY,
DATA & IT

Table of Contents
INTRODUCTION...........................................................................................................................1
QUESTION 1...................................................................................................................................1
a. Explanation of numerator.........................................................................................................1
b. Explanation of denominator.....................................................................................................1
QUESTION 2...................................................................................................................................1
Expressing 24 / 40 and 18 / 42 in simplest forms........................................................................1
QUESTION 3...................................................................................................................................1
a. Expressing the fractions 2 / 3, 3 / 4 and 5 / 6 equivalent with a denominator of 12................1
b. Making up the two thirds of the remainder..............................................................................2
QUESTION 4...................................................................................................................................2
Price of each pair of the running shoes........................................................................................2
QUESTION 5...................................................................................................................................2
a. What is 240.50 * 19.54............................................................................................................2
b. Writing the number 52100 as the power of 10........................................................................2
QUESTION 6...................................................................................................................................3
a. Total saving made by Patty and her siblings............................................................................3
b. Average saving per person.......................................................................................................3
QUESTION 7...................................................................................................................................3
a. 3 / 4 – 7 / 9 + 2 / 3....................................................................................................................3
b. Largest number from all the values.........................................................................................3
QUESTION 8...................................................................................................................................3
The percentage of men that said no.............................................................................................3
QUESTION 9...................................................................................................................................4
The latest time that Annabelle can leave home...........................................................................4
QUESTION 10.................................................................................................................................4
Analysis of the box which is heavier...........................................................................................4
QUESTION 11.................................................................................................................................4
a. The country with lowest number of overall medals.................................................................4
b. The country or countries which competed the least number of games....................................4

c. The mode in the number of games countries participated in...................................................5
d. Range between the gold medals awarded to the 10 countries.................................................5
e. Number of countries that got more silver medals than bronze medals....................................5
f. The countries which got more gold, silver and bronze medals than Great Britain..................5
g. The country which performed best..........................................................................................5
h. Reasons due to which country like Jamaica does not feature in the top 10 medals................5
i. Determination of the medal category in which the US far outperformed its closest
competitor....................................................................................................................................5
j. The three countries that has most evenly distributed number of gold, silver and bronze medal
......................................................................................................................................................6
QUESTION 12.................................................................................................................................6
Creating the excel including rows and columns, identifiers, test formatting and column
highlighting..................................................................................................................................6
QUESTION 13.................................................................................................................................6
a. Actions or steps in excel which could be taken to rank the figures from 1 to 10th.................6
b. Specific actions or steps in excel which will produce a list of the countries with 800 or more
medals in total..............................................................................................................................7
c. The graphs which will be suitable for representing only gold medals information.................7
d. The column in which might replication have been used..........................................................9
e. The excel formula which could be used to calculate the overall total medals.........................9
QUESTION 14.................................................................................................................................9
a. Excel formula to calculate total medals for Germany and Great Britain.................................9
b. Formula to calculate average number of silver medals for European country........................9
c. Excel formula to sum the medals total for gold for those counties with less than 20 games
involvement..................................................................................................................................9
d. Excel formula to search the data base to find Italy and also the corresponding medals total. 9
QUESTION 15...............................................................................................................................10
a. Calculation of medium number of medals for each medal type, stating the formula which is
used to determine the median for the medals.............................................................................10
b. Calculation of mean for the medals along with the formula which is used for the same
calculation..................................................................................................................................10

c. calculation of standard deviation of total medals awarded to each country..........................11
d. Usefulness of a standard deviation in the data set.................................................................13
QUESTION 16...............................................................................................................................13
a. Chart to compare fully labelled chart to compare gold, silver and bronze medal.................13
b. Chart to reflect the contribution of each country to the overall medals total........................14
CONCLUSION..............................................................................................................................14
REFERENCES..............................................................................................................................15

INTRODUCTION
Numeracy data could be defined as the information which is available in numerical
format. It is very important for the individuals who are working on the data analysis to use excel
in systematic manner. Apart from this, it is also very important for them to make sure that they
are having good and effective IT skills. Lack of it may result in inappropriate attainment of all
the requirements of the project on which individuals are working. If they will not be able to use
excel in systematic manner of they will having low knowledge of it then it will affect their ability
to complete the projects (Ayob, 2017). Present report is based upon use of excel and different
types of excel formulas so that all the requirements of project could be met. This assignment is
focused with different topics such as use of basic numerical operations with whole number ,
integers, fractions and decimals, calculation of proportions, percentages and averages along with
ratios. Additionally, construction of appropriate graphical techniques to present simple data
along with spreadsheet is also covered in this report.
QUESTION 1
a. Explanation of numerator
Numerator could be defined as the top number in the fraction. It is the part of fraction
which is depicted above the line and divided by denominator. For example if a fraction is 1 / 2
then 1 will be the numerator in it.
b. Explanation of denominator
The number which is depicted below the line in the fraction is called denominator. It
functions as the divisor for numerator. For example, if a fraction is 2 / 5 then 5 will be the
denominator in this fraction (Beverley, Hughes and Hastings, 2018).
QUESTION 2
Expressing 24 / 40 and 18 / 42 in simplest forms
The simplest form of 24 / 40 is 3 / 5. The simplest form for 18 / 42 is 3 / 7.
QUESTION 3
a. Expressing the fractions 2 / 3, 3 / 4 and 5 / 6 equivalent with a denominator of 12
The fractions with denominator of 12 are as follows:
1

2 / 3 = 8 / 12
3 / 4 = 9 / 12
5 / 6 = 10 / 12
b. Making up the two thirds of the remainder
Total books in library = 60000
Books of business = 14000
Books of health care = 22000
Books of psychology and law = 12000
Remaining books = 12000
2 / of the remainder = 12000 * 2 / 3
= 8000
Percentage of books on computing = 8000 / 48000 * 100
= 16.67%
QUESTION 4
Price of each pair of the running shoes
The payment which was made by Liz = three notes of 50 pounds which means 150
pounds (Fitzallen, Watson and Wright, 2017).
Change received by Liz = 10.50 pounds
Total payment which was made by Liz = 150 – 10.50
= 139.5
139.5 payment was made for two pairs of running shoes.
Cost of one pair of shoes = 139.5 / 2
= 69.75
QUESTION 5
a. What is 240.50 * 19.54
The value will be 4700 in the two significant figures.
b. Writing the number 52100 as the power of 10
5.21 *10 ^ 4 is the power of 10 fir 52100.
2

QUESTION 6
a. Total saving made by Patty and her siblings
Total payment made by Patty and her siblings = 210
As the payment is made after the 30% discount so the actual payment which was required
to be made by them will be = 210 / 70 * 100
= 300
So the total savings by all of them will be = 300 – 210
= 90
b. Average saving per person
Total payment which was made by three of them after discount = 210
Payment made by each person = 210 / 3
= 70
Total payment before discount = 300
Payment for each person = 300 / 3
= 100
Saving per person = 100 – 70
= 30
QUESTION 7
a. 3 / 4 – 7 / 9 + 2 / 3
3 / 4 – 7 / 9 + 2 / 3
= 0.75 – 0.7777 + 0.6666
= 0.6389 or 0.64
b. Largest number from all the values
The largest number from all the values is 0.10.
QUESTION 8
The percentage of men that said no
Total number of men = 90
Total number of women = 60
Total people who watched movie = 150
3

People who said yes = 150 * 3 / 5
= 90
Women who said yes = 60 * 3 / 10
= 18
Total men who said yes = Total people who said yes – women who said yes
= 90 – 18
= 72
Total men who said no = 90 – 72
= 18
Percentage of men who said no = 18 / 90 * 100
= 20%
QUESTION 9
The latest time that Annabelle can leave home
The latest time that Annabelle can leave home is 8:05 am because if she will leave home
at this time then she can reach to the railway station at 9:05 in 60 minutes and she can catch the
train that run 5 minute past the hour. The train will reach to the Birmingham station at 10:15 and
the walking distance will be 5 minutes so she will reach to the meeting at 10:20. 10 minutes
could be taken by her to get prepare for the meeting (Grebitusand Davis, 2019). If she will leave
the home after 8:05 then she will have to catch the train that run 25 minute past the hour.
QUESTION 10
Analysis of the box which is heavier
The box which is heavier is of Wheetabix as it is having 0.36 Kg in it and the box with
Shredded wheat have only 0.35 kg weight in it (Kjelvik and Schultheis, 2019).
QUESTION 11
a. The country with lowest number of overall medals
Sweden is the country which is having lowest number of overall medals.
b. The country or countries which competed the least number of games
The countries which competed the least number of games are China and Soviet Union.
4

c. The mode in the number of games countries participated in
Mode in the number or games in which countries participated in is 23.3. The formula
which is used to calculate it is ΣX / N. X is the games and N is number of countries (Laracy,
Hojnoski and Dever, 2016).
d. Range between the gold medals awarded to the 10 countries
The rage between the gold medals awarded to 10 countries is 875 because the maximum
gold medals are 1022 and minimum gold medals are 147 and the range is difference between
them.
e. Number of countries that got more silver medals than bronze medals
Great Britain, Soviet Union and United Stats are the countries which have won more
silver medals than the bronze medals.
f. The countries which got more gold, silver and bronze medals than Great Britain
The countries which have won more gold, silver and bronze medals than Great Britain
are Germany and Soviet Union (Lindberg and Silvennoinen, 2018).
g. The country which performed best
By comparing all the top 10 countries for the Olympics it has been analysed that Soviet
Union is performed best because it has taken part in 10 games only and the total medals which
are received by it are 1122 which is very high. It shows that it is the best performing country.
h. Reasons due to which country like Jamaica does not feature in the top 10 medals
The reasons due to which Jamaica does not feature in the top 10 medals are as follows:
 It would have taken part in less games.
 By analysing the countries that are featured in top 10 it is determined that these countries
are whether very large in size or part of EU. Jamaica is not featured in it because it is
neither large in size nor a part of European Union (Mardiana and Mudrikah, 2016).
i. Determination of the medal category in which the US far outperformed its closest competitor
By analysing the list of top 10 countries in Olympics it has been determined that in Silver
and Bronze medals United States outperformed its closest competitor which is Soviet Union.
5

j. The three countries that has most evenly distributed number of gold, silver and bronze medal
By analysing the top 10 list of the countries it has been determined that the countries that
has most evenly distributed number of gold, silver and bronze medals are Australia, Hungry and
Sweden (Mukwambo, Mhakure and Sitwala, 2020).
QUESTION 12
Creating the excel including rows and columns, identifiers, test formatting and column
highlighting
Olympic Games Medals Table (Top 10)
Team
Total
Games Gold Silver Bronze Total
Australia 26 147 163 187 497
China 10 227 165 151 543
France 28 212 241 260 713
Germany 24 275 313 349 937
Great Britain 28 263 295 289 847
Hungry 26 175 147 169 491
Italy 27 206 178 193 577
Soviet Union 10 440 357 325 1122
Sweden 27 147 170 179 496
United States 27 1022 794 704 2520
QUESTION 13
a. Actions or steps in excel which could be taken to rank the figures from 1 to 10th
In order to rank the countries following steps could be taken:
Step 1: Apply the rank function in the data set which is (RANK)
6

Step 2: Now the range which is required to be ranked will be selected. For example:
[=RANK(B2,B2:B11).
Step 3: At last the ranks will be provided to all the figures.
b. Specific actions or steps in excel which will produce a list of the countries with 800 or more
medals in total
In order to produce the list of countries with 800 or more medals specific setps required
are as follows:
Step 1: Selecting the rows that are required to be grouped under the 800 or more medals.
Step 2: Then the user will be required to go to the data ribbon.
Step 3: After than group is required to be selected.
Step 4: At last the group will be selected again.
c. The graphs which will be suitable for representing only gold medals information
There are various types of graphs which could be used for the purpose of representing
only the information of gold medals. These are bar, column and pie. All of them can represent
the information of gold medals in systematic manner with the countries (Nienkemper and
Grotlüschen, 2019). The examples for all of them are as follows:
Bar graph:
Australia
China
France
Germany
Great Britain
Hungry
Italy
Soviet Union
Sweden
United States
0 200 400 600 800 1000 1200
147
227
212
275
263
175
206
440
147
1022
Gold
Column Graph:
7

Australia
China
France
Germany
Great Britain
Hungry
Italy
Soviet Union
Sweden
United States
0
200
400
600
800
1000
1200
147
227 212 275 263
175 206
440
147
1022
Gold
Pie graph:
147
227212
275
263
175 206 440 147
1022
Australia
China
France
Germany
Great Britain
Hungry
Italy
Soviet Union
Sweden
United States
d. The column in which might replication have been used
By analysing the data set of all the countries regarding medals it has been determined that
replication have been used in the columns of Gold, Silver and Bronze medal's columns because
for all of them the data objective which is the team or country were same (Scott, 2016).
8

e. The excel formula which could be used to calculate the overall total medals
The formula which could be used in excel to calculate the over all total medals is SUM.
In order to use it the users are required to apply the SUM formula and then select the columns
where the value of medals won by the countries is mentioned. In the data set it is applied in the
column named Total (Pensiero and Green, 2018). The example of it is as follows:
[=SUM(C3:E3)]
By applying it, total medals awarded to each country are calculated by selecting the rows
in which the data is listed. The above example is for Australia (Thompson, 2020).
QUESTION 14
a. Excel formula to calculate total medals for Germany and Great Britain
Formula for total medals for Germany = [=SUM(C6:E6)]
Formula for total medals for Great Britain = [=SUM(C7:E7)]
b. Formula to calculate average number of silver medals for European country
Formula for calculating average number of silver medals for European country
[=Average(C5:E5,C6:E6,C9:E9)]
c. Excel formula to sum the medals total for gold for those counties with less than 20 games
involvement
The formula which could be used in the excel file to calculate the total gold which is won
by the countries which have taken part in the Olympics is as follows:
[=SUM(C4+C10)]
d. Excel formula to search the data base to find Italy and also the corresponding medals total
In order to find Italy in excel find formula could be applied. It is as follows:
=FIND(Find_Text;text:position)
QUESTION 15
a. Calculation of medium number of medals for each medal type, stating the formula which is
used to determine the median for the medals
Formula for calculating medium = n / 2
The data for calculating Median is required to be sorted ascending order so the sorted
data for all type of medals is as follows:
9

S. No Countries Gold Countries Silver Countries Bronze
1 Australia 147 Hungry 147 China 151
2 Sweden 147 Australia 163 Hungry 169
3 Hungry 175 China 165 Sweden 179
4 Italy 206 Sweden 170 Australia 187
5 France 212 Italy 178 Italy 193
6 China 227 France 241 France 260
7 Great Britain 263 Great Britain 295 Great Britain 289
8 Germany 275 Germany 313 Soviet Union 325
9 Soviet Union 440 Soviet Union 357 Germany 349
10 United States 1022 United States 794 United States 704
Median = 10 / 2
= 5th value
Median for Gold will be 212 which is for France. For Silver the median will be 178
which is for Italy. The value of median for Bronze will be 193 which is for Italy.
b. Calculation of mean for the medals along with the formula which is used for the same
calculation
Formula = ΣX / N
The table with total of all the medals is as follows:
Team Gold Silver Bronze
Australia 147 163 187
China 227 165 151
France 212 241 260
Germany 275 313 349
Great Britain 263 295 289
10

Hungry 175 147 169
Italy 206 178 193
Soviet Union 440 357 325
Sweden 147 170 179
United States 1022 794 704
Total medals 3114 2823 2806
Mode for Gold medals = 3114 / 10
= 311.4
Mode for Silver medals = 2823 / 10
= 282.3
Mode for Bronze medal = 2806 / 10
= 280.6
c. calculation of standard deviation of total medals awarded to each country
Formula = √Σ (X - μ)2 / N
In order to calculate μ following table is generated:
Standard deviation for Gold medals:
Team Medal (x) X / μ (X / μ)2
Australia 147 -164.4 27027.36
China 227 -84.4 7123.36
France 212 -99.4 9880.36
Germany 275 -36.4 1324.96
Great Britain 263 -48.4 2342.56
Hungry 175 -136.4 18604.96
Italy 206 -105.4 11109.16
Soviet Union 440 128.6 16537.96
Sweden 147 -164.4 27027.36
11

United States 1022 710.6 504952.36
Total 0 625930.4
= √625930.4 / 10
= 791.185 / 10
= 79.1158
Standard deviation for Silver medal
Team Silver X - μ (X - μ)2
Australia 163 -119.3 14232.49
China 165 -117.3 13759.29
France 241 -41.3 1705.69
Germany 313 30.7 942.49
Great Britain 295 12.7 161.29
Hungry 147 -135.3 18306.09
Italy 178 -104.3 10878.49
Soviet Union 357 74.7 5580.09
Sweden 170 -112.3 12611.29
United States 794 511.7 261836.89
Total 0 340014.1
= √340014.1 / 10
= 583.107 / 10
= 58.3107
Standard deviation for Bronze medal:
Team Bronze X - μ (X - μ)2
Australia 187 -93.6 8760.96
China 151 -129.6 16796.16
France 260 -20.6 424.36
12

Germany 349 68.4 4678.56
Great Britain 289 8.4 70.56
Hungry 169 -111.6 12454.56
Italy 193 -87.6 7673.76
Soviet Union 325 44.4 1971.36
Sweden 179 -101.6 10322.56
United States 704 423.4 179267.56
Total 0 242420.4
= √242420.4 / 10
= 492.362 / 10
= 49.23
d. Usefulness of a standard deviation in the data set
Accoring to Nieto and Ramos, (2017), standard deviation could be defined as the number
which is used to tell the way in which measurements for formulating a group are spread out from
the average. It is very useful in the data set as it helps to determine the spread out of data. It has
facilitated the identification of outlines and determining the elements that are unusual. It is very
useful for the assessment of the data set so that highest and lowest standard deviation in the
available information could be evaluated and determined.
13

QUESTION 16
a. Chart to compare fully labelled chart to compare gold, silver and bronze medal
Australia
China
France
Germany
Great Britain
Hungry
Italy
Soviet Union
Sweden
United States
0 200 400 600 800 1000 1200
147
227
212
275
263
175
206
440
147
1022
163
165
241
313
295
147
178
357
170
794
187
151
260
349
289
169
193
325
179
704
Bronze
Silver
Gold
Medals
Countries
b. Chart to reflect the contribution of each country to the overall medals total
497
543
713
937
847
491 577
1122
496
2520
Contribution of countries in total medals
Australia
China
France
Germany
Great Britain
Hungry
Italy
Soviet Union
Sweden
United States
14

CONCLUSION
From the above project report it has been concluded that data analysis is a technique
which is used to analyse and evaluate the available information. While performing numerical
operations it is very important for the individuals to be aware of numbers, integer, decimals and
fractions. While planning to calculate ratios, percentage and averages it is very important for all
the individuals to use appropriate graphical techniques to present simple data. Apart from this,
excel knowledge is also required for constructing spreadsheet and graphs to present data.
15

REFERENCES
Books and Journals:
Ayob, S., 2017. Using structured movement educational activities to teach numeracy and
literacy concepts to preschoolers (Doctoral dissertation, University of Pretoria).
Beverley, M., Hughes, J. C. and Hastings, R. P., 2018. Improving Essential Numeracy Skills in
Primary Schoolchildren Using a Brief Fluency-building Intervention: A Randomised
Control Trial. Cylchgrawn Addysg Cymru/Wales Journal of Education. 20(1). pp.114-
134.
Fitzallen, N., Watson, J. and Wright, S., 2017. The heat is on! Using a stylised graph to engender
understanding. Australian Primary Mathematics Classroom. 22(2). p.3.
Grebitus, C. and Davis, G. C., 2019. Does the new nutrition facts panel help compensate for low
numeracy skills? An eye‐tracking analysis. Agricultural Economics. 50(3). pp.249-258.\
Kjelvik, M. K. and Schultheis, E. H., 2019. Getting messy with authentic data: Exploring the
potential of using data from scientific research to support student data literacy. CBE—
Life Sciences Education. 18(2). p.es2.
Laracy, S. D., Hojnoski, R. L. and Dever, B. V., 2016. Assessing the classification accuracy of
early numeracy curriculum-based measures using receiver operating characteristic curve
analysis. Assessment for Effective Intervention. 41(3). pp.172-183.
Lindberg, M. and Silvennoinen, H., 2018. Assessing the basic skills of the highly educated in 21
OECD countries: an international benchmark study of graduates’ proficiency in literacy
and numeracy using the PIAAC 2012 data. Comparative Education. 54(3). pp.325-351.
Mardiana, D. and Mudrikah, A., 2016. Developing Numeracy Skills by Using Numbers Lottery
Game. Proceedings of AICS-Social Sciences, 6, pp.375-380.
Mukwambo, M., Mhakure, D. and Sitwala, N., 2020. The Impact of Silozi Language on the
Teaching of Numeracy in the Zambezi Region. International Journal of Trends in
Mathematics Education Research. 3(1). pp.13-19.
Nienkemper, B. and Grotlüschen, A., 2019. Using PIAAC data to learn more about the literacy
practices of adults. International Journal of Lifelong Education. 38(4). pp.393-405.
Nieto, S. and Ramos, R., 2017. Overeducation, skills and wage penalty: Evidence for Spain
using PIAAC data. Social Indicators Research. 134(1). pp.219-236.
Pensiero, N. and Green, A., 2018. The effects of post‐compulsory education and training systems
on literacy and numeracy skills: A comparative analysis using PISA 2000 and the 2011
survey of adult skills. European Journal of Education. 53(2). pp.238-253.
Scott, F. J., 2016. An investigation into students’ difficulties in numerical problem solving
questions in high school biology using a numeracy framework. European Journal of
Science and Mathematics Education. 4(2). pp.115-128.
Thompson, J., 2020. An Exploratory Case Study: Using Web-based Micro Videos as a Parent
Involvement Tool to Support Families with Preschool Age Children in Early Numeracy
Development.
16