Strange formatting of Zeros

I'm going crazy with this so I thought I'd ask if anyone 
knows what's up. I've got identical columns with 
numerical entries (Currency) and sum toals at the bottom 
of the columns. when both are Zero, onw will show black 
0.00 while the other will show up red -0.00. I know I 
could change the number formatting to eliminate the red 
but why qwould Zero show as a negative? Both columns are 
setup the same. Just for curiosity, I tried changing the 
range of the cell with the red result to only include the 
last few cells of the column and it showed up as blac 
without the minus just like the other one. But when I 
have it calculate the entire column it shows up as red 
with a minus sign.

Anyone have any idea why?
0
GR8PIPES1 (1)
10/19/2003 10:49:17 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
385 Views

Similar Articles

[PageSpeed] 0

J,

Compare the formatting codes.  Format - Cells - Number - Custom.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"J Brian" <GR8PIPES@ballads.com> wrote in message
news:01eb01c39693$3a039ca0$a601280a@phx.gbl...
> I'm going crazy with this so I thought I'd ask if anyone
> knows what's up. I've got identical columns with
> numerical entries (Currency) and sum toals at the bottom
> of the columns. when both are Zero, onw will show black
> 0.00 while the other will show up red -0.00. I know I
> could change the number formatting to eliminate the red
> but why qwould Zero show as a negative? Both columns are
> setup the same. Just for curiosity, I tried changing the
> range of the cell with the red result to only include the
> last few cells of the column and it showed up as blac
> without the minus just like the other one. But when I
> have it calculate the entire column it shows up as red
> with a minus sign.
>
> Anyone have any idea why?


0
nowhere8060 (363)
10/19/2003 11:34:12 PM
Because you have a negative value but since you probably are using currency
format with 2 decimal values it will show as $0.00 while it might be -0.001.
You can either round these numbers or use more decimal places or use
precision as displayed under tools>option>calculation.

-- 

Regards,

Peo Sjoblom

"J Brian" <GR8PIPES@ballads.com> wrote in message
news:01eb01c39693$3a039ca0$a601280a@phx.gbl...
> I'm going crazy with this so I thought I'd ask if anyone
> knows what's up. I've got identical columns with
> numerical entries (Currency) and sum toals at the bottom
> of the columns. when both are Zero, onw will show black
> 0.00 while the other will show up red -0.00. I know I
> could change the number formatting to eliminate the red
> but why qwould Zero show as a negative? Both columns are
> setup the same. Just for curiosity, I tried changing the
> range of the cell with the red result to only include the
> last few cells of the column and it showed up as blac
> without the minus just like the other one. But when I
> have it calculate the entire column it shows up as red
> with a minus sign.
>
> Anyone have any idea why?


0
terre081 (3244)
10/19/2003 11:36:09 PM
Format the sums to General.  You will see that the negative one is not 
zero, though the difference from zero is smaller than 0.01.

I will guess that the negative column has values computed from formulas, 
which do not compute exactly to the penny.  I will further guess that 
the "identical" positive column was hand entetered, using the rounded 
values that are displayed in the negative column.  As a result, they are 
not really identical, even though they agree to two decimal places. 
Make a third column that computes the difference, and format it to 
General, then you will find where the differences are coming in.

If you  don't want anything beyond the second decimal place, you either 
have to explicitly round the formula results using the ROUND() function, 
or else set Tools|Options|Calculate|Precision as displayed.

Jerry

J Brian wrote:

> I'm going crazy with this so I thought I'd ask if anyone 
> knows what's up. I've got identical columns with 
> numerical entries (Currency) and sum toals at the bottom 
> of the columns. when both are Zero, onw will show black 
> 0.00 while the other will show up red -0.00. I know I 
> could change the number formatting to eliminate the red 
> but why qwould Zero show as a negative? Both columns are 
> setup the same. Just for curiosity, I tried changing the 
> range of the cell with the red result to only include the 
> last few cells of the column and it showed up as blac 
> without the minus just like the other one. But when I 
> have it calculate the entire column it shows up as red 
> with a minus sign.
> 
> Anyone have any idea why?

0
post_a_reply (1395)
10/19/2003 11:45:47 PM
Reply:

