Numeracy, Data & IT (Portfolio of Tasks)

This document is a portfolio of tasks for the subject Numeracy, Data & IT. It includes solved questions on various topics such as numerators and denominators, percentages, calculations, rankings, and Excel functions. The document also contains information on creating Excel sheets, conditional formatting, and suitable graphs for representing data. It is a comprehensive resource for students studying Numeracy, Data & IT.

Numeracy, Data & IT (Portfolio of Tasks)

Table of Contents
PART 1............................................................................................................................................3
Question 1....................................................................................................................................3
Question 2....................................................................................................................................3
Question 3....................................................................................................................................3
Question 4....................................................................................................................................3
Question 5....................................................................................................................................4
Question 6....................................................................................................................................4
Question 7....................................................................................................................................4
Question 8....................................................................................................................................5
Question 9....................................................................................................................................5
Question 10..................................................................................................................................5
Part 2................................................................................................................................................6
Part 3................................................................................................................................................8
12. Create excel...........................................................................................................................8
13.................................................................................................................................................8
14) Write the Excel functions for the following........................................................................12
15...............................................................................................................................................14
16...............................................................................................................................................17

PART 1
Question 1
Numerator: It is the value which is placed at upper part of the fraction. It shows how many parts
it have.
Denominator: It is placed lower part of the fraction. It shows in how many equal parts the value
is divided into.
Question 2
24/40 = 0.6
18/42 = 0.43 (approx.)
Question 3
a) 2/3, 3/4 and 5/6
8
12 ,
9
12 ∧10
12
b) Percentage of library books on computing
Remainder book = 60,000 – 14,000 – 22,000 – 12,000
= 12,000
Computing books = 12,000 × 2
3
= 8,000
Percentage of books on computing = 8,000
60,000 ×100
= 13.33%

Question 4
Total money given by Liz = £50 × 3 = £150
Total price of 2 pairs of shoes = £150 - £10.50
= £139.50
Price of each pair of shoes = £139.50/2
= £69.75 per pair
Question 5
a) 240.50 × 19.54
= 4,699.37
b) 521 × 1010
Question 6
a)
Total amount paid for sign up = £210
Total discount received = £210 × 30
100
= £63
Hence, the total savings made = £63
b)
Average savings per person = £63 / 3 (Patty and her 2 siblings)
= £21 per person
Question 7
a) 3/4 - 7/9 + 2/3

Applying BODMAS = 17/12 – 7/9
= 237/108
b) 0.1
Question 8
Total number of people said yes = (90 + 60) × 3
5
= 90
Total number of women said yes = 60 × 3
10
= 18
Total number of man said yes = 90 – 18
= 72
Number of man said no = 90 -72 = 18
Percentage of the men said no = 18
90 × 100
= 20%
Question 9
Here, reverse calculation method will be adopted:
Target time at which Annabelle requires to attend conference meeting = 10:30am
Time taken from Euston Rail station to Birmingham is 1 hour 10 minutes, for this she need to
take train at = 10:30am – 1:15hrs = 09:15am
As there’s no train exact this time; Annabelle has only option to take train at 09:05am
Time taken from her home to station is 1hr = 09:05am – 1hr
= 08:05am

Therefore; she needs to leave home at 08:05am.
Question 10
Converting both values in simple form = 9/25
= 0.36Kg
Therefore, 0.36 Kg or 9/25 Kg is heavier than 0.35Kg of Shredded Wheat.
Part 2
11)
a) Hungary
b) China and Soviet Union
c) 27
d) Minimum = 147; Maximum = 1,022
Range = Maximum – Minimum
= 1,022 – 147
= 875
e) 4 countries
f) Germany, United States and Soviet Union
g)
Country
Total
Games
Total
medals
Medals per
game
Australia 26 497 19.11538462
China 10 543 54.3
France 28 713 25.46428571
Germany 24 937 39.04166667
Great Britain 28 847 30.25
Hungary 26 491 18.88461538

