Combining ROUND and SUM functions in Excel

Combining the operations of two or more functions such as ROUND and SUM into a single formula in Excel is called a nesting function. Nesting is achieved by having one function as an argument to a second function. Follow this guide and learn how to properly nest functions and combine operations in Microsoft Excel.

Information in this article applies to Excel 2019, 2016, 2013, 2010, 2007; Excel for Mac, Excel for Office 365, Excel Online, Excel for Android, Excel for iPhone, and Excel for iPad.

Content
  1. General rules for combining functions in Excel
  2. Combine ROUND AND SUM functions
  3. Using Excel Array/CSE Formula
  4. Use Excel’s ROUNDUP and ROUNDDOWN Functions

General rules for combining functions in Excel

When evaluating nested functions, Excel always executes the deepest or innermost function first and then goes out.

Depending on the order of the two functions in combination, the following conditions apply:

  • Rows or columns of data are summed and then rounded to the specified number of decimal places in one worksheet cell (see line 6 above).

  • The values ​​are rounded up and then added together (see line 7 above).

  • Values ​​are rounded and then summed, all in one cell using nested array formula SUM/ROUND (see rule 8 above).

As of Excel 2007, the number of levels of functions that can be nested within each other is 64. Before this version, only seven levels of nesting were allowed.

Combine ROUND AND SUM functions

Although you can manually enter the entire formula, you may find it easier to use the Function Arguments dialog box to enter the formula and arguments.

 = ROUND (СУММ (А2: А4), 2) 

The dialog box makes it easy to enter function arguments one at a time without worrying about function syntax, such as parentheses around arguments and commas that act as separators between arguments.

Although the SUM function has its own dialog box, it cannot be used when the function is nested inside another function. Excel does not allow you to open a second dialog box when you enter a formula.

To follow this tutorial for using the ROUND and SUM functions together, start by entering data in rows 1, 2, 3, 4, and 5. Then do the following:

  1. Select cell B6 to make it active.

  2. Select tab formulas on the tape.

  3. Select Mathematics and Triggers to open the drop-down list of functions.

  4. Select ROUND in the list to open the Function Arguments dialog box. On a Mac, the Formula Builder opens.

  5. Place cursor in text field Number

  6. Enter SUM(A2:A4) to enter the SUM function as the Number argument to the ROUND function.

  7. Place cursor in text field Number_digits

  8. Enter 2 to round the answer to the SUM function to 2 decimal places.

  9. Select Okay to complete the formula and return to the sheet. Except for Excel for Mac where you choose Finished

  10. The answer 764.87 appears in cell B6 because the sum of the data in cells D1-D3 (764.8653) is rounded to 2 decimal places.

  11. Select cell C3 to display the nested function in the formula bar above the worksheet.

Using Excel Array/CSE Formula

An array formula, such as the one in cell B8, allows you to perform multiple calculations in a single worksheet cell. An array formula is easily identified by braces or braces that surround the formula.

However, these brackets are not entered, but are entered by pressing the keys Shift ctrl Enter on the keyboard. Because of the keys used to create them, array formulas are sometimes referred to as CSE formulas.

Array formulas are usually entered without the help of a function dialog box. Use this formula to enter the array formula SUM/ROUND in cell B8:

 {= ROUND (СУММ (А2: А4), 2)} 
  1. Select cell B8 to make it active.

  2. Enter the formula:

     {= ROUND (СУММ (А2: А4), 2)} 
  3. Click and hold the keys Shift ctrl

  4. Press key Input

  5. Release the keys Shift check

  6. The value 764.87 appears in cell B8.

  7. Select cell B8 to display a matrix 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. These are the ROUNDUP and ROUNDDOWN functions. These functions are used when you want values ​​to be rounded in a certain direction, rather than relying on Excel’s rounding rules.

Since the arguments for both functions are the same as those of the ROUND function, both can be easily replaced with the nested formula shown on line 6.

ROUNDUP/SUM formula form:

 = НЕДЕЛЯ (СУММЫ (А2: А4), 2) 

ROUNDDOWN/SUM formula form:

 = ОКРУГЛВНИЗ (СУММ (А2: А4), 2) 

Leave a Reply

Your email address will not be published.