Similar Artilces:

Strange Output
Hello, everybody !!! Why this fragment of code shows : ac cout<<"a\??/??/bc"; Thanks All !!! "Alex Dmitriev" <firejump@mail.ru> wrote in message news:ecTgPp8pEHA.3800@TK2MSFTNGP14.phx.gbl... > Hello, everybody !!! > Why this fragment of code shows : ac > > cout<<"a\??/??/bc"; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elconTrigraphs.asp '??/' is a '\' so you end up with "a\\\bc" http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elc...

How do I set up formula to copy format from one worksheet to anoth
How do I set up a formula to copy both value and format from one worksheet to another? When I use the = sign, it copy the value only. How do I get the format be copied also? you can't "link" the formatting in the same way as you link the contents what you need to do is to copy all the cells in your sheet X A B C ... 1 2 click on the grey square which I have illustrated with the X this selects all the cells in the sheet press control-C (copy) go to your new sheet and select the same X square now do edit > paste special > format / column width. if you are doin...

How do I retrieve Emails stored in .pst format?
I cannot retrieve my Emails archived/stored in My Documents and Settings folder because it is a .pst format. I'm using outlook 2003. Any advise on our I can retrieve appreciated. On Sat, 23 Feb 2008 03:58:00 -0800, Anno <Anno@discussions.microsoft.com> wrote: >I cannot retrieve my Emails archived/stored in My Documents and Settings >folder because it is a .pst format. I'm using outlook 2003. Any advise on our >I can retrieve appreciated. Please repost your question in a newsgroup supporting Outlook. This group is for a different program, Microsoft Access. The webp...

how do i format telephone numbers
I have a column of telephone numbers which I want formatted to the following ##### ### ###. I have formatted a blank column and tried to copy and paste the column of telephone numbers using paste special>Values, however this does not seem to work. Does anyone have any suggestions Thanks Your telephone numbers are probably not real numbers, but text. Custom formats only work on true numbers. You should convert them to numbers *before* you copy them to your pre-formatted column. Right click in a new, unused, empty cell, and choose "Copy". Select the column of telephone nu...

Excel Object showing strange behavior
Hello, I have a sheet that I created that contains an Excel object (small spreadsheet) that when double clicked, will open up and allow it to be populated. After they populate it, and close it,...the data is automatically reflected in the object on the original sheet. This is shared with everyone and it's working great. BUT.....(always the but) Why have a few people sent the form back to me saying that when they double click the object it opens up a "Format Object" dialogue box rather than the editable sheet that it was supposed to do? They sent it back to me and sure enough...

Date formatting in footer
How do I change the format of the date option in the footer of a worksheet? For example, currently it shows 8/9/06; how would I change it to Aug 8, 2006 or 9 Aug 2006 for example?. TIA You can make some changes in Windows Regional Settings to your short date format which is what Excel uses for the footer date. Gord Dibben MS Excel MVP On Thu, 10 Aug 2006 16:50:18 -0700, "computerguy" <computerguy394-news@yahoo.com> wrote: >How do I change the format of the date option in the footer of a worksheet? >For example, currently it shows 8/9/06; how would I change it to ...

Anyone know how to format cells as yes/no boolean logic?
Thinking of using XL to create electronic attendance register I need to check cells on/off (true/false) quickly (mouse click would be nice) I would also need to perform simple maths on them using built-in functions Sounds pretty basic need (Format/Cells/Boolean?), but i can't see it anywhere Any ideas? or maybe an attendance register template A couple of options for a quick browse .. a. Post by Bob Phillips: A worksheet sub which can insert a checkmark in a designated range (C2:C100 in Bob's example therein) upon a mouse-click: http://tinyurl.com/c2j6t b. A suitable template? ...

Formatting Zero Values
I have a range of cells with numeric values, some of which are formulas. When the cell value is zero I would like to display a hyphen "-" rather than 0 or blank. Does anybody have a way to do this, preferably with a custom formatting that can easily be applied to each cell? Thanks, Kaval Hi use a custom format like 0;-0;"-" -- Regards Frank Kabel Frankfurt, Germany "Kaval" <Kaval@discussions.microsoft.com> schrieb im Newsbeitrag news:1284DEDF-83D3-4439-BB04-F11A207AB3B6@microsoft.com... > I have a range of cells with numeric values, some of which ...

Conditional formatting #70
I want to change font when a cell takes a certain value from a formula, but in the conditional formatting box the font selection is ghosted out. Any tips on how I can do this? -- Shamus Hi, Change the Font or the Font Size is not allows in conditional formatting. You can change only those elements that are not fuzzed out. The only solution would be to write a VBA macro to solve this problem. -- Thanks, Shane Devenshire "Shamus" wrote: > I want to change font when a cell takes a certain value from a formula, but > in the conditional formatting box the font select...

What's the detailed format of pMask Buffer of GPE::SetPointerShape method?
Hi All: I'm developing display driver with windows CE 6.0 R2 version. I'm confusing with the buffer format when debugging GPE::SetPointerShape method. As the document said: The pMask parameter points to a 1-bpp surface with a width of cx and a height of (2cy). The top half defines the AND mask for the pointer, and the lower half defines the XOR mask. but where is the 1st pixel bit located, the 1st bit of the 1st byte or the 7th bit of 1st byte? the document has no related words. And there is another problem, the prototype of this method is as the following: SC...

Number format #6
When copying a number from an email like 260016008390 and pasting into Excel 2003 it gets changed to 2.60016E+11 How can I prevent this from happening apart from using paste special and selecting text. Steve If the cell width is too small... widen the cell to show the whole number. If you're not calculalting with the number, format cells as text, and if the number starts with a zero, it will keep it... Beege "Steveqae" <steve.shipping@blueyonder.co.uk> wrote in message news:e4R07AltGHA.2224@TK2MSFTNGP06.phx.gbl... > When copying a number from an emai...

Exchange Permissions--Strange One
Hi by default, domain admins (and ent. admins) are denied "receive as" and "send as" permissions to the mailbox store. additionally, by default, on the exchange advanced tab under the mailbox rights button, domain admins are DENIED Full Mailbox Access. However, if you look at the security tab for each user object, by default domain admins are allowed "send as" and "receive as" permissions. now i know that the "receive as" right is ignored on the ad user object, but i ran a small test. i logged on as a domain admin, opened up the domain admin...

Conditional formatting of checkbox?
In Access 2003, is conditional formatting possible on a Y/N checkbox? I have a fairly important checkbox that I would like to highlight in red if checked, to grab the user's attention. If it's not possible, any suggestions for other ways of doing this?? Many thanks CW On Fri, 18 Jan 2008 12:12:05 -0800, CW wrote: > In Access 2003, is conditional formatting possible on a Y/N checkbox? > I have a fairly important checkbox that I would like to highlight in red if > checked, to grab the user's attention. > If it's not possible, any suggestions for other ways of doi...

Excel
I have imported a list of birthdates that are formatted as a number suc as: 19730612 which represents June 12, 1973 Is there anyway to have Excel recognize this as a date in the for yyyymmdd and then convert it to a different format such as mm/dd/yyyy? Thanks, Be -- Message posted from http://www.ExcelForum.com Hi Select the range and then use Data / Text to Columns. This gives you the option of telling Excel it is a date and also the order of the day, month and year within it. -- Andy. "benallred99 >" <<benallred99.17wcg6@excelforum-nospam.com> wrote in messa...

Strange Email failure error
Can anyone help? Using Outlook 2002 and POP3 mail. I've always been able to send emails to my wife's work email address until this week, when I get this very strange failure notice. "Connected to 213.2.49.230 but greeting failed. Remote host said: 421 reppsrv01.repp.co.uk is not accepting new connections. Please try later I'm not going to try again; this message has been in the queue too long." Does anyone know what the hell this means? thanks -- Google is your Friend Email address deliberately false to avoid spam: gordonATgbpcomputingDOTcoDOTuk www.gbpcomputin...

EXCEL
Is it possible to put more than 3 conditional formats? I want to put around 8... If 0-5 blue, If 6-10 red, If 11 - 15 brown etc.... Thanks.... No, I believe 3 is the maximum. >-----Original Message----- >Is it possible to put more than 3 conditional formats? >I want to put around 8... If 0-5 blue, If 6-10 red, If >11 - 15 brown etc.... >Thanks.... >. > You'll find that you can't add more than 3 conditions. i.e. the answer to the OP's question is No. You would have to use an event macro (maybe On_Calculate) to scan the cells and set the colors if ...

Very Strange
Hi I have a Word 2007 document. In this I am using 3 different heading styles. I want to use the feature 'Select Text with Similar Formatting' but it is not working. I have 5 headings that are all Title Case, size 10, Times New Roman, Left aligned and Bold there is exactly the same formatting applied to all 5 headings - I even checked the paragraph spacing - all headings are 0pt before and 12pt after. I checked show and hide - everything I repeat everything is the same - can you tell me why oh why will this feature only select 3 of my headings and not the 5???? ...

Date format change when using Series() function
I’m using the Series() function with graphs, and the name ranges within the graphs seem to work, however, the X axis date range now goes weird on me. The named range for my X axis looked fine before, but when I put the name range in the Series, the dates are now all wrong (1/1/1900, 2/1/1900, etc). Any thoughts? Thank you. ...

Conditional formatting Icons
Hi All, I have values for two months - Current and Previous. If current month's value is closer to zero than previous month's value then i want to show a green arrow. If current month's value is away from zero than previous month's value then i want to show a red arrow. Though these values are positive and negative both, i need to apply conditional formatting on absolute values. e.g. previous Current 1.2% 2.4% Red Arrow 1.2% -2.4% Red Arrow -1.2% 2.4% Red Arrow -1.2% ...

why this strange result with vlookup
hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE) but any time i pick a code from my list items in column A it gives #NA but as soon as i change to a different code it gives the name. any help why My best guess would be a data type mismatch. Looking up text in a column of numbers. When you change the value the type gets converted and the result is returned. Hard to say though based on yoru description. The ohter thig to look for might be blank characters padded at the end of the input. -- HTH... Jim Thomlinson "Amin" wrot...

DestroyWindow ASSERT problem....strange, strange!
Hi I have derived a dialog-class in a DLL (shared MFC) I can construct an object of this class in my app, and when overloading DoModal() and adding an AFX_MANAGE_STATE(AfxGetStaticModuleState( )); call, I can see the CMyDialog dialog. But here is the issue, I have a timer set for a certain time, it posts a message and the dialog has to be killed - so I use CDialog::OnCancel. Then DoModal calls DestroyWindow() - Here a copy of the m_hWnd is made in hWndOrig. #ifdef _DEBUG hWndOrig = m_hWnd; #endif Then m_hWnd gets set to Null by ::DestroyWndow (which calls OnNcDestroy). But later in Dest...

Strange behavior #3
Hello. I use Outlook 2003 with a internal pop/smtp server (Merak). Using Oulook 2003 (it's the case for all users in the company), when we send mails to internal addresses, in the recipient mailbox our address doesn't appear, we are considered as ghost users! But, if on the same PC we use another email client (Outlook express for example), the mail arrives with my address as sender! Does anyone feel a ghostbuster? Thanks by advance. ...

Chart data with zeroes
I have used an IF formula to replace blanks, zeroes etc. with #N/A as I thought charts ignored this. My source data is 2 columns as below displaying as a line chart: #N/A #N/A 0.20 0.0017497 0.40 0.0017613 0.60 0.0017538 0.80 0.0017459 1.00 0.0017397 1.20 0.0017358 1.60 0.0019809 2.00 0.0021619 #N/A #N/A #N/A #N/A #N/A #N/A The #N/As are plotted as zeroes with #N/A displaying instead of a figure on the X axis. I need to get rid of the #N/As displaying on the X axis and have excel ignore them on the chart. Any help greatly appreciated. ---------------- This post is a suggestion for Micr...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Function/Formatting
Have a spreadsheet with a range of numbers(for ref the last 30 lotter draws) in cells A1:g31. What I would like is - if a number is entere in say cell m2, a formula or conditional format that would highligh that number in the range. Is it possible? Thanking everyone in advance. Scap ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way, select A1:G31 with A1 as the active cell (if you start selecting from A1 it will be active) or select it by typing A1:A31 in the n...