Basic FunctionsBeginner

Round billable hours up to the next quarter hour with CEILING, then calculate the amount to bill

CEILING rounds a number up to the next multiple you choose, rather than to the nearest one, which is the rule most quarter-hour billing policies actually run on.

Task:

You invoice clients for an IT support contractor who logs time through a tracking app as a raw decimal — 7.38 hours, 6.12 hours, and so on. The services agreement bills in quarter-hour increments, rounded up in the firm's favor rather than to the nearest quarter hour. In column D, use CEILING to round each contractor's raw hours in column C up to the next 0.25. In column E, multiply the rounded hours by the hourly rate in column B to get the amount to bill. Fill in rows 2 through 6.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

6 rows × 5 columns10 cells you fill in
ABCDE
1ContractorHourly RateRaw HoursBilled HoursBilled Amount
2Nina Osei207.38
3Marco Felici186.12
4Grace Kim268.63
5Tomas Novak24.55
6Aaliyah Brooks227.87
What this exercise teachesMay contain the answer

CEILING(C2,0.25) divides 7.38 by 0.25, gets 29.52, rounds that up to the next whole number, 30, and multiplies back out to 7.50 — the next quarter hour above, never the nearest one, which is why 6.12 jumps all the way up to 6.25 rather than down to 6.00 the way a nearest-rounding function would. Tomas Novak's row proves CEILING isn't padding every number up for its own sake: 5.00 divided by 0.25 is already a whole 20, so CEILING leaves it exactly where it is instead of bumping it to 5.25. Multiplying by the hourly rate only after rounding, rather than rounding the dollar total afterward, is what keeps that bump landing on actual billed time rather than on a dollar figure a client could never trace back to a quarter-hour increment.