Forum Discussion
Excel sum
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"
That's not the spreadsheet; it's an image of the spreadsheet, and it does show what you've been describing. But there's no way from an image to see WHY, and that's what you want help with. So unless there's something confidential elsewhere in the spreadsheet itself, please post a copy of the actual spreadsheet. IF there is confidential info, just post the part that's doing this. But please not an image.
Possible to stop if from rounding sums?? you asked.
It's absolutely possible.
Is it possible for you to post EITHER your actual spreadsheet/workbook (devoid of any actual confidential or private info) OR some close resemblance of it. That way we can see first-hand what it is that's flummoxing you.