Microsoft Excel: Round Prices To Next Highest $5

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.

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. 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.

Summary: The Ceiling function will round a number up to the nearest increment.

Functions Discussed: =CEILING(); =FLOOR()

See all Microsoft Excel tips

Images