Actual value a non-zero number but display is Zero

Hi,

In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
have a formula = A1+B1+C1.

I want to print the range A1 to D1 and see the value 6 in D1 but in A1
to C1 I want the excel display to be zero.

I have probably read in thi group that some folks use rightclick on
cell and use Format and use some variation of Custom to achieve this
(not very sure).

Please guide me.

Please 
Regards,
Hari
India

0
9/5/2005 3:22:10 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
467 Views

Similar Articles

[PageSpeed] 58

Hi Hari,

You are definitely on the right track with number formats!  Try this:
- Select cells A1 to C1 and right-click, then select Format Cells
- Go to the Number tab and select Custom
- Type this in the format type box: "0";"0";"0"
- Click OK

Hope this helps.

Cheers,

Mark


"Hari" wrote:

> Hi,
> 
> In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
> have a formula = A1+B1+C1.
> 
> I want to print the range A1 to D1 and see the value 6 in D1 but in A1
> to C1 I want the excel display to be zero.
> 
> I have probably read in thi group that some folks use rightclick on
> cell and use Format and use some variation of Custom to achieve this
> (not very sure).
> 
> Please guide me.
> 
> Please 
> Regards,
> Hari
> India
> 
> 
0
MarkHone (25)
9/5/2005 3:43:27 PM
Hi Mark,

Thanks a lot for your help.

Im able to get Zeroes in the aforementioned cells. A doubt. If instead of 
writing "0";"0";"0" I write it as "0" I still able to make it work.

So whats the purpose of writing zero 3 times

Regards,
Hari
India

"Mark Hone" <MarkHone@discussions.microsoft.com> wrote in message 
news:D52167A7-52CD-4D09-B94D-DB8625C3152B@microsoft.com...
> Hi Hari,
>
> You are definitely on the right track with number formats!  Try this:
> - Select cells A1 to C1 and right-click, then select Format Cells
> - Go to the Number tab and select Custom
> - Type this in the format type box: "0";"0";"0"
> - Click OK
>
> Hope this helps.
>
> Cheers,
>
> Mark
>
>
> "Hari" wrote:
>
>> Hi,
>>
>> In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
>> have a formula = A1+B1+C1.
>>
>> I want to print the range A1 to D1 and see the value 6 in D1 but in A1
>> to C1 I want the excel display to be zero.
>>
>> I have probably read in thi group that some folks use rightclick on
>> cell and use Format and use some variation of Custom to achieve this
>> (not very sure).
>>
>> Please guide me.
>>
>> Please
>> Regards,
>> Hari
>> India
>>
>> 


0
9/6/2005 5:37:20 AM
Hi Hari,

The purpose of writing it three times is that you can apply different 
formats to positive numbers (>0), negative numbers (<0) and zeros (=0).  If 
you just had one "0" as you suggest then if someone typed in e.g. -6, that 
would still display as -6 and not 0.

Admittedly the last "0" is redundant as it's for zero values anyway, but I 
do like to be thorough ;-).

Cheers,

Mark


"Hari Prasadh" wrote:

> Hi Mark,
> 
> Thanks a lot for your help.
> 
> Im able to get Zeroes in the aforementioned cells. A doubt. If instead of 
> writing "0";"0";"0" I write it as "0" I still able to make it work.
> 
> So whats the purpose of writing zero 3 times
> 
> Regards,
> Hari
> India
> 
> "Mark Hone" <MarkHone@discussions.microsoft.com> wrote in message 
> news:D52167A7-52CD-4D09-B94D-DB8625C3152B@microsoft.com...
> > Hi Hari,
> >
> > You are definitely on the right track with number formats!  Try this:
> > - Select cells A1 to C1 and right-click, then select Format Cells
> > - Go to the Number tab and select Custom
> > - Type this in the format type box: "0";"0";"0"
> > - Click OK
> >
> > Hope this helps.
> >
> > Cheers,
> >
> > Mark
> >
> >
> > "Hari" wrote:
> >
> >> Hi,
> >>
> >> In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
> >> have a formula = A1+B1+C1.
> >>
> >> I want to print the range A1 to D1 and see the value 6 in D1 but in A1
> >> to C1 I want the excel display to be zero.
> >>
> >> I have probably read in thi group that some folks use rightclick on
> >> cell and use Format and use some variation of Custom to achieve this
> >> (not very sure).
> >>
> >> Please guide me.
> >>
> >> Please
> >> Regards,
> >> Hari
> >> India
> >>
> >> 
> 
> 
> 
0
MarkHone (25)
9/6/2005 12:15:03 PM
Hi Mark,

