## Microsoft Excel: Round Prices to Next Highest \$5

written by: Mr Excel•edited by: Michele McDonough•updated: 7/6/2011

Need to round numerical values in a non-standard manner in Excel? Read on to learn how.

• slide 1 of 3

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()

• slide 2 of 3

• slide 3 of 3