Forum Discussion
Excel sum
How can I get Excel to provide exact sums instead of averaging the data? When I enter values of 1.0, 1.5, 10.0, 8.0, and 0.75 into a column, I get a sum of 21.3 instead of 21.25. I need exact values to determine hours worked.
13 Replies
Please excuse a bit of nit-picking. You're looking for exactness in the numbers. I think it's equally important to be exact with words (even while recognizing that words are often fuzzy despite our best efforts).
Anyway, you wrote How can I get Excel to provide exact sums instead of averaging the data?
Excel wasn't averaging at all; It was rounding (probably here because the display was set to a single decimal place). It's an important distinction, insofar as Excel, when you want it to, has functions AVERAGE and ROUND, the former turning those numbers you cited into 4.25, and the latter, if you want to round "to the nearest 10" yields 20. With larger numbers, you can use ROUND to get to the nearest 100, nearest 1000, whatever. There's also ROUNDUP, ROUNDDOWN.
Precision in words (and Functions) is as important as precision in calculations.
See the attached. Play around with the numbers in column A to see how the different functions work. Here's a screen image, but the actual spreadsheet is also attached,
Haha, you're right, I'm having problems with rounding. I have 11 separate categories, each divided into 2 columns (job 1 number of hours aligned with number of pages, ... job 11, i.e., 22 columns), and each category contains 9-12 separate entries (job 1.1...). In preparing an invoice at the end of the month, I need the separate sums of hours and pages within each category and separate sums of hours and pages across categories. To save time, I've been using the Sum function for each one. I have no problem with the number of digits entered for each unit, but the sum for each one is always rounded to 2 decimal places. Final calculation of the dollar amount based on overall hours isn't at all accurate and I wind up with the trusty calculator. Possible to stop if from rounding sums?? mathetes
It does occur to me, reading all of your responses, to others as well, that you're changing the decimal places in the numbers you are adding, but not in the cell that contains the =SUM() function. Is that the case?
That's where you want to show three or more decimals, in the final number (perhaps in all the others as well, though less crucially).
So with that very cell as the active one (place your cursor there), where the =SUM() function is, so it's highlighted, then go up to the tools across the top and click on the one with the red arrow pointing at it.
That should increase the number of decimals. If it doesn't, make sure the cell is set for either "Number" or "Currency"
Make sure that the column is wide enough to display 21.25
If the column is too narrow, Excel will drop decimal places:
Also, make sure that the number format is either set to General or to Number with 2 (or more) decimal places. If it is set to Number with 1 decimal place, the displayed number will be rounded (although the correct value is still stored in the cell)
- that is probably just a display issue. If you change the cell format to display additional decimal places you should be good. Under Home -> Number Group click the button with and arrow to the left to add additional decimal places.
