Data Analytics in Excel

Which of the following functions can be used to add the values in a range that meet a specific criterion?

• SUM

• SUMIF

• AVERAGE

• COUNTIF

What is the primary purpose of using a PivotTable in Excel?

• To format data

• To summarize and analyse data

• To sort data

• To filter data

Which Excel function would you use to look up a value in the first column of a range and return a value in the same row from a specified column?

• INDEX

• HLOOKUP

• MATCH

• VLOOKUP

If you want to apply a specific format to cells that meet certain criteria, which feature should you use?

• Cell Styles

• Data Validation

• Conditional Formatting

• Filter

What does the formula =COUNTIFS(A2:A10, ">=100", B2:B10, "<=50") do?

• Counts the number of cells in A2 that are greater than or equal to 100.

• Counts the number of cells in B2 that are less than or equal to 50.

• Counts the number of rows where values in A2 are >= 100 and B2 are <= 50.

• Sums the values in A2 and B2 based on the criteria.

Which of the following can be achieved using the 'Data Validation' feature in Excel?

• Restricting the type of data or values that can be entered into a cell

• Creating dynamic charts

• Summing a range of cells based on criteria

• Automatically formatting cells based on their values

What is the purpose of the IF function in Excel?

• To find a value in a table

• To create a logical test that returns different results based on whether the test is true or false

• To filter data based on criteria

• To format cells automatically

Which formula will correctly use an absolute reference to keep the reference constant when copying across multiple cells?

• =A1

• =A$1

• =$A$1

• =$A1

In Excel, what does the formula =AVERAGEIF(A2:A10, ">100") do?

• Averages all numbers in the range A2.

• Averages all numbers in the range A2 to A10 that are greater than 100.

• Finds the maximum value in the range A2 that is greater than 100.

• Counts all numbers in the range A2 to A10 that are greater than 100.

Which function would you use to calculate the sum of values in a range that meets multiple criteria?

• SUMIFS

• SUM

• AVERAGEIFS

• SUMIF