Ultimate Excel Course #7: Advanced Formulas for Analysing Data

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
7 Lessons (44m)
    • 1. Introduction to the Class


    • 3. Summing the Values Between Two Dates

    • 4. Summing Values by a Specific Weekday - SUMPRODUCT Example 2

    • 5. An Advanced Sum Formula with SUMPRODUCT

    • 6. The Secret AGGREGATE Function

    • 7. An Advanced Array Example of AGGREGATE


About This Class


This class is #7 in a series called the Ultimate Excel Course.

In this class you will learn the best Excel formulas to analyse data.

In this class we start by showing examples of the SUMIF, COUNTIF and AVERAGEIF functions. These are the most common functions of their type and will complete most of your analytical tasks.

We then look at using the multiple condition versions of those functions - SUMIFS, COUNTIFS and AVERAGEIFS. As an example we sum the values between two dates. The dates are stored in cells so that the formula can be dynamically changed by simply changing a date.

We then look at the brilliant SUMPRODUCT - a personal favourite of mine. This function is so powerful and useful.

We first use it to create more complex logic. An advanced example that the SUMIFS function would not be able to handle. We then use a more niche example of summing the values that occur on a specific weekday.

And finally we explore the AGGREGATE function. This unknown function can be very useful indeed. We first demonstrate a typical example of its use. And then move onto an advanced version.

The Excel files used during the class are provided so that you can follow along. You can then take on the project exercises to test your skills in action.

Please post any questions in the discussion area and I will get back to you.

Grab a coffee, enroll and let's do this.

Please note that this is class #7 in a series called the Ultimate Excel Course.

You can find direct links to the other classes in the series below.

Class #1 - Excel Formulas Made Easy - Get up to Speed with Excel Formulas Fast

Class #2 - Essential Skills for Working with Large Spreadsheets

Class #3 - Logical Functions - The Decision Making Formulas of Excel

Class #4 - Conditional Formatting: Make your Data Come to Life

Class #5 - Lookup Functions - The Powerful VLOOKUP Functions and Beyond

Class #6 - Validating and Protecting Excel Data

Class #7 - Advanced Formulas for Analysing Data

Class #8 - Excel Charts for Simple and Effective Data Presentation

Class #9 - Cleaning and Preparing Data Ready for Analysis

Class #10 - Formatting your Data Ranges as Tables for Superior Management

Class #11 - Master PivotTables for Powerful Analysis and Reporting

Class #12 - Get & Transform Data with Power Query

Class #13 - Introduction to the Data Model and PowerPivot