Microsoft Excel Formulas and Functions Application Questions and Answers 1 — 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?
- =SUMIF(A:A, "Laptops", B:B) (Correct answer)
- =COUNTIF(A:A, "Laptops")
- =SUM(B:B)
- =VLOOKUP("Laptops", A:B, 2, FALSE)
Correct answer: =SUMIF(A:A, "Laptops", B:B)
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?
- =IF(B2=0, A2/B2, "N/A")
- =ISERROR(A2/B2, "N/A")
- =IFERROR(A2/B2, "N/A") (Correct answer)
- =DIVIDE(A2, B2, "N/A")
Correct answer: =IFERROR(A2/B2, "N/A")
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?
- =IF(OR(D2<>"", E2>TODAY()), "Eligible", "Not Eligible")
- =IF(AND(D2<>"", E2>TODAY()), "Eligible", "Not Eligible") (Correct answer)
- =IF(D2<>"" AND E2>TODAY(), "Eligible", "Not Eligible")
- =IF(NOT(D2="", E2<=TODAY()), "Eligible", "Not Eligible")
Correct answer: =IF(AND(D2<>"", E2>TODAY()), "Eligible", "Not Eligible")
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?
- =COUNT(C2:C50, ">90")
- =SUMIF(C2:C50, ">=90")
- =COUNTIF(C2:C50, ">=90") (Correct answer)
- =IF(C2:C50>=90, COUNT)
Correct answer: =COUNTIF(C2:C50, ">=90")
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?
- HLOOKUP
- INDEX
- MATCH
- VLOOKUP (Correct answer)
Correct answer: VLOOKUP
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.
Question 6: You need to combine the first name from cell A2 and the last name from cell B2 into a single cell, C2, formatted as 'Lastname, Firstname'. Which formula would achieve this?
- =A2 & ", " & B2
- =CONCAT(B2, ", ", A2) (Correct answer)
- =COMBINE(B2, ", ", A2)
- =TEXTJOIN(" ", TRUE, A2, B2)
Correct answer: =CONCAT(B2, ", ", A2)
The CONCAT function (or CONCATENATE in older versions) joins several text strings into one string. The formula `=CONCAT(B2, ", ", A2)` correctly takes the last name from B2, adds a comma and a space, and then adds the first name from A2 to produce the desired format.
