Excel Jeopardy

Conditional Functions

100

What is the correct syntax to sum the values in range B2:B10 where the corresponding category in A2:A10 is "Sales"?   

• A) =SUMIF("Sales", A2:A10, B2:B10)

• B) =SUMIF(A2:A10, "Sales", B2:B10)

• C) =SUMIF(B2:B10, A2:A10, "Sales")

• D) =SUMIF(A2:A10, B2:B10, "Sales")

B) =SUMIF(A2:A10, "Sales", B2:B10)

200

You want to count how many cells in range C2:C20 contain a value strictly greater than 50. Which formula should you use? 

• A) =COUNTIF(C2:C20, >50)

• B) =COUNTIF(C2:C20, ">50")

• C) =COUNTIF(C2:C20, C2:C20 > 50)

• D) =COUNTIF(">50", C2:C20)

B) =COUNTIF(C2:C20, ">50")

300

Question: What does the formula =AVERAGEIF(A1:A5, "<>", B1:B5) return? 

• A) The average of B1:B5 excluding zero values.

• B) A #VALUE! error. 

• C) The average of B1:B5 for cells where A1:A5 are NOT blank.

• D) The sum of A1:A5 divided by B1:B5.

C) The average of B1:B5 for cells where A1:A5 are NOT blank.

400

 If you want to use wildcards in COUNTIF to count how many cells in A1:A10 start with the letter "A", which criterion is correct? 

• A) "A?"

• B) "A#"

• C) "A%"

• D) "A*"

D) "A*"

500

What is the main difference in argument order between SUMIF and SUMIFS? 

• A) In SUMIF, the sum range comes last; in SUMIFS, the sum range comes first.

• B) In SUMIFS, you do not specify a sum range.

• C) They have the exact same argument structure.

• D) In SUMIF, the first argument is always the text condition.

A) In SUMIF, the sum range comes last; in SUMIFS, the sum range comes first.

Conditional Min/Max

100

 What is the primary purpose of the MAXIFS function? 

• A) To return the absolute maximum value in an entire workbook.

• B) To return the maximum value among cells that meet one or more specified conditions.

• C) To multiply the maximum value by a condition.

• D) To count how many cells exceed the maximum value.

B) To return the maximum value among cells that meet one or more specified conditions.

200

What is the required first argument in the MINIFS function? 

• A) min_range (the range from which the minimum value will be determined)

• B) criteria_range1

• C) criteria1

• D) evaluation_range

• A) min_range (the range from which the minimum value will be determined)

300

 In =MAXIFS(D2:D100, A2:A100, "North", B2:B100, "2024"), what is being evaluated? 

• A) The maximum sum of range A2:A100 for the year 2024.

• B) The maximum value in D2:D100 where the region is "North" AND the year is "2024".

• C) The minimum value of the North-2024 combination.

• D) The total count of "North" entries in 2024.

B) The maximum value in D2:D100 where the region is "North" AND the year is "2024".

400

 If no cells meet the specified criteria in a MINIFS formula, what result does Excel return? 

• A) #N/A

• B) #VALUE!

• C) 0

• D) An empty text string ""

C) 0

500

What happens if the criteria range (A1:A10) and min range (B1:B20) are not the same size in MINIFS? 

• A) Excel automatically resizes the range.

• B) It returns a #VALUE! error.

• C) It evaluates only the first 10 cells without error.

• D) It returns 0.

B) It returns a #VALUE! error.

Logical Logic

100

What is the result of =IF(10 > 20, "Pass", "Fail")? 

• A) Pass

• B) Fail

• C) TRUE

• D) #VALUE!

B) Fail

200

 The formula =AND(A1>5, B1<10) evaluates to TRUE only when: 

• A) At least one of the conditions is true.

• B) A1 is equal to B1..

• C) Neither condition is true.

• D) Both conditions are true at the same time.

D) Both conditions are true at the same time.

300

What value does =OR(5 > 10, 2 = 2, 8 < 3) return? 

• A) FALSE

• B) TRUE

