Need to round numerical values in a non-standard manner in Excel? Read on to learn how.
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()
Images
References and Additional Resources
If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel . This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, quick fomatting techniques and more. If you can’t find what you’re looking for, leave a comment below!
Other Resources:
Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/
Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com .
