Summative Quiz (Excel, Online Platforms and Collaboration)

You have a table with sales data in columns B to F, and you want to find the total sales for the first quarter (January to March). Which Excel formula is most appropriate for this task if you have name of the months on column title?

• =SUM(B1:D1)

• =SUM(B2:D2)

• =SUM(B1:D5)

• =SUM(B2:D5)

You have a column of expenses in cells D1 to D20, and you want to find the total expenses that are greater than $100. Which function should you use?

• =SUMIF(D1:D20, ">100")

• =SUMIF(D1:D20, ">=100")

• =SUMGREATER(D1:D20, 100)

• =SUM(D1:D20, ">100")

You have a dataset in cells B1 to B15, but you want to exclude the value in cell B7 from the average calculation. Which formula will accomplish this?

• =AVERAGE(B1:B15) - B7

• =AVGIF(B1:B15, "<>B7")

• =AVERAGE(B1:B6, B8:B15)

• =AVGEXCLUDE(B1:B15, B7)

You have a dataset in cells C1 to C15, and you want to find the average of values that meet two criteria: the value is greater than 50 in column C and corresponding values in column D are "Yes." Which function should you use?

• =AVERAGE(C1:C15, C1:C15, ">50", D1:D15, "Yes")

• =AVGIFS(C1:C15, C1:C15, ">50", D1:D15, "Yes")

• =AVERAGEIF(C1:C15, ">50", D1:D15, "Yes")

• =AVERAGETWO(C1:C15, ">50", D1:D15, "Yes")

You have the first name in cell A1 and the last name in cell B1. You want to combine them into a full name in cell C1 with space in between. Which formula should you use?

• =CONCAT(A1, B1)

• =CONCATENATE(A1," ",B1)

• =CONCATENATE(A1, B1)

• =MERGE(A1, B1)

You have a list of names in column B from B2 to B15. You want to count the number of cells that contain string data in this range. Which Excel function would you use?

• =COUNTA(B2:B15)

• =COUNT(B2:B15)

• =SUM(B2:B15)

• =LEN(B2:B15)

You have a cell (A1) containing the text "Excel is Fun." You want to convert all the text to lowercase. Which Excel function should you use?

• =UPPER(A1)

• =LOWER(A1)

• =PROPER(A1)

• =TRIM(A1)

You have a column of sales data in column B2. You want to calculate a bonus of 5% for sales greater than $10,000 and no bonus otherwise. Which Excel function should you use?

• =IF(B2 > 10000, 0.05 * B2, 0)

• =IF(B2 < 10000, 0, 0.05 * B2)

• =IF(B2 > 10000, 0, 0.05 * B2)

• =IF(B2 > 10000, 0.05 * B2, B2)

You have a cell (E1) containing a date. You want to check if the date is in the past. If it is, display "Expired"; otherwise, display "Valid." Which Excel function should you use?

• =IF(E1 > TODAY(), "Expired", "Valid")

• =IF(E1 = TODAY(), "Valid", "Expired")

• =IF(E1 < TODAY(), "Expired", "Valid")

• =IF(E1 < NOW(), "Valid", "Expired")

You have a formula in cell D5 that multiplies the value in cell B5 by 2. What type of cell reference should you use for B5 to ensure that when you copy the formula to other cells, it always refers to B5?

• Relative reference: B5

• Absolute reference: $B$5

• Mixed reference: B$5

• Mixed reference: $B5