title

10 Advanced IF formulas every analyst should know

description

Almost 70% of spreadsheets contain IF formula. So how well do you know it? In this *ADVANCED* video, learn all about:
-- Using IF to check ONLY One of condition
-- Any Two out of Three conditions
-- Between condition with MEDIAN function
-- Replacing nested if formulas with *NEW* IFS function
-- Speeding up IF logic with Boolean replacement hack
-- Check List membership
-- Using Arrays with if (MedianIF example)
-- Wildcard comparisons in IF formula
-- Using IF with Conditional Formatting
-- IF + Charts for awesome results
Timestamps:
===========
0:00 - Intro
0:25 - Only One of condition (XOR)
2:12 - Any two out of three conditions
4:58 - Between formula (AND method & MEDIAN method)
7:00 - Nested IF replacement with IFS()
9:03 - Pop Quiz
9:16 - Avoiding IF formulas with Boolean Replacement Hack
11:42 - List membership check with IF formula
13:35 - Using Arrays with IF - Median IF example
16:10 - Wildcard check with IF
17:45 - Conditional formatting with IF
19:48 - Charts with IF formula
21:23 - Closing remarks
Example files:
===========
https://chandoo.org/wp/advanced-if-tricks/
ðŸ‘‰Checkout my online course on Advanced Excel & Data analysis
===================================================
ðŸ’»ðŸ“—ðŸ’¥ https://chandoo.org/wp/excel-school-program/
#IFLove #Excel

detail

