Excel’s core strength is its ability to perform calculations, and calculations are done with formulas and functions. This course focuses on mathematical formulas as well as a broad view of useful functions that do the heavy lifting in Excel. We will review the basics of building formulas and functions, beginning from one simple technique that makes simple formulas, you’ll build up through more and more complex situations learning the correct way to integrate your numbers no matter where they’re located.
As well as teaching you how to create the most commonly used functions (with a special focus on IF and VLOOKUP), this course will also show you how to understand any complicated function formula you might encounter in future. Not only will you know many useful tricks by the end of the course, you’ll have laid the foundation for future self-learning up to a very high level of complexity.
If you’ve looked at other peoples’ spreadsheets full of words like IF, VLOOKUP, ROUND, OFFSET, SUMIF and NETWORKDAYS etc. and wondered how they worked, then this is the course you are looking for.
What we will cover:
Review of the basics of Formula creation
- Cell reference
- AutoSum and Auto fill
- Mathematical symbols
- Rules of formula making
- Rules of functions
- The Fx button
- Nesting functions
- Sum, Average, Min, max, count, counta, vlookup, hlookup, product
Logical Functions
- If
- And
- Or
- True/false
Reference Functions
- Advance Vlookup
- Match
- Choose
- Index
- Offset
- Indirect
Statistical Functions
- Median
- Rank
- Large and Small
- Countblank
Data Functions
- Weekday
- Networkdays
- Workday
- Dateif
Math Functions
- Mod
- Rand and Randbetween
- Convert
- Aggregate
Array formulas
- Transpose
- Frequency
- Trend and Growth
Financial Functions
- PMT
- FV
- PV
Information Functions
- IS
- ISERR, ISERROR, IFERROR