This set of 6 UIL Computer Applications videos contains the following parts.
(NOTE: You can also order all 29 UIL Computer Applications videos which includes this set!)
These four videos for the UIL Computer Applications contest cover the nuts and bolts of IF statements, nested IF statements, and the conditional formulas (SUMIF and COUNTIF) in Microsoft Excel.
Excel IF Statements Basics - In the first video, we focus on the IF formula and construction of IF statements in Excel. In this lesson, you'll learn how the IF formula works, how to think of the IF formula as a sentence to help with constructing accurate IF statements, how to diagram an IF statement graphically, which will help you to conceptualize and understand how nested IF statements work, which we'll cover in latter videos, what the parts are to an IF statement and how to think about these types of formulae and statements, etc.
Excel Nested IF, PART 1 - In the second video lesson, we introduce you to a nested IF statement in Excel. A nested IF statement is a formula that includes more than one IF statement within it. In this video, you'll learn how to think about construction of IF statements and nested IF statements, how to diagram IF statements and nested IF statements graphically, which will help you to master the creation of complex IF statements, what the parts are to an IF statement and how to think about nesting multiple IF statements in situations where you have multiple decision criteria that need to be considered in order to output the correct value, etc.
Excel Nested IF, PART 2 - In the third video, we dig deeper on the subject of nested IF statements by walking through an example which requires the use of 5 disparate IF statements nested into one formula. A nested IF statement is a formula that includes more than one IF statement within it. In this session, you'll learn how to think about construction of IF statements and nested IF statements, tips and tricks to use to know how to sequence different decision criteria in your nested IF statement, and how multiple different approaches to the same nested IF statement can yield equally viable results despite differences in approach. This will also give you some insight into some personal preferences you may find that you have when it comes to setting up nested IF statements, how to ‘reality check’ your IF statement by walking through some examples to see how sample values are treated by the nested IF statement, etc.
Excel SUMIF & COUNTIF- In the final lesson, we will focus our attention on the COUNTIF and SUMIF formulas in Excel, which allow you to calculate conditional counts and conditional sums. In this video, you'll learn how COUNTIF and SUMIF work and when they're applicable, the input variables for both COUNTIF and SUMIF formulas (including an explanation of when the 'sum range' input variable for a SUMIF formula is an optional input), how to count the number of cells within a range that match a criteria than you stipulate (using the COUNTIF function), how to sum up the values in a range of cells that meet a criteria you stipulate (using the SUMIF formula), how to use the SUMIF formula in such a way that you values you sum are related to but not the same as the values you consider when evaluating your indicated criteria, examples of situations when you may find the SUMIF and COUNTIF formulae to be useful. ALSO - tips and tricks as well as suggestions of what to watch out for when using both COUNTIF and SUMIF, etc. To illustrate these things, we'll go through some examples in Microsoft Excel.
These two videos geared toward the UIL Computer Applications contest cover the fundamentals of Dates and Times, and also LOOKUP and HLOOKUP functions in Microsoft Excel.
Excel Dates & Times Video - In this 20-minute video lesson, we focus on the dates and times in Microsoft Excel. You'll learn: how dates and times work, different common date and time number formats, whole numbers versus decimals/fractions in dates and times when formatted as general numbers, Common Formulas (TODAY, DAY, MONTH, YEAR, DATE, TIME, WEEKDAY, etc), Basic calculations involving dates and times, etc. To illustrate these things, we'll go through some examples in Microsoft Excel. Note that starting point worksheets are attached as are the ending point worksheets (in the attached Excel workbook) to allow you to work off of the starting document and compare your results to the ending document.
Excel LOOKUP & HLOOKUP Video - In this 16-minute video lesson, we will focus our attention on the VLOOKUP and HLOOKUP formulas in Excel, which allow you to compare lists or pull in data from distinct but related flat data sets. In this tutorial, you'll learn how to use VLOOKUP and HLOOKUP formulas in Excel to compare lists or pull in data from multiple flat data files, the parts to the formulas and how they work, things to look out for when using the formulas, shortcuts and tips to use when using the formulas. To illustrate these concepts, we'll go through some examples in Microsoft Excel. Note that a starting point worksheet is attached as is the ending point worksheets (in the attached Excel workbook) to allow you to work off the starting document and compare your results to the ending document.
OTHER INFO (GENERAL)
- Downloadable files are included with most videos.
- Videos are sold as an online subscription, good until June 1st (end of the academic year).
- Once your credit card, PayPal, or purchase order is processed/verified, we will create an account for you manually and email you login credentials. If you already have an account, please login here.
- TIP: Once your videos are viewed once, they move to the “Completed” tab. Please be sure to let your students know to look in the “Completed” tab in case someone else on your team has already viewed your videos.
ABOUT OUR UIL COMPUTER APPLICATIONS VIDEOS
Save time teaching and give your students a real chance to learn some of the concepts of the Texas UIL Computer Applications Contest with video from building a basic pivot table in Excel, to mastering complex update queries, to learning all the ‘ins and outs’ of dates and times, and much more! Each lesson incorporates the latest screen casting technology with the expertise of Huntley Tarrant. Videos are presented in a step-by-step, tutorial format so that students can follow along on their own computer, stopping and starting the video as needed to keep up.