Prerequisites
This course is designed for Excel users who already have a basic knowledge of Excel or have attended the Introduction course

Course goals
Delegates will learn how to work with calculations from basic calculations such as adding, subtracting, multiplying and dividing though some of the basic functions and on to more advanced functions.  The functions covered on the day will be determined by the requirements of the group.
Topics

Basic Calculations
Addition, Subtraction, Multiplication, Division
Autofilling Calculations
Using Absolute Referencing and Mixed Referencing
Basic Functions
SUM
AVERAGE
MAX
MIN
Calculations across Worksheets and Workbooks
Calculations across Worksheets
Calculations across Workbooks
Named Ranges
Creating Named Ranges
Using Named Ranges in Calculations
Function wizard
Using the Function Wizard to Structure your Function
Using the Function Wizard to find out about New Functions
Logical Functions
IF – to perform 2 different actions based on a condition, for example giving a discount if more than a certain number of the product is bought otherwise charging full price
AND and OR – to make IF more flexible
Nested IF – to perform multiple different actions based on multiple different conditions, for example having multiple different discount percentages based on multiple different quantities sold

Lookup Functions
VLOOKUP – to enable looking up data from pricing tables or equivalent
INDEX and MATCH – another way to look up data
Using ERROR functions to error trap
Date Functions
General Date Calculations – to calculate the number of days between 2 dates
TODAY – to insert todays date that automatically updates
DATEDIFF – to calculate the number of working days between 2 dates
Text Functions
CONCATENATE – to put information from different cell together into one cell, for example first name and surname
UPPER, PROPER, LOWER – to change the case of data
LEFT, MID, RIGHT – to extract information from the start, middle or the end of the text (for example separating out codes)
TRIM – removes extra spaces at the start or end of text to help create consistent data
Statistical Functions
COUNTIF, SUMIF, AVERAGEIF – to find the number of entries, total or average of specific data
COUNTIFS, SUMIFS, AVERAGEIFS – as above but more flexible
Troubleshooting Functions
What do Error Messages mean?
Predecessors and Successors
Watch Window
Show Formulas
My Calculations Don’t Update.  Why?