# Combining the ROUND and SUM Functions in Excel

Combining the operations of two or more functions – such as ROUND and SUM – in a single formula in Excel is often referred to as nesting functions.

Nesting is accomplished by having one function act as an argument for the second function.

In the image above:

• The example in row six has the SUM function acting as the Number argument for the ROUND function.
• Row seven has multiple ROUND functions as Number arguments for the SUM function.

### Combining the ROUND and SUM Functions in Excel

Since Excel 2007, the number of levels of functions that can be nested inside each other is 64.

Prior to this version, only seven levels of nesting were permitted.

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,

• Rows or columns of data can be summed and then rounded to a set number of decimal places all within a single worksheet cell, row six above.
• Values can first be rounded and then summed, row seven above.
• Values can first be rounded and then summed, all in a single cell using a SUM/ROUND nested array formula, row eight above.

Even though the formulas in rows six to eight produce very similar results, the order of the nested functions may be important.

The results for the formulas in rows six and seven differ in value by only 0.01, which may or may not be significant depending on data requirements.

### ROUND/SUM Formula Example

The steps below cover how to enter the ROUND/SUM formula located in cell B6 in the image above.

=ROUND(SUM(A2:A4),2)

Although it is possible to enter the complete formula manually, many people find it easier to use a function's 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.

1. Click on cell B6 to make it the active cell.
2. Click on the Formulas tab of the ribbon.
3. Click on Math & Trig in the menu to open the function drop down list.
4. Click on ROUND in the list to open the ROUND function dialog box.
5. Click on the Number line in the dialog box.
6. Type SUM (A2 : A4) to enter the SUM function as the Number argument of the ROUND function.
7. Click on the Num_digits line in the dialog box.
8. Type a 2 in this line in order to round the answer to the SUM function to 2 decimal places.
9. Click OK to complete the formula and return to the worksheet.
10. The answer 764.87 should appear in cell B6 since we have rounded off the sum of the data in cells D1 to D3 (764.8653) to 2 decimal places.
11. Clicking on cell C3 will display the nested function
=ROUND(SUM(A2:A4),2) in the formula bar above the worksheet.

### SUM/ROUND Array or 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:

1. Click on cell B8 to make it the active cell.
2. Type in the formula =ROUND(SUM(A2:A4),2).
3. Press and hold down the Shift + Ctrl keys on the keyboard.
4. Press and release the Enter key on the keyboard.
5. The value 764.86 should appear in cell B8.
6. Clicking on cell B8 will display the array formula
{=ROUND(SUM(A2:A4),2)} in the formula bar.

### Using ROUNDUP or ROUNDDOWN Instead

Excel has two other rounding functions that are very similar to the ROUND function –​ ROUNDUP and ROUNDDOWN. 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 above nested formula in row six.

The form of the ROUNDUP/SUM formula would be:

=ROUNDUP(SUM(A2:A4),2)

The form of the ROUNDDOWN/SUM formula would be:

=ROUNDDOWN(SUM(A2:A4),2)

Format
mla apa chicago