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.

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?