Excel Advanced Formulas
 

Our Microsoft Excel Formulas course is suitable for those with an advanced working knowledge of Excel who want a greater understanding of more sophisticated formulas & functions within Excel. Familiarity with creating functions including IF, VLOOKUPs and HLOOKUPs or our Excel Advanced course.

 

Benefits

  • At the end of this course you will have a greater understanding of building complex formulas using nested functions (such as IF statements). 
  • You will find quicker ways to customise formulas by taking advantage of the built-in functions available in Excel. Save time by learning more sophisticated search & auditing techniques when working with data-heavy spreadsheets.

 

Duration & Cost

Option 1: 
1 day (9 hours), was £450, currently only £195

Option 2: 
3 days (3 hours per day), was £450, currently only £195

Option 3: 
12 Weeks (6 hours per week - 3 hours per day), £1500.00

 

Course Syllabus

  • Building complex formulas

- Using nested IF statements
- Creating compound logical tests using AND, OR, NOT functions with IF statements
- Nesting LOOKUP functions
- Source table structure information using CHOOSE function
- Using MATCH function to locate data
- The INDEX function for retrieving information by location
- Using a nested formula containing INDEX, MATCH and MATCH (two-way lookup)

  • Advanced functions in formulas

- Using COUNTIFS,SUMIFS, & AVERAGEIFS for tabulating data based on single/multiple criteria
- Statistical functions; MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD
- AGGREGATE function to sum data in ranges with errors
- Use a variety of Financial functions such PMT, FV, IRR

  • Data & Text Functions

- Smarter ways to calculate date & time; TODAY
- NETWORKDAYS, WORKDAY, DATEDIF 
- Use TYPE to identify data type of existing cell contents
- Text functions; UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN
- TRIM excess space in cells

  • Auditing formulas

- Tracing formula precedents, dependents, and errors
- Correcting errors in formulas
- Combining IF with VLOOKUP to suppress error messages
- Using the IS information function
- Error checking functions; ISERR, ISERROR, IFERROR

Refer A Friend Banner

Accountancy Course Info Request

Form by ChronoForms - ChronoEngine.com

Save 30% on Student Oyster Card

Student Oyster Discount Offer


Website security

NUS NY 728x90 2016 12 15 V1 NC

Opening Hours

Monday
09:00 - 21:00
Tuesday
09:00 - 21:00
Wednesday
09:00 - 21:00
Thursday
09:00 - 21:00
Friday
09:00 - 21:00
Saturday
09:00 - 21:00
Sunday
09:00 - 21:00
Go to top
Join us at Amity College Ltd.