{'title': '10 Advanced IF formulas every analyst should know', 'heatmap': [{'end': 107.667, 'start': 65.51, 'weight': 0.736}, {'end': 277.155, 'start': 261.728, 'weight': 0.722}, {'end': 359.047, 'start': 328.352, 'weight': 0.738}, {'end': 539.187, 'start': 509.036, 'weight': 0.986}, {'end': 1043.727, 'start': 1012.472, 'weight': 0.842}, {'end': 1173.471, 'start': 1115.438, 'weight': 0.881}, {'end': 1266.925, 'start': 1221.05, 'weight': 0.935}], 'summary': 'Introduces 10 advanced examples of if formulas in excel, including exclusive or condition and checking for two out of three conditions, as well as demonstrating excel logical functions for evaluating multiple conditions and simplified calculation using boolean replacement.', 'chapters': [{'end': 244.688, 'segs': [{'end': 107.667, 'src': 'heatmap', 'start': 55.229, 'weight': 0, 'content': [{'end': 65.51, 'text': 'such a thing is called exclusive or, and you can use the if function along with the XOR function to get what you want.', 'start': 55.229, 'duration': 10.281}, {'end': 80.497, 'text': 'so you will say if XOR gender is equal to male, comma, salary under 85, 000 and then whatever you want as an output.', 'start': 65.51, 'duration': 14.987}, {'end': 82.618, 'text': 'so, for example, I would like to include them.', 'start': 80.497, 'duration': 2.121}, {'end': 90.24, 'text': 'so I will point to H4 and make it an absolute reference, same with exclude absolute reference.', 'start': 82.618, 'duration': 7.622}, {'end': 97.763, 'text': 'to make something an absolute reference, you can just point to the cell and then press the F4 key.', 'start': 90.24, 'duration': 7.523}, {'end': 101.224, 'text': 'Excel will add the necessary dollars for you.', 'start': 97.763, 'duration': 3.461}, {'end': 107.667, 'text': 'just drag this down and you will find people who are meeting only one of those conditions.', 'start': 101.224, 'duration': 6.443}], 'summary': 'Using xor function in excel to find individuals meeting specific conditions.', 'duration': 35.011, 'max_score': 55.229, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK455229.jpg'}, {'end': 170.806, 'src': 'embed', 'start': 140.819, 'weight': 1, 'content': [{'end': 149.442, 'text': 'this is, uh, very useful in many situations and you cannot directly write an end or or formulas to solve this.', 'start': 140.819, 'duration': 8.623}, {'end': 152.242, 'text': "so i'll show you one simple way of doing this here.", 'start': 149.442, 'duration': 2.8}, {'end': 157.144, 'text': 'i want to find out which employees are meeting any two out of the three conditions.', 'start': 152.242, 'duration': 4.902}, {'end': 164.025, 'text': 'so what we will do is we will say if and then open bracket check for the condition.', 'start': 158.064, 'duration': 5.961}, {'end': 170.806, 'text': "so we'll say department is equal to website.", 'start': 164.025, 'duration': 6.781}], 'summary': 'Demonstrating a method to find employees meeting specified conditions.', 'duration': 29.987, 'max_score': 140.819, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4140819.jpg'}, {'end': 228.025, 'src': 'embed', 'start': 201.437, 'weight': 3, 'content': [{'end': 208.859, 'text': 'Department is equal to website, year is equal to 2019, salary greater than 90, 000 and we are adding them with the plus option.', 'start': 201.437, 'duration': 7.422}, {'end': 213.961, 'text': 'So normally this check alone will give you a true or false value.', 'start': 209.499, 'duration': 4.462}, {'end': 222.644, 'text': 'But when you add such true or false values with plus option, what Excel will do is it will take the true value, convert it into one.', 'start': 214.341, 'duration': 8.303}, {'end': 228.025, 'text': 'if there is a false value, it will convert it into a zero and perform an arithmetic operation.', 'start': 223.324, 'duration': 4.701}], 'summary': "In 2019, a department's website had salaries over $90,000, and excel converts true to 1 and false to 0 for arithmetic operations.", 'duration': 26.588, 'max_score': 201.437, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4201437.jpg'}], 'start': 0.309, 'title': 'Advanced if formula examples and excel logical function example', 'summary': 'Introduces 10 advanced examples of if formulas, including an exclusive or condition and checking for two out of three conditions, using excel to demonstrate the concepts. it also demonstrates using excel logical functions to evaluate multiple conditions and determine employees meeting specific criteria, in order to identify those satisfying at least two out of the three conditions.', 'chapters': [{'end': 140.819, 'start': 0.309, 'title': 'Advanced if formula examples', 'summary': 'Introduces 10 advanced examples of if formulas, including an exclusive or condition and checking for two out of three conditions, using excel to demonstrate the concepts and providing practical examples for clarity.', 'duration': 140.51, 'highlights': ['Introducing exclusive OR condition using IF and XOR function The chapter demonstrates how to use the IF function along with the XOR function to implement an exclusive OR condition, providing a practical example of identifying employees who meet only one of two specified conditions, and explaining the process of creating absolute references in Excel for the function.', 'Practical example of implementing exclusive OR condition in Excel The chapter provides a specific example in Excel, showing how to use the IF and XOR functions to identify employees who meet only one of two specified conditions, and gives a clear demonstration of the exclusion process using absolute references.', 'Illustrating the exclusion process using specific employee data The chapter uses specific employee data to illustrate the exclusion process, showcasing how the IF and XOR functions are employed to identify employees who satisfy only one of the specified conditions, with clear examples provided for better understanding.', 'Explanation of creating absolute references in Excel The chapter explains the procedure for creating absolute references in Excel, emphasizing the use of the F4 key to achieve this, and demonstrates how to apply absolute references in the context of the IF and XOR functions.', 'Demonstrating the process of creating absolute references for IF and XOR function The chapter provides a step-by-step demonstration of creating absolute references in Excel for the IF and XOR functions, ensuring that viewers understand how to implement this technique effectively for their own formulas.', 'Introduction to checking for two out of three conditions The chapter introduces the concept of checking for two out of three conditions, indicating that further examples and demonstrations will be provided to illustrate this particular use case of the IF function.']}, {'end': 244.688, 'start': 140.819, 'title': 'Excel logical function example', 'summary': 'Demonstrates using excel logical functions to evaluate multiple conditions and determine employees meeting specific criteria, including department, year of joining, and salary, in order to identify those satisfying at least two out of the three conditions.', 'duration': 103.869, 'highlights': ["By using Excel logical functions, such as IF and plus option, it's possible to evaluate multiple conditions to determine employees meeting specific criteria, such as department, year of joining, and salary, in order to identify those satisfying at least two out of the three conditions.", "The example specifically evaluates the conditions of department being equal to 'website', year of joining being 2019, and salary being greater than 90,000, and demonstrates how Excel converts true or false values into numeric equivalents for arithmetic operations.", 'The process involves adding the logical checks for department, year, and salary using the plus option, which results in a numeric value, allowing for the determination of employees meeting the specified criteria.']}], 'duration': 244.379, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4309.jpg', 'highlights': ['Introducing exclusive OR condition using IF and XOR function The chapter demonstrates how to use the IF function along with the XOR function to implement an exclusive OR condition, providing a practical example of identifying employees who meet only one of two specified conditions, and explaining the process of creating absolute references in Excel for the function.', "By using Excel logical functions, such as IF and plus option, it's possible to evaluate multiple conditions to determine employees meeting specific criteria, such as department, year of joining, and salary, in order to identify those satisfying at least two out of the three conditions.", 'Practical example of implementing exclusive OR condition in Excel The chapter provides a specific example in Excel, showing how to use the IF and XOR functions to identify employees who meet only one of two specified conditions, and gives a clear demonstration of the exclusion process using absolute references.', "The example specifically evaluates the conditions of department being equal to 'website', year of joining being 2019, and salary being greater than 90,000, and demonstrates how Excel converts true or false values into numeric equivalents for arithmetic operations.", 'Illustrating the exclusion process using specific employee data The chapter uses specific employee data to illustrate the exclusion process, showcasing how the IF and XOR functions are employed to identify employees who satisfy only one of the specified conditions, with clear examples provided for better understanding.']}, {'end': 522.035, 'segs': [{'end': 298.69, 'src': 'heatmap', 'start': 261.728, 'weight': 2, 'content': [{'end': 267.631, 'text': 'Then you can say they should be included, else they should be excluded.', 'start': 261.728, 'duration': 5.903}, {'end': 272.053, 'text': 'So this will automatically identify all of that.', 'start': 269.411, 'duration': 2.642}, {'end': 273.954, 'text': 'Now here is a bonus trick.', 'start': 272.673, 'duration': 1.281}, {'end': 277.155, 'text': 'Instead of dragging the formula like this,', 'start': 274.114, 'duration': 3.041}, {'end': 288.161, 'text': 'you can Simply select the first cell in your formula series and go to the corner when your mouse pointer changes into that dark colored plus icon.', 'start': 277.155, 'duration': 11.006}, {'end': 292.368, 'text': 'Just double click and Excel will automatically fill this down.', 'start': 288.721, 'duration': 3.647}, {'end': 298.69, 'text': 'Number three is an alternative to the BETWEEN formula.', 'start': 295.188, 'duration': 3.502}], 'summary': 'Excel tip: use auto-fill to save time and between formula alternative.', 'duration': 26.017, 'max_score': 261.728, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4261728.jpg'}, {'end': 381.919, 'src': 'heatmap', 'start': 328.352, 'weight': 1, 'content': [{'end': 345.902, 'text': 'if that condition is met, we would like to have a review with them else blank,', 'start': 328.352, 'duration': 17.55}, {'end': 352.925, 'text': 'and you can just fill this down and you will identify people who need to be reviewed.', 'start': 345.902, 'duration': 7.023}, {'end': 359.047, 'text': 'this is the usual way of doing the between condition, but here is a shorter and another way of doing this.', 'start': 352.925, 'duration': 6.122}, {'end': 367.971, 'text': 'you could use the median function to check for between condition, so you would say median your date and then the starting date.', 'start': 359.047, 'duration': 8.924}, {'end': 368.371, 'text': 'it could.', 'start': 367.971, 'duration': 0.4}, {'end': 370.092, 'text': 'this will work for both dates and numbers.', 'start': 368.371, 'duration': 1.721}, {'end': 381.919, 'text': "so you'll say date 2019, 1, 1 and then the ending date is 2019, 6, 30.", 'start': 370.092, 'duration': 11.827}], 'summary': 'Use the median function to check for between condition for dates and numbers.', 'duration': 28.994, 'max_score': 328.352, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4328352.jpg'}, {'end': 487.442, 'src': 'embed', 'start': 461.461, 'weight': 0, 'content': [{'end': 467.504, 'text': 'But do you know that you can use the IFS function? This is a new function introduced in Excel.', 'start': 461.461, 'duration': 6.043}, {'end': 476.348, 'text': 'It works in Excel 365 and Excel 2019 and you can use this to reduce the length of your formulas and make them more readable.', 'start': 467.564, 'duration': 8.784}, {'end': 479.13, 'text': "So I'll show you how the IFS function would work.", 'start': 477.009, 'duration': 2.121}, {'end': 481.091, 'text': 'IFS department.', 'start': 479.65, 'duration': 1.441}, {'end': 484.981, 'text': 'is equal to website.', 'start': 482.239, 'duration': 2.742}, {'end': 487.442, 'text': 'the bonus is one percent.', 'start': 484.981, 'duration': 2.461}], 'summary': 'The ifs function in excel 365 and excel 2019 can reduce formula length and improve readability, e.g., department=website, bonus=1%.', 'duration': 25.981, 'max_score': 461.461, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4461461.jpg'}], 'start': 244.688, 'title': 'Excel formulas and functions', 'summary': 'Covers checking values and an alternative to the between formula, while demonstrating examples of reviewing employees based on specific date conditions. it also introduces a shorter way of checking the between condition using the median function and the ifs function for bonus calculation, simplifying the process and enhancing readability of formulas.', 'chapters': [{'end': 352.925, 'start': 244.688, 'title': 'Excel formulas and functions', 'summary': 'Covers checking values, including a bonus trick for filling down formulas and an alternative to the between formula, while demonstrating examples of reviewing employees based on specific date conditions.', 'duration': 108.237, 'highlights': ['The chapter covers checking values, including a bonus trick for filling down formulas and an alternative to the BETWEEN formula, while demonstrating examples of reviewing employees based on specific date conditions.', 'Instead of dragging the formula, a bonus trick is to select the first cell and double click on the dark colored plus icon, allowing Excel to automatically fill down the formula series.', 'An alternative to the BETWEEN formula is demonstrated by using the AND function to check for specific date conditions, such as identifying employees who joined within a specified date range.']}, {'end': 434.83, 'start': 352.925, 'title': 'Using median function for between condition', 'summary': 'Introduces a shorter way of checking the between condition using the median function for both dates and numbers, providing an alternative to nested if functions for calculating employee bonus based on rules.', 'duration': 81.905, 'highlights': ['The median function can be used to check for between condition for both dates and numbers, providing a shorter alternative to the usual method.', 'The median function can be used to determine if a date falls between two other dates, offering an efficient way to perform this check.', 'Introducing a substitute for nested if functions to calculate employee bonus based on certain rules, providing a more efficient and concise approach.']}, {'end': 522.035, 'start': 435.57, 'title': 'Using ifs function in excel for bonus calculation', 'summary': 'Introduces the ifs function in excel 365 and excel 2019 for bonus calculation, offering 1% bonus for all website staff, 3% for sales department staff who joined in 2018, and 2% for others, simplifying the bonus calculation process and enhancing readability of formulas.', 'duration': 86.465, 'highlights': ['The IFS function in Excel 365 and Excel 2019 simplifies the bonus calculation process and enhances the readability of formulas, offering 1% bonus for all website staff, 3% for sales department staff who joined in 2018, and 2% for others.', 'The traditional approach involves using nested IF functions to come up with the bonus calculation, whereas the IFS function streamlines the process and makes the formulas more manageable and concise.', 'The IFS function streamlines the bonus calculation process, making it more readable and reducing the length of the formulas, enhancing efficiency and ease of use for the users.']}], 'duration': 277.347, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4244688.jpg', 'highlights': ['The IFS function in Excel 365 and Excel 2019 simplifies the bonus calculation process and enhances the readability of formulas, offering 1% bonus for all website staff, 3% for sales department staff who joined in 2018, and 2% for others.', 'The median function can be used to check for between condition for both dates and numbers, providing a shorter alternative to the usual method.', 'Instead of dragging the formula, a bonus trick is to select the first cell and double click on the dark colored plus icon, allowing Excel to automatically fill down the formula series.']}, {'end': 696.355, 'segs': [{'end': 617.546, 'src': 'embed', 'start': 573.605, 'weight': 0, 'content': [{'end': 580.208, 'text': 'you could use the boolean replacement logic to calculate those numbers directly instead of the if formula.', 'start': 573.605, 'duration': 6.603}, {'end': 583.25, 'text': "So I'll show you how this will be done.", 'start': 580.928, 'duration': 2.322}, {'end': 587.291, 'text': 'The way to do this will depend on each individual situation.', 'start': 583.87, 'duration': 3.421}, {'end': 588.752, 'text': "So you'll have to think a little bit.", 'start': 587.311, 'duration': 1.441}, {'end': 600.137, 'text': 'But the advantage of boolean replacement over regular if or ifs functions is it tends to be a little faster and it feels more natural once you get used to it.', 'start': 589.232, 'duration': 10.905}, {'end': 601.538, 'text': "So let's get into this.", 'start': 600.658, 'duration': 0.88}, {'end': 611.682, 'text': 'so what we will do is everybody will get 2% and then, if you are in website, you get 1% less than that.', 'start': 604.136, 'duration': 7.546}, {'end': 617.546, 'text': 'so that is 1%, and if you are both in sales and joined in 2018, you get 1% extra.', 'start': 611.682, 'duration': 5.864}], 'summary': 'Demonstrates boolean replacement logic for calculating percentages, offering speed advantage and natural feel.', 'duration': 43.941, 'max_score': 573.605, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4573605.jpg'}], 'start': 522.035, 'title': 'Simplified calculation using boolean replacement', 'summary': 'Discusses a simplified approach to calculations using boolean replacement, providing examples of calculating percentages based on different conditions, resulting in more readable and maintainable formulas.', 'chapters': [{'end': 696.355, 'start': 522.035, 'title': 'Simplified calculation using boolean replacement', 'summary': 'Discusses a simplified approach to calculations using boolean replacement, which is faster and more natural than nested if formulas, resulting in a more readable and maintainable formula, and provides examples of using boolean replacement to calculate percentages based on different conditions.', 'duration': 174.32, 'highlights': ['The advantage of boolean replacement over regular if or ifs functions is it tends to be a little faster and it feels more natural once you get used to it, making it a more efficient and intuitive approach to calculations.', 'Demonstrates a specific example of using boolean replacement to calculate percentages based on different conditions, such as giving 2% to everyone, 1% less for those in the website department, and 1% extra for those in both sales and joined in 2018, showcasing the practical application of this approach.', 'The formula using boolean replacement is much more readable than nested if formulas, providing a simpler and easier-to-maintain solution for complex calculations.']}], 'duration': 174.32, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4522034.jpg', 'highlights': ['Demonstrates a specific example of using boolean replacement to calculate percentages based on different conditions, such as giving 2% to everyone, 1% less for those in the website department, and 1% extra for those in both sales and joined in 2018, showcasing the practical application of this approach.', 'The formula using boolean replacement is much more readable than nested if formulas, providing a simpler and easier-to-maintain solution for complex calculations.', 'The advantage of boolean replacement over regular if or ifs functions is it tends to be a little faster and it feels more natural once you get used to it, making it a more efficient and intuitive approach to calculations.']}, {'end': 972.697, 'segs': [{'end': 744.36, 'src': 'embed', 'start': 718.863, 'weight': 0, 'content': [{'end': 724.606, 'text': 'And I want to just check if the person here is also appearing on the other list.', 'start': 718.863, 'duration': 5.743}, {'end': 726.607, 'text': 'Here is how you would do that.', 'start': 725.507, 'duration': 1.1}, {'end': 731.789, 'text': 'If And then you would use one of the counting functions.', 'start': 727.948, 'duration': 3.841}, {'end': 733.63, 'text': "So I'll use the COUNTIFS function.", 'start': 731.829, 'duration': 1.801}, {'end': 739.155, 'text': 'Point to my range that has the on call support team.', 'start': 734.351, 'duration': 4.804}, {'end': 742.318, 'text': 'Make sure that this address is absolute reference.', 'start': 739.716, 'duration': 2.602}, {'end': 743.439, 'text': "So we'll select everything.", 'start': 742.338, 'duration': 1.101}, {'end': 744.36, 'text': 'Press F4.', 'start': 743.519, 'duration': 0.841}], 'summary': 'Using countifs function to check for appearance on another list.', 'duration': 25.497, 'max_score': 718.863, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4718863.jpg'}, {'end': 805.304, 'src': 'embed', 'start': 777.107, 'weight': 5, 'content': [{'end': 784.892, 'text': 'well, not necessary, because, remember, the logical test will have to just come up with a true or false value.', 'start': 777.107, 'duration': 7.785}, {'end': 787.413, 'text': 'What does the COUNTIFS function come up with??', 'start': 785.532, 'duration': 1.881}, {'end': 789.714, 'text': 'It will come up with 0 or 1..', 'start': 787.673, 'duration': 2.041}, {'end': 792.676, 'text': 'Now technically 0 is false and 1 is true.', 'start': 789.714, 'duration': 2.962}, {'end': 796.318, 'text': 'So we do not have to specify any extra condition here.', 'start': 792.816, 'duration': 3.502}, {'end': 805.304, 'text': 'Just writing the COUNTIFS alone is sufficient because if the count is 0, Excel will treat that as false and go to not on call automatically.', 'start': 796.819, 'duration': 8.485}], 'summary': 'Using countifs function in excel to determine true or false values, with 0 as false and 1 as true.', 'duration': 28.197, 'max_score': 777.107, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4777107.jpg'}, {'end': 889.941, 'src': 'embed', 'start': 853.335, 'weight': 3, 'content': [{'end': 855.218, 'text': 'This is where the if formula can work.', 'start': 853.335, 'duration': 1.883}, {'end': 860.484, 'text': 'All you have to do is say median and then write the if function inside.', 'start': 855.538, 'duration': 4.946}, {'end': 871.097, 'text': 'If select the entire list of departments is equal to website and then point to the salary.', 'start': 861.165, 'duration': 9.932}, {'end': 880.615, 'text': 'close the bracket, close it again, notice what this if function does, it is checking a bunch of values against website.', 'start': 872.469, 'duration': 8.146}, {'end': 889.941, 'text': 'so this part will return a bunch of true or false values and wherever it is true, this will give the corresponding salary.', 'start': 880.615, 'duration': 9.326}], 'summary': 'The if formula can be used to check values against a condition and return corresponding salaries.', 'duration': 36.606, 'max_score': 853.335, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4853335.jpg'}, {'end': 933.822, 'src': 'embed', 'start': 904.23, 'weight': 4, 'content': [{'end': 907.411, 'text': "Now let's just see what happens for this internal part alone.", 'start': 904.23, 'duration': 3.181}, {'end': 910.233, 'text': 'I can select that and here is a bonus trick for you.', 'start': 907.451, 'duration': 2.782}, {'end': 919.077, 'text': 'Just select the portion of formula that you want to see the result, not the whole thing, just this bit alone and press control equal to.', 'start': 911.113, 'duration': 7.964}, {'end': 921.338, 'text': 'You could also use the F9 key.', 'start': 919.657, 'duration': 1.681}, {'end': 924.999, 'text': 'It will give you the answer for that part alone.', 'start': 922.098, 'duration': 2.901}, {'end': 928.601, 'text': 'This is very useful for understanding and debugging things as well.', 'start': 925.199, 'duration': 3.402}, {'end': 933.822, 'text': 'And I can see that it is false wherever the person is not in website.', 'start': 929.361, 'duration': 4.461}], 'summary': 'Using ctrl + = or f9 for formula results helps in debugging and understanding. useful for specific parts.', 'duration': 29.592, 'max_score': 904.23, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4904230.jpg'}, {'end': 972.697, 'src': 'embed', 'start': 947.667, 'weight': 6, 'content': [{'end': 953.889, 'text': 'it ignores all the false values and it only pays attention to the number and then it will calculate the median for that.', 'start': 947.667, 'duration': 6.222}, {'end': 956.116, 'text': "So that's how that works.", 'start': 955.014, 'duration': 1.102}, {'end': 962.264, 'text': "Now a quick note, if you are using Excel 365, you can just type this formula, press enter, it'll work.", 'start': 956.776, 'duration': 5.488}, {'end': 968.332, 'text': 'In older versions of Excel, you would need to press control shift enter to get the correct result.', 'start': 962.764, 'duration': 5.568}, {'end': 972.697, 'text': 'Time for our 8th advanced if formula trick.', 'start': 969.875, 'duration': 2.822}], 'summary': 'The median is calculated by ignoring false values, and a specific formula is needed for older versions of excel.', 'duration': 25.03, 'max_score': 947.667, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4947667.jpg'}], 'start': 696.355, 'title': 'Using countifs and advanced if formula tricks', 'summary': 'Explains the superior use of countifs to check employee on call support team, and advanced if formula for calculating median salary, offering alternative to lookup functions and a bonus trick for understanding and debugging formulas.', 'chapters': [{'end': 777.107, 'start': 696.355, 'title': 'Using countifs to check employee in on call support team', 'summary': "Explains how to use the countifs function to check if an employee is part of the on call support team, offering an alternative to lookup functions and demonstrating the process with a range of staff and the use of absolute references, emphasizing the counting function's superiority.", 'duration': 80.752, 'highlights': ["The chapter explains how to use the COUNTIFS function to check if an employee is part of the on call support team, offering an alternative to lookup functions and demonstrating the process with a range of staff and the use of absolute references, emphasizing the counting function's superiority.", 'The technique involves utilizing the COUNTIFS function to compare a specific employee with a range of staff in the on call support team, with the necessity of using absolute references, and the practicality of filling down the function for multiple employees.', 'The tutorial highlights the advantage of using the COUNTIFS function over lookup functions for checking if an employee is part of the on call support team, emphasizing the simplicity and efficiency of the counting function.']}, {'end': 972.697, 'start': 777.107, 'title': 'Advanced if formula tricks', 'summary': 'Explains the countifs function, where using just countifs alone is sufficient, and the array if formula for calculating the median salary of staff in a specific department, providing a bonus trick for understanding and debugging formulas.', 'duration': 195.59, 'highlights': ["The COUNTIFS function comes up with 0 or 1, where 0 is false and 1 is true, making it unnecessary to specify any extra condition, and Excel will treat 0 as false and go to 'not on call' automatically.", 'The array IF formula is used to calculate the median salary of staff in a specific department by checking a bunch of values against the department, providing a bonus trick for understanding and debugging formulas by selecting a portion of the formula and pressing control equal to or using the F9 key for seeing the result.', 'For understanding and debugging, one can use the bonus trick of selecting a portion of the formula and pressing control equal to or using the F9 key for seeing the result, providing a useful method for understanding and debugging formulas.', 'In older versions of Excel, one would need to press control shift enter to get the correct result, while in Excel 365, typing the formula and pressing enter will work.', "The COUNTIFS function comes up with 0 or 1, where 0 is false and 1 is true, making it unnecessary to specify any extra condition, and Excel will treat 0 as false and go to 'not on call' automatically."]}], 'duration': 276.342, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4696355.jpg', 'highlights': ["The chapter explains how to use the COUNTIFS function to check if an employee is part of the on call support team, offering an alternative to lookup functions and demonstrating the process with a range of staff and the use of absolute references, emphasizing the counting function's superiority.", 'The technique involves utilizing the COUNTIFS function to compare a specific employee with a range of staff in the on call support team, with the necessity of using absolute references, and the practicality of filling down the function for multiple employees.', 'The tutorial highlights the advantage of using the COUNTIFS function over lookup functions for checking if an employee is part of the on call support team, emphasizing the simplicity and efficiency of the counting function.', 'The array IF formula is used to calculate the median salary of staff in a specific department by checking a bunch of values against the department, providing a bonus trick for understanding and debugging formulas by selecting a portion of the formula and pressing control equal to or using the F9 key for seeing the result.', 'For understanding and debugging, one can use the bonus trick of selecting a portion of the formula and pressing control equal to or using the F9 key for seeing the result, providing a useful method for understanding and debugging formulas.', "The COUNTIFS function comes up with 0 or 1, where 0 is false and 1 is true, making it unnecessary to specify any extra condition, and Excel will treat 0 as false and go to 'not on call' automatically.", 'In older versions of Excel, one would need to press control shift enter to get the correct result, while in Excel 365, typing the formula and pressing enter will work.']}, {'end': 1313.776, 'segs': [{'end': 1001.729, 'src': 'embed', 'start': 973.118, 'weight': 6, 'content': [{'end': 978.182, 'text': 'This is where I want to check if the name contains the letters BO anywhere.', 'start': 973.118, 'duration': 5.064}, {'end': 980.644, 'text': 'Basically a wildcard based search.', 'start': 978.563, 'duration': 2.081}, {'end': 982.406, 'text': 'There are many ways to do this.', 'start': 981.105, 'duration': 1.301}, {'end': 984.588, 'text': "I'll show you my favorite method of doing this.", 'start': 982.466, 'duration': 2.122}, {'end': 988.611, 'text': 'Feel free to comment and share your own technique of doing this as well.', 'start': 985.028, 'duration': 3.583}, {'end': 996.178, 'text': "So I'll say if, and then I'm using the COUNTIFS function, because this function is aware of the wildcard setup,", 'start': 989.192, 'duration': 6.986}, {'end': 1001.729, 'text': "and then I'll say this is my range and the criteria is that.", 'start': 996.178, 'duration': 5.551}], 'summary': 'Using countifs function with wildcard for name search.', 'duration': 28.611, 'max_score': 973.118, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4973118.jpg'}, {'end': 1115.438, 'src': 'heatmap', 'start': 1012.472, 'weight': 0, 'content': [{'end': 1015.173, 'text': "so I'm saying anything, bow, anything.", 'start': 1012.472, 'duration': 2.701}, {'end': 1016.653, 'text': "that's what we are looking for.", 'start': 1015.173, 'duration': 1.48}, {'end': 1030.784, 'text': "count for that, and if so, we'll simply say bow person, else not a bow person, and it will give you the answer.", 'start': 1016.653, 'duration': 14.131}, {'end': 1033.365, 'text': 'Wherever the person has bow in it.', 'start': 1031.444, 'duration': 1.921}, {'end': 1037.445, 'text': 'like this person has bow, Chess Bonnel has bow.', 'start': 1033.365, 'duration': 4.08}, {'end': 1043.727, 'text': 'it will tag that person as bow person and everybody else will be not bow persons.', 'start': 1037.445, 'duration': 6.282}, {'end': 1045.847, 'text': "So that's the wild card one.", 'start': 1044.387, 'duration': 1.46}, {'end': 1052.128, 'text': 'Now these eight tricks that I have shown you are writing a formula.', 'start': 1048.068, 'duration': 4.06}, {'end': 1062.023, 'text': 'The next two tricks that I will show you will teach you how to use if formula or the logical construct in other situations.', 'start': 1052.428, 'duration': 9.595}, {'end': 1073.009, 'text': 'So the number nine I have for you is using conditional formatting to highlight people that are in a specific department and gender.', 'start': 1062.503, 'duration': 10.506}, {'end': 1077.432, 'text': "So we'll quickly set up an input area for both department and gender.", 'start': 1073.469, 'duration': 3.963}, {'end': 1082.321, 'text': 'And you can give whatever value you want here.', 'start': 1080.12, 'duration': 2.201}, {'end': 1084.202, 'text': "So I'll say website mail.", 'start': 1082.361, 'duration': 1.841}, {'end': 1088.304, 'text': 'And based on these two conditions, I want to highlight the staff here.', 'start': 1084.982, 'duration': 3.322}, {'end': 1093.546, 'text': 'So we go select all of this, go to conditional formatting, new rule.', 'start': 1088.724, 'duration': 4.822}, {'end': 1097.468, 'text': "And let's quickly write a formula based rule.", 'start': 1094.867, 'duration': 2.601}, {'end': 1107.573, 'text': "we will say now you might be tempted to write if department is equal to this and this, but you don't need to write the if formula here.", 'start': 1098.547, 'duration': 9.026}, {'end': 1115.438, 'text': 'for the conditional formatting to work, all you have to do is come up with a boolean or a logical expression that will evaluate to true or false.', 'start': 1107.573, 'duration': 7.865}], 'summary': 'Using excel to tag bow persons and highlight staff based on department and gender.', 'duration': 82.073, 'max_score': 1012.472, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK41012472.jpg'}, {'end': 1205.351, 'src': 'heatmap', 'start': 1115.438, 'weight': 3, 'content': [{'end': 1122.955, 'text': 'so I will simply say and select the top department cell.', 'start': 1115.438, 'duration': 7.517}, {'end': 1128.622, 'text': 'make sure the reference is not in this format, but it is locked to column E.', 'start': 1122.955, 'duration': 5.667}, {'end': 1135.672, 'text': 'so $E50 is equal to that comma.', 'start': 1128.622, 'duration': 7.05}, {'end': 1139.701, 'text': 'gender is equal to this.', 'start': 1135.672, 'duration': 4.029}, {'end': 1142.383, 'text': 'so we construct the end statement like this.', 'start': 1139.701, 'duration': 2.682}, {'end': 1147.287, 'text': 'it might look a bit complicated, but have a read at this once again and it will make sense.', 'start': 1142.383, 'duration': 4.904}, {'end': 1150.529, 'text': 'we go to format, select a fill color.', 'start': 1147.287, 'duration': 3.242}, {'end': 1157.894, 'text': "let's select something nice and bright and make the font bold as well,", 'start': 1150.529, 'duration': 7.365}, {'end': 1163.558, 'text': 'so it will highlight all the male employees in website department and those alone.', 'start': 1157.894, 'duration': 5.664}, {'end': 1164.538, 'text': 'now i can change this.', 'start': 1163.558, 'duration': 0.98}, {'end': 1173.471, 'text': "i can say sales, I can say female, I can say finance and I'll get what I want.", 'start': 1164.538, 'duration': 8.933}, {'end': 1179.292, 'text': "So that's how you can use the if or the Boolean logic inside conditional formatting.", 'start': 1174.071, 'duration': 5.221}, {'end': 1180.232, 'text': "That's our number nine.", 'start': 1179.312, 'duration': 0.92}, {'end': 1182.353, 'text': 'We are nearly at the end.', 'start': 1181.253, 'duration': 1.1}, {'end': 1183.553, 'text': 'Number ten.', 'start': 1182.993, 'duration': 0.56}, {'end': 1190.234, 'text': 'And I want to highlight any employees that are making more than the average salary of all the staff.', 'start': 1184.393, 'duration': 5.841}, {'end': 1203.068, 'text': 'Right. So this is where let me just place it here and anybody whose salary is more than the average of all the staff we want to highlight.', 'start': 1190.834, 'duration': 12.234}, {'end': 1204.61, 'text': "so we'll go to the data here.", 'start': 1203.068, 'duration': 1.542}, {'end': 1205.351, 'text': 'we will add,', 'start': 1204.61, 'duration': 0.741}], 'summary': 'Utilizing conditional formatting to highlight specific data in excel, including gender and salary comparisons.', 'duration': 54.822, 'max_score': 1115.438, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK41115438.jpg'}, {'end': 1305.151, 'src': 'heatmap', 'start': 1221.05, 'weight': 4, 'content': [{'end': 1225.413, 'text': 'because when you drag it down it wants to still look at G50 to G65..', 'start': 1221.05, 'duration': 4.363}, {'end': 1228.475, 'text': 'Then I want their salary.', 'start': 1226.093, 'duration': 2.382}, {'end': 1231.297, 'text': 'Else I want nothing, NA.', 'start': 1229.396, 'duration': 1.901}, {'end': 1235.4, 'text': 'Once all of this is there, time for another bonus trick.', 'start': 1232.258, 'duration': 3.142}, {'end': 1237.181, 'text': 'Just quickly add this to chart.', 'start': 1235.54, 'duration': 1.641}, {'end': 1240.123, 'text': 'You can right click, go to select data and add it.', 'start': 1237.721, 'duration': 2.402}, {'end': 1242.225, 'text': 'But here is a tricky, easy way to do this.', 'start': 1240.183, 'duration': 2.042}, {'end': 1243.846, 'text': 'Select your data, copy.', 'start': 1242.485, 'duration': 1.361}, {'end': 1247.253, 'text': 'Select the chart, paste, Ctrl V.', 'start': 1244.932, 'duration': 2.321}, {'end': 1255.018, 'text': 'It will add the data to your chart and it will also now show two bars for someone who is picking more than average.', 'start': 1247.253, 'duration': 7.765}, {'end': 1258.26, 'text': 'Select the second bar, format it.', 'start': 1255.619, 'duration': 2.641}, {'end': 1260.962, 'text': 'You can format with Ctrl 1 by the way.', 'start': 1258.961, 'duration': 2.001}, {'end': 1266.925, 'text': 'Set the overlap to 100% so that the color will go and sit on top of this.', 'start': 1261.502, 'duration': 5.423}, {'end': 1273.898, 'text': "Let's select the other one and quickly make it this color,", 'start': 1267.486, 'duration': 6.412}, {'end': 1281.284, 'text': 'so that above average salaries are highlighted in dark orange and regular salaries are highlighted in dull orange color.', 'start': 1273.898, 'duration': 7.386}, {'end': 1282.666, 'text': 'There you go.', 'start': 1282.165, 'duration': 0.501}, {'end': 1285.168, 'text': 'Ten advanced if formula tricks.', 'start': 1283.066, 'duration': 2.102}, {'end': 1293.314, 'text': 'I hope you found all of this useful and interesting, and I hope you found the bonus tricks that are peppered throughout the video helpful as well.', 'start': 1285.448, 'duration': 7.866}, {'end': 1297.298, 'text': 'If you need a hand, just go and grab this file.', 'start': 1293.935, 'duration': 3.363}, {'end': 1300.781, 'text': 'The file link is in the video description and check it out.', 'start': 1297.458, 'duration': 3.323}, {'end': 1305.151, 'text': 'But if you would like to learn more about conditional formatting,', 'start': 1301.789, 'duration': 3.362}], 'summary': 'Demonstrating advanced excel tricks for conditional formatting and chart manipulation.', 'duration': 43.649, 'max_score': 1221.05, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK41221050.jpg'}], 'start': 973.118, 'title': 'Excel formula tricks', 'summary': 'Covers wildcard search, logical constructs, and conditional formatting tricks in excel, offering a total of 10 tricks for data manipulation and visualization, without using the if formula, and additional bonus tricks for excel users.', 'chapters': [{'end': 1062.023, 'start': 973.118, 'title': 'Wildcard search and logical constructs', 'summary': "Demonstrates a wildcard search using the countifs function to identify names containing 'bo' and provides additional methods for using logical constructs in excel formulas, offering a total of 10 tricks for manipulating data.", 'duration': 88.905, 'highlights': ["The COUNTIFS function is used to search for names containing 'BO' by setting the criteria as '*&'BO'&*' to identify 'bow' within a name, allowing for efficient data manipulation.", 'The chapter provides 10 tricks for manipulating data, including wildcard searches and logical constructs using IF formulas, offering a comprehensive guide to enhancing Excel skills.']}, {'end': 1182.353, 'start': 1062.503, 'title': 'Conditional formatting to highlight staff', 'summary': 'Demonstrates how to use conditional formatting to highlight staff based on specific department and gender, without using the if formula, resulting in efficient data visualization and analysis.', 'duration': 119.85, 'highlights': ['The chapter explains how to use conditional formatting to highlight staff based on specific department and gender, without using the IF formula, resulting in efficient data visualization and analysis.', 'The speaker guides through the steps of setting up an input area for department and gender, allowing users to input any desired values for efficient data highlighting.', 'The process involves constructing a Boolean or logical expression that evaluates to true or false, without the need for writing the if formula within the conditional formatting, simplifying the process for users.', 'The demonstration showcases the practical application by highlighting male employees in the website department, with the ability to easily change the values to highlight staff from different departments and genders.', 'The chapter concludes by emphasizing the use of Boolean logic inside conditional formatting for efficient data highlighting and visualization.']}, {'end': 1313.776, 'start': 1182.993, 'title': 'Advanced if formula tricks', 'summary': 'Covers ten advanced if formula tricks, including highlighting employees with salaries above the average, adding the highlighted data to a chart, and providing additional bonus tricks for excel users.', 'duration': 130.783, 'highlights': ['Employees with salaries above the average are highlighted in dark orange, while regular salaries are highlighted in dull orange color, providing visual representation of the data.', 'The chapter includes ten advanced IF formula tricks, offering practical techniques for Excel users to identify and highlight employees with salaries above the average.', 'The speaker encourages viewers to grab the file from the video description for further assistance, providing a helpful resource for applying the discussed techniques in Excel.', 'The chapter provides bonus tricks throughout the video, enhancing the learning experience for viewers and demonstrating practical applications of the highlighted techniques.']}], 'duration': 340.658, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/-yFpzIRifK4/pics/-yFpzIRifK4973118.jpg', 'highlights': ['The chapter provides 10 tricks for manipulating data, including wildcard searches and logical constructs using IF formulas, offering a comprehensive guide to enhancing Excel skills.', 'The process involves constructing a Boolean or logical expression that evaluates to true or false, without the need for writing the if formula within the conditional formatting, simplifying the process for users.', 'The demonstration showcases the practical application by highlighting male employees in the website department, with the ability to easily change the values to highlight staff from different departments and genders.', 'The chapter concludes by emphasizing the use of Boolean logic inside conditional formatting for efficient data highlighting and visualization.', 'The chapter includes ten advanced IF formula tricks, offering practical techniques for Excel users to identify and highlight employees with salaries above the average.', 'The chapter provides bonus tricks throughout the video, enhancing the learning experience for viewers and demonstrating practical applications of the highlighted techniques.', "The COUNTIFS function is used to search for names containing 'BO' by setting the criteria as '*&'BO'&*' to identify 'bow' within a name, allowing for efficient data manipulation.", 'Employees with salaries above the average are highlighted in dark orange, while regular salaries are highlighted in dull orange color, providing visual representation of the data.', 'The speaker encourages viewers to grab the file from the video description for further assistance, providing a helpful resource for applying the discussed techniques in Excel.', 'The chapter explains how to use conditional formatting to highlight staff based on specific department and gender, without using the IF formula, resulting in efficient data visualization and analysis.']}], 'highlights': ['The IFS function in Excel 365 and Excel 2019 simplifies the bonus calculation process and enhances the readability of formulas, offering 1% bonus for all website staff, 3% for sales department staff who joined in 2018, and 2% for others.', 'The median function can be used to check for between condition for both dates and numbers, providing a shorter alternative to the usual method.', 'Instead of dragging the formula, a bonus trick is to select the first cell and double click on the dark colored plus icon, allowing Excel to automatically fill down the formula series.', 'Demonstrates a specific example of using boolean replacement to calculate percentages based on different conditions, such as giving 2% to everyone, 1% less for those in the website department, and 1% extra for those in both sales and joined in 2018, showcasing the practical application of this approach.', 'The formula using boolean replacement is much more readable than nested if formulas, providing a simpler and easier-to-maintain solution for complex calculations.', 'The advantage of boolean replacement over regular if or ifs functions is it tends to be a little faster and it feels more natural once you get used to it, making it a more efficient and intuitive approach to calculations.', "The chapter explains how to use the COUNTIFS function to check if an employee is part of the on call support team, offering an alternative to lookup functions and demonstrating the process with a range of staff and the use of absolute references, emphasizing the counting function's superiority.", 'The technique involves utilizing the COUNTIFS function to compare a specific employee with a range of staff in the on call support team, with the necessity of using absolute references, and the practicality of filling down the function for multiple employees.', 'The tutorial highlights the advantage of using the COUNTIFS function over lookup functions for checking if an employee is part of the on call support team, emphasizing the simplicity and efficiency of the counting function.', 'The array IF formula is used to calculate the median salary of staff in a specific department by checking a bunch of values against the department, providing a bonus trick for understanding and debugging formulas by selecting a portion of the formula and pressing control equal to or using the F9 key for seeing the result.', 'For understanding and debugging, one can use the bonus trick of selecting a portion of the formula and pressing control equal to or using the F9 key for seeing the result, providing a useful method for understanding and debugging formulas.', "The COUNTIFS function comes up with 0 or 1, where 0 is false and 1 is true, making it unnecessary to specify any extra condition, and Excel will treat 0 as false and go to 'not on call' automatically.", 'In older versions of Excel, one would need to press control shift enter to get the correct result, while in Excel 365, typing the formula and pressing enter will work.', 'The chapter provides 10 tricks for manipulating data, including wildcard searches and logical constructs using IF formulas, offering a comprehensive guide to enhancing Excel skills.', 'The process involves constructing a Boolean or logical expression that evaluates to true or false, without the need for writing the if formula within the conditional formatting, simplifying the process for users.', 'The demonstration showcases the practical application by highlighting male employees in the website department, with the ability to easily change the values to highlight staff from different departments and genders.', 'The chapter concludes by emphasizing the use of Boolean logic inside conditional formatting for efficient data highlighting and visualization.', 'The chapter includes ten advanced IF formula tricks, offering practical techniques for Excel users to identify and highlight employees with salaries above the average.', 'The chapter provides bonus tricks throughout the video, enhancing the learning experience for viewers and demonstrating practical applications of the highlighted techniques.', "The COUNTIFS function is used to search for names containing 'BO' by setting the criteria as '*&'BO'&*' to identify 'bow' within a name, allowing for efficient data manipulation.", 'Employees with salaries above the average are highlighted in dark orange, while regular salaries are highlighted in dull orange color, providing visual representation of the data.', 'The speaker encourages viewers to grab the file from the video description for further assistance, providing a helpful resource for applying the discussed techniques in Excel.', 'The chapter explains how to use conditional formatting to highlight staff based on specific department and gender, without using the IF formula, resulting in efficient data visualization and analysis.']}