Head ‘Em Up, Move ‘Em Out! What do you think of when you hear the term Roundup? If you’ve seen any Western movies or TV programs, live in the Western USA like I do, or spend time working in your yard, then you might be thinking of cattle herding or a weed killer and not of an Excel function.
However, ROUNDUP is also one of several useful Excel ROUND functions that can help you build more accurate formulas and calculations.
The ROUNDUP function and its buddy ROUNDDOWN are specialized variations of the ROUND function. Each of these functions are key tools in your Excel toolbox as they all help to insure that the values used in your worksheet formulas are the same values displayed to your readers and reviewers. For instance, if the numbers which are printed and shown on a worksheet are formatted with 2 decimal places, than all of the formulas in your worksheet should only perform calculations up to 2 decimal places. When you have data which includes percentages, shares or portions, or other data which may have many more decimal places than you want to show in a worksheet, you need to use the ROUND function or one of its lesser known siblings.
Briefly, the structure of the ROUND function is:
The assumption with ROUND is that anything 5 or higher will round up to the next number. Specifically, if the result of a calculation is 1.316 and it was rounded to 2 decimal places, it would change to 1.32. With the ROUND function, a value of 1,234.567 rounded to 0 decimal places would be calculated and displayed as 1,235. Learn more about the Microsoft Excel ROUND function.
As the names imply, the ROUNDUP function always rounds up and the ROUNDDOWN function always rounds down regardless of the value.
When would you use the ROUNDUP function? This is a helpful choice when calculating pricing or other values where consistency is key.
Create the ROUNDUP function with the formula structure or syntax of:
When would you use the ROUNDDOWN function? Imagine that you are a small business owner that is using Microsoft Excel to track the number of vacation hours earned by employees based on the number of hours that they have worked. With this function, you insure that an employee does not receive more vacation than they have earned. Now, if you are the employee, you might want to keep this idea to yourself <grin>.
Create the ROUNDDOWN function with:
Add all of these ROUND functions to your Excel worksheets for clearer, more accurate results.
By Dawn Bjork, The Software Pro®
Microsoft Certified Trainer, Technology Speaker, Software Consultant