Italy 27 577 21.37037037
Soviet Union 10 1122 112.2
Sweden 27 494 18.2962963
United States 27 2520 93.33333333
Hence, United States has received 93.33 medals per game which is highest among all countries.
Therefore, United States has the highest number of medals received per game.
h) Reasons are as follows:
1. Jamaica has failed to receive hardly any gold medal in overall Olympics games; due to which
it was not taken into top 10 rankings.
2. Jamaican athletes have only participated in one game which is sprinting.
i)
Country Gold
United States 1,022
Soviet Union 440
582
Country Silver
United States 794
Soviet Union 357
437
Country
Bronz
e
United States 704
Soviet Union 325
379
The maximum difference between medal categories with its closest competitor is Gold (582);
thus Gold is the category where United States has far outperformed its closest competitor.
j)
Team Gold Silver Bronze Range
Hungary 175 147 169 28

Italy 206 178 193 28
Great Britain 263 295 289 32
Sweden 147 170 179 32
Australia 147 163 187 40
France 212 241 260 48
Germany 275 313 349 74
China 227 165 151 76
Soviet Union 440 357 325 115
United States 1022 794 704 228
Three countries which has most evenly distributed number of gold, silver and bronze medals
(least smallest range) are Hungary, Italy and Great Britain.
Part 3
12. Create excel
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
Hungary 26 175 147 169 491
Italy 27 206 178 193 577
Soviet Union 10 440 357 325 1,122
Sweden 27 147 170 179 496
United States 27 1,022 794 704 2,520
13.
a) What actions or steps in Excel can you take to rank them from 1st to 10th
The RANK function in Excel returns the rank of a number in a list of numbers. Use RANK.AVG
to return the average rank if more than one number has the same rank.
1. If the third argument is omitted (or 0), Excel ranks the largest number first, second largest
number second, etc.

2. If the third argument is 1, Excel ranks the smallest number first, second smallest number
second, etc.
3. The RANK.AVG function in Excel returns the average rank if more than one number has the
same rank.
There are 3 arguments for the RANK function:
 Number: in the above example, the number to rank is in cell B2
 Ref: We want to compare the number to the list of numbers in cells $B$2:$B$11. Use an
absolute reference ($B$2:$B11), instead of a relative reference (B2:B11)so the
referenced range will stay the same when you copy the formula down to the cells below
 Order: (optional) This argument tells Excel whether to rank the list in ascending or
descending order.
o Use zero, or leave this argument empty, to find the rank in the list in descending
order. In the example above, the order argument was left blank, to find the rank in
descending order.
=RANK(B2,$B$2:$B$11)
o For ascending order, type a 1, or any other number except zero.
If you were comparing golf scores, you could type a 1, to rank in ascending order.
=RANK(B2,$B$2:$B$11,1)
In the RANK function, the 3rd argument (order), is optional. The order argument tells Excel
whether to rank the list in ascending or descending order.
If you use a zero as the setting for order, or if you don't use the 3rd argument, the rank is set in
descending order.
 The largest number gets a rank of 1
 The 10th largest number gets a rank of 10.
If you use a 1 as the setting for order, or if you enter any number except zero as the 3rd
argument, the rank is set in ascending order.

 The smallest number gets a rank of 1
 The 10th smallest number gets a rank of 10.
Instead of typing the order argument number into a RANK formula, use a cell reference, to
create a flexible formula.
For example, type a 1 in cell E1, and link to cell E1 for the order argument.
By linking to a cell, you can quickly see different results, without changing the formula. Type a
zero in cell E1, or delete the number, and the rank will change to Descending order.
For the order option, there are only 2 choices - Ascending or Descending. To make it easier for
people to change the order, use a check box to turning Ascending order ON or OFF.
 If it is turned ON, the RANK order will be Ascending
 If it is turned OFF, the RANK order will be Descending
b) State the specific action(s) or step(s) in Excel that will produce a list/display of those countries
with 800 or more medals in total?
To apply this action; conditional formatting is the excel tool which can visible the values below
800 through displaying it as different color. Below are the steps to get the result:
To build this basic formatting rule, follow these steps:
1. Select the information cells in the target field (cells C3: C14 in this template), click the Home
tab of the Excel ribbon, and then select Power Format ⇒ New Rule. The New Format Rule
Exchange box opens.
2. In the list box at the beginning of the switch box, click Use formula to find out which cells
make the selection. This decision assesses the values that depend on a particular recipe. On the

