Lab 10
Logical Functions Practice
Seven tasks across five worksheets — each using a different logical or conditional technique. Three tasks introduce functions not covered in Topic 10: AVERAGEIF, COUNTIFS, and SWITCH.
Gross Margin worksheet · H7:H14
Gross margin formula — wrapped in IF to prevent #DIV/0!
Calculate gross margin as
(Retail − Cost) / Cost for each part. But two parts have a cost of zero — dividing by zero gives a #DIV/0! error. Wrap the calculation in an IF that checks whether Cost is not zero before dividing, and returns "Cost is 0!" otherwise.
= IF(E7 <> 0, (G7 - E7) / E7, "Cost is 0!")
IFERROR alternative
You could also write
=IFERROR((G7-E7)/E7, "Cost is 0!") — it catches the error after it happens rather than preventing it. Both approaches produce the same result here. The IF version is more intentional — it checks the specific cause rather than catching any error.Expected results — Gross Margin column
| Description | Cost | Gross Margin |
|---|---|---|
| Gangley Pliers | $10.47 | 71.4% |
| HCAB Washer | 0 | Cost is 0! |
| Finley Sprocket | $1.57 | 87.9% |
| Thompson Socket | 0 | Cost is 0! |
| S-Joint | $6.85 | 45.3% |
3 more rows — verify against the results file
Inventory worksheet · Reorder Now? column
Nested logical — reorder when Qty Available ≤ Reorder Level AND no order pending
A product needs reordering when both conditions are true at the same time: Qty Available is at or below the Reorder Level, AND Qty On Order is 0 (no reorder already placed). Return
"Yes" if a reorder is required, or nothing ("") otherwise.
= IF(AND(E2 <= G2, F2 = 0), "Yes", "")
Check your result
Count how many rows show "Yes"
| Question | Your answer |
|---|---|
| Total rows showing "Yes" in the Reorder Now? column |
Verify specific rows
Northwind Traders Olive Oil: Qty Available = 15, Reorder Level = 10, Qty On Order = 0 → 15 > 10 → condition fails → blank.
Northwind Traders Gnocchi: Qty Available = 10, Reorder Level = 30, Qty On Order = 0 → 10 ≤ 30 AND 0 = 0 → Yes.
Northwind Traders Gnocchi: Qty Available = 10, Reorder Level = 30, Qty On Order = 0 → 10 ≤ 30 AND 0 = 0 → Yes.
Inventory worksheet · Cell K1
SUMIF — total value of products with non-zero Qty Available
Sum the Value column (K) only for products where Qty Available is not zero. Use
"<>0" as the criteria — this is the not equal to zero operator written as a quoted string, which is how SUMIF handles comparison-based criteria.
= SUMIF(E2:E45, "<>0", K2:K45)
Operators inside quotes
When SUMIF, COUNTIF, or AVERAGEIF criteria use a comparison operator, wrap the whole expression in quotes:
"<>0", ">100", "<=50". The quotes tell Excel it's a condition string, not a cell reference.Cell K1 — your result
| Cell | Value |
|---|---|
| K1 |
Inventory worksheet · Cell K2
SUMIFS — Qty On Hand for "Soup" products with no stock on hold
Sum the Qty On Hand column for products whose name contains "Soup" and whose Qty On Hold is zero. The wildcard
* surrounding the word means "anything before or after" — it matches any product name that includes "Soup" anywhere.
= SUMIFS(D2:D45, A2:A45, "*Soup*", C2:C45, 0)
The * wildcard
*Soup* matches any text containing "Soup" — before, after, or both. Without the asterisks, "Soup" would only match cells where the entire text is exactly "Soup". Both products here are: Vegetable Soup (Qty On Hand = 25) and Chicken Soup (Qty On Hand = 30), both with Qty On Hold = 0.Cell K2 — your result
| Cell | Value |
|---|---|
| K2 |
Parts worksheet · Cell F16
AVERAGEIF — average gross margin for parts costing less than $10
Use structured table references — the table is named
Parts. Average the Gross Margin column only for rows where Cost is less than $10.Function introduced in this lab
AVERAGEIF — conditional average
Same structure as SUMIF — but averages the matching values instead of summing them. If the average_range is omitted, it averages the range itself.
= AVERAGEIF(range, criteria, [average_range])
= AVERAGEIF(Parts[Cost], "<10", Parts[Gross Margin])
Five parts qualify (Cost < $10)
HCAB Washer ($0.12), Finley Sprocket ($1.57), Thompson Socket ($3.11), LAMF Valve ($4.01), S-Joint ($6.85). The average of their five Gross Margin values gives the result.
Cell F16 — enter as decimal (e.g. 0.812)
| Cell | Value (decimal) |
|---|---|
| F16 |
Customers worksheet · Cell L1
COUNTIFS — customers from Oregon, USA
Count customers where Country = "United States" AND Region = "OR". Note carefully: "OR" here is the two-letter abbreviation for Oregon — it is not the OR logical function. Excel evaluates it as plain text.
Function introduced in this lab
COUNTIFS — count with multiple conditions
COUNTIF handles one condition. COUNTIFS handles as many as you need. Add range/criteria pairs for each additional condition — all must be true for a row to be counted.
= COUNTIFS(range1, criteria1, range2, criteria2, ...)
= COUNTIFS(J2:J92, "United States", H2:H92, "OR")
"OR" is Oregon — not the function
The criteria
"OR" is a text string — Oregon's state abbreviation. If you accidentally type OR without quotes as a formula argument, Excel may misinterpret it. Always quote text criteria in COUNTIFS.Cell L1 — your result
| Cell | Value |
|---|---|
| L1 |
Orders worksheet · Weekday column
SWITCH + WEEKDAY — convert date serial numbers to day names
The Date column contains Excel serial numbers. The Weekday column should show the full day name (Wednesday, Thursday, Friday, etc.) for each date. Use WEEKDAY to get the day number, then SWITCH to convert it to a name.
Function introduced in this lab
SWITCH — match a value to a list of cases
SWITCH evaluates an expression and returns the result for the first matching value. It's cleaner than nested IF when you have many specific cases. The last argument (without a matching value) is the default — returned if nothing matches.
= SWITCH(expression, val1, result1, val2, result2, ..., [default])
= SWITCH(WEEKDAY(B2),
1, "Sunday",
2, "Monday",
3, "Tuesday",
4, "Wednesday",
5, "Thursday",
6, "Friday",
7, "Saturday")
1, "Sunday",
2, "Monday",
3, "Tuesday",
4, "Wednesday",
5, "Thursday",
6, "Friday",
7, "Saturday")
Expected results — first few rows
| Date (serial) | Weekday |
|---|---|
| 43985 | Wednesday |
| 43986 | Thursday |
| 43987 | Friday |
| 43988 | Saturday |
| 43996 | Sunday |
Full column — verify against the results file