Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range
When you want to sum or average a range of cells that need to each be rounded beforehand, here’s an efficient alternative to rounding each cell individally. In the pictured example, daily rainfall amounts with one decimal are to be summed, based on original amounts with two decimals.
The picture shows two methods that arrive at the same result. One method uses a helper column (column C) to round each of the 31 numbers, and then a SUM function in cell C34 sums the helper column’s ROUND function formulas.
The other method uses a single array formula in cell E2 to do the entire cell-by-cell rounding process, and then sums those 31 rounded elements with no helper cells needed.
The array formula in cell E2 is =SUM(ROUND(B3:B33,1)).
Recall, an array formula is applied to the cell by pressing Ctrl+Shift+Enter, not just Enter.
If you are unfamiliar with array formulas, see my video and explanation of arrays here.
Leave a Reply