off chance that a particular value is not valued as TRUE, the restrictive group is deployed in that
cell.
3. In the recipe entry box, enter the equation shown here. Note that you can only refer to the
main cell in the target area. It is not necessary to mention the whole range.
4. Snap the Shape button. The Format Cells dialog box opens, where you have a full set of
alternatives to customize text style, margin, and fill for your target cell. After selecting your
organization options, click the "OK" button to confirm your progress and visit the New Format
Rule check box.
5. Again in the New Format Rule Talk box, click the "OK" button to confirm the group rule.
To show differential shading for cells below the measured value, the improvements are followed:
To compile this basic design guide, follow these methods: 1. Select the information cells in the
target area (cells E3: C14 in this form), click the Home tab of the Excel ribbon, then select New
Rule formatting of the finishing tool.
2. In the margin box at the top of the dialog box, click Use formula to determine in which cells to
choose a shape. This decision evaluates the values based on a specified equation. If a particular
value is evaluated TRUE, the restricted organization is deployed to that cell.
3. In the equation inbox, enter the recipe displayed with this progress. Note that you are basically
looking at your target cell (E3) with the stimulus in the test cell ($ B $ 3). As with normal
equations, you need to make sure that you use the references completely so that all stimuli in the
field are compared to the relevant correlation cell.

4. Snap the Shape button. This opens the Format Cells switchbox, where you have a full setup of
alternatives for the text style, margin, and fill for your target cell. When you're done selecting
your design options, click the OK button to confirm your progress and revisit the New Format
Rule Exchange box.
5. Again in the New Format Rule exchange box, click the OK button to confirm the design
principle.
c) Which type of graph will be suitable for representing only gold medals information?
Line graph or bar graph is more suitable in representing gold medal information
d) In which column(s) might replication have been used?
Total; in total column replicating formula of SUM for all other teams has been used.
e) What Excel formula can be used to calculate the overall total medals awarded?
=SUM() formula can be used to total overall medals
14) Write the Excel functions for the following
a. Give the total number of medals for Germany and Great Britain.
Formula:
= SUM(F32 + F38)
b. Give the average number of silver medals for a European country
Formula:
European countries = France, Germany, Great Britain, Italy, Soviet Union and Sweden
Total 6 counties.
=AVERAGE (F31 + F32 + F33 + F35 + F36 + F37)
c. Sum the Medals Total for Gold for those countries with less than 20 games involvement.

For solving this; “what if” function will be applied, after that the result will be in the form of
FALSE TRUE; where ‘FALSE’ indicates values less than 20 and ‘TRUE’ shows values more
than 20. After that, all the cells showing TRUE value will be sorted and values of GOLD will be
calculated. These steps has been mentioned below:
1. Select the cell containing the value you want to change. When you use Goal Seek, you'll
need to select a cell that already contains a formula or function.
2. From the Data tab, click the What-If Analysis command, then select Goal Seek from
the drop-down menu.
3. A dialog box will appear with three fields:
Set cell: This is the cell that will contain the desired result. In our example, cell B7 is
already selected.
To value: This is the desired result. In our example, we'll enter 70 because we need
to earn at least that to pass the class.
By changing cell: This is the cell where Goal Seek will place its answer. In our
example, we'll select cell B6 because we want to determine the grade we need to
earn on the final assignment.
4. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.
5. The result will appear in the specified cell. In our example, Goal Seek calculated that
we will need to score at least a 90 on the final assignment to earn a passing grade.
d. Search the database (the whole spreadsheet) to find ‘Italy’ and also the corresponding
Medals Total.
Here the VLOOKUP functionality is implemented; VLOOKUP stands for "direct query". In
Excel, this means searching for information directly in a spreadsheet, using spreadsheet
sections: and unique identifiers within these categories, as the primary basis for your search.
As you review your information, it must be recorded directly wherever it is found.
1. Identify a column of cells you'd like to fill with new data.
2. Select 'Function' (Fx) > VLOOKUP and insert this formula into your highlighted cell.