• C) #N/A

• D) 0

B) TRUE

400

 What is the result of =NOT(OR(5 > 2, 3 = 3))? 

• A) TRUE

• B) NULL

• C) #VALUE!

• D) FALSE

D) FALSE

500

You want to check if an employee gets a bonus: Sales > 1000 OR Tenure > 5 years, AND zero absences (C2 = 0). Which formula is correct? 

• A) =IF(AND(OR(Sales>1000, Tenure>5), C2=0), "Bonus", "No Bonus")

• B) =IF(OR(AND(Sales>1000, Tenure>5), C2=0), "Bonus", "No Bonus")

• C) =IF(AND(Sales>1000, Tenure>5, C2=0), "Bonus", "No Bonus")

• D) =IF(NOT(OR(Sales>1000, C2=0)), "Bonus", "No Bonus")

A) =IF(AND(OR(Sales>1000, Tenure>5), C2=0), "Bonus", "No Bonus")

Pivot Tables & Charts

100

What is the primary function of a Pivot Table in Excel? 

• A) To automatically format cell colors.

• B) To write VBA macros.

• C) To insert complex mathematical functions into individual cells.

• D)  To summarize, analyze, and group large amounts of data quickly.

D)  To summarize, analyze, and group large amounts of data quickly.

200

 By default, what calculation does a Pivot Table apply when a text field is dragged to the "Values" area? 

• A) Sum

• B) Average

• C) Count

• D) Max

C) Count

300

What is the purpose of adding a "Secondary Axis" to a chart? 

• A) To change the title formatting of the chart.

• B) To plot two data series with significantly different scale ranges (e.g., $ Amounts and % Percentages).

• C) To move the chart legend to the bottom.

• D) To hide data series from view.

B) To plot two data series with significantly different scale ranges (e.g., $ Amounts and % Percentages).

400

Which Pivot Table feature allows you to add custom calculations based on other existing fields? 

• A) Slicer

• B) Calculated Field

• C) Timeline

• D) Conditional Format

B) Calculated Field

500

When source data changes, what must you do to update the Pivot Table? 

• A) Nothing, it updates automatically in real time.

• B) Click "Refresh" or press ALT + F5.

• C) Recreate the Pivot Table from scratch.

• D) Fix the #REF! error.

B) Click "Refresh" or press ALT + F5.

Conditional Formatting

100

What is the primary purpose of Conditional Formatting? 

• A) To change font family across the whole worksheet.

• B) To lock cells so they cannot be edited. 

• C)  To automatically apply visual formatting (colors, borders, fonts) based on cell values or rules.

• D) To password protect a worksheet.
  
  
  

C)  To automatically apply visual formatting (colors, borders, fonts) based on cell values or rules.

200

What happens when a cell meets the criteria of two conflicting Conditional Formatting rules? 

• A) Excel throws an error.

• B) The rule higher in the hierarchy order within the Rules Manager takes precedence.

• C) The cell becomes transparent.

• D) The most recently created rule always wins.

B) The rule higher in the hierarchy order within the Rules Manager takes precedence.

300

 When using a formula rule to highlight an entire row across $A$2:$D$10 based on Column A, how should the column reference be formatted? 

• A) Relative column and row (e.g., A2="Completed")

• B) Relative column, absolute row (e.g., A$2="Completed") 

• C) Absolute column and row (e.g., $A$2="Completed")

• D) Absolute column, relative row (e.g., $A2="Completed")

D) Absolute column, relative row (e.g., $A2="Completed")

400

Which of the following is NOT a built-in Conditional Formatting feature? 

• A) Data Bars

• B) Color Scales

• C) Icon Sets

• D) Sparklines

D) Sparklines

500

What does checking the "Stop If True" box in Conditional Formatting do? 

• A) Deletes remaining rules if an error occurs.

• B) Prevents subsequent rules from being evaluated if the current rule condition is met.

• C) Stops macro execution.

• D) Locks the cell value.

B) Prevents subsequent rules from being evaluated if the current rule condition is met.