sum totals in excel

[Sk]

My excel sheet is set up with dollars and cents format account.

I have used auto sum to total columns U6:U19.

My formulas are in said columns.

The excel total is less than the numbers in the columns when added up
manually.

I have tried entering round=(u6:u19) and I keep getting an error message in
both the cells and sum total.

How to I get the round feature and exactly how to I type in the formulas and
auto sum?

Thanks,
SK

 

[Francis]

try

=ROUND(SUM(U6:U19),2)

this will round to 2 decimal places
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[AltaEgo]

Rounding depends what you are trying to to achieve:

If you are working with currency I anticipate you need MRound()

MRound - Returns a number rounded to the desired multiple. =MROUND(U6,0.05)
will round U6 to the nearest multiple of five cents (as required for
currency calculations in my country). NOTE: requires installation of the
Analysis Toolpack add-in. See:

XL2003
http://office.microsoft.com/en-us/excel/HP011277241033.aspx?pid=CH062527761033
XL2007
http://office.microsoft.com/en-us/excel/HP100215691033.aspx?pid=CH100870211033

To use any rounding function, you need to round it either separate from
other formulae (e.g. in V6 enter =MROUND(U6,0.05) ) or use it in conjunction
with another formula (e.g. in U20 enter =MROUND(sum(U6:U19),0.05)). Note:
the latter is more accurate in terms of invoicing sales amounts rounded to
the nearest 5 cents (i,e =MROUND(SUM(A2:A4),0.05) will round three entries
of $4.21 to $12.65 whereas mrounding individual values before summing will
result in $12.60.

For help on other rounding functions:
http://office.microsoft.com/en-us/excel/HP030561441033.aspx?pid=CH010005131033

 

[Ken Wright]

There is a very simple solution that doesn't require any addins, and that is
simply to use the formula

=SUM(ROUND(U6:U19,2)) but you need to Array Enter it using CTRL+ENTER

You can also just use

=SUMPRODUCT(ROUND(U6:U19,2)) without having to enter it as an array formula

What may be an idea though is to actually use the ROUND() function to 2 dps
on your formulas in U6:U19 in each of the cells, such that what you see is
really what you get. Then a simple SUM from autosum will give you what you
expected it to.

Assuming your formulas were something like =A1*(B2/C3)+D4 and you wanted to
use the ROUND() function on them, it would like this:-

=ROUND(Your_Formula,2)

eg:
=ROUND(A1*(B2/C3)+D4,2)

Regards
Ken..................

 

[Shane Devenshire]

Hi,

1. You can't get AutoSum to round your values for you, you either write a
formula or apply a format, depending on your needs.
2. Of course round may not be the issue, you haven't shown us the data and
the result you get and expect. It may be that this is just an issue of how
computers work - they work in binary even though we enter decimal number
which results in approximations by Excel and any computer.

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html