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

If all you did was formatthe 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 formulato 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.

AWESOME! Thank you so much!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History