Learn the most important Excel functions and how to use them for business data analysis.
Formulas & Techniques covered in this video:
====================================
SUMIFS
COUNTIFS
AVERAGEIFS
XLOOKUP
MAXIFS
INDEX + MATCH
FILTER
MAX
LARGE
~
How and when to use each formula
Working with wildcards & patterns
Using date ranges in your criteria
Multiple criteria with formulas
How to combine formulas to solve a problem
Download the Sample Workbooks:
============================
✅ Completed Workbook: chandoo.org/wp/wp-content/uploads/2022/03/top-formulas-for-data-analysis-answers.xlsx
🟩 Blank Workbook (to practice): chandoo.org/wp/wp-content/uploads/2022/03/top-formulas-for-data-analysis-blank.xlsx
⏱Topics in the video:👇
===================
0:00 - Introduction to the data & 15 Formulas
1:20 - Q1 Total Downloads of SugarRush App (SUMIFS)
3:17 - Q2 Total downloads for C apps (wildcards)
5:04 - Q3 Uninstalls in December 2021 (Using Dates)
7:00 - Q4 Downloads in Oct, Nov, Dec 2021 (Date Ranges)
9:21 - Q5 How many times we had more than 8000 downloads per app month? (COUNTIFS)
10:52 - Q6 5 star rating % for Sugar Rush (Complex Problem)
12:31 - Q7 Average uninstalls for Subway Wafers (AVERAGEIFS)
13:10 - Q8 Which month has highest uninstall ratio? (MAX & XLOOKUP)
16:23 - Q9 Average monthly downloads (AVERAGEIFS)
17:06 - Q10 Most downloads in any month / app? 2nd and 3rd places also? (MAX, LARGE)
18:18 - Q11 Which month and app was that? (XLOOKUP, INDEX+MATCH)
20:02 - Q12 In October 2021, which app has most 1star ratings? (XLOOKUP vs. FILTER)
23:35 - Q13 Does it change by December 2021? (XLOOKUP vs. FILTER)
24:50 - Q14 What are the best download figures for each app? (MAXIFS)
26:00 - What is the least downloaded app in each month? (FILTER)
28:03 - How to learn more?
👋 ALTERNATIVE FORMULAS FOR OLDER EXCEL VERSIONS:
==================================================
XLOOKUP alternative = Use INDEX MATCH.
Instead of =XLOOKUP(F61,F53:F59,C53:C59)
use = INDEX(C53:C59, MATCH(F61, F53:F59,0))
MAXIFS alternative = Use MAX(IF()) Array formula.
Instead of =MAXIFS(data[Downloads],data[App],C99)
Use =MAX(IF(data[App] = C99, data[Downloads])) and press CTRL+Shift+Enter to get the correct result.
NEXT STEPS FOR YOU 👌
====================
💥💥💥
Do you want to learn how & when to use various Excel formulas, how they compare with other ways of analysing data in Excel? Consider going for Excel School program. In this online class, I teach how to use Excel and how to do your work better. Visit chandoo.org/wp/excel-school-program/ for full info & to enrol.
💥💥💥
Additional videos on the formulas:
===========================
IF formula & Advanced IF formulas - youtu.be/-yFpzIRifK4
SUMIFS formula (10 examples) - youtu.be/YEt-aYbDTrs
INDEX MATCH in detail - youtu.be/kly0uPIM4IU
XLOOKUP - youtu.be/gpwqUc3y7ZA
VLOOKUP - youtu.be/8rtvDQVQaA0
FILTER - youtu.be/JuTdj2j-9Kg
~
Don't forget to be AWESOME 😎
#Excel #DataAnalysis
- These are the ONLY 15 functions you need to know in Excel (to get most things done) ( Download)
- Top 10 Essential Excel Formulas for Analysts in 2024 ( Download)
- Excel Formulas and Functions You NEED to KNOW! ( Download)
- Top 10 Most Important Excel Formulas - Made Easy! ( Download)
- Excel Formulas and Functions Tutorial ( Download)
- Top 10 Excel Functions you should learn in 2024 ( Download)
- Excel for Accounting - 10 Excel Functions You NEED to KNOW! ( Download)
- Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function ( Download)
- Master Excel 2024: Advanced Filter, Sort, and Switch Functions for Data Analysis ( Download)
- 3 Quick tips to get better in Excel formulas ( Download)
- How to find the Top 5 values in Excel with one formula (LARGE formula) ( Download)
- 20 Excel Shortcuts to Save You HOURS of Work ( Download)
- Mastering Excel: Top 10 Formulas You Need to Know ( Download)
- Master Data Analysis on Excel in Just 10 Minutes ( Download)
- EASIEST Top 10 List in Excel | DYNAMIC and Interactive ( Download)
