week 3 final assignment

• Online Degree Explore Bachelor’s & Master’s degrees
• MasterTrack™ Earn credit towards a Master’s degree
• University Certificates Advance your career with graduate-level learning
• Top Courses
• Join for Free

Excel Skills for Business: Intermediate II

This course is part of Excel Skills for Business Specialization

Taught in English

Some content may not be translated

Instructors: Nicky Bull +2 more

Instructors

Instructor ratings.

We asked all learners to give feedback on our instructors based on the quality of their teaching style.

Financial aid available

207,227 already enrolled

(8,024 reviews)

Skills you'll gain

• Lookup Table
• Microsoft Excel Vba

Data Validation

• Microsoft Excel Macro

Details to know

Add to your LinkedIn profile

See how employees at top companies are mastering in-demand skills

Build your subject-matter expertise

• Learn new concepts from industry experts
• Gain a foundational understanding of a subject or tool
• Develop job-relevant skills with hands-on projects
• Earn a shareable career certificate

Earn a career certificate

Add this credential to your LinkedIn profile, resume, or CV

Share it on social media and in your performance review

There are 7 modules in this course

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to

Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.

We kick off this course with data validation and conditional formatting. This module takes you through creating and applying data validation, as well as working with formulas in data validation. This is followed by basic and advanced conditional formatting.

What's included

8 videos 6 readings 7 quizzes 1 discussion prompt

8 videos • Total 42 minutes

• Week 1 Introduction • 2 minutes • Preview module
• Week 1 Discussion • 4 minutes
• Practice Video: Data Validation • 6 minutes
• Practice Video: Creating Drop-down Lists • 6 minutes
• Practice Video: Using Formulas in Data Validation • 7 minutes
• Practice Video: Working with Data Validation • 7 minutes
• Practice Video: Advanced Conditional Formatting • 5 minutes
• Week 1 Wrap-Up • 2 minutes

6 readings • Total 65 minutes

• Welcome to Excel Skills for Business: Intermediate II • 10 minutes
• Course goals and weekly learning objectives • 10 minutes
• Read me before you start: Quizzes and navigation • 10 minutes
• Read me before you start: Versions and regions • 10 minutes
• Download the Week 1 workbooks • 5 minutes
• Keyboard Shortcuts, Excel Terminology, and Ninja Tips • 20 minutes

7 quizzes • Total 210 minutes

• Week 1 Final Assignment • 30 minutes
• Data Validation • 30 minutes
• Creating Drop-down Lists • 30 minutes
• Using Formulas in Data Validation • 30 minutes
• Working with Data Validation • 30 minutes
• Advanced Conditional Formatting • 30 minutes
• Week 1 Practice Challenge • 30 minutes

1 discussion prompt • Total 10 minutes

• Excel: Who, Why, How? • 10 minutes

Conditional Logic

Excel has several logical functions and this module explores some of them. Start by learning the concept of conditional logic in formulas, followed by how to conduct logic tests and use conditional operations, to your benefit. We will also look at nested IF functions to evaluate data.

8 videos 2 readings 7 quizzes

8 videos • Total 38 minutes

• Week 2 Introduction • 2 minutes • Preview module
• Week 2 Discussion • 4 minutes
• Practice Video: Logical Functions I: IF • 7 minutes
• Practice Video: Logical Functions II: AND, OR • 5 minutes
• Practice Video: Combining Logical Functions I: IF, AND, OR • 5 minutes
• Practice Video: Combining Logical Functions II: Nested IFs • 5 minutes
• Practice Video: Handling Errors: IFERROR, IFNA • 6 minutes
• Week 2 Wrap-Up • 1 minute

2 readings • Total 25 minutes

• Download the Week 2 workbooks • 5 minutes

7 quizzes • Total 188 minutes

• Week 2 Final Assignment • 30 minutes
• Logical Functions I: IF • 8 minutes
• Logical Functions II: AND, OR • 30 minutes
• Combining Logical Functions I: IF, AND, OR • 30 minutes
• Combining Logical Functions II: Nested IFs • 30 minutes
• Handling Errors: IFERROR, IFNA • 30 minutes
• Week 2 Practice Challenge • 30 minutes

Automating Lookups

How do you find information from different parts of a workbook? This module introduces you to functions like CHOOSE, VLOOKUP, INDEX, MATCH and other dynamic lookups to find and display data from several sources.

8 videos • Total 39 minutes

