Be able to perform searches using a variety of operators including AND, OR, NOT, >, <, =, >=, <=, <>

Topic 20 – Spreadsheets: Searching with Operators

Objective

Students will be able to perform searches and logical tests using the operators AND, OR, NOT, >, <, =, >=, <=, <> in spreadsheet formulas and to apply these operators in the range of functions and features required by the Cambridge IGCSE ICT 0417 syllabus.

1. Syllabus Checklist – Section 20 (Spreadsheets)

2. Logical Operators

3. Comparison Operators

4. Order of Operations & Parentheses

• PEMDAS: Parentheses → Exponents → Multiplication/Division → Addition/Subtraction.
• Logical functions (IF, AND, OR…) are evaluated after any arithmetic inside their arguments.

Example – combine arithmetic with a logical test:

=IF((A2*B2)+C2>100, "High", "Low")

1. Multiply A2 by B2.
2. Add C2.
3. Compare the sum with 100.
4. Return “High” if true, otherwise “Low”.

5. Cell Referencing

6. Named Cells & Ranges

1. Select the cells you wish to name (e.g., B2:B20).
2. Enter a name in the Name Box or use Data ► Named Ranges (Excel) / Data ► Named ranges (Google Sheets).
3. Use the name in any formula: =SUM(Sales), =AVERAGE(Sales), =COUNTIF(Sales,">=200").

7. Using Operators in Common Functions

7.1 COUNTIF / COUNTIFS

When the criterion is a comparison, the operator must be inside quotation marks.

=COUNTIF(A2:A100,">=200")                     'counts values ≥200
=COUNTIFS(A2:A100,">=200", B2:B100,"<>", C2:C100,"Yes")

7.2 SUMIF / SUMIFS

=SUMIF(D2:D50,">0",E2:E50)                     'adds E where D > 0
=SUMIFS(E2:E50, A2:A50,">=2023-01-01", B2:B50,"North")

7.3 FILTER (Google Sheets) & FILTER (Excel 365)

=FILTER(A2:E100, (A2:A100>500)*(OR(B2:B100="North",B2:B100="East")))

The same formula works in Excel 365 because FILTER is a dynamic‑array function. In earlier versions of Excel use Data ► Advanced Filter with a criteria range.

7.4 LOOKUP Functions

• VLOOKUP: =VLOOKUP(lookupvalue, tablearray, col_index, FALSE)
• HLOOKUP: =HLOOKUP(lookupvalue, tablearray, row_index, FALSE)
• XLOOKUP (Excel 365/2021):

  =XLOOKUP(lookupvalue, lookuparray, return_array, "Not found", 0)

  • 0 = exact match (most common in exam questions).
  • -1 = exact match or next smaller item.
  • 1 = exact match or next larger item.

Operators can be part of the lookup_value. Example – find the first price greater than 100:

=VLOOKUP(">100",A:B,2,FALSE)

8. Data Validation with Logical Tests

Validate that a numeric entry is between 0 and 100:

Data ► Data validation ► Criteria: Custom formula is
=AND(A2>=0, A2<=100)

If the entry fails, an error message is displayed, reinforcing the use of AND and comparison operators.

9. Conditional Formatting Based on Operators

Highlight scores below 50:

1. Select the range (e.g., B2:B20).
2. Home ► Conditional Formatting ► New Rule ► Use a formula.
3. Enter =B2<50 and choose a red fill.

10. Printing & Page‑Setup

• Print Area: Set to the filtered range (Excel: Page Layout ► Print Area ► Set Print Area).
• Margins & Orientation: Adjust to fit the data; use “Fit to one page width” if required.
• Headers/Footers: Insert codes such as &[File] (file name) and &[Page] (page number).

11. Spreadsheet Workflow – Saving, Exporting, Sharing

• Save in the native format (.xlsx for Excel, .gsheet for Google Sheets) to retain formulas, named ranges, and validation.
• Export as .csv when only raw data is needed (e.g., for database import).
• Share via cloud services (Google Drive, OneDrive) or attach the file to an email for collaborative work.

12. Practical Exercise

1. Create a sheet with the columns: Student ID, Score, Passed (Yes/No), Region.
2. Enter at least 15 rows of realistic data.
3. In column E, flag students who scored ≥ 70 and are from “South”:

   =AND(C2>=70, D2="South")

4. Count how many students meet the condition:

   =COUNTIFS(C2:C16,">=70", D2:D16,"South")

5. Display only the flagged rows:

   • Google Sheets: =FILTER(A2:E16, E2:E16=TRUE)
   • Excel 365: Same FILTER formula.
   • Excel (pre‑365): Use Data ► Advanced Filter with the criteria range E1:E2 where E1 contains the header and E2 contains TRUE.

6. Apply conditional formatting to the filtered view so that flagged rows are highlighted in green (use the same formula as the flag column).
7. Set the print area to the filtered rows, add a header “South Region – High Scorers”, and print to PDF.
8. Save the workbook as .xlsx and also export the filtered data as .csv.

13. Common Mistakes to Avoid

• Enclose text criteria in double quotes: "North", not North.
• Use <> for “not equal to”. != is not recognised in spreadsheet formulas.
• When a criterion contains a comparison operator, place the whole operator‑value pair inside quotation marks, e.g. =COUNTIF(A2:A10,">=200").
• Logical operators (AND, OR, NOT) return TRUE/FALSE; they cannot be used as arithmetic symbols.
• Check whether you need relative or absolute references before copying a formula; otherwise the criteria may shift unintentionally.
• Remember operator precedence: arithmetic → comparison → logical functions. Use parentheses to force the order you need.

14. Summary of Operators