International experts in Excel

Your power-packed agenda  

Make formulae easier to follow

• Shorten formulae by knowing the order of precedence.
• Use names to build formulae more quickly and make them easier to follow.
• Modify the layout for ease of understanding.

Understand the calculation options

• Know the pros and cons of Precision as Displayed.
• Beware of traps with Manual and Iteration options.

Common formulae problems

• Find the sources of circular references.
• Discover solutions to the problem of formulae that refuse to calculate.
• Be aware of the problems caused by decimal to binary conversions and their solutions.
• Avoid problems when inserting rows, columns or sheets.
• Know why some formulae are slow to calculate.
• Discover volatile functions.
• Hide very long formulae.
• Beware of the Extend formulae feature.

Array formulae

• Discover array formulae and how to create them.
• Hear the pros and cons.
• Sum absolute values.
• Sum rounded values.
• Find max of numbers entered as text.
• Check whether data is sorted.

File-link formulae

• Know the best way to create file-link formulae.
• Be aware of the many traps and the solutions.
• Find the source of unwanted "update links" messages.
• Remove file links.

Maintainable formulae

• Make formulae easy to track.
• Build formulae that don't need changing every month.
• Build self-maintaining formulae.
• Reduce errors through properly constructed cross-checks.
• Build cross-checks that can't be missed.
• Include comments in formulae.

Learn about functions new to Excel 2007 and 2010

• Such as SUMIFS, COUNTIFS, AVERAGEIFS, IFERROR and AGGREGATE.

Functions and Formulae

• Sum positives.
• Sum but ignore errors.
• Prevent duplicate entries.
• Sum alternate rows or columns.
• Sum the largest five entries.
• Round to nearest 5, 20, or any number.
• Round up or down.
• Avoid divide-by-zero errors.
• Learn how to build complex IF formulae.
• Use MIN and MAX as a shorter alternative to IF.
• Return a particular month's figure from a range.
• Calculate year-to-date for the specified month.
• Prevent formulae being corrupted by a move.
• Sum the last several months.
• Create self-extending ranges for charts, PivotTables and formulae.
• Use lookups to create self-maintaining systems.
• Return item from left of search column.
• Return name of biggest item.
• Use faster alternatives to VLOOKUP and SUMIF.
• Discover alternatives to confusing multiple-nested IFs.
• Sum only visible rows.
• Create any criteria you require for use with the "D" functions.
• Analyse and cross-tabulate database data.
• Count the entries in each category.
• Display path filename, day, date, time.
• Display date and time across multiple columns.
• Calculate with dates and times.
• Generate a series of month-end dates.
• Convert 6-digit date to an Excel date.
• Calculate working days between two dates.
• List working days in month.
• Discover text formulae and their uses.
• Join numbers, dates and items of text into a single paragraph.
• Convert numeric entries to text and vice versa.
• Split full names into first name and last name.
• Make subtotal numbers bold.
• Discover Conditional Formatting.
• Convert text case to upper, lower or proper.

Functions covered include: SUMPRODUCT, SUBTOTAL,SUMIF, SUMIFS, COUNTIF, COUNTIFS, IF, AVERAGEIFS, LARGE, ROUND, INT, INDEX, VLOOKUP, TRANSPOSE, MATCH, DSUM, MAX, MIN, CELL, COUNT, NOW, CHOOSE, ABS, &, T, LEFT, MID, RIGHT, MOD, OFFSET, FIND, SEARCH, TRIM, UPPER, PROPER, VALUE, TEXT, DATE, DATEVALUE, TIME, TIMEVALUE, NETWORKDAYS, WORKDAY, ISERROR, IFERROR, ISNA, AGGREGATE, FREQUENCY... and more.

Who should attend:

• Accountants (and many non-accountants, too) who have been using spreadsheets for some time and are quite familiar with the basics of formula construction, formatting and printing.
• The seminars are designed for those working in accounting: it is not essential for delegates to be qualified accountants.