Course Content
Advanced Word
0/9
Advanced Document Setup and Formatting Concepts
Advanced Text and Paragraph Formatting
Styles, Templates and Automated Features
Tables, Columns, Tabs and Document Layout
Mail Merge and Labels
Advanced Find, Replace and Document Automation
Advanced Graphics, Images and Objects
Reviewing, Protection and Collaboration
Test
Advanced Spreadsheets
This topic covers the ICDL Advanced Spreadsheet module: conditional formatting, advanced functions, charts and tables, data analysis and what-if scenarios, data validation and auditing, macros and templates, and collaboration features.
0/9
Advanced Workbook and Data Setup
Named Ranges, 3D References and Linking
Logical and Conditional Functions
Statistical, Mathematical, Date/Time and Text Functions
Data Management: Tables, Sorting, Filtering and Subtotals
Advanced Charts and Visualisation
Data Analysis Tools and What-If Scenarios
Auditing, Protection and Macros
Excel Test
Advanced Presentation
This topic covers the ICDL Advanced Presentation module: planning professional presentations, using slide masters and templates, working with graphical objects, charts and diagrams, integrating multimedia and animation, embedding and linking data from other sources, and managing presentations for different audiences.
0/9
Presentation Planning
Slide Masters & Templates & Themes
Advanced Text & Paragraph Formatting
Working With Images, Graphics & Design
Animations and Transition
Working With Charts, Tables and SmartArt
Reviewing, Comments, Proofing and Collaboration
Output & Distribution
Test
Advanced Databases
This topic covers the ICDL Advanced Database module: designing and planning relational databases, creating tables and relationships, developing advanced queries, building forms and subforms, generating complex reports, using macros and SQL for automation, and implementing database security and linking data from external sources.
0/9
Advanced Database Concepts and Design
Advanced Tables and Field Properties
Relationships and Referential Integrity
Advanced Select Queries
Action Queries: Update, Append, Delete and Make-Table
Advanced Forms and Controls
Labels and mailing reports
External Data, Macros and Final Database Tools
Test
00:45:00
Test
Protected: Icdl advanced

Advanced Spreadsheets

Lesson 4 – Statistical, Mathematical, Date/Time and Text Functions

This lesson introduces statistical, mathematical, date/time, text and financial functions used for analysis, reporting and data preparation.

1. Statistical functions

2. Mathematical functions

Mathematical functions allow you to control rounding, perform calculations and clean numeric data.

  • ROUND, ROUNDDOWN, ROUNDUP – control decimal places.
  • INT – round a number down to the nearest whole number.
  • SUMPRODUCT – multiply corresponding items and sum the results (useful in weighted analysis).
  • ABS – return the absolute (positive) value.

3. Date and time functions

These functions allow you to work with dates for reporting, validation, and automation.

  • TODAY() – returns the current date.
  • NOW() – returns the current date and time.
  • DATE(year, month, day) – builds a valid date.
  • YEAR, MONTH, DAY – extract parts of a date.
  • NETWORKDAYS – calculates working days between two dates.

4. Text functions

Text functions help clean, extract and format text data, especially when working with imported data or building IDs.

  • CONCAT / CONCATENATE – join text from multiple cells.
  • LEFT, RIGHT – extract characters from the start or end of a string.
  • MID – extract characters from the middle of a string.
  • LEN – count characters in a cell.
  • UPPER, LOWER, PROPER – change text case.
  • TRIM – remove extra spaces.

5. Financial functions

Financial functions support loan, investment and repayment calculations.

  • PMT – calculate regular loan repayments.
  • PV – present value.
  • FV – future value.
  • Document assumptions such as annual interest rate, periods and payment timing.

6. Combining different functions

Spreadsheet models frequently require combining text, logical and date functions.

  • Nesting different functions can automate decisions and generate meaningful output.
  • Example: =IF(TODAY() > DueDate, “Overdue”, “On time”)
  • Example: =LEFT(UPPER(Surname), 3) & YEAR(TODAY()) – generate a simple ID.

7. Practical Activity

Complete the following tasks to apply the lesson skills:

  • Create a table of employees including: Name, Start Date, Salary.
  • Use date functions to calculate length of service (for example: =YEAR(TODAY()) – YEAR(StartDate)).
  • Use RANK or LARGE/SMALL to identify highest and lowest salaries.
  • Use text functions to build email addresses (for example: =LOWER(LEFT(Firstname,1) & Surname & “@company.co.uk”)).

Previous
Next