Thanks a lot for such a NEAT explanation.

Couldnt have guessed it myself.

One doubt from where could I read up stuff which explains the range of 
things one can achieve with the custom options.

Like how do you know that 3 "entities" could be written and first one would 
apply for positive, second for negative and so on.

Regards,
Hari
India

"Mark Hone" <MarkHone@discussions.microsoft.com> wrote in message 
news:DC6C5ABE-E76E-4565-A6FD-30FD1A13350C@microsoft.com...
> Hi Hari,
>
> The purpose of writing it three times is that you can apply different
> formats to positive numbers (>0), negative numbers (<0) and zeros (=0). 
> If
> you just had one "0" as you suggest then if someone typed in e.g. -6, that
> would still display as -6 and not 0.
>
> Admittedly the last "0" is redundant as it's for zero values anyway, but I
> do like to be thorough ;-).
>
> Cheers,
>
> Mark
>
>
> "Hari Prasadh" wrote:
>
>> Hi Mark,
>>
>> Thanks a lot for your help.
>>
>> Im able to get Zeroes in the aforementioned cells. A doubt. If instead of
>> writing "0";"0";"0" I write it as "0" I still able to make it work.
>>
>> So whats the purpose of writing zero 3 times
>>
>> Regards,
>> Hari
>> India
>>
>> "Mark Hone" <MarkHone@discussions.microsoft.com> wrote in message
>> news:D52167A7-52CD-4D09-B94D-DB8625C3152B@microsoft.com...
>> > Hi Hari,
>> >
>> > You are definitely on the right track with number formats!  Try this:
>> > - Select cells A1 to C1 and right-click, then select Format Cells
>> > - Go to the Number tab and select Custom
>> > - Type this in the format type box: "0";"0";"0"
>> > - Click OK
>> >
>> > Hope this helps.
>> >
>> > Cheers,
>> >
>> > Mark
>> >
>> >
>> > "Hari" wrote:
>> >
>> >> Hi,
>> >>
>> >> In A1 i have the value 1, In B1 let's say 2 and C1 value 3 and in D1 I
>> >> have a formula = A1+B1+C1.
>> >>
>> >> I want to print the range A1 to D1 and see the value 6 in D1 but in A1
>> >> to C1 I want the excel display to be zero.
>> >>
>> >> I have probably read in thi group that some folks use rightclick on
>> >> cell and use Format and use some variation of Custom to achieve this
>> >> (not very sure).
>> >>
>> >> Please guide me.
>> >>
>> >> Please
>> >> Regards,
>> >> Hari
>> >> India
>> >>
>> >>
>>
>>
>> 


0
9/6/2005 8:00:00 PM
Hi Hari,

I thought I'd found this out using the application help but I can't find it 
there now!

If you want to learn how to do all the clever stuff with Excel, then I'd 
recommend this book: Walkenbach, J. & Underdahl, B (2001) Excel 2002 Bible 
published in UK by Hungry Minds

This book covers the format of the custom number formats.

Cheers,

Mark


"Hari Prasadh" wrote:

> One doubt from where could I read up stuff which explains the range of 
> things one can achieve with the custom options.
> 
> Like how do you know that 3 "entities" could be written and first one would 
> apply for positive, second for negative and so on.

0
MarkHone (25)
9/7/2005 2:00:05 PM
Reply:

Similar Artilces:

