Combine the ROUND and SUM Functions
Combining the operations of two or more functions, such as ROUND and SUM, in a single formula within Excel is referred to as a nesting function. Nesting is accomplished by having one function act as an argument for the second function. Follow this tutorial and learn how to properly nest functions and combine operations in Microsoft Excel. Begin by entering the data in rows 1, 2, 3, 4, and 5 shown in the image above. Then, follow these steps: Although it is possible to enter the complete formula manually, you may find it easier to use the Function Arguments dialog box to enter the formula and arguments. The dialog box simplifies entering the function’s arguments one at a time without having to worry about the function’s syntax such as the parenthesis surrounding the arguments and the commas that act as separators between the arguments. Even though the SUM function has its own dialog box, it cannot be used when the function is nested inside another function. Excel doesn’t allow a second dialog box to be opened when entering a formula.
Use an Excel Array / CSE Formula
An array formula, such as the one in cell B8, allows for multiple calculations to take place in a single worksheet cell. An array formula is readily recognized by the braces or curly brackets { } that surround the formula. These braces are not typed in, however, but are entered by pressing the Shift+Ctrl+Enter keys on the keyboard. Because of the keys used to create them, array formulas are sometimes referred to as CSE formulas. Array formulas are normally entered without the aid of a function’s dialog box. To enter the SUM/ROUND array formula in cell B8, use this formula:
Use Excel’s ROUNDUP and ROUNDDOWN Functions
Excel has two other rounding functions that are very similar to the ROUND function. They are the ROUNDUP and ROUNDDOWN functions. These functions are used when you want values to be rounded in a specific direction, rather than relying on Excel’s rounding rules. Since the arguments for both of these functions are the same as those of the ROUND function, either can easily be substituted into the nested formula shown in row 6. The form of the ROUNDUP/SUM formula is: The form of the ROUNDDOWN/SUM formula is:
General Rules for Combining Functions in Excel
When evaluating nested functions, Excel always executes the deepest or innermost function first and then works its way outward. Depending on the order of the two functions when combined, the following applies:
Rows or columns of data are summed and then rounded to a set number of decimal places all within a single worksheet cell (see row 6 above). Values are rounded and then summed (see row 7 above). Values are rounded and then summed, all in a single cell using a SUM/ROUND nested array formula (see row 8 above).
