Information and
Communication Technology
Spreadsheets
Using a spreadsheet to analyse numerical information
People have been using spreadsheets for a long time, although not
always by that name. They have their origins in the world of commerce, where people
needed to keep a count of the goods that they were buying and selling.
Hello Class 11th
Revise all these Famous Functions of MS Excel before Mock Exam
| Formula | Description (Result) |
| =IF(A2=15, "OK", "Not OK") | If the value in cell A2 equals 15, then return "OK". (OK) |
| =IF(A2<>15, "OK", "Not OK") | If the value in cell A2 is not equal to 15, then return "OK". (Not OK) |
| =IF(NOT(A2<=15), "OK", "Not OK") | If the value in cell A2 is not less than or equal to 15, then return "OK". (Not OK) |
| =IF(A5<>"SPROCKETS", "OK", "Not OK") | If the value in cell A5 is not equal to "SPROCKETS", then return "OK". (Not OK) |
| =IF(AND(A2>A3, A2<A4), "OK", "Not OK") | If 15 is greater than 9 and less than 8, then return "OK". (Not OK) |
| =IF(AND(A2<>A3, A2<>A4), "OK", "Not OK") | If 15 is not equal to 9 and 15 is not equal to 8, then return "OK". (OK) |
| =IF(OR(A2>A3, A2<A4), "OK", "Not OK") | If 15 is greater than 9 or less than 8, then return "OK". (OK) |
| =IF(OR(A5<>"Sprockets", A6<>"Widgets"), "OK", "Not OK") | If the value in cell A5 is not equal to "Sprockets" or "Widgets", then return "OK". (Not OK) |
| =IF(OR(A2<>A3, A2<>A4), "OK", "Not OK") | If 15 is not equal to 9 or 15 is not equal to 8, then return "OK". (OK) |
| =VLOOKUP("ID-4537", A1:D7, 4, FALSE) | Lookup the badge number, ID-4537, in the first column and return the matching value in the same row of the fourth column (3355) |
=VLOOKUP(5.93, A1:B6, 2, TRUE) Looks up 5.93 in column A, finds the next largest value that is less than 5.93, which is 5.77, and then returns the value from column B that's in the same row as 5.77 (green)
| =HLOOKUP("Bolts", A1:C4, 3) | Looks up Bolts in row 1, and returns the value from row 3 that's in the same column (10) |
| =HLOOKUP(78658,A1:D4,2, TRUE) | Looks up $78,658 in Row 1, finds the next largest value that is less than $78,658, which is $50,000, and then returns the value from row 2 that's in the same column as $50,000 (20%) |
| =IF(A2<=100,"Within budget","Over budget") | If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget) |
| =IF(A2=100,SUM(B5:B15),"") | If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned () |
| =IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) | Assigns a letter grade to the first score (F) |
| =IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) | Assigns a letter grade to the second score (A) |
| =IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F")))) | Assigns a letter grade to the third score (C) |
| =LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) | Assigns a letter grade to the first score (F) |
| =LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) | Assigns a letter grade to the second score (A-) |
| =LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) | Assigns a letter grade to the third score (C+) |
| =SUM(B2:B3,B5) | Adds two invoices from Buchanan, and one from Suyama (44,000) |
| =SUM(B2,B5,B7) | Adds individual invoices from Buchanan, Suyama, and Dodsworth (57,500) |
| =SUMIF(A2:A7,"Buchanan",B2:B7) | Sum of invoices for Buchanan (29000) |
| =SUMIF(B2:B7,">=9000",B2:B7) | Sum of large invoices greater than or equal to 9,000 (66500) |
| =SUMIF(B2:B7,"<9000",B2:B7) | Sum of small invoices less than 9,000 (13000) |
| =SUM(IF((A2:A11="South")*(C2:C11="Meat"),D2:D11)) | Sum of Meat sales in the South region (14719) | ||
| =SUM(IF((A2:A11="South")+(A2:A11="East"),D2:D11)) | Sum of sales where the region is South or East (32753) |
| =RANK(A3,A2:A6,1) | Rank of 3.5 in the list above (3) |
| =RANK(A2,A2:A6,1) | Rank of 7 in the list above (5) |
| =AVERAGE(A2:A7) | Averages all of numbers in list above (9.5) |
| =AVERAGE(A2:A4,A7) | Averages the top three and the last number in the list (7.5) |
| =AVERAGE(IF(A2:A7<>0, A2:A7,"")) | Averages the numbers in the list except those that contain zero, such as cell A6 (11.4) |
THERE IS NO FUNCTION FOR SUBTRACTION, YOU WILL TYPE FORMULA FOR IT
|
| =A2*A3 | Multiplies the numbers in the first two cells (75) |
| =PRODUCT(A2:A4) | Multiplies all the numbers in the range (2250) |
| =PRODUCT(A2:A4,2) | Multiplies all the numbers in the range, and 2 (4500) |
|
| =ROUND(A2,0) | Rounds 20.3 down, because the fractional part is less than .5 (20) |
| =ROUND(A3,0) | Rounds 5.9 up, because the fractional part is greater than .5 (6) |
| =ROUND(A4,0) | Rounds -5.9 down, because the fractional part is less than -.5 (-6) |
|
| =MIN(A2:A7) | Smallest number in the range (0) |
| =MAX(A2:A7) | Largest number in the range (27) |
| =SMALL(A2:A7, 2) | Second smallest number in the range (4) |
| =LARGE(A2:A7,3) | Third largest number in the range (9) |
| =COUNT(A2:A6) | Counts number of cells that contain numbers (3) in the list. A date is a number. |
| =COUNT(A2:A3,A6) | Counts number of cells that contain numbers (2), of the top two and bottom cells in the list. A date is a number. |
| =COUNTA(A2:A6) | Counts the number of nonblank cells in the list above (3) |
| =COUNTA(A2:A3, A6) | Counts the number of nonblank cells in the top two, and bottom cell in the list (1) |
|
| =COUNTIF(B2:B7,">9000") | Numbers above 9000 (3) |
| =COUNTIF(B2:B7,"<=9000") | Numbers less than or equal to 9000 (3) |
| =TODAY() | Current date (varies) |
| =NOW() | Current date and time (varies) |
IF YOU NEED ADDITIONAL HELP FOR ANYONE OF THESE FUNCTIONS THE WEBSITE
http://office.microsoft.com/
WILL PROVIDE YOU HELP
The word spreadsheet comes from the large sheets of paper, ruled
with rows and columns, that were used to keep accounts. The term was kept when
spreadsheets were created with software applications.
This page looks at some of the commoner aspects of using a
spreadsheet.
Entering data
Using a spreadsheet means handling numbers
Using a spreadsheet is regarded by examiners as requiring a higher
level of skill than using many other software packages. So, for the IGCSE
practical examination, and probably for other practical examinations as well,
it is likely that you will be given a spreadsheet to work with. It will have
already been partially completed and the examination questions will ask you to
perform a number of tasks with it.
If you are creating your own spreadsheet however, you will need to
be able to put the data into the sheet.
The commonest way is to type the data in using a keyboard, but nearly all modern
spreadsheets allow the import of data from external sources as well. The
details differ between spreadsheets but they will all accept data from:
·
other spreadsheets
·
a comma seperated variable
(CSV) file
·
tables in HTML documents,
on the Internet or stored locally.
Some of these options may allow a link to be formed, so that the
spreadsheet updates each time that it opens, or when the data in the source
changes.
And of course, you can also copy and paste from anything with
a table, such as a word processor
document. Although in that case, you may need to try both 'paste' or 'paste
special', with appropriate selection of data type, to get the layout correct.
Formatting the data
Making the numbers mean what you want them to
There are two types of formatting to consider when using a
spreadsheet.
The first is the type that is used in other applications such as
word processors and is concerned with appearance, e.g. text size, colour, font,
borders and backgrounds, etc.
The second is more specific to spreadsheets and involves selecting
the type of data that is to be shown in each cell.
Formatting for appearance.
This is likely to come under 'making a publication fit for purpose'.
In an examination, there may be marks for specific formatting
tasks, e.g. Making the data display as blue,12 point, bold, in the Arial font.
There may also be marks for implied tasks, e.g. you may be asked
to ensure that the result of a calculation is clear, or make the spreadsheet
easy to understand.
This type of formatting also covers items which are specific to
tables. In general, a spreadsheet can perform all the formatting tasks that can
be carried out with word processor tables, including; text wrap, row height,
column width, gridlines, merged cells, cell borders, shading, etc.
Formatting for data type.
All spreadsheets have a default data type that is used for all the
cells when a new sheet is created. It is usually a general type that will will
allow you to type numbers or text into a cell.
Common data types that you should be able to use are:
·
Text. Cells formatted with
this data type accept alphanumeric entries. You will not be able to do any
calculations with text but it does allow you to show things such as 6-2, which
in numeric cells would be displayed as 4.
·
Number. The number fomat is
probably the one that you will use most often. It enables calculations to be
performed on a cell's contents. The number format usually has an option for
setting the number of decimal places, and may allow other choices such as
leading zeros or displaying negative numbers in red.
·
Currency. The main option
here is the currency being used. Fortunately nearly all currencies use a
decimal system, so it is really just a matter of having the correct symbol
showing.
·
Percentage. This one can
cause a few problems if you don't know how it works. If you enter a number less
than one, the spreadsheet will display it as a percentage of 1. e.g. 0.5
displays as 50%
If you enter 1 or greater, the number is
displayed with a following % sign. e.g. 1.5
displays as 1.5%
In both cases, if the number is used in a
calculation the spreadsheet treats it as the
actual number. i.e. 0.5 and
1.5, not 50 and 1.5
·
Date and time. These are
two related data types. Usually the Time type will include options for having
date and time together, while the Date options have more display formats but do
not show time with the date.
It is important to look at how the date and / or
time is formatted. If you get it wrong the
cell will display incorrect
data, even if you put in the correct figures. e.g. months and days
may be swapped around.
Some calculations can be performed on date
- time cells, e.g. adding 2 to a date should
display the date
for two days later. Other calculations such as dividing a date may not
work so well.
Cell referencing
Column first, row second
When using a spreadsheet, you have to be able to tell the
spreadsheet, and sometimes other people, where the numbers are. This is done by
referring to the cells, the little boxes that make up the sheet. Spreadsheet
cells are named with a column letter and row number. e.g. A1, C6, R42. When there
are more than 26 columns, two letters are used e.g. AA32,
These cell names may be used to refer to the contents of a cell in
a formula or function.
When a formula or function is copied and pasted from one cell into
another, the cell reference will change automatically unless you tell it not
to. e.g. if cell A1 holds the formula =B1 + C1and that formula is
copied and then pasted into A2, the cell references will change so that the
formula in A2 is =B2 + C2
In other words, the spreadsheet will try to anticipate how a cell
reference would be changed if you had to do it by hand. In most cases this will
give the correct answer. This is because spreadsheets are usually set out in a
logical manner, with columns of figures displaying rows of calculations.
Where cell references can be automatically changed by the
spreadsheet, the cell reference is known as a relative cell address.
Sometimes though, it is essential that the cell reference does not
change when a formula or function is moved into a new cell. e.g. the cell
reference may point to a single figure that must be used in many different
calculations. This might be a tax rate or a conversion factor for currencies.
Having relative addressing for that cell would mean having to duplicate the
figure and put it into a new cell whenever the formula or function is moved.
This is possible but a big waste of time and effort.
The answer is to use absolute addressing. This involves putting $
symbols into the cell reference e.g. $a$1, $A1, A$1
The $ symbol tells the spreadsheet that whatever comes after the
symbol must not be changed if the formula or function is moved.
So, $A$1 means that the cell referred to must
always be A1.
$A1 means that the column
must always be A, but the row number can be changed
A$1 means that the column
can be changed but the row must always be 1.
Formulae and Functions
Both formulae and functions are ways of manipulating the contents
of a spreadsheet.
For the purposes of the IGCSE, a formula is
something you write yourself. e.g. =A1 + A2
while a function is something that is built into
the spreadsheet and is indicated by a key word e.g.=SUM(A1...A2)
Both of these would in fact give the same
answer, they just get there in a different way.
This distinction is a bit artificial though,
since a formula can contain a function
= A1 / SUM(A1...A2)
and a function can contain a formula =SUM(A1/2+
B1)
For examination questions, you will be expected to write formulae
which use the common mathematical operators, + - * / . You may need to use
more than one operator in a single function. e.g. = A1 + 2 / A2
- 6
When using more than one operator, you must
remember the order in which the calculations are performed () * / + -
Thus 3 * 1+2 = 5 because the
3*1 will be calculated first, then the 2 will be added
But. 3*(1+2) = 9 because the
(1+2) will be calculated first and then multiplied by 3.
For the IGCSE ICT, you will need to know how to use a few simple
functions:
SUM, AVG and IF...THEN
SUM adds up the contents
of a set of cells. The cells do not have to be grouped together, although they
often are. e.g. =SUM(A1...A3)
A common mistake with SUM is to include + signs e.g. =SUM(A1+
A2+ A3) This will give a correct answer but is likely to lose
marks in an examination.
AVG This calculates the average
of the contents of a set of cells. e.g. =AVG(A1...A3)
IF...THEN This allows the
spreadsheet to make a decision based on the content of a cell. The exact format
of this function differs between spreadsheets but in general terms it will take
the form =IF(decision, true value, false value) e.g. =IF(A1=B1,1,0) will
give an answer of 1 if cell A1 = cell B1, otherwise it will give an answer of
0.
Linking tables
Quite often, you will develop or use a spreadsheet that has more
than one sheet. These extra sheets will show as tabs, usually at the bottom of
the spreadsheet window.. You may also be able to
see them in a separate navigation window, depending on which spreadsheet you
use.
When you reference a cell on one sheet from one on another sheet,
the cell reference must include the sheet name. Apart from that, the rules for
relative and absolute addressing are the same as when working on a single
sheet.
It is also possible to reference a cell on a completely different
spreadsheet by including the name of the other sheet in the cell reference.
The only complication is that the second sheet must be open on
your computer while the data is being looked at.
Fortunately however, references to other spreadsheets is not
needed for the IGCSE practical examination.
Macros
A macro is the name given to a piece of computer code or scripting
which allows you to perform a particular task in a spreadsheet.
Macros are usually written in a programming language such as Java
or Visual Basic for Applications, or in a scripting language such as Java
Script.
Fortunately, at IGCSE level, you don't need to actually write a
macro as modern spreadsheets include a macro recorder.
The recorder tracks all of the mouse clicks and keystrokes that
are involved in performing a task and writes a macro for you.
The macro can then be run to repeat that task automatically.
Modern spreadsheets usually have a number of built-in macros that
you can use. These will allow you to create items such as drop-down boxes or
buttons which will run another macro when clicked.
For the IGCSE practical examination, it is unlikely that you will
need to produce a macro at all, although you may have to explain what one is
and justify how it might be used.
First of all, what is the difference between a chart and a graph?
Trick question, they're both the same, a visual representation of
data. i.e. a picture. Different spreadsheets use different terms, some call
them charts, some call them graphs.
For the IGCSE, the term graph tends to be used for pictures that
represent the data by a single line, while other forms such as pie charts are
called charts. As long as you understand what sort of picture is being asked
for, it doesn't really matter whether it is called a graph or a chart. I'll
refer to them all as charts for the rest of this section.
There are lots of different types of chart, but for the IGCSE you
only need to know: pie chart, bar chart, single line graph, scattergram.
Pie chart.This is a circular chart
divided into sectors. The whole circle represents a total quantity of something
and each sector represents a sub-division of that something. The area of each
sector is proportional to the quantity it represents.
Pie charts work best when there are only a few sectors and none of
them are very small compared to the others.
Bar chart. This has rectangular
bars where the bar length is proportional to the quantity it represents. The
bars can be horizontal or vertical. Bar charts work best for data which has
specific values, such as age or class size. It is not as useful for continuous
data such as height unless the data is rounded off. e.g. height to nearest
centimetre.
Single line. This shows the data
as a series of points which are joined by a line. In many cases, the data
points should be joined by a smooth curve representing the line of best fit.
Most spreadsheets cannot produce such a curve and just join the points with a
series of straight sections.
Scattergram. This shows the data
as a set of points but does not draw a line through them. Scattergrams are
often used when looking for relationships between two values. e.g. height and
weight.
Creating graphs and charts
How to score marks in the examination
It is almost certain that an IGCSE ICT practical examination will
have a question on spreadsheets.
It is very likely that the question will include the creation of a
chart. There are lots of marking points in a typical chart and it's easy to
mark.
It should be simple to get all the marks for a chart, but it is
also very easy to throw marks away by creating a poor chart.
When creating a chart, you need to think about: appropriate
format, titles, data range, axes, labels, legends.
Appropriate format. This
covers two main aspects. Firstly, what type of chart is best for showing the
required information. You may be explicitly told the type of graph, bar chart,
line, etc. but if not you must read what the question says and decide what type
is best. If you have to make a choice, you will probably have to justify that
choice as well.
Secondly, should you use landscape of portrait orientation. You
are more likely to have to make this decision than you are the one about chart
type. It is harder to set a mark scheme that allows for different types of
chart, but easy to give a mark for landscape / portrait. The information you
need will be in the question. e.g. it may ask for more divisions on the X axis
than the Y axix. In which case landscape is probably best. It may be that the
chart has to fit into a column of a newsletter, so making portrait a better
idea.
Titles. Read the question, if
a specific title is given, make sure you use it, and spell it correctly, and
use capital letters where needed. If the question just says the chart is about
xxxx or must show xxxx, then you will need to make up your own title. In that
case, the title must reflect what is asked for, and be spelled correctly, and
have capital letters where needed. e.g. If you are asked to create a chart
showing the number of pupils in each class of Bankside College, giving the
title 'pupil numbers' is unlikely to get a mark. The title 'Class sizes in
Bankside College' probably will.
Data range. This not a specific
item in the IGCSE specification, but it is essential to get it right. If a
specific range is not given in the question, it should be safe to assume that
all of the data is needed. A common mistake is to include blank columns when
selecting the data. The spreadsheet will still create a chart but you will lose
marks.
Axes. Get them the right
way round. Conventionally, the data type that you control goes on the X,
horizontal axis and the data type that you are measuring goes on the Y,
vertical axis. e.g. if you are charting the number of people who have
particular shoe sizes, the show size is X because you decide what shoe sizes to
look at. The number of people who have each shoe size is Y, because each
person's shoe size is what you measure.
Labels. You need some. At the
least you should label the axes. X and Y don't count as labels, make sure you
put in something appropriate, and spell it correctly, and use capital letters
where needed. You may also want to label other parts of the chart. e.g. on a
line chart showing two or more lines, you may want to label each line, although
that could also be done with a legend (see below).
Legend. This is a key to what
the lines or bars or points on the chart represent. Most spreadsheets will put
one in automatically when you create a chart. If you want a legend, make sure
that the automatic labelling of that legend makes sense..
Important. If there is only one
set of bars, line, etc. you do not need a legend. Get rid of it, you will lose
marks if you don't.
Spreadsheet (Thanks to Daniyal Ali)
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.