From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365
Unlock the full course today
Join today to access over 22,600 courses taught by industry experts or purchase this course individually.
Solution: Create advanced formulas
From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365
Solution: Create advanced formulas
(upbeat music) - [Instructor] Welcome to the solution video for the chapter four challenge. For this challenge, we used the 04_14 challenge exercise file. And I challenged you to complete all the tasks in 12 minutes, or less. Let's get started. First, we will save the file as my challenge in the chapter four folder. File, and save as, chapter four folder, my challenge, and save. Complete the task on the project one through four worksheets, project one. Create a drop down list in G5 containing the names of all sales people. Only the names in the list can be used. Let's go to G5, we'll create a data validation list. Settings, list, our source will be the sales people. Only names in the list can be used, make sure the error alert is set to stop. That looks good. Select Booker. In cell H5 use the function to return the commission rate for the employees selected in G5. So the commission rate is to the left of…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
Use AND(), OR(), and NOT() in nested functions6m 9s
-
Use IF() and IFS() in nested functions4m 31s
-
(Locked)
Use SWITCH() in nested functions3m 38s
-
Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions5m 2s
-
(Locked)
Use COUNT() and COUNTIF()2m 54s
-
Use MAXIFS() and MINIFS() in nested functions3m 27s
-
(Locked)
Look up data using VLOOKUP() and HLOOKUP()5m 52s
-
Use the MATCH() and INDEX() functions5m 47s
-
(Locked)
Use NOW() and TODAY() functions and calculate dates using WEEKDAY() and WORKDAY() functions8m 15s
-
(Locked)
Summarize data from multiple ranges by using the Consolidate feature3m 10s
-
(Locked)
Use What-If Analysis Goal Seek and Scenario Manager5m 57s
-
(Locked)
Calculate financial data with NPER() and PMT() functions3m 30s
-
(Locked)
Troubleshoot formulas9m 24s
-
(Locked)
Challenge: Create advanced formulas27s
-
(Locked)
Solution: Create advanced formulas9m 8s
-
-
-
-