Practice
One formula, one workplace sheet. The answer cell is marked. Everything else is data.
B1 References and arithmetic
- References · Cell referenceLevel 1
Point at a cell
In Result on the first line, subtract Budget (column H) from Actual (column I). Point at those two cells. A typed number can match this line and still fail when the amounts change.
- References · ParenthesesLevel 1
Order of operations
In Result, take List Price (column D), remove 10%, then add 8% tax, and round to cents. Parentheses decide the order. Do this on every row.
- References · RangeLevel 1
Cell, range, or whole column
In Result, total every On Hand value. On Hand is column E. A range that stops at row 10 matches this week and misses next week’s row. Use the whole column.
- References · @rowLevel 1
Smartsheet reference syntax
In Result, multiply this row’s Hours by this row’s Rate. In Smartsheet mode use [Hours]@row and [Rate]@row. Excel mode is the A1 comparison, H1*I1, filled down.
- References · AVERAGE / AVGLevel 1
Same idea, other product
In Result, average the Amount column (column D). Excel and Google Sheets call it AVERAGE. Smartsheet calls it AVG. The other four translations in the explanation are reading, not part of this check.
B2 Totals and counts
- Aggregating · SUMLevel 1
Total the hours
In Result (column K), total every Hours value. Hours is column H. In Smartsheet mode, use the column’s name. Don’t stop at the last row you can see — the next import will add tasks.
- Aggregating · AVERAGE / AVGLevel 1
Average deal size
Round the average Amount to the nearest dollar. Amount is column D. Smartsheet calls the average function AVG.
- Aggregating · MIN / MAXLevel 1
Smallest and largest order
In Result, show the largest PO Amount and the smallest, separated by space-slash-space. PO Amount is column D. Use MAX and MIN. Both products use those names.
- Aggregating · COUNT / COUNTALevel 1
Count numbers or filled cells
In Result, count rows that have a name. Name is column A. In Excel and Google Sheets use COUNTA. In Smartsheet use COUNT, which counts non-blank cells. COUNTA is not a Smartsheet function in this practice set.
- Criteria · COUNTIFLevel 1
Count the wins
Count deals whose Stage is Won. Stage is column C. Put the word Won in quotes.
- Numbers · ROUNDLevel 1
Nearest dollar of budget per hour
Budget divided by Hours, rounded to the nearest dollar, on every row.
- Debugging · Off-by-one rangeBug
The total is short
The formula in the bar is wrong. Fix it so every Hours value is included, including rows that get added later.
B3 First decisions with IF
- Decisions · IFLevel 1
Closed or still open
On every row, return Closed when Status is Done, otherwise Open. Write the formula for the first row; it will fill down.
- Decisions · IF comparisonLevel 1
Over budget flag
In Result, write Over when Actual is greater than Budget, and OK otherwise. Actual is column I. Budget is column H. Equal amounts are OK.
- Decisions · IF numberLevel 1
Reorder point
In Result, return 50 when On Hand is less than or equal to Reorder Point, and 0 otherwise. On Hand is column E. Reorder Point is column F.
B5 Common errors
- Debugging · Wrong columnBug
High isn’t a status
Fix the formula so it counts tasks whose Priority is High.
- Debugging · #DIV/0!Bug
Dividing by zero
The formula divides Cost by Units and breaks on the zero lines. Return a blank when Units is 0, and Cost divided by Units otherwise. Cost is column D. Units is column E. Do not wrap the error with IFERROR; test the divisor.
- Debugging · #VALUE!Bug
Text where a number belongs
The formula adds Amount and Adjustment. One Adjustment is the word n/a, so the addition cannot run. When Adjustment is n/a, return Amount alone. Otherwise add them. Amount is column D. Adjustment is column E.
- Debugging · #NAME?Bug
Typo in a function or missing quotes
The formula treats Active as a name instead of a word. Return Yes when Status is Active, and No otherwise. Status is column D. Put Active in quotes.
B6 Smartsheet foundations
I1 Conditional totals
- Criteria · COUNTIFSLevel 2
East proposals
Count deals in the East (Region, column G) that are still at Proposal (Stage, column C). Both have to be true on the same row.
- Criteria · SUMIFLevel 2
East pipeline dollars
Sum Amount (column D) where Region (column G) is East. The column you test is not the column you add.
- Criteria · SUMIFSLevel 2
Won dollars in the East
Sum Amount for deals that are Won and in the East.
I2 Logic
- Decisions · IFS and nested IFLevel 2
Priority into SLA days
Turn Priority into those day counts on every row. Excel and Google Sheets can use IFS. Smartsheet needs nested IF.
- Decisions · AND / ORLevel 2
Who needs a look
Return Attention when Status is Blocked, or when Priority is High and Status is not Done. Otherwise return OK.
- Decisions · IFERRORLevel 2
Budget per hour, safely
Budget divided by Hours, rounded to 2 decimals. If the division fails, return n/a.
I3 Lookups
- Lookups · VLOOKUPLevel 2
Owner email from the directory
For each owner, return the email from Directory. Exact match only.
- Lookups · INDEX / MATCHLevel 3
Mentor name, looking left
Return the mentor’s employee name. If MentorID is blank, return an em dash —.
- Lookups · XLOOKUPLevel 3
Where the manager sits
Return the manager’s Location. If Manager is blank, or the name isn’t on the roster, return —.
I4 Text
I5 Dates
- Dates · NETWORKDAYSLevel 2
Working days on each task
For each task, how many working days are there from Start through End, counting both ends and skipping weekends?
- Dates · TODAY and date mathLevel 2
Calendar days until close
Calendar days from today until Close. Negative means the close date has already passed. Count weekends.
I6 Absolute and relative
- Debugging · Locked referenceBug
Every row shows the first share
Each row should be its own Hours divided by the total Hours. Fix the formula so it no longer sticks to row 1.
- Numbers · @row column formulasLevel 2
Share of the hours
Percent of total Hours for this row, rounded to 1 decimal. 40 hours out of 324 is 12.3, not 12.35.
I7 Smartsheet working formulas
I8 Smartsheet hierarchy
- Hierarchy · CHILDRENLevel 3
Hours of the direct children
On every row, total Hours of the direct children only — not grandchildren. A row with no children totals 0. Smartsheet has CHILDREN. Excel and Google Sheets can SUMIF the Parent column.
- Hierarchy · PARENTLevel 2
Status of the parent
Show the parent row’s Status. The root has no parent, so leave it blank.
- Hierarchy · ANCESTORSLevel 3
Path from the top
Join ancestor task names from the top down with “ > ”. A root row is blank.
I9 Smartsheet lists and multi-select
- Multi-select · HASLevel 2
Who lists SQL
Return Yes when Skills contains the choice SQL, otherwise No.
- Multi-select · COUNTMLevel 2
How many skills are selected
Count how many skills are selected on this row.
- Lists · TEXTJOIN and JOINLevel 3
List the finished tasks
Build one comma-separated list of Task names whose Status is Done.
- Lists · UNIQUE / DISTINCTLevel 2
Regions, once each
Return a comma-separated list of distinct Region values.