ASET11. Advanced Office Applications

It is the Excel function that allows to sum numerical data in any number of columns or rows by selecting them or typing them.

• COUNT
• SUM
• MAX
• AVERAGE

This function is used to summate the values of cells in a range that meet the criteria that have been specified.

• SUMIF
• COUNTIF
• AVERAGEIF
• SUM

You have a list of test scores for 10 students in cells A2. You want to calculate the average score. What formula would you use?

• =MEDIAN(A2:A11)
• =SUM(A2:A11)
• =AVERAGE(A2:A11)
• =COUNT(A2:A11)

You have sales data in column A (Sales Amount) and in column B (Region) for 50 transactions. You want to find the average sales amount for transactions from the "North" region. What formula would you use?

• =AVERAGEIF(B1:B50, "North", A1:A50)
• =SUMIF(B1:B50, "North", A1:A50)
• =COUNTIF(B1:B50, "North")
• =MAXIF(B1:B50, "North", A1:A50)

You are grading student performance based on their scores in column A. You want to display "Pass" for scores of 60 and above, and "Fail" for scores below 60. What formula should you use in cell B2 to evaluate the score in A2?

• =IF(A2>=60, 'Pass', 'Fail')
• =IF(A2=60, 'Fail', 'Pass')
• =IF(A2>=50, 'Pass', 'Fail')
• =IF(A2<60, 'Pass', 'Fail')

You have a list of students' exam scores in columns A and B. Column A contains math scores, and column B contains science scores. You want to check if a student passed both exams, where passing is defined as a score of 50 or higher. What formula would you use in cell C2 to check if the student passed both subjects?

• =IF(A2>=50, B2>=50, FALSE)
• =AND(A2>=50, B2>=50)

• =IF(AND(A2>=50, B2>=50), "Pass", "Fail")

• =IF(A2>=50, B2>=50)

You have a product inventory list where column A contains product IDs, and column B contains product names. You want to find the name of the product with the ID "P102" listed in cell D1. What formula would you use?

• =VLOOKUP(D1, A2:B100, 2, FALSE)

• =VLOOKUP(D1, A2:B100, 1, FALSE)

• =VLOOKUP(A1, D2:D100, 2, FALSE)

• =VLOOKUP(D1, B2:A100, 2, TRUE)

You have a list of numbers in cells A1, and a few of the cells are empty. You want to count how many cells in that range contain numbers. What formula should you use?

• =COUNTBLANK(A1:A10)

• =COUNT(A1:A10)
• =COUNTIF(A1:A10, ">0")

• =COUNTA(A1:A10)

You have a list of student grades in column A. You want to count how many students scored 85 or higher. Which formula would you use?

• =COUNT(A:A)

• =COUNTIF(A:A, ">=85")
• =COUNTIF(A:A, "<85")

• =COUNT(A:A, ">=85")

You have a list of product names in column A and their corresponding categories in column B. You want to create a combined string in column C that says "Product [Product Name] belongs to [Category]". Which formula would you use in cell C2?

• =CONCAT(A2, B2)

• =CONCATENATE(A2, B2)
• =CONCATENATE("Product ", A2, " belongs to ", B2)

• =TEXTJOIN(A2, B2)