Go Back   English Forum Switzerland > Off-Topic > Off-Topic > General off-topic  
Reply
 
Thread Tools Display Modes
  #1  
Old 02.04.2014, 21:46
Island Monkey's Avatar
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
Island Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond repute
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
Reply With Quote
  #2  
Old 02.04.2014, 21:51
TiMow's Avatar
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
TiMow has a reputation beyond reputeTiMow has a reputation beyond reputeTiMow has a reputation beyond reputeTiMow has a reputation beyond reputeTiMow has a reputation beyond reputeTiMow has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Yep .... use OOo.
Reply With Quote
  #3  
Old 02.04.2014, 21:52
Island Monkey's Avatar
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
Island Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
Yep .... use O.O.o.
Pardon….?
Reply With Quote
  #4  
Old 02.04.2014, 21:54
mirfield's Avatar
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
mirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Add 0.5 and use INT()
Reply With Quote
  #5  
Old 02.04.2014, 22:10
me.anon's Avatar
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
me.anon has a reputation beyond reputeme.anon has a reputation beyond reputeme.anon has a reputation beyond reputeme.anon has a reputation beyond reputeme.anon has a reputation beyond reputeme.anon has a reputation beyond repute
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.
Reply With Quote
  #6  
Old 02.04.2014, 22:12
mirfield's Avatar
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
mirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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.
Reply With Quote
  #7  
Old 02.04.2014, 22:14
Island Monkey's Avatar
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
Island Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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
Reply With Quote
  #8  
Old 02.04.2014, 22:24
jaudi's Avatar
Forum Veteran
 
Join Date: Sep 2007
Location: zurich
Posts: 576
Groaned at 5 Times in 5 Posts
Thanked 495 Times in 231 Posts
jaudi has a reputation beyond reputejaudi has a reputation beyond reputejaudi has a reputation beyond reputejaudi has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Instead of =A1+B1 for you sum, use =round(A1,0)+round(B1,0)
Reply With Quote
  #9  
Old 02.04.2014, 22:32
Island Monkey's Avatar
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
Island Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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))
Reply With Quote
  #10  
Old 02.04.2014, 22:36
mirfield's Avatar
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
mirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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.
Reply With Quote
  #11  
Old 02.04.2014, 22:43
Island Monkey's Avatar
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
Island Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
Add another column with the rounded numbers.
Thanks, that works
Reply With Quote
  #12  
Old 02.04.2014, 23:07
jaudi's Avatar
Forum Veteran
 
Join Date: Sep 2007
Location: zurich
Posts: 576
Groaned at 5 Times in 5 Posts
Thanked 495 Times in 231 Posts
jaudi has a reputation beyond reputejaudi has a reputation beyond reputejaudi has a reputation beyond reputejaudi has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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
Reply With Quote
  #13  
Old 02.04.2014, 23:27
Island Monkey's Avatar
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
Island Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
You can use SUMPRODUCT for that e.g. =SUMPRODUCT(ROUND(E4:E50,0)) will add the rounded values in E4..E50
Awesome! Thanks!
Reply With Quote
  #14  
Old 02.04.2014, 23:47
mimi1981's Avatar
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
mimi1981 has a reputation beyond reputemimi1981 has a reputation beyond reputemimi1981 has a reputation beyond reputemimi1981 has a reputation beyond reputemimi1981 has a reputation beyond reputemimi1981 has a reputation beyond repute
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.
Reply With Quote
  #15  
Old 03.04.2014, 00:03
Bozza's Avatar
Forum Veteran
 
Join Date: Jul 2009
Location: Zurich
Posts: 535
Groaned at 12 Times in 8 Posts
Thanked 349 Times in 193 Posts
Bozza has earned some respectBozza has earned some respect
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.
Reply With Quote
  #16  
Old 03.04.2014, 09:37
Island Monkey's Avatar
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
Island Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond reputeIsland Monkey has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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!
Reply With Quote
  #17  
Old 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
Chuff has a reputation beyond reputeChuff has a reputation beyond reputeChuff has a reputation beyond reputeChuff has a reputation beyond reputeChuff has a reputation beyond reputeChuff has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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.
Reply With Quote
  #18  
Old 03.04.2014, 10:15
Swiss Cheddar's Avatar
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
Swiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond repute
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
Attached Thumbnails
arrrggggh-microsoft-excel-decimal-places.jpg  
Reply With Quote
  #19  
Old 03.04.2014, 10:19
mirfield's Avatar
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
mirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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.
Reply With Quote
  #20  
Old 03.04.2014, 10:23
Swiss Cheddar's Avatar
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
Swiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond reputeSwiss Cheddar has a reputation beyond repute
Re: Arrrggggh - Microsoft Excel

Quote:
View Post
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
Reply With Quote
Reply




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Excel Help MrsSerendipity General off-topic 3 22.03.2013 08:32
Help with Excel Trikk General off-topic 14 24.10.2012 20:52
Excel training offered Ulric Commercial 0 07.10.2012 15:19
Microsoft Excel Cashboy Other/general 7 13.01.2010 10:39


All times are GMT +2. The time now is 14:52.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO 3.1.0