 | | | 
02.04.2014, 21:46
|  | Forum Legend | | Join Date: Mar 2008 Location: Wallis
Posts: 7,064
Groaned at 132 Times in 95 Posts
Thanked 8,085 Times in 3,650 Posts
| | | Arrrggggh - Microsoft Excel
I have done some calculations on excel, which result in answers to 2 decimal places.
However I want to round these figures to the nearest whole number and then add them together. I have selected 0 decimal places, but when adding them, the final total is out by a few numbers, it is adding the numbers with the decimal places, not the rounded numbers.
Anyone know how to overcome this?
Thanks
|
02.04.2014, 21:51
|  | Forum Legend | | Join Date: Dec 2010 Location: Fribourg
Posts: 9,295
Groaned at 237 Times in 154 Posts
Thanked 12,188 Times in 5,300 Posts
| | | Re: Arrrggggh - Microsoft Excel
Yep .... use OOo.
|
02.04.2014, 21:52
| | Forum Legend | | Join Date: Mar 2008 Location: Wallis
Posts: 7,064
Groaned at 132 Times in 95 Posts
Thanked 8,085 Times in 3,650 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | |  | | | Yep .... use O.O.o. | | | | | Pardon….?  |
02.04.2014, 21:54
|  | Moddy Wellies | | Join Date: Apr 2007 Location: North Yorkshire
Posts: 8,729
Groaned at 53 Times in 47 Posts
Thanked 9,942 Times in 3,654 Posts
| | | Re: Arrrggggh - Microsoft Excel
Add 0.5 and use INT()
|
02.04.2014, 22:10
|  | Forum Veteran | | Join Date: Jan 2012 Location: thun
Posts: 2,298
Groaned at 58 Times in 40 Posts
Thanked 3,024 Times in 1,470 Posts
| | | Re: Arrrggggh - Microsoft Excel
Yes. Frustrating isn't it. Just try to put in some telephone numbers and watch how it strips away all those 'unwanted' preceding zeroes.
|
02.04.2014, 22:12
| | Moddy Wellies | | Join Date: Apr 2007 Location: North Yorkshire
Posts: 8,729
Groaned at 53 Times in 47 Posts
Thanked 9,942 Times in 3,654 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | Yes. Frustrating isn't it. Just try to put in some telephone numbers and watch how it strips away all those 'unwanted' preceding zeroes. | | | | | Just add a ' before any number you want to be treated as text.
|
02.04.2014, 22:14
| | Forum Legend | | Join Date: Mar 2008 Location: Wallis
Posts: 7,064
Groaned at 132 Times in 95 Posts
Thanked 8,085 Times in 3,650 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | Just add a ' before any number you want to be treated as text. | | | | | But the numbers, are results of a formula typed into the cell. So I don't think that works  |
02.04.2014, 22:24
|  | Forum Veteran | | Join Date: Sep 2007 Location: zurich
Posts: 576
Groaned at 5 Times in 5 Posts
Thanked 495 Times in 231 Posts
| | | Re: Arrrggggh - Microsoft Excel
Instead of =A1+B1 for you sum, use =round(A1,0)+round(B1,0)
|
02.04.2014, 22:32
| | Forum Legend | | Join Date: Mar 2008 Location: Wallis
Posts: 7,064
Groaned at 132 Times in 95 Posts
Thanked 8,085 Times in 3,650 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | Instead of =A1+B1 for you sum, use =round(A1,0)+round(B1,0) | | | | | That works for 2 cells, but not for many it seems i.e. =sum(round(E4,0):round(E50,0))
|
02.04.2014, 22:36
| | Moddy Wellies | | Join Date: Apr 2007 Location: North Yorkshire
Posts: 8,729
Groaned at 53 Times in 47 Posts
Thanked 9,942 Times in 3,654 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | That works for 2 cells, but not for many it seems i.e. =sum(round(E4,0):round(E50,0)) | | | | | Add another column with the rounded numbers.
|
02.04.2014, 22:43
| | Forum Legend | | Join Date: Mar 2008 Location: Wallis
Posts: 7,064
Groaned at 132 Times in 95 Posts
Thanked 8,085 Times in 3,650 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | Add another column with the rounded numbers. | | | | | Thanks, that works  |
02.04.2014, 23:07
| | Forum Veteran | | Join Date: Sep 2007 Location: zurich
Posts: 576
Groaned at 5 Times in 5 Posts
Thanked 495 Times in 231 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | That works for 2 cells, but not for many it seems i.e. =sum(round(E4,0):round(E50,0)) | | | | | You can use SUMPRODUCT for that e.g. =SUMPRODUCT(ROUND(E4:E50,0)) will add the rounded values in E4..E50
|
02.04.2014, 23:27
| | Forum Legend | | Join Date: Mar 2008 Location: Wallis
Posts: 7,064
Groaned at 132 Times in 95 Posts
Thanked 8,085 Times in 3,650 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | You can use SUMPRODUCT for that e.g. =SUMPRODUCT(ROUND(E4:E50,0)) will add the rounded values in E4..E50 | | | | | Awesome! Thanks!
|
02.04.2014, 23:47
|  | Forum Legend | | Join Date: Apr 2007 Location: London, previously Basel
Posts: 3,773
Groaned at 21 Times in 19 Posts
Thanked 5,792 Times in 2,173 Posts
| | | Re: Arrrggggh - Microsoft Excel
And this is why my boss is sending me on a language course to speak Excel....apparently far more useful than German in my new role. |
03.04.2014, 00:03
|  | Forum Veteran | | Join Date: Jul 2009 Location: Zurich
Posts: 535
Groaned at 12 Times in 8 Posts
Thanked 349 Times in 193 Posts
| | | Re: Arrrggggh - Microsoft Excel
My life is Excel... (depending on your inclination that's a really good or a really bad thing)
Once we move to Zurich in May/June, I am happy to give anyone a lesson or three at any level.
|
03.04.2014, 09:37
| | Forum Legend | | Join Date: Mar 2008 Location: Wallis
Posts: 7,064
Groaned at 132 Times in 95 Posts
Thanked 8,085 Times in 3,650 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | My life is Excel... (depending on your inclination that's a really good or a really bad thing)
Once we move to Zurich in May/June, I am happy to give anyone a lesson or three at any level. | | | | | Do you know of any good books to learn from? It must do so much that I have no clue about!
|
03.04.2014, 10:07
| | Forum Legend | | Join Date: Mar 2009 Location: Zurich
Posts: 14,109
Groaned at 1,414 Times in 936 Posts
Thanked 21,316 Times in 8,142 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | Do you know of any good books to learn from? It must do so much that I have no clue about! | | | | | Look on Amazon UK for the most popular and best reviewed Excel books.
|
03.04.2014, 10:15
|  | Forum Veteran | | Join Date: Jan 2008 Location: Zug
Posts: 2,139
Groaned at 40 Times in 27 Posts
Thanked 3,047 Times in 1,239 Posts
| | | Re: Arrrggggh - Microsoft Excel
Why everyone is suggesting over complicated solutions?
1) Highlight all the cells you want to change.
2) Right click
3) Choose 'format cells'
4) Select 'number' from the list under the numbers tab.
5) Then choose how many decimal places you require.
Picture attached......
cheers
SC
|
03.04.2014, 10:19
| | Moddy Wellies | | Join Date: Apr 2007 Location: North Yorkshire
Posts: 8,729
Groaned at 53 Times in 47 Posts
Thanked 9,942 Times in 3,654 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | Why everyone is suggesting over complicated solutions?
1) Highlight all the cells you want to change.
2) Right click
3) Choose 'format cells'
4) Select 'number' from the list under the numbers tab.
5) Then choose how many decimal places you require. | | | | | That is where the OP started. It only affects how the number is displayed; the decimals are retained and will be used in any calculations involving those cells.
|
03.04.2014, 10:23
| | Forum Veteran | | Join Date: Jan 2008 Location: Zug
Posts: 2,139
Groaned at 40 Times in 27 Posts
Thanked 3,047 Times in 1,239 Posts
| | | Re: Arrrggggh - Microsoft Excel
| Quote: | | | | | That is where the OP started. It only affects how the number is displayed; the decimals are retained and will be used in any calculations involving those cells. | | | | |
LOL, I just re-read the first post...........Fail of the day.
This is what happens when I get up this early, I should of stayed in bed with my Romanian cam girls.
cheers
SC
| |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | | | | Thread Tools | | | | Display Modes |  Linear Mode | 
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts
HTML code is Off
| | | All times are GMT +2. The time now is 14:52. | |