• Week 3 Introduction • 2 minutes • Preview module
• Week 3 Discussion • 4 minutes
• Practice Video: Introduction to Lookups: CHOOSE • 3 minutes
• Practice Video: Approximate Matches: Range VLOOKUP • 5 minutes
• Practice Video: Exact Matches: Exact Match VLOOKUP • 6 minutes
• Practice Video: Finding a Position: MATCH • 7 minutes
• Practice Video: Dynamic Lookups: INDEX, MATCH • 7 minutes
• Week 3 Wrap-Up • 1 minute
• Download the Week 3 workbooks • 5 minutes
• Week 3 Final Assignment • 30 minutes
• Introduction to Lookups: CHOOSE • 30 minutes
• Approximate Matches: Range VLOOKUP • 30 minutes
• Exact Matches: Exact Match VLOOKUP • 30 minutes
• Finding a Position: MATCH • 30 minutes
• Dynamic Lookups: INDEX, MATCH • 30 minutes
• Week 3 Practice Challenge • 30 minutes

Formula Auditing and Protection

If you are worried that errors have crept into your worksheet, this module will show you how to check for errors, trace precedents and dependents, resolve circular references, and finally, protect your worksheets and workbooks from further harm.

8 videos 2 readings 7 quizzes 1 discussion prompt

8 videos • Total 47 minutes

• Week 4 Introduction • 2 minutes • Preview module
• Week 4 Discussion • 3 minutes
• Practice Video: Error Checking • 9 minutes
• Practice Video: Formula Calculation Options • 6 minutes
• Practice Video: Trace Precedents and Dependents • 5 minutes
• Practice Video: Evaluate Formula, Watch Window • 8 minutes
• Practice Video: Protecting Workbooks and Worksheets • 9 minutes
• Week 4 Wrap-Up • 1 minute
• Download the Week 4 workbooks • 5 minutes
• Week 4 Final Assignment • 30 minutes
• Error Checking • 30 minutes
• Formula Calculation Options • 30 minutes
• Tracing Precedents and Dependents • 30 minutes
• Evaluate Formula, Watch Window • 30 minutes
• Protecting Workbooks and Worksheets • 30 minutes
• Week 4 Practice Challenge • 30 minutes

1 discussion prompt • Total 15 minutes

• Find the remaining errors • 15 minutes

Data Modelling

This module is all about data modelling. Learn to model different scenarios based on input, assumptions and/or outcomes. Also learn the use of functionalities like Goal Seek, Data Tables and the Scenario Manager to make your models more robust.

8 videos • Total 44 minutes

• Week 5 Introduction • 2 minutes • Preview module
• Week 5 Discussion • 4 minutes
• Practice Video: Modelling Functions: SUMPRODUCT • 5 minutes
• Practice Video: Data Tables • 7 minutes
• Practice Video: Goal Seek • 5 minutes
• Practice Video: Scenario Manager • 8 minutes
• Practice Video: Solver • 9 minutes
• Week 5 Wrap-Up • 1 minute

2 readings • Total 15 minutes

• Download the Week 5 workbooks • 5 minutes
• Keyboard Shortcuts, Excel Terminology, and Ninja Tips • 10 minutes
• Week 5 Final Assignment • 30 minutes
• Modelling Functions (SUMPRODUCT) • 30 minutes
• Data Tables • 30 minutes
• Goal Seek • 30 minutes
• Scenario Manager • 30 minutes
• Solver • 30 minutes
• Week 5 Practice Challenge • 30 minutes

Recording Macros

We all love a bit of automation, and this module teaches you just that. By the time you have completed this module, you will be able to identify the uses of macros, as well as create, edit and manage them to increase your efficiency.

• Week 6 Introduction • 2 minutes • Preview module
• Week 6 Discussion • 3 minutes
• Practice Video: Record a Macro • 7 minutes
• Practice Video: Run a Macro • 5 minutes
• Practice Video: Edit a Macro • 7 minutes
• Practice Video: Working with Macros • 5 minutes
• Practice Video: Relative Reference Macros • 8 minutes
• Week 6 Wrap-Up • 1 minute
• Download the Week 6 workbooks • 5 minutes
• Week 6 Final Assignment • 30 minutes
• Record a Macro • 30 minutes
• Run your Macro • 30 minutes
• Edit a Macro • 30 minutes
• Working with Macros • 30 minutes
• Relative Reference Macros • 30 minutes
• Week 6 Practice Challenge • 30 minutes

Final Assessment

1 quiz • total 30 minutes.

• Final Assessment • 30 minutes

