Decide and Choose
A teacher wants Excel to check whether a student's grade is at least 70 and automatically display "Pass" or "Not Yet Passing". Which function should be used?
What is:
IF function (=IF(A2>=70,"Pass","Not Yet Passing")).
A teacher wants to add all the money spent on school supplies by one specific student. Which function should be used?
What is: SUMIF
A store wants to add the subtotals for "Accessories" orders that specifically used "Standard" shipping. Which function should be used?
What is: SUMIFS
What comparison operator is used in Excel formulas to represent "not equal to"?
What is: <>
Why is it vital for growing businesses to rely on Excel formulas rather than manually calculating order totals?
What is: Formulas save time, eliminate human calculation errors, and automatically recalculate when data changes.
A company has four different shipping modes (Standard, Second, Express, Overnight) and wants to return the correct price based on the selected mode without using complex nested statements. Which function is best suited for evaluating this list of conditions cleanly?
What is: IFS function (or SWITCH).
A company wants to find the average subtotal for all orders placed using "Standard" shipping. Which function should be used?
What is: AVERAGEIF
A store wants to calculate the average subtotal for "Phones" ordered using "Second" shipping. Which function should be used?
What is: AVERAGEIFS
What symbol is added to a cell reference (e.g., $A$2) to create an absolute reference that locks the cell when copied?
Answer: Dollar sign ($)
When using COUNTIFS to count sales, what rule must you follow regarding the size of your criteria ranges (like B2:B100 and C2:C100)?
They must be the exact same size and shape.
Why: Excel pairs up the cells line-by-line. If one column range is longer than the other, Excel will get confused and return an error!
A business needs to decode part numbers where the first two characters determine the category (e.g., "EL" for Electronics, "TY" for Toys). Which function compares a single expression against exact data matching pairs?
What is SWITCH FUNCTION
An inventory manager wants to count how many orders were processed as "Web Order". Which function should be used?
What is: COUNTIF
Which function would you use to find the single largest sale made in the "West" territory during the first quarter ("Q1")?
What is: MAXIFS
If you drag a formula containing the reference A$2 horizontally across columns and vertically down rows, which element stays fixed?
What is: The row (Row 2) stays fixed, while the column letter changes.
How could a game developer structure a single nested formula to assign player ranks ("Gold", "Silver", "Bronze") based on point thresholds?
What is: By nesting IF functions sequentially
(=IF(Points>=1000, "Gold", IF(Points>=500, "Silver", "Bronze"))).
What occurs when a function is placed inside another function as an argument, and how many levels of this does Excel support?
What is: Function nesting; Excel supports up to 64 levels of nested functions.
In the formula =SUMIF(A2:A100, "Web Order", B2:B100), what role does B2:B100 serve compared to A2:A100?
What is : B2:B100 is the actual summation range (the values added), while A2:A100 is the range evaluated against the criteria ("Web Order").
How does the syntax structure of SUMIFS differ from SUMIF regarding the position of the sum range?
What is: In SUMIFS, the sum range is the first argument, whereas in SUMIF, the sum range is the last (optional) argument.
Why are absolute references essential when referencing a master shipping price table in a sheet with hundreds of customer orders?
What is: They prevent lookup coordinates from shifting relatively as the formula is copied down to other orders.
If a company expands its logistical operations to include a fifth shipping option ("Same-Day"), what updates must be made to an existing IFS or nested IF shipping formula?
What is: A new logical pair/condition testing for "Same-Day" and its corresponding rate must be inserted into the formula syntax.
How many levels of nested functions (putting formulas inside other formulas) does Excel allow you to stack inside a single cell?
What is: Up to 64 levels.
What happens if an employee accidentally uses SUMIF instead of SUMIFS when an order calculation requires matching both customer name AND shipping region?
What is: Answer: Excel will evaluate only one condition, leading to inaccurate totals that include unwanted data (Error Message).
Why can't you just add extra conditions to the end of a SUMIF formula to turn it into a SUMIFS formula?
What is: Because the order switches!
In SUMIF, the column of numbers you are adding goes at the end.
In SUMIFS, the column of numbers you are adding goes at the beginning.
What logical function can be nested inside an IF statement to ensure an action occurs only when all specified logical tests are true?
What is: AND Function
(e.g., =IF(AND(B2>=65, C2>=65), "Pass", "Fail")).
What is the main difference between using IFS and SWITCH when looking for a matching item in a list?
IFS lets you test full math rules (like >70 or <10).
SWITCH only looks for exact matching words or codes (like matching "EL" to "Electronics").