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:
Select cell B6 to make it the active cell. Select the Formulas tab of the ribbon. Select Math & Trig to open the function drop-down list. Select ROUND in the list to open the Function Arguments dialog box. On a Mac, the Formula Builder opens. Place the cursor in the Number text box. Type SUM (A2:A4) to enter the SUM function as the Number argument of the ROUND function. Place the cursor in the Num_digits text box. Type a 2 to round the answer to the SUM function to 2 decimal places. Select OK to complete the formula and return to the worksheet. Except in Excel for Mac, where you select Done instead. The answer 764. 87 appears in cell B6 since the sum of the data in cells D1 to D3 (764. 8653) is rounded to 2 decimal places. Select cell B6 to display the nested function in the formula bar above the worksheet.
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:
Select cell B8 to make it the active cell. Type the formula: {=ROUND(SUM(A2:A4),2)} Press and hold the Shift+Ctrl keys. Press the Enter key. Release the Shift+Control keys. The value 764. 87 appears in cell B8. Select cell B8 to display the array formula in the formula bar.
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).