Macquarie is ranked among the top one per cent of universities in the world, and with a 5-star QS rating, we are recognised for producing graduates who are among the most sought-after professionals in the world. Since our foundation 54 years ago, we have aspired to be a different type of university: one focused on fostering collaboration between students, academics, industry and society.

Recommended if you're interested in Business Essentials

Macquarie University

Excel Skills for Business: Advanced

Excel Skills for Business: Intermediate I

Excel Skills for Business

Specialization

Excel Skills for Business: Essentials

Why people choose coursera for their career.

Learner reviews

Showing 3 of 8024

8,024 reviews

Reviewed on Apr 16, 2020

I really enjoy each and every learning exercise, the course is designed in a very well as exercises, tips, practice challenges are very well designed and ensured we learned from fundament.

Reviewed on Aug 12, 2020

The video lectures are nice but pls improve the quality of practice excel workbooks because sometimes it did not open and excel shows that the workbook is corrupted and it is very disturbing.

Reviewed on May 20, 2021

I've learned so much. I'm currently unemployed and hoping to get a job where I can put my skills to work. I need to practice practice practice because "Practice makes permanent!". Thanks

New to Business Essentials? Start here.

Open new doors with Coursera Plus

Unlimited access to 7,000+ world-class courses, hands-on projects, and job-ready certificate programs - all included in your subscription

Advance your career with an online degree

Earn a degree from world-class universities - 100% online

Join over 3,400 global companies that choose Coursera for Business

Upskill your employees to excel in the digital economy

Frequently asked questions

What version of excel do you teach.

In this course, we are using Microsoft Excel 2016 (Office 365) on a Windows PC with all options set to default. The Office 365 version of Excel is the latest version at the time of writing and developing this course. If you have a different version of Excel or you are using a Mac, some tools and buttons will look different, they may be located in a different spot, or some might be missing altogether. If there are any major differences, we usually point them out during the practice videos. If you cannot find a tool that we mention, you can always ask in the forums or search the Internet. Most of the functionality we demonstrate is also available in other spreadsheet programs, although the interface will look different. Most other spreadsheet software can import and export files in the Microsoft Excel format. If you are not sure which version of Excel you are using, check the Microsoft support pages for help.

If you cannot use Excel 2016 on a PC, you will be able to follow along with the videos, however, please note that some of the assessment tasks will require Excel. Where possible, we have designed assessment tasks so that users of older versions of Excel (such as 2013) can still solve them.

When will I have access to the lectures and assignments?

Access to lectures and assignments depends on your type of enrollment. If you take a course in audit mode, you will be able to see most course materials for free. To access graded assignments and to earn a Certificate, you will need to purchase the Certificate experience, during or after your audit. If you don't see the audit option:

The course may not offer an audit option. You can try a Free Trial instead, or apply for Financial Aid.

The course may offer 'Full Course, No Certificate' instead. This option lets you see all course materials, submit required assessments, and get a final grade. This also means that you will not be able to purchase a Certificate experience.

What will I get if I subscribe to this Specialization?

When you enroll in the course, you get access to all of the courses in the Specialization, and you earn a certificate when you complete the work. Your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile. If you only want to read and view the course content, you can audit the course for free.

What is the refund policy?

If you subscribed, you get a 7-day free trial during which you can cancel at no penalty. After that, we don’t give refunds, but you can cancel your subscription at any time. See our full refund policy Opens in a new tab .

Is financial aid available?

Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.

More questions

Week 3 Final Assignment >> Excel Skills for Business: Intermediate II

1. Download the following file in order to answer all the questions in this final assessment.

C3 W3 Final Assessment.xlsx

How many sheets does this file have?

2. The CHOOSE function

Using the data in Data Task 1, use the CHOOSE function to describe the items coded.

The data is available in the ‘Data’ sheet. Here you will need to click the “plus” icon near ‘Task 1’ to reveal the relevant data.

Now, using the data in Task 1, use the CHOOSE function to fill the green cells D31:E33.

Every time you enter something in the green cells, the Assessment Code for Task 1 will change.

This code has been programmed by us for the purposes of checking your answers.

When you have the correct answers, and if you have correctly used the CHOOSE function, the Assessment Code that is generated will let us know this.

Please do not try to type anything into the yellow cell as that would disrupt the assessment code. If you do type into the yellow cell, re-download the Excel file from Coursera.

You will be required to enter the data in the green cells, as well as the data in the yellow cell onto the Coursera platform so that we can mark you.