Grid control only displays 1 record
I have a form with a subform that has a grid control. If I run the sql for the recordsource of the grid control in an access query, many records are returned. However, only one record displays in the gridcontrol. ANy assistance appreciated. hi, On 24.03.2010 15:21, Finance guy wrote: > I have a form with a subform that has a grid control. If I run the sql for > the recordsource of the grid control in an access query, many records are > returned. However, only one record displays in the gridcontrol. Grid control? What grid control? mfG --> stefan <-- O...

adding a leading 00 to some loan numbers
I've got several thousand loan numbers which need to be updated to a ten-digit format by adding a leading 00. That's easy enough to do, HOWEVER, some of them have already been updated with the leading 00. What formula can I use to add 00 only to those which have not already been updated? Thanks. -- alan =right(rept("0",10)&a1,10) If the values are all numeric (not text), you could apply a custom format: Select the range format|cells|Number tab|Custom category 0000000000 or use a formula like this if the cell looks numeric: =text(a1,rept("0",10)) or =tex...

Inser a page number in MS Excel
I have list of name with their Contribution , i want to sub -total after every Page that why i want to enter page no before each name . Pleese help me ? Use the Subtotals command on the Data menu and check the box that says Page Break betwen Groups. You can then set the Page Number in the Custom Header or Footer section of Page Setup using the 2nd button from left HTH Sheila "Arindam Maitra" wrote: > I have list of name with their Contribution , i want to sub -total after > every Page that why i want to enter page no before each name . Pleese help > me ? ...

Display names in Global address book do not update
I made a change to one of my users so that their display name shows as last name, first name. This change has not been applied to the global address book, but you can see the change has been made in active directory users and computers. Any idea how I update this change for the global address book? Thanks, Mike YOu need to wait for the global catalogs to refresh.....depends on your AD topology. "Mike Fefferman" <mike@nowhere.com> wrote in message news:%23v%23sxI5PFHA.3156@TK2MSFTNGP15.phx.gbl... >I made a change to one of my users so that their display name show...

formula
Hi, I want to copy A1, A3, A5, A7 etc. into a seperate column, but when I try to copy it down, it doesn't seem to recognise my odd numbers request. What formula should I use? Thanks! Use =INDIRECT("A"&ROW(A1)*2-1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Georgyneedshelp" <Georgyneedshelp@discussions.microsoft.com> wrote in message news:464A8951-DB29-4907-AE09-AA7CE4F0C15A@microsoft.com... > Hi, > > I want to copy A1, A3, A5, A7 etc. into a seperate column, but when I try to > copy it down, it doe...

Outlook 2000 display name
Trying to change the display name of an account in Outlook 2000 SP-3 Windows XP machine But I can't find the option to do it, all I keep on getting is e-mail services and nothing that says accounts even when I go through options. Any help would be appreciated Corp/Workgroup mode does not use the term account. Your Internet Mail Service is your email account. Set the name there in Properties -- Russ Valentine [MVP-Outlook] "Miranda" <anonymous@discussions.microsoft.com> wrote in message news:0c2a01c46e89$a46fc5e0$a601280a@phx.gbl... > Trying to change the display n...

Paste a number as text
We often use the copy paste function to export some data from our software into excel for further analysis. In one colomn we have a text field which containes sometimes only numers like "23000.3000". If we copy and paste this field, the result in excel would be "23000.3". (It is interpreted as number) We tried already to add a ' before the value but the the ' is showed as normal letter. Is there any solution to paste any number as a text in Excel without programming a full Excel automation. Thank you for your help! If it's just one cell, format it as text ...

New "Card Vault" retrieves card numbers in version 1.3
We have released a very useful application that I feel anyone could benefit from. With version 1.3 the credit cards are all masked, there is no way around this. They are also deleted from the batch table after the settlement so they cannot be retrieved at a future time. Before version 1.2 if there was a problem settling the batch you could just change the status in the visanetauthorization table and re-batch, this is not possible with version 1.3. If this ever happens with the new version you have to call the bank and they hopefully have the credit card number, you then have to re-ring in a tr...

