Excel Formulae and Functions

This is an advanced course focusing on creating powerful, effective formulas. 20 of the top functions used by analysts are covered including 6 new functions introduced in Excel 2016.

Most users are familiar with just the tip of the iceberg in this application. This is an opportunity to expand your calculation repertoire with advanced formula techniques and functions that deal with a variety of spreadsheet data. Make this powerful application work for you.

Ideal for anyone involved in organizational data, finances or those dealing with recorded results. Confidence using absolute and mixed cell references a must!

Key content

  • Recapping nested IF and logical functions and IFERROR
  • Using the new logical functions IFS and SWITCH
  • Understanding date calculations including EDATE and WORKDAY
  • Recapping VLOOKUP and the more flexible INDEX and MATCH functions that can replace it
  • Utilising INDIRECT for concatenated cell refs and data validation
  • Using the new TEXTJOIN function and a recap of functions to manipulate text – CONCAT, FIND, LEN, REPLACE
  • Summarising data with criteria - SUMIFs and COUNTIFs and the new MAXIFS and MINIFS
  • Working with Array formulas

Objectives

  • Insert a variety of these top worksheet functions
  • Understand how to 'nest' functions for maximum efficiency
  • Utilise different cell references, names and Table Structured References where appropriate

Prerequisite

Excel Intermediate

Suitable for

This is an advanced level workshop which is suitable for those who have completed the prerequisite or who are competent with the topics covered in that workshop. Before registering, it is your responsibility to ensure that you are competent with the topics covered in the prerequisite workshop.

Workshop attendees should be able to use absolute and mixed cell references confidently and be familiar with the way that Functions can be entered.

Notes

PC and Mac computers are available on a first-come, first-served basis. If you require a Mac, please contact us so that we can make the necessary arrangements. Excel 2016 is used. Also suitable for 2013 users.

Cost

Free for University of Auckland and UniServices staff
Free for University of Auckland postgraduate students
$125+GST for others

Registration

Visit our Registration page to learn about registering, withdrawing and our cancellation policy.

Dates, times and venues for 2017

July - September 2017

Monday 17 July 2017, 1-4pm - CANCELLED
Room 305, Level 3, Building 620, 49 Symonds Street, City Campus

Friday 4 August 2017, 9am-12pm - CANCELLED
Room 305, Level 3, Building 620, 49 Symonds Street, City Campus

Wednesday 13 September 2017, 9am-12pm
Room 305, Level 3, Building 620, 49 Symonds Street, City Campus

October - December 2017

Thursday 26 October 2017, 1-4pm
Room 305, Level 3, Building 620, 49 Symonds Street, City Campus

Tuesday 21 November 2017, 9am-12pm
Room 305, Level 3, Building 620, 49 Symonds Street, City Campus