Problem: You handle pricing for a company. You have a spreadsheet showing your cost per SKU, as shown in Fig. 461. Your manager tells you to take the current manufacturing cost for each item, multiply by 2, add $3, and then round up to the next highest multiple of 5. A standard rounding function won't work here, so what do you do?
Note: Click any figure below for a larger view of the image.
Strategy: The first portion of this calculation is fairly easy. The formula in C2 shows the manufacturing cost multiplied by 2 with an additional $3, as shown in Fig. 462.
How do you round up to the nearest $5? You can use the CEILING function. This function takes one number and the number to round up to. =CEILING(421,5) will result in a 425, as shown in Fig. 463. The answer is always higher than the original number.
Additional Information: There is also a FLOOR function that works similarly to the CEILING function. With the FLOOR function, the number would be rounded down to the nearest multiple of 5.
Gotcha: Both CEILING and FLOOR require the Analysis toolpack to be installed. For details on installing this set of tools, please see Excel Data Analysis Tools.
Summary: The Ceiling function will round a number up to the nearest increment.
Functions Discussed: =CEILING(); =FLOOR()