Spreadsheets: functions, searching and formatting Cambridge IGCSE Information & Communication Technology revision
Not started
Learn it
In plain words
Functions do in a few characters what would take a long formula, or could not be done at all. A dozen of them cover everything the practical papers ask for.
8 things to know
- SUM adds a range. AVERAGE finds its mean. MAX and MIN give the largest and smallest values. COUNT counts the cells that contain numbers.
- INT gives the whole-number part of a number: INT(7.9) is 7. ROUND rounds to a number of decimal places: ROUND(7.846, 1) is 7.8.
- IF tests a condition and gives one result if it is true and another if it is false: =IF(B2>=50,"Pass","Fail").
- A nested function is a function inside another, such as an IF inside an IF to choose between three results.
- Lookup functions find a value in a table. VLOOKUP searches down the first column of a table and returns a value from another column of the same row. HLOOKUP searches along the first row. XLOOKUP searches one range and returns the matching value from another.
- Data can be sorted on one column or several, and searched using the operators =, <>, >, <, >=, <=, with AND, OR and NOT, and with wildcards.
- Formatting: decimal places, currency symbols and percentages. Conditional formatting changes how a cell looks depending on what it contains, such as turning red if a mark is below 40.
- For printing: set the orientation, the print area and the number of pages, and choose whether to show gridlines and the row and column headings.
Worked example
B2 holds a test mark. Write a formula for C2 that shows "Merit" for 70 or more, "Pass" for 50 or more, and "Fail" otherwise.
- Test for the highest grade first: IF(B2>=70,"Merit", something else).
- The "something else" is another IF: IF(B2>=50,"Pass","Fail").
- Put it inside: =IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail")).
Tips and tricks
- In a nested IF, test the conditions in order, highest first, or a mark of 80 will stop at "Pass".
- A lookup table's range should use absolute references, so that it does not move when the formula is copied down.
It lands in your notebook with its questions as flashcards.
Spreadsheets: functions, searching and formatting: 6 questions and answers
These are the quiz’s questions. Do the quiz first, then come back here for the ones that got you.
What does =MAX(B2:B10) give?
MIN gives the smallest.
What does =COUNT(A1:A20) give?
Cells with text or nothing in them are not counted.
What does =INT(9.99) give?
INT removes the decimal part. It does not round.
What does =ROUND(4.567,2) give?
Two decimal places, and the 7 rounds the 6 up.
B5 contains 72. What does =IF(B5>60,"High","Low") display?
The condition is true, so the first result is shown.
Which function searches down the first column of a table and returns a value from the same row?
V for vertical: it looks down a column.
Quiz
6 questions
Tap an answer and you’ll see straight away whether it’s right, and why.
Worksheet
3 questions, 6 marks. Write your answers on paper, then check them.
Spreadsheets: functions, searching and formatting
Cambridge IGCSE Information & Communication Technology 0417 · 6 marks · papermunch.org
Name ______________________________ Date ______________
B2 contains 45. State what =IF(B2>=50,"Pass","Fail") displays, and explain why.[2]
Show answerHide answer
Fail. The condition B2>=50 is false, so the second result is shown.
State the value of =INT(12.7) and of =ROUND(12.75,1).[2]
Show answerHide answer
12 and 12.8.
Explain what conditional formatting does, with an example.[2]
Show answerHide answer
It changes the appearance of a cell depending on its contents. For example, a cell is shaded red if the value in it is less than 40.
Answers: Spreadsheets: functions, searching and formatting
- 1. Fail. The condition B2>=50 is false, so the second result is shown.
- 2. 12 and 12.8.
- 3. It changes the appearance of a cell depending on its contents. For example, a cell is shaded red if the value in it is less than 40.