Rounded values in Excel can pose a few issues. There is a formula you can use that can round a range of values and then SUM the results. This can be used as a check total for rounded values.

I recently wrote an article for the CPA Australia INTHEBLACK magazine that discussed various issues associated with validations, including rounding issues. You can check out the article here, it includes a companion video.

Have a look at the image below where we have a list of values in column B and we have rounded them to zero decimal places in column C.

When we add up the rounded values the difference between the actual total and the rounded total is more than one. This is because all of the values were rounded up.

If you need to create a check total for the rounded total you can use a single formula to figure out what the total should be if all of the values were rounded to zero decimals places.

The image below shows the calculated amount and the formula involved.

The SUMPRODUCT function is Excel’s most flexible function. It has the ability to perform calculations on ranges that can normally only be performed on cells. The formula below is an example of that.

=SUMPRODUCT(ROUND(B2:B6,0))

Normally you can only round a single cell with the ROUND function but when you use it in combination with the SUMPRODUCT function you can perform rounding calculations with a range.

You can see how this operates in the image below.

The F9 function key when used when editing a formula will display the results of the selected part of the formula. This is useful when debugging formulas and when explaining formulas.

F9 Warning: If you do use the F9 key to calculate parts of the formula then make sure that you press the Esc key otherwise the values are hard keyed into the formula. If you forget you can use the Undo option to reinstate the formula.