Excel sum

[DJedit]

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.

[#2]

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.

[#3]

@DJedit 

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)

[#4]

@DJedit 

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,

[#5]

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 

[#6]

hmm. tried that. Expanded number of decimal places to three, highlighted the numbers I want summed, chose the Sum function, and got the rounded number.

@mtarler 

[#7]

@DJedit 

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.

[#8]

@DJedit 

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"

[#9]

@mathetes  The far right column should be 23.56 

[#10]

@DJedit 

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.

[#11]

@mathetes 

[#12]

@DJedit 

So all it needed was to do what we've (all the various people who've responded) been telling you from the beginning. Change the number of decimal places in the bottom row. I've set them all as numbers with two decimal places.

My image with the red arrow should show you how.

[#13]

@mathetes 

Thanks, I thought I had done that. I've been fooling around with my template and somehow now lost the ability for it to continuously update the sums ......I am one of those impatient people who to try and figure things out without going through formal (or informal) training. I'm going to start over. Thanks again.

[#14]

@DJedit 

You're welome.

And I commend you for being "impatient people who to try and figure things out without going through formal (or informal) training."

I was an impatient person back in the 1980s (or was it the 1970s?) when I first learned Lotus 1-2-3. I took a single course and was bored to death. (I had learned how to program earlier, so this introductory course was not what I needed)....so I got something that's harder to get nowadays: the manuals. Printed instructions on how everything worked. And I read them, cover to cover. Most such resources are on-line now...i.e., when you buy Excel (or the entire Office suite) you probably don't even get it on disks anymore--it's downloaded). But as a result it's necessary for a good intro to get to a good website for the purpose, or any number of books... You might find this helpful, if it's still available: https://smile.amazon.com/Excel-2019-Business-Basics-Beyond/dp/1615470611/ref=sr_1_2?crid=GTBXJI8SDEO...

Now that I've said all that--and I really do commend you for trying to figure it out on your own-- may I ask what the circumstances are? What is the nature of the data you're trying to collect? What will you be doing with it?

I ask because it appeared as if you might, possibly, have been making a common mistake of beginners....formatting everything for how it will look as an output, and entering your data into that output layout. It often (I tend toward saying "always") is far better to assemble (enter) the data in one way, in an input sheet of some kind, and then let Excel's many data manipulation methods do the "heavy lifting" of extracting and summarizing, producing the output.

So if you'd be willing to humor me a bit, if you could describe the larger context here...the nature of the data, where and how it is generated, and then what you expect in the way of summary, analysis, etc., maybe I or somebody else here in the techcommunity can offer suggestions on how to better tackle that project.