A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Prevent rounding up and down



 
 
Thread Tools Display Modes
  #1  
Old November 15th 16, 03:52 PM
TerrySmith33 TerrySmith33 is offline
Junior Member
 
First recorded activity by ExcelBanter: Nov 2016
Posts: 1
Default Prevent rounding up and down

Hi

I have a table of data which has a series of percentages. The client wants these % displayed to 0 decimal places. However, Excel rounds anything 99.5% and above to 100% and 0.4% and below to 0%.

Is there any way of displaying those as 99% and 1% without affecting the rest of the data or changing the decimal places?

Thanks
Terry
Ads
  #2  
Old November 15th 16, 06:17 PM posted to microsoft.public.excel.worksheet.functions
GS[_6_]
external usenet poster
 
Posts: 584
Default Prevent rounding up and down

> Hi
>
> I have a table of data which has a series of percentages. The client
> wants these % displayed to 0 decimal places. However, Excel rounds
> anything 99.5% and above to 100% and 0.4% and below to 0%.
>
> Is there any way of displaying those as 99% and 1% without affecting
> the rest of the data or changing the decimal places?
>
> Thanks
> Terry


Set the NumberFormat to zero decimal places, perhaps!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3  
Old November 15th 16, 06:34 PM posted to microsoft.public.excel.worksheet.functions
Claus Busch
external usenet poster
 
Posts: 3,401
Default Prevent rounding up and down

Hi Terry,

Am Tue, 15 Nov 2016 14:52:11 +0000 schrieb TerrySmith33:

> I have a table of data which has a series of percentages. The client
> wants these % displayed to 0 decimal places. However, Excel rounds
> anything 99.5% and above to 100% and 0.4% and below to 0%.
>
> Is there any way of displaying those as 99% and 1% without affecting the
> rest of the data or changing the decimal places?


try a custom number format:
[<=0.004]1%;[>=0.995]99%;0%


Regards
Claus B.
--
Windows10
Office 2016
 




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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
prevent user from deleting a tab prevent running macro from menu joemeshuggah Excel Programming 5 February 25th 10 11:58 PM
Prevent EXCEL from rounding the result of a calculation elusiverunner Excel Discussion (Misc queries) 3 January 9th 08 09:38 AM
HOW CAN I PREVENT EXCEL FILES FROM BEING DELETED OR PREVENT TRASH ROB Excel Discussion (Misc queries) 2 April 2nd 07 01:13 PM
prevent rounding up jhahes[_51_] Excel Programming 1 March 23rd 06 08:36 PM
HOW TO PREVENT NUMBERS IN SPREAD SHEET FROM ROUNDING BSA New Users to Excel 1 October 19th 05 06:26 PM


All times are GMT +1. The time now is 11:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.