We all run it into it at some point.
You do a sum, such as in your financial statements. Your sum formula is correct. However, to your user, it looks like the sum is off by 1 or more, because the numbers displayed are rounded.
The exercise is in the Rounded Sum Exercise file. The solution is in the Rounded Sum Result file.
Here we add the total sales, which are displayed in even dollars. The underlying numbers are in cents.
Since we just display the dollars, it appears that the total sales should only be $498,199. However, there is another $1 in sales that comes from the sum of the underlying cents.
Let's assume that displaying cents is not an option.
Let's also assume, as is the case here, that there is not an underlying formula that calculates the individual sales number. If that were the case, the best solution would be to round the underlying calculation (see our prior lesson on using the Round function).
Life is too short to adjust the sum to add 1 or subtract 1 so it looks right. What happens later when you have an update? Putting a manual adjustment in your formulas is asking for trouble.
Fortunately, there is a solution in Excel. We don't think many people know about this. We wished we learned this one long ago.
This is a combination of using both the Sum and the Round functions together. The next effect is that you sum rounded numbers. Here is how it looks after the fix:
The adjusted formula is {=SUM(ROUND(B2:B6,0))}
Let's look at the parts, going inside out, a good technique for analyzing formulas:
ROUND(B2:B6,0)- We will round each of the numbers to a whole number when we do the sum
=SUM(ROUND(B2:B6,0))- We put a Sum around it to add the rounded numbers
{=SUM(ROUND(B2:B6,0))}- The finishing touch- important- we save the formula by clicking the Ctrl, Shift and Enter keys at the same time, since this is an array. This puts the brackets around the formula. If we just finished the formula by clicking Enter, we would get an error.
(You saw arrays before when we demonstrated the quick way to do Weighted Averages. An array is a block of cells, in the above case, B2 to B6. By using an array, you can do calculations on multiple cells at once, rather than having to the calculations one at a time and modify individual cells. Arrays are great time savers, as we saw when we were able to do a weighted average calculation in one formula rather than multiple formulas).
You might say, well the total sales were really $498,200. However, when that precision does not matter (i.e nobody cares if it is off by 1), but it does matter that you give the best impression of numbers that visual foot correctly, using an array and rounding each number in a sum as we did above is a great way to save a petty headache.