• Excel Book Excerpt

Excel Round Prices to the Next Highest $5

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Round Prices to the Next Highest $5

Problem: I handle pricing for a company, and I have a spreadsheet that shows my cost per SKU. My manager tells me 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.

LE10000332.jpg 

Figure 352 38.9615 doesn’t make a nice price.

Strategy: After doing the math to get a preliminary price, you can use the CEILING function. This function takes one number and the number to round up to. For example, =CEILING(421,5) will result in 425. Note that with CEILING, the answer is always higher than the original number.

LE10000333.jpg 

Figure 353 Use CEILING to round up to a multiple.

Additional Details: Excel also has a FLOOR function. With the FLOOR function, the number would be rounded down to the nearest multiple of 5.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy