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

how do you make negative numbers show up as 0 in Excel?



 
 
Thread Tools Display Modes
  #1  
Old November 14th 05, 01:17 PM
djjny
external usenet poster
 
Posts: n/a
Default how do you make negative numbers show up as 0 in Excel?

I want to force a formula I have to show up as a zero if the value is less
than zero. I have tried embedding something in the formula to do this, but
it will not work. anyone have any suggestions? Is there a way to format a
field to display zero when the value is less than zero? I am using Excel
2000.
Ads
  #2  
Old November 14th 05, 01:23 PM
tiah
external usenet poster
 
Posts: n/a
Default how do you make negative numbers show up as 0 in Excel?

Hello !

go in Format Menu/cells

customize

the type is 0;"0";0

first argument is positive numbers
second is negatives numbers
third is 0 numbers
so the "0" between quotes tells to write 0
the 0 without quotes means the number itself will display

  #3  
Old November 14th 05, 01:36 PM
Duke Carey
external usenet poster
 
Posts: n/a
Default how do you make negative numbers show up as 0 in Excel?

tiah's solution will change the DISPLAY but not the value. In other words,
although the cell might show 0, any formula that referenced the cell would
still use the negative value.

If you need the VALUE to be 0, use

=MAX(your current formula,0)


"djjny" wrote:

> I want to force a formula I have to show up as a zero if the value is less
> than zero. I have tried embedding something in the formula to do this, but
> it will not work. anyone have any suggestions? Is there a way to format a
> field to display zero when the value is less than zero? I am using Excel
> 2000.

  #4  
Old November 14th 05, 04:39 PM
tiah
external usenet poster
 
Posts: n/a
Default how do you make negative numbers show up as 0 in Excel?

Hi Duke !

I know this is a display, but i answered to the question !

he ask for a way to "show" 0 and ask for a "format" to "display"

=)

sure i would use your formula

have a nive day !

  #5  
Old November 14th 05, 05:29 PM
Duke Carey
external usenet poster
 
Posts: n/a
Default how do you make negative numbers show up as 0 in Excel?

I offered a different solution only because sometimes the posters don't
realize the consequences of what they are asking for. You have a good day,
too

"tiah" wrote:

> Hi Duke !
>
> I know this is a display, but i answered to the question !
>
> he ask for a way to "show" 0 and ask for a "format" to "display"
>
> =)
>
> sure i would use your formula
>
> have a nive day !
>
>

  #6  
Old March 7th 06, 05:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default how do you make negative numbers show up as 0 in Excel?

Hi, this worked great, but now my decimals are missing. Any Help?

"tiah" wrote:

> Hello !
>
> go in Format Menu/cells
>
> customize
>
> the type is 0;"0";0
>
> first argument is positive numbers
> second is negatives numbers
> third is 0 numbers
> so the "0" between quotes tells to write 0
> the 0 without quotes means the number itself will display
>
>

 




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
How can I type Excel numbers to show as a barcode? johnp Excel Worksheet Functions 2 November 2nd 05 03:24 PM
Can I make Excel sort and graph a list of numbers? Nivek Charts and Charting in Excel 5 October 27th 05 10:05 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Excel text to numbers MBAKID007 Excel Discussion (Misc queries) 1 May 24th 05 05:52 PM
how do I make make my hyperlinks show the email address they are . Dustin Excel Discussion (Misc queries) 0 January 13th 05 01:39 AM


All times are GMT +1. The time now is 01:56 AM.


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