FLOOR to the quarter hour
The multiple can be a fraction: bill time in completed quarter hours.
Task:
Consultants record time in decimal hours, and the contract only bills completed quarter hours. In C2:C6 give the billable hours: each figure rounded down to a multiple of 0.25.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of CEILING, FLOOR
0/4The same formula in the other shapes it takes at work.
- CEILING and FLOOR Functions
Round numbers up or down to specific multiples.
CoreIntermediate - FLOOR to the quarter hour
The multiple can be a fraction: bill time in completed quarter hours.
Intermediate - CEILING for a .99 price ending
Round up to the next whole unit, then take a penny off.
Intermediate - CEILING to buy whole packs
You need 130 screws; they come in boxes of 50. What does that cost?
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Task | Hours logged | Billable hours |
| 2 | Workshop | 2.38 | |
| 3 | Report | 4.9 | |
| 4 | Call | 0.2 | |
| 5 | Review | 1.75 | |
| 6 | Travel | 3.6 |
What this exercise teachesMay contain the answer
The significance does not have to be a whole number: 0.25, 0.5 or 0.05 all work, which is how prices are rounded to the nearest five pence or times to the nearest quarter. FLOOR suits this contract because only completed quarters count; CEILING would bill every started one.