How to analyze your data by using conditional calculations
Conditional Calculations in Excel (IF, COUNTIF, AVERAGEIF, and more)
In this video, we will go over several conditional calculations in Excel. These calculations depend of the attainment, or not, of a single criterion or of several criteria. Specifically, we will go over the IF, IFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMIF, SUMIFS, and SWITCH functions and we will be using the 2022 Hong Kong PISA (Programme for International Student Assessment) dataset as the data source for our examples.
Chapters:
0:00 Introduction & setting up the data
0:38 What are conditional calculations?
1:08 The IF function
2:47 Returning TRUE/FALSE without IF
3:09 The IFS function (multi-criteria buckets)
5:41 The COUNTIF function
6:45 Adding operators with quotation marks & ampersand
7:11 Linking criteria to a cell
7:49 The COUNTIFS function (multiple conditions)
9:44 The AVERAGEIF function
9:55 Converting numbers to text with SWITCH
11:37 Calculating averages by gender
13:22 Excluding a value with the "not equal" operator
14:04 The AVERAGEIFS function
17:07 SUMIF and SUMIFS
18:05 Conditional standard deviation with a spilled array
20:04 Wrapping the array in STDEV.S
21:12 Recap
For more information on basic Excel skills, see my Microsoft Excel Basic Guide at https://lib.unb.ca/guides/microsoft-e...
Intro and outro music from Stereoalex at https://icons8.com/music/track/synthw...