Excel - Conditional Functions

Full Name

Student Id (ID No.)

Email ID

CountA function can count on the following:

• Numbers

• Text

• Blank Cells

• Non-blank Cell

Which of the following is the syntax for Averageifs?

• Average_range, criteria_range1, criteria,...

• Criteria_range1, Criteria, Average_range,....

• Criteria, Criteria_range1, Average_range,...

• Criteria_range1, Average_range, Criteria,..

John wishes to count all the products that have been marked with a Yes in the Expired column B5:B9) AND under the Stored column (C5:C9) is marked as Refrigerator. Which of the functions below will permit him to calculate this when entered into cell B11 (the highlighted cell in the screenshot alongside)?

• =COUNTIF(B5:B9,”Yes”,C5:C9,”Refrigerator”)

• =SUMIFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)

• =IFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)

• =COUNTIFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)

Which formula shall yield us the average income of employees in Ohio?

• =averageif(C2:C10, "OHIO", C2:C10)

• =averageif("Ohio", C2:C10, A2:A10)

• =averageif(A2:A10, "Ohio", C2:C10)

• =averageifs(A2:A10, "Ohio", C2:C10)

The adjacent table gives the details of revenue by account numbers. Which formula shall return the total revenue for "Gold" accounts in the state of NY?

• =total(D2:D11, "Gold", "NY")

• =sumif(D2:D11, Type, "Gold", State, "NY")

• =Sumifs(Revenue, Type, "Gold", State, "NY")

• =SumTotal(D2:D11, "Gold", "NY")

Which formula will you use to calculate the Number of people who were absent?

• =COUNTBLANK(Attendance)

• =SUM(Attendance)

• =COUNTA(Attendance)

• =COUNT("Attendance")

What is the difference between Sum and Sumifs?

• Sum only calculates the total, Sumifs returns the sum of cells which meet the multiple criteria

• Sumifs is used for adding numbers in different columns, while Sum is used for adding numbers in the same column.

• Sumifs requires more parameters than Sum.

• Sumifs is used for calculating the average, while Sum is used for calculating the total.