Set Number of Previous Documents under FILE
When I open Excel and click on FILE on the menu bar, it only shows 4 previously opened documents. I want it to show 8-10 but can't find any options to change it. Can it be done? I have the student and teacher edition 2003. <Tools> <Options> <General> tab, Change number of entries by the "RecentlyUsedFileList". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Carolyn" <anonymous@discussions.microsoft.com> wrote i...

HTML Displayed in Outlook
I cannot display html script in Outlook 2002. I changed security settings to Internet which matches IE. Anyone ever had this problem with Window XP and Outlook? ...

Excel drop-down values versus labels
I can't figure out how to duplicate Web-based Select boxes, as drop-down lists in Excel. I can create the drop-down list using Data | Validation | Settings, selecting from Allow the opion List (Ignore Blank and In-Cell dropdown checked), and then enter into Source: Standard, Large, Extra Large (each item separated by commas). But I can't figure out how to calculate which item is selected without duplicating the data, It seems inefficient to duplicate "Standard, Large, Extra Large" elsewhere, and do a lookup. 1. Is there not a function which will return the Nth it...

Can
I need an IF then state to Edit/paste special/value into the cell so that the value only remains, not the function. I only want it to calculate once. Is there an easy way? Thank you Hi Ron this can not be done with a formula, you need to use VBA code ... this can be done "automatically" via a Worksheet_Change procedure, e.g. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$1" And Target.Value <> "" Then Range("A3").Copy Range("A3").PasteSpecial (xl...

Can you compile Excell vba to keep code Non-Viewable ?
Is there a way to protect your vba code in an excel app? thanks - tmb Hi, In the Visual Basic Editor, Right click on the module Node that you want to protect, select the protection tab, and enter a password. this will protect your VBA code. Hope this helps Sunil Jayakumar "tmb" <topmailbox@yahoo.com> wrote in message news:bwe3e.12668$vd.7397@tornado.tampabay.rr.com... > Is there a way to protect your vba code in an excel app? > > thanks - tmb > > > www.ayyoo.com/credit-cards.html Just to add to Sunil's post. This will keep most people fro...

Displaying an image
Hi, I have an asx file which plays various videos. However, I want tio show a gif image for several seconds between wmv videos. Can this be done, and if so what code should I use in the asx file? Thanks inadvance, Simon Keeling On Wed, 25 Nov 2009 09:44:46 -0800 (PST), Simon Keeling <simon@weatherweb.net> wrote: >Hi, > >I have an asx file which plays various videos. > >However, I want tio show a gif image for several seconds between wmv >videos. > >Can this be done, and if so what code should I use in the asx file? > Hi, I replied...

not enough resources to display completely starting excel 2010
I have just performed a clean install of Office 2010, having totally removed my Office 2007. I have installed the 64 bit variant, running on my (fairly new) Sony Vaio with 250 Gb ssd and 6 Gb RAM. I am running windows 7 ultimate (64 bit). Imagine my surprise that, whenever I start Excel, I get the error message: Not enough resources to display completely!!! I have not opened any workbook, nor do I have any other programs running, and I am a bit perplexed as to what could be causing this issue. Any ideas? I did also get some error messages when first starting Word but these see...

Hiding Non-Duplicated Entries
Dear All!!! It's very urgent, please help!!! I've got to edit duplicated entries but not remove them - how can hide NON-duplicated rows? Resulting column should contain even number of duplicated entries. Thanks in advance!!! Lana :eek -- Lan ----------------------------------------------------------------------- Lana's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=88 View this thread: http://www.excelforum.com/showthread.php?threadid=38315 I've created two columns with formulas that display 1 if preceding o following rows are identical, and...

Displaying Message from event sink to Edit control in dialog box
Hai I am working on FTP Software using MFC and i have to write the ftp server request and response to tracer window. The server request and response were caught by Eventsink and i donot know how to pass this text to edit control in dialog box which is another class. Here is coding of Event Sink / EventSink.cpp : implementation file // #include "stdafx.h" #include "RoboFTPClient.h" #include "RoboFTPClientDlg.h" #include "Eventsink.h" #ifdef _DEBUG #define new DEBUG_NEW #undef THIS_FILE static char THIS_FILE[] = __FILE__; #endif extern long gnResult...

Find Discontinued Number
I have a spredsheet to a few columns and one of the field is check numbers. This column looks like this: Check # 101 102 103 106 107 109 110 .... 120 I want to find out which numbers are missing between 101 and 120. The result I want should be:104, 105,108... Could anybody help? Plumstone, There may be better ways of doing it but try: Sub tester() Dim x As Long Dim y As Long Dim n As Long Dim EndCell As Long Dim StartCell As Long Dim StartCol As Integer StartCell = 9 StartCol = 3 EndCell = Cells(Rows.Count, StartCol).End(xlUp).Row n = StartCell ...

outlook 2010 folder display missing after email moved into it
After you move a message into a folder, the folder name on the folders display pane on the left disappeared... the only way to get it back is to exit and restart outlook 2010 However, this doesn't happen if you move a message into a subfolder.. ...

How do I create charts having non absolute reference values?
Not sure what you're asking. If you want to change an axis so it doesn't begin at zero, double click on it and enter your own scaling parameters. If that's not it, try using TWO sentences to explain what you need. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "rvboxel" <rvboxel@discussions.microsoft.com> wrote in message news:97BB5A7C-5AD5-4D84-83A1-02E8B96D2FAE@microsoft.com... > ...

articel number??????
Hello, I like to connect numbers and a description! For example: ___----------A B 1 1234 Watch 2 1235 Jeans If I put in a number I like to get the description automatic Maybe I put the number 1234 in A1 at a other sheet then I like to get the description Watch at b1. In Germany it is called �sverweis� but I don�t know the English function! Please Help me!!!! Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Dominick Sheet1 Column A contains numbers. Shee...

Excel default number format problem
I have a user with an incorrect default cell format in Excel. His default number of decimal places for a cell with numbers is set to 4 instead of 2. I have no idea how he caused this and I have no idea how to correct it. In case I'm being ambiguous, here is the procedure: Open a new Excel workbook, choose any cell and go to Format. In the Format Cell dialog box go to the Number tab and choose the Number category. The Decimal Places box will be set to 2 by default but in this user's Excel it is set to 4. How do I get it back to the default of 2? http://www.imagedump.com/index.cgi?pick...

Q on how to find best combination of values
Greetings all, I'm trying to find a way (an algorithm) for determining this: Maximize the value of A Keep B < 10 Data: A B F1 9 4 F2 11 6 F3 10 6 F4 10 3 F1 is 'fraction 1' I can do this manually in Excel, but when I go to larger data sets it'd be a huge pain. Does anyone know of an algorithm or a way in Excel that I could easily do this? I'm thinking that it'd take some macro programming in order to do it, but first I need an algorithm. Even if I couldn't do it with Excel/macros I'd still like to know how to do it. It's been a long time since I&#...

Using formulas to modify pivot table values
Is there a way to modify the output of the data in the body of a pivot table to be included in a calculaton. Of course this can be done post pivot table creation but I would like to do it in one go. I need to divide all the counted values in the body of the pivot table by a cell value, which is different for each row of the pivot table. Help would be much appreciated. --- Chris I don't think so. Maybe you could add an extra column and do your calculation against that (and include it in the pivottable). Or copy the pivottable and convert to values and do what you want. geechr wro...

Cell Formatting to disp. ### numbers
I am trying to format the cells so that it only allows three numbers t be displayed. To be more descriptive: We work with zip codes quite often, but, we only use the first thre digits. Someone sent us a xls file with 12000 zip codes, in one column, and need to know how to make the column show only the first three digits o all the zip codes.. there is another problem, when I convert them to a numeric value, i removes the zero in front...ex. 08245, becomes 8245, but i need to kee that zero in front. Thanks -Eri -- Message posted from http://www.ExcelForum.com Assuming your zip codes a...