Unlock hundreds more features
Save your Quiz to the Dashboard
View and Export Results
Use AI to Create Quizzes and Analyse Results

OR
Sign inSign in with Facebook
Sign inSign in with Google
Quizzes > Quizzes for Business > Technology

Excel Formula Evaluation Quiz Challenge

Sharpen Your Excel Function Analysis Techniques

Difficulty: Moderate
Questions: 20
Learning OutcomesStudy Material
Colorful paper art displaying questions for Excel Formula Evaluation Quiz

Ready to challenge your Excel formula skills? This Excel Formula Evaluation Quiz features a variety of formula quiz questions designed to sharpen your understanding of calculations and functions. Ideal for students, professionals, or anyone looking to boost spreadsheet performance, this formula quiz can be fully adapted in the editor to fit custom learning goals. Explore similar Excel Skills Assessment Quiz or advance with the Excel Intermediate Skills Assessment, and don't forget to browse more quizzes for endless practice.

In Excel, which operator does Excel evaluate first in the formula =2+3*4?
Exponentiation
Multiplication
Subtraction
Addition
Excel follows the order of operations known as PEMDAS. Multiplication is evaluated before addition. Therefore in =2+3*4, the multiplication occurs first.
Which of the following is a relative cell reference in Excel?
$A$1
A$1
A1
$A1
A relative reference like A1 adjusts both row and column when copied. The dollar signs in other references lock either row, column, or both. Thus A1 is fully relative.
What does the reference $B$2 signify in an Excel formula?
Row relative, column absolute
Column and row both relative
Column and row both absolute
Row absolute, column relative
The dollar sign before both the column letter and row number locks each element. $B$2 will not change when the formula is copied. This makes both column and row absolute.
What will the formula =IF(5>3, "Yes", "No") return in Excel?
TRUE
FALSE
Yes
No
The IF function tests the logical condition 5>3, which is TRUE. It returns the second argument when TRUE. Therefore it returns "Yes".
Which of these formulas will produce a #DIV/0! error?
=SUM(1,2)
=10/2
=10/0
0/10
A #DIV/0! error occurs when a formula divides by zero. The expression =10/0 attempts to divide ten by zero, triggering that error. The other formulas divide by nonzero or perform a valid sum.
What is the result of =2+3*4^2 in Excel?
20
80
64
50
Excel evaluates exponentiation first (4^2 = 16), then multiplication (3*16 = 48), and finally addition (2+48 = 50). The order is ^, then *, then +.
After copying a formula containing the reference $A1 from cell B2 to C3, what does $A1 become?
A$2
A$1
$A1
$A2
In $A1, the column A is absolute and the row is relative. Copying one row down changes the row from 1 to 2, while the column remains A. Hence it becomes $A2.
Identify the syntax error in the formula =IF(A1>0,"Positive""Negative").
Missing quotes around Positive
Missing comma between value_if_true and value_if_false
Missing second argument
Extra parentheses
The IF function syntax requires commas between its arguments. Here there is no comma separating "Positive" and "Negative". Adding the comma fixes the error.
Which combination allows returning a value to the left of the lookup column in older Excel versions?
VLOOKUP
INDEX/MATCH
HLOOKUP
MATCH/INDEX
VLOOKUP can only look to the right. INDEX and MATCH used together can look in any direction by matching a value in one column and returning from another. This combination supports leftward lookups.
If the range_lookup argument is omitted in VLOOKUP, what match type does Excel use by default?
Left lookup
Case-sensitive match
Approximate match
Exact match
When range_lookup is omitted or set to TRUE, VLOOKUP performs an approximate match. It finds the closest value not greater than the lookup value in a sorted list.
Given =IF(A1>10,"High",IF(A1>5,"Medium","Low")) and A1=7, what is returned?
High
#VALUE!
Medium
Low
The first IF tests A1>10, which is FALSE for 7. The second nested IF tests A1>5, which is TRUE. Therefore it returns "Medium".
Which error type does #REF! indicate in Excel?
Name error
Invalid cell reference
Division by zero
Circular reference
#REF! appears when a formula refers to a cell that doesn't exist, such as after deleting a referenced row or column. It signals an invalid cell reference.
Which of these is a valid mixed reference in Excel?
$A$1
A1
A$1
$A1
A mixed reference locks either the column or the row. In $A1 the column A is absolute while row 1 is relative. This makes $A1 a valid mixed reference.
What result does =-2^2 produce in Excel?
-4
4
2
0
Excel evaluates exponentiation before negation. The expression is interpreted as -(2^2), which equals -4. Parentheses would be needed to get +4.
Which best describes Excel's order of operations?
Left to right in the order typed
All operations simultaneously
Multiply before Exponents
Parentheses, then Exponents, then Multiply/Divide, then Add/Subtract
Excel follows standard mathematical precedence: parentheses first, then exponentiation, multiplication/division, and finally addition/subtraction. This ensures consistent calculation results.
A formula in B2 is =SUM($A2:A$5). When copied to C3, what will the formula become?
=SUM(A2:A$5)
=SUM($A3:B$5)
=SUM($A3:C$5)
=SUM($A2:B$5)
In $A2, column A is locked and row is relative so row increments from 2 to 3. In A$5, row 5 is locked and column shifts from A to B. The result is =SUM($A3:B$5).
Given A1=150, B1=60, C1="No", what does =IF(AND(A1>100,OR(B1<50,C1="Yes")),"Pass","Fail") return?
Fail
TRUE
#VALUE!
Pass
A1>100 is TRUE, but OR(B1<50,C1="Yes") is FALSE (neither condition true). AND(TRUE,FALSE) is FALSE, so the IF returns the value_if_false argument, "Fail".
Identify the correction needed for =INDEX(A1:A5, MATCH("X" B1:B5,0)).
Change MATCH to VLOOKUP
Remove the 0 from MATCH
Add a comma between "X" and B1:B5
Add parentheses around the range
MATCH requires two arguments separated by a comma: lookup_value and lookup_array. The formula is missing the comma between "X" and B1:B5. Adding that comma fixes the syntax.
Which formula correctly sums C2:C10 where A2:A10>10 and B2:B10<20?
=SUMIF(A2:A10,">10",C2:C10)+SUMIF(B2:B10,"<20",C2:C10)
=SUMIFS(C2:C10,A2:A10,">10",B2:B10,"<20")
=SUM(C2:C10*(A2:A10>10)*(B2:B10<20))
=SUMIF(C2:C10,A2:A10>10,B2:B10<20)
SUMIFS allows multiple criteria over the same sum range. Here it sums C2:C10 only when A2:A10>10 and B2:B10<20. The other formulas are either incorrect syntax or different logic.
Given A1=2, A2=-3, A3=5, what does the array formula =SUM(IF(A1:A3>0,A1:A3,0)) return?
2
10
0
7
The IF part tests each element for >0, returning the positive values 2 and 5, and zero for negative. SUM then adds 2 + 0 + 5, giving 7. This must be entered as an array formula in older Excel versions.
0
{"name":"In Excel, which operator does Excel evaluate first in the formula =2+3*4?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"In Excel, which operator does Excel evaluate first in the formula =2+3*4?, Which of the following is a relative cell reference in Excel?, What does the reference $B$2 signify in an Excel formula?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Learning Outcomes

  1. Analyse cell reference precedence in multi-tier formulas
  2. Evaluate nested IF statements and lookup functions accurately
  3. Master calculation order of operations for complex expressions
  4. Identify and resolve common formula syntax errors
  5. Apply absolute and relative references effectively
  6. Demonstrate use of conditional and lookup functions

Cheat Sheet

  1. Master the Order of Operations - In Excel formulas, functions like SUM, AVERAGE, or VLOOKUP run before any arithmetic operations, so always remember that =SUM(A1:A5)*2 calculates the total first, then multiplies by 2. This keeps your calculations predictable and error-free. Practice Reading Formulas
  2. Use the Evaluate Formula Tool - Break down complex formulas one step at a time with Excel's built-in Evaluate Formula feature. Watching each part execute helps you pinpoint exactly where things go right (or wrong), making debugging a breeze. Evaluate a Nested Formula One Step at a Time
  3. Control Cell References - Absolute ($A$1), relative (A1), and mixed ($A1 or A$1) references dictate how your cell pointers shift when you copy formulas around. Mastering these ensures your references always land on the correct cells, saving you headaches as your spreadsheets grow. Overview of Formulas in Excel
  4. Create Nested IF Statements - Stack multiple IF conditions to handle complex logic, such as grading scales or tiered commission rates. Plan the sequence carefully so each test runs in the right order - think of it as a choose-your-own-adventure for your data. Evaluate Nested Formulas Step by Step
  5. Spot and Fix Common Formula Errors - Errors like #NAME? (unrecognized text) and #REF! (invalid cell reference) are Excel's way of saying "Oops!" Learning what triggers each error and how to resolve it keeps your sheets clean and your sanity intact. Excel Worksheet and Expression Evaluation
  6. Trace Precedents & Dependents - Visualize which cells feed into a formula (precedents) and which rely on its result (dependents) using Excel's auditing tools. This map of connections is like a treasure map, guiding you straight to the source of any discrepancies or errors. Formula Auditing in Excel
  7. Unlock Lookup Powers - VLOOKUP and HLOOKUP let you pull data from tables without the tedious manual search - perfect for matching names, prices, or any other info across your workbook. Just remember to lock your table ranges if you don't want them shifting on you! Overview of Formulas in Excel
  8. Watch Your Cells - Keep an eye on critical cells and formulas in real time with the Watch Window - no more jumping around sheets to monitor key values. It's like having your personal control panel for spreadsheet sanity. Evaluating Formulas in Excel
  9. Tackle Circular References - When a formula refers back to its own cell, Excel raises the dreaded "circular reference" flag, stopping your calculations in their tracks. Learning to identify and correct these loops ensures your formulas run smoothly and your data stays reliable. Overview of Formulas in Excel
  10. Handle Errors with IFERROR - Wrap your formulas in IFERROR to capture any missteps and replace them with user-friendly messages or alternative values. This turns potential #DIV/0! or #N/A disasters into graceful fallbacks your worksheet can handle elegantly. Overview of Formulas in Excel
Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Technology Quizzes

Powered by: Quiz Maker