Duration
1 day
LESSON 1 – FORMULAS AND FUNCTIONS BASIC
INTRODUCTION FORMULA BASICS
USE CELL REFERENCES
TRANSPOSE A FORMULA
COPY AND PASTE VALUES
PERFORM A QUICK CALCULATION
LESSON 2 - STATISTICAL AND LOGICAL FUNCTIONS
USE COUNTIF TO CALCULATE SURVEY RESULTS
USE SUMIF TO ADD DATA THAT SATISFY CONDITION
USE AVERAGEA TO INCLUDE ALL CASES
RETURN MINIMUM AND MAXIMUM VALUE USING MAXIFS AND MINIFS
USE IF FUNCTION TO PREVENT DIVISION BY ZERO
IFS AND SWITCH FUNCTIONS HELP SPECIFY A SERIES OF CONDITIONS
USE ISERROR FUNCTION TO AVOID ERROR DISPLAY
CREATE MULTIPLE CONDITIONS USING NESTED IF
LESSON 3: LOOKUP & REFERENCE FORMULAS
USE VLOOKUP TO FIND SPECIFIC DATA
USE HLOOKUP TO FIND VALUES IN ROWS
USE INDEX & MATCH TO RETRIEVE DATA
LESSON 4 - TEXT FORMULAS
CHANGE CASE OF TEXT
JOIN TEXT FROM MULTIPLE COLUMNS
COMBINE TEXT AND FORMATTED NUMBERS
CONVERT IMPORTED TEXT FORMAT INTO NUMBERS
BREAK IMPORTED DATE FIELD INTO INDIVIDUAL COLUMNS
EXTRACT A NAME FIELD INTO THREE COLUMNS
COMBINE TEXT STRINGS USING TEXTJOIN AND CONCAT
LESSON 5: DATE & TIME FORMULAS
CALCULATE A PERIOD OF TIME BETWEEN TWO DATES
PERFORM CALCULATION WITH TIME FIELDS
LESSON 6: ARRAY AND DATABASE FUNCTIONS
USE ARRAY FORMULAS
CALCULATE THE DIFFERENCE BETWEEN MAXIMUM AND MINIMUM VALUES IN AN ARRAY
FIND VALUE IN LIST
USE FREQUENCY FUNCTION TO COUNT RESPONSES
ANALYSE DATA IN A DATABASE
LESSON 7: EFFICIENCY TIPS
REDUCE FORMULA RECALCULATION TIME
ENTER FORMULAS QUICKLY BY SHORTENING SHEET NAMES
SELECT & PROTECT CELLS CONTAINING FORMULAS
REDUCE DATA ENTRY ERRORS BY USING DATA VALIDATION
DISPLAY & PRINT FORMULA SYNTAX
USE AUDITING TOOLS TO CHECK FOR ERRORS
CHECK DATA BY USING TRACE PRECEDENTS
USE COMMENTS TO ANNOTATE YOUR WORKSHEET
Programme scope, delivery arrangements and suitability are confirmed during enquiry.