Accounting Information Systems

1

Excel Workshop

Subject Code: ACC 202

Subject Name: Accounting Information Systems

Workshop Dates: Week 3, Week 6, Week 8 and Week 9

Workshop Location: Computer Lab

Objective: Microsoft Excel is the most used spreadsheet program in many business

activities; therefore, these workshops are aimed to provide you with the

fundamental and some advanced excel features.

Workshop Outline: The workshop questions are divided into Exercises and Practice

Questions. The lecturers are required to explain the concept with the help

of given exercises. The students are required to practice similar kind of

questions through practice questions.

2

Week 6

Overview

Exercises Practice Questions

❖ Exercise 8

IF Function

❖ Exercise 9

IF Function (True, False)

❖ Exercise 10

IFERROR Function

❖ Exercise 11

AVERAGEIF Function

❖ Exercise 12

Remove Duplicate

❖ Exercise 13

MAX and MIN Function

❖ Exercise 14

Data Validation - Range

❖ Exercise 15

Data Validation - Drop Down Menu

❖ Practice Question 10

IF Function (True, False)

❖ Practice Question 11

IFERROR Function

❖ Practice Question 12

AVERAGEIF Function

❖ Practice Question 13

AVERAGEIF Function

❖ Practice Question 14

Data Validation

3

Exercise 8 – IF Function

The IF function is one of the most popular functions in Excel, and it allows you to make logical

comparisons between a value and what you expect. Enter the following data into Excel.

Once you have completed the exercise, your answer should look like this:

4

Exercise 9 – IF Function (True, False)

The IF function can perform a logical test and return one value for a TRUE result, and another for a

FALSE result. For example, to "pass" scores above or equal to 70: =IF(B2>=70,"Pass","Fail"). More than

one condition can be tested by nesting IF functions.

Once you have completed the exercise, your answer should look like this:

5

Practice Question 10 – IF Function (True, False)

From the given data, identify the students who have aced equivalent to 50 or above to pass.

Once you have completed the practice question, your answer should look like this:

6

Exercise 10 – IFERROR Function

Excel displays the #DIV/0! error when a formula tries to divide a number by 0. IFERROR checks for the

following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Once you have completed the exercise, your answer should look like this:

7

Practice Question 11 – IFERROR Function

From the data given below, apply the IFERROR function.

Once you have completed the practice question, your answer should look like this:

8

Exercise 11 – AVERAGEIF Function

The AVERAGE function calculates the average of a series of numbers. That’s great, but what if you want

to choose a specific subset of numbers for your average? That’s where AVERAGEIF comes in. To find out

the value of the average test score in Spring:

Once you have completed the exercise, your answer should look like this:

9

Practice Question 12 – AVERAGEIF Function

From the data given in the table below, practice and find out the average value for chocolate pasties.

Once you have completed the practice question, your answer should look like this:

10

Practice Question 13 – AVERAGEIF Function

From the data given in the table below, practice and find out the average test value for Spring and Fall.

Once you have completed the practice question, your answer should look like this:

11

Exercise 12 – Remove Duplicate

If the duplicate data set is given, you can easily remove it in Excel.

Once you have completed the exercise, your answer should look like this:

12

Exercise 13 – MAX and MIN Function

The MAX function can be used to return the largest value from a set of data. The MIN function can be

used to return the smallest value from a set of data.

Once you have completed the exercise, your answer should look like this:

13

Exercise 14 – Data Validation - Range

Set data validation on cells C2:C15 to restrict users to a whole number between 0 and 26.

Once you have completed the exercise, your answer should look like this:

14

Exercise 15 – Data Validation – Drop Down Menu

Set a data validation list for cells C2:C15 (drop down list of three departments – Sales, Admin and

Accounts).

Once you have completed the exercise, your answer should look like this:

15

Practice Question 14 – Data Validation

From the data given below, set data validation on cells D2:D15 hours to restrict users to a whole

number between 0 and 32 and set a data validation list for cells C2:C15 ( Sales, Accounts). For column E,

calculate the pay: $22/hour multiply by number of hours worked.

Once you have completed the practice question, your answer should look like this:

End of the workshop