« Back to Class Listing

Excelling at Excel: More Formulas and Functions

Description


In Part 2 of this course, we will continue to discuss VLOOKUPS/HLOOKUPS to pull together information from large, database-like workbooks. From there, we will construct logical comparisons between given and expected values using IF…THEN…ELSE statements to make data more readable.

You will discover how to formulate smarter worksheets by understanding Excel’s time format and using Date Calculations such as TODAY, YEAR, and ROUNDDOWN. Track time spent volunteering, traveling, or working by creating a worksheet that calculates the interval between time in and time out. To keep data accurate, we will also learn to use Data Validation criteria.

Using COUNTIF, SUMIF and AVERAGEIF will help us perform calculations on data meeting specific criteria to extract more meaning from our worksheets. Finally, discover how to calculate totals, averages, and counts within a database system based on certain criteria using DSUM, DAVERAGE, and DCOUNT

Audience: We designed this course for current, advanced users of Excel who want to know more about formulas and functions. This course builds on "Excelling at Excel: The FUNdamentals," “Excelling at Excel: The 5 Fs,” and “Excelling at Excel: Formulas and Functions.”

Software: We teach this course using Microsoft Excel 2016 on our lab PCs, but the content should also apply to Excel 2007, 2010, and 2013 for Windows. Material learned in this course may not apply to Excel for Mac.

After completing this course, participants will be able to:

  • Explain more uses of VLOOKUPS/HLOOKUPS
  • Establish criteria using Data Validation to more easily search for data
  • Measure time in hours and minutes, days, and years
  • Create multiple IF-THEN-ELSE statements for more efficient functions
  • Develop functions—COUNTIFS, SUMIFS, AVERAGEIFS, DSUM, DCOUNT, and DAVERAGE—that give results based on criteria

Section: MS-EXCEL-FORM2 (01AUG18)
Dates: Aug 1, 2018 - Aug 1, 2018 Time: 9:30 AM
Tuition: $185.00 Instructor: Greg Creech Register

Lesson 1: Data Validation
  • Create rules and lists for data and functions
  • Develop error alerts and rules for text, numbers, and dates
  • Restrict users to specific choices in SUMIFS, AVERAGEIFS, and COUNTIF functions

Lesson 2: HLOOKUP/VLOOKUP
  • Multiple VLOOKUP functions carrying data from one worksheet to another
  • Use VLOOKUP for quick searches in a worksheet

Lesson 3: Date functions and Timesheets
  • TODAY Function that measures time in days and years
  • Creating and editing an Excel Timesheet for tracking hours and minutes

Lesson 4: Functions based on Criteria
  • IF. . . THEN. . . ELSE (Multiple criteria)
  • SUMIF and SUMIFS
  • AVERAGEIF and AVERAGEIFS
  • COUNTIF and COUNTIFS

Lesson 5: Database functions
  • DSUM
  • DCOUNT
  • DAVERAGE

Class Schedule

Date Times Meeting Type Location
08/01/2018 9:30 AM - 12:30 PM Classroom Executive Park

Additional Information

Final Enrollment: Aug 1, 2018
Clock Hours: 3.0