Advanced Excel Formulas and Functions (1 day)

Scheduled courses in Peterborough or £595, plus VAT, on-site.

The ability to understand and create complex formulas is critical to the building of effective Excel solutions. This one day course shows experienced Excel users how to rank and tabulate data using statistical functions, format data with text and math functions, and work with financial data using advanced formulas.

Text Functions

Locating and extracting data with FIND, SEARCH, and MID; Extracting specific data with LEFT and RIGHT; Removing extra spaces with TRIM and removing hidden characters with CLEAN; Using ampersands and CONCATENATE to combine data from different cells;  Adjusting the case within cells with PROPER, UPPER, and LOWER; Adjusting character content with REPLACE and SUBSTITUTE; Using other utility text functions: LEN, REPT, VALUE, TEXT

Statistical Functions

Finding the middle value with MEDIAN; Ranking data without sorting with RANK; Finding the largest and smallest values with LARGE and SMALL; Tabulating blank cells with COUNTBLANK; Using COUNT, COUNTA, and the status bar

Mathematical Functions

Working with ROUND, ROUNDUP, and ROUNDDOWN; Working with MROUND, CEILING, and FLOOR for specialized rounding; Using the INT and TRUNC functions to extract integer data; Finding the remainder with MOD and using MOD with conditional formatting; Practical uses for the random number functions RAND and RANDBETWEEN; Converting a value between measurement systems with CONVERT; Using the powerful AGGREGATE function to bypass errors and hidden data; Using the ROMAN and ARABIC functions to display different numeral systems

Date and Time Functions

Understanding Excel date/time capabilities in formulas; Using TODAY and NOW functions for dynamic date/time entry; Identifying the day of the week with WEEKDAY; Counting working days with NETWORKDAYS; Determining a completion date with WORKDAY; Tabulating date differences with DATEDIF; Calculating end-of-month and future/past dates with EDATE and EOMONTH; Converting text entries into dates and times with DATEVALUE and TIMEVALUE

Array Formulas and Functions

Extending formula capabilities with arrays; Counting unique entries in a range with an array formula; Determining frequency distributions with FREQUENCY; Flipping row/column orientation with TRANSPOSE; Building analysis via regression techniques with TREND and GROWTH; Using array formula techniques with the MATCH function for complex lookups

Reference Functions

Getting data from remote cells with OFFSET; Returning references with INDIRECT; Using INDIRECT with data validation for two-tiered pick list scenarios; 11. Text Functions; Locating and extracting data with FIND, SEARCH, and MID; Extracting specific data with LEFT and RIGHT; Removing extra spaces with TRIM and removing hidden characters with CLEAN; Using ampersands and CONCATENATE to combine data from different cells;  Adjusting the case within cells with PROPER, UPPER, and LOWER; Adjusting character content with REPLACE and SUBSTITUTE; Using other utility text functions: LEN, REPT, VALUE, TEXT

Information Functions

Extracting information with the CELL and INFO functions; Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER; Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA; Using the ISFORMULA function with conditional formatting