Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sum totals in excel
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sum totals in excel
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 "Sk" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sum totals in excel
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/ex...CH062527761033 XL2007 http://office.microsoft.com/en-us/ex...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/ex...CH010005131033 -- Steve "Sk" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sum totals in excel
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.................. "Sk" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
sum totals in excel
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 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sk" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
Grand Totals with Nested Sub Totals | Excel Discussion (Misc queries) | |||
how to enter totals and sub totals from receipts into excel. | New Users to Excel | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions |