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: https://chandoo.org/wp/wp-content/uploads/2022/03/top-formulas-for-data-analysis-answers.xlsx
🟩 Blank Workbook (to practice): https://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 https://chandoo.org/wp/excel-school-program/ for full info & to enrol.
💥💥💥
Additional videos on the formulas:
===========================
IF formula & Advanced IF formulas – https://youtu.be/-yFpzIRifK4
SUMIFS formula (10 examples) – https://youtu.be/YEt-aYbDTrs
INDEX MATCH in detail – https://youtu.be/kly0uPIM4IU
XLOOKUP – https://youtu.be/gpwqUc3y7ZA
VLOOKUP – https://youtu.be/8rtvDQVQaA0
FILTER – https://youtu.be/JuTdj2j-9Kg
~
Don’t forget to be AWESOME 😎
#Excel #DataAnalysis