3. Enter the lookup value for which you want to retrieve new data.
4. Enter the table array of the spreadsheet where your desired data is located.
5. Enter the column number of the data you want Excel to return.
6. Enter your range lookup to find an exact or approximate match of your lookup value.
7. Click 'Done' (or 'Enter') and fill your new column.
15.
a) Calculate the median number of medals for each medal type, stating the formula you would
use for determining the median for the gold medals.
 When the total number of supplied numbers is odd, the median is calculated as the middle
number in the group.
 When the total number of supplied numbers is even, the median is calculated as the
average of the two numbers in the middle.
 Cells that contain text, logical values, or no value are ignored.
 Numbers can be supplied as numbers, ranges, named ranges, or cell references that
contain numeric values. Up to 255 numbers can be supplied.
The MEDIAN function has no built-in way to apply criteria. Given a range, it will return the
MEDIAN (middle) number in that range. To apply criteria, we use the IF function inside
MEDIAN to "filter" values.
So, first of all values will be sorted in ascending order and median for 5th and 6th position will be
identified.
Gold Median:
219.5
Silver Median:
209.5

Bronze median:
206.5
b) Calculate the mean number of medals for each of the 3 medal types, stating the formula
you would use for determining the mean for the bronze medals.
Mean:
Gold = 311; Silver = 282 and Bronze = 281
Formula;
Mean is nothing but an average of set of values. Thus average function will be applied to
identify the mean of Bronze models (= AVERAGE(E29:E38))
c) Calculate the standard deviation of the total medals awarded to each country (column F)
using the formula below.
Standard Deviation σ = 288.60926
Variance σ2 = 83295.306
Count n = 12
Mean x¯ = 478.83333
Sum of Squares SS = 999543.67
σ = √ ∑ ( x −u ) 2
N
σ = √ 999543.67
12
= √83295.306
= 288.60926
d) Using the given spreadsheet as a basis, discuss the usefulness of a standard deviation in a
given dataset.

Standard deviation is the most commonly used distribution proportion in a measurable practice
when averaging is used to measure focal bias. Like these lines, it estimates the average
circulation. Because of the close links to the mean, a normal movement can have a significant
effect if the mean provides an unaided part of the focus deflection.
Standard deviation also apply to exceptions that a value can generally add in the aftermath of the
general deviation. In this sense, general bias is a reasonable indication that there are exceptions.
This makes normal motion a very useful part of the distribution for right proportions without
irregularities.
Standard deviation is also useful when looking at the distribution of two separate pieces of data
of average size. There is less scatter of estimates around information gathering with the usual
small bias, and in this way for the most part the effects are smaller or smaller. An object selected
from an information index that has a low mean deviation with a better ability to be close to the
mean is an object from an information index that has a higher mean deviation.
In most cases, the more widespread the effects, the greater the general bias. For example,
consider that you need to separate two specific provisions of test results from a class of 30 high
school students, the main exam has scores ranging from 31% to 98%, varying levels from 82% to
93%. With these extensions, the overall bias would be greater for the effect of the main test.
Standard deviation can be difficult to determine no matter how much it takes to consider
disseminating the information in general. The size of the average estimate of the information
collection is based on the size of the standard deviation. When estimating something that is in the
millions, measurements that are "close" to the average value are less important than when
estimating the weight of two. For example, the percentage of two large groups with a $ 10,000
split in annual income is seen as very close, while the proportion of two with a 30-kilogram
weight difference is seen as a factor in depth. This is why, most of the time, it is useful to
examine the magnitude of the general deviation relative to the average of the information
collection.

While the Standard deviation is not as vulnerable to extreme effects as the range, the general bias
is even more dynamic than the range of a quarter. If the possibility of high values (outliers)
presents itself, then the standard deviation should be supplemented by the semi-quartile range.
16.
a) Produce an appropriate fully-labeled chart to compare the gold, silver and bronze medals
totals of the 10 countries.
Australia
China
France
Germany
Great Britain
Hungary
Italy
Soviet Union
Sweden
United States
0
500
1000
1500
2000
2500
3000
Total
b) Use a suitable and fully-labeled chart to reflect the contribution of each country to the overall
medals total.

6% 6%
8%
11%
10%
6%7%
13%
6%
29%
Total
Australia
China
France
Germany
Great Britain
Hungary
Italy
Soviet Union
Sweden
United States