What is the ‘Assessment Code for Task 1’? Note that this will only work in the English version of Excel 2013 or newer. If you are using Excel in a different language, please set it to English while doing this assessment task.

3. In the Instructions sheet, what is in cell D31?

4. In the Instructions sheet, what is in cell E32?

5. Range VLOOKUP

Using the data in Data Task 2, use Range VLOOKUP to complete this task.

Long Distance phone calls are based on a sliding scale depending on the length of time take on a call. The charges rates are found in the ‘Data’ sheet in Task 2. For example, a call that lasts between 10 and 20 minutes will be charged $2.00. As soon as a call duration passes the threshold it is charged at the higher rate. For example, a 7 minute call will be charged $1.75.

Complete the Phone bill charges and calculate the total at the bottom.

The data is available in the Data sheet. Here you will need to click the “plus” icon near Task 2 to reveal the relevant data.

Now, using the data in Task 2, use Range VLOOKUP to fill the green cells D50:D54.

Every time you enter something in the green cells, the Assessment Code for Task 2 will change.

When you have the correct answers, and if you have correctly used Range VLOOKUP, the Assessment Code that is generated will let us know this.

What is the ‘Task 2 Total’ in cell D55? (Enter this as a number with two decimal places and without a $ sign)

6. What is the Assessment Code for Task 2?

Note that this will only work in the English version of Excel 2013 or newer. If you are using Excel in a different language, please set it to English while doing this assessment task.

Exact Match VLOOKUP

At a fast food Café, the waitress went round and took down the orders for a large group of 10

Use VLOOKUP with exact matching to complete the table.

Make any adjustment to the items wherever necessary

Calculate the Grand Total for the party of 10

The data is available in the Data sheet. Here you will need to click the “plus” icon near ‘Task 3’ to reveal the relevant data.

Now, using the data in Task 3, use Exact Match VLOOKUP to fill the green cells C72:C82.

Every time you enter something in the green cells, the Assessment Code for Task 3 will change.

When you have the correct answers, and if you have correctly used Exact Match VLOOKUP, the Assessment Code that is generated will let us know this. For the final argument to VLOOKUP, range_lookup, you should use FALSE instead of 0, otherwise the Assessment Code will not be correctly generated.

What is the ‘Task 3 Total’ in cell E83? (Enter this as a number with one decimal place and without a $ sign)

8. What is the Assessment Code for Task 3?

9. INDEX and MATCH

An outdoor clothing and accessories store placed prices on items according to the colours in demand for the item as per table in Data Task 4

Using the INDEX and MATCH functions, find the prices on these three items

The data is available in the Data sheet. Here you will need to click the “plus” icon near ‘Task 4’ to reveal the relevant data.

Now, using the data in Task 4, use INDEX and MATCH to fill the green cells D99:D101.

Every time you enter something in the green cells, the Assessment Code for Task 4 will change.

When you have the correct answers, and if you have correctly used INDEX and MATCH, the Assessment Code that is generated will let us know this.

What is the ‘Task 4 Total’ in cell D102? (Enter this as a number without a $ sign)

10. What is the Assessment Code for Task 4 in cell G99?

Related Questions & Answers:

• Week 1 Final Assignment >> Excel Skills for Business: Intermediate II Week 1 Final Assignment >> Excel Skills for Business: Intermediate II 1. The attached spreadsheet provides information regarding the companies ... Read more...
• Week 2 Final Assignment >> Excel Skills for Business: Intermediate II Week 2 Final Assignment >> Excel Skills for Business: Intermediate II 1. We would like to make a courtesy phone ... Read more...
• Week 4 Final Assignment >> Excel Skills for Business: Intermediate II Week 4 Final Assignment >> Excel Skills for Business: Intermediate II 1. We have been given a vehicle spare parts ... Read more...
• Week 5 Final Assignment >> Excel Skills for Business: Intermediate II Week 5 Final Assignment >> Excel Skills for Business: Intermediate II 1. The following file describes the situation faced by ... Read more...
• Final Assessment >> Excel Skills for Business: Intermediate II Final Assessment >> Excel Skills for Business: Intermediate II 1. You are required to work on a spreadsheet for an ... Read more...
• Week 3 Final Assignment >> Excel Skills for Business: Advanced Week 3 Final Assignment >> Excel Skills for Business: Advanced 1. You have received some sales data from your company’s ... Read more...

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Please Enable JavaScript in your Browser to Visit this Site.

• Study Guides
• Homework Questions

Assignment week 3 TClemonsLesson 2021