Excel - problems with SUM function

Microsoft Office excel 2007 - upgrade
January 24, 2011 at 16:15:51
Specs: Windows XP
I've formatted my cells to round numbers to the nearest whole (ie. 10.123 = 10)

When I use the SUM function however, it does a sum of the decimal numbers. But I need the sum of the rounded numbers which are being displayed in the cells. How do I make it do so?

Thanks for your help!

PS. not sure if it matters, I'm using Office 2007 Pro

See More: Excel - problems with SUM function

Report •

January 24, 2011 at 16:40:48
If all you did was format the cell to only show the integer portion of the number, you haven't "rounded" it as far as Excel is concerned.

Excel still considers 10.123 to be 10.123 when it comes to using the value in a calculation.

If you really want to sum the "rounded" numbers, then you need to actually round the values first.

There are (at least) 2 ways to do this:

1 - Use another column to ROUND all of the numbers and then SUM the results of the ROUND functions.

With 10.123 in A1, =ROUND(A1, 0) will return 10 and Excel will consider it to actually be 10 when used in a calculation.

2 - Use an array formula to SUM the original values by letting Excel ROUND them before they are summed:

An array formula is entered by pressing Ctrl-Shift-Enter after typing the formula into a cell. After you type this in, and press Ctrl-Shift-Enter

=SUM(ROUND(A1:A5, 0))

You'll get {=SUM(ROUND(A1:A5, 0))}

Excel will now "internally" ROUND each value before it SUMs them.

Keep in mind that every time you edit the formula, you must use Ctrl-Shift-Enter to retain the array format.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 27, 2011 at 08:23:36
AWESOME! Thank you so much!

Report •

Related Solutions

Ask Question