Microsoft Excel Formulas and Functions Application Questions and Answers

Question 1

A sales manager has a worksheet containing sales data. Column A lists the product names, and Column B lists the corresponding sales amounts. The manager wants to find the total sales for 'Laptops' only. Which of the following formulas is the most appropriate to accomplish this?

Accepted Answer

=SUMIF(A:A, "Laptops", B:B)

Answer

The SUMIF function is used to sum values in a range that meet a specific criterion. In this scenario, it correctly sums the values in column B where the corresponding value in column A is 'Laptops'.

Question 2

You are creating a report to calculate commissions. In cell C2, you have a formula that divides the total profit (A2) by the number of units sold (B2). However, if a product has zero units sold (B2=0), the formula results in a #DIV/0! error. Which formula should be used in C2 to display the text 'N/A' instead of the error, while still performing the division if B2 is not zero?

Accepted Answer

=IFERROR(A2/B2, "N/A")

Answer

The IFERROR function checks if the first argument (the formula) results in an error. If it does, it returns the second argument ('N/A'). If the formula does not result in an error, it returns the result of the formula. This is the most direct way to handle potential errors like #DIV/0!.

Question 3

A worksheet tracks employee training. Column D contains the 'Training Completion Date' and Column E contains the 'Certification Expiration Date'. You need to create a formula in Column F that displays 'Eligible' if an employee's training is complete (Column D is not blank) AND their certification has not expired (Column E is greater than today's date). Otherwise, it should display 'Not Eligible'. Which formula correctly implements this logic?

Accepted Answer

=IF(AND(D2<>"", E2>TODAY()), "Eligible", "Not Eligible")

Answer

This scenario requires two conditions to be met simultaneously. The AND function is the correct logical function to use within the IF statement's logical test. It checks if D2 is not empty (`D2<>""`) and if the date in E2 is in the future (`E2>TODAY()`). Only if both conditions are true will the formula return 'Eligible'.

Question 4

In a table of student grades, you want to count how many students scored 90 or higher. The scores are located in the range C2:C50. What is the correct formula to find this count?

Accepted Answer

=COUNTIF(C2:C50, ">=90")

Answer

The COUNTIF function is designed to count the number of cells within a range that meet a single criterion. The formula `=COUNTIF(C2:C50, ">=90")` correctly specifies the range of scores and the criterion to count only those that are greater than or equal to 90.

Question 5

Which of the following functions would you use to look up an employee's salary from a table, where employee IDs are in the first column and their corresponding salaries are in the third column?

Accepted Answer

VLOOKUP

Answer

VLOOKUP stands for 'Vertical Lookup' and is used to search for a value in the first column of a table array and return a value in the same row from a specified column. This perfectly matches the scenario of finding a salary based on an employee ID.

Excel Practice Test

Excel Practice Test

Microsoft Excel Formulas and Functions Application Questions and Answers