Displaying zeros as blanks

I know this is an old question. Is there a way to display 
zero fields as blanks? I know I can do this with and If
(x=0,""...etc,  but then if this cell is used in a 
subsequent formula, such as a sum, I get a VALUE error.

Thanks,
Pradhan
0
pradhan (1)
7/5/2004 12:15:09 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
448 Views

Similar Articles

[PageSpeed] 28

Pradhan,

Select:
Tools \ Options \ View Tab
Unselect <Zero Values>

Regards
Jacques
>-----Original Message-----
>I know this is an old question. Is there a way to display 
>zero fields as blanks? I know I can do this with and If
>(x=0,""...etc,  but then if this cell is used in a 
>subsequent formula, such as a sum, I get a VALUE error.
>
>Thanks,
>Pradhan
>.
>
0
anonymous (74722)
7/5/2004 1:28:11 PM
You can also wrap a SUM formula (or most any other formula) in an "IF", to
prevent zeros from being displayed.........

=IF(SUM(A1:A10)=0,"",SUM(A1:a10)

Vaya con Dios,
Chuck, CABGx3


"Jacques Brun" <anonymous@discussions.microsoft.com> wrote in message
news:25fcd01c46293$eaac2000$a601280a@phx.gbl...
> Pradhan,
>
> Select:
> Tools \ Options \ View Tab
> Unselect <Zero Values>
>
> Regards
> Jacques
> >-----Original Message-----
> >I know this is an old question. Is there a way to display
> >zero fields as blanks? I know I can do this with and If
> >(x=0,""...etc,  but then if this cell is used in a
> >subsequent formula, such as a sum, I get a VALUE error.
> >
> >Thanks,
> >Pradhan
> >.
> >


0
croberts (1377)
7/5/2004 3:02:33 PM
<<"but then if this cell is used in a subsequent formula, such as a sum, I
get a VALUE error">>

The SUM() function will still work with ( "" ) returned in a cell, included
in the sum range, from a formula.
You might have something else wrong to produce the #VALUE! error.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pradhan Balter" <pradhan@retlabgraphics.com> wrote in message
news:2674b01c46289$b7611110$a501280a@phx.gbl...
> I know this is an old question. Is there a way to display
> zero fields as blanks? I know I can do this with and If
> (x=0,""...etc,  but then if this cell is used in a
> subsequent formula, such as a sum, I get a VALUE error.
>
> Thanks,
> Pradhan

0
ragdyer1 (4060)
7/5/2004 5:46:02 PM
Apart from using Tools|Options|View and unchecking 'zero values', which
affects all cells on the worksheet, you can limit the effect to specific
cells with a custom number format. To do this, you could start with any of
the inbuilt number formats that have both +ve and -ve formats and simply add
a semi-colon (ie ';') to the end. Or you could roll your own, defining your
own +ve and -ve formats and adding a semi-colon (ie ';') to the end.

Cheers


"Pradhan Balter" <pradhan@retlabgraphics.com> wrote in message
news:2674b01c46289$b7611110$a501280a@phx.gbl...
> I know this is an old question. Is there a way to display
> zero fields as blanks? I know I can do this with and If
> (x=0,""...etc,  but then if this cell is used in a
> subsequent formula, such as a sum, I get a VALUE error.
>
> Thanks,
> Pradhan


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 2/07/2004


0
macropod (18)
7/6/2004 11:14:28 AM
Sorry, but I don't understand your terminology . . . can
you elaborate?  What's +ve and -ve?
>-----Original Message-----
>Apart from using Tools|Options|View and unchecking 'zero
values', which
>affects all cells on the worksheet, you can limit the
effect to specific
>cells with a custom number format. To do this, you could
start with any of
>the inbuilt number formats that have both +ve and -ve
formats and simply add
>a semi-colon (ie ';') to the end. Or you could roll your
own, defining your
>own +ve and -ve formats and adding a semi-colon (ie ';')
to the end.
>
>Cheers
>
>
>"Pradhan Balter" <pradhan@retlabgraphics.com> wrote in message
>news:2674b01c46289$b7611110$a501280a@phx.gbl...
>> I know this is an old question. Is there a way to display
>> zero fields as blanks? I know I can do this with and If
>> (x=0,""...etc,  but then if this cell is used in a
>> subsequent formula, such as a sum, I get a VALUE error.
>>
>> Thanks,
>> Pradhan
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.714 / Virus Database: 470 - Release Date:
2/07/2004
>
>
>.
>
0
anonymous (74722)
7/7/2004 7:55:37 PM
+ve = positive
-ve = negative


"SidBord" <anonymous@discussions.microsoft.com> wrote in message
news:27f1301c4645c$5f3fc4c0$a601280a@phx.gbl...
> Sorry, but I don't understand your terminology . . . can
> you elaborate?  What's +ve and -ve?
> >-----Original Message-----
> >Apart from using Tools|Options|View and unchecking 'zero
> values', which
> >affects all cells on the worksheet, you can limit the
> effect to specific
> >cells with a custom number format. To do this, you could
> start with any of
> >the inbuilt number formats that have both +ve and -ve
> formats and simply add
> >a semi-colon (ie ';') to the end. Or you could roll your
> own, defining your
> >own +ve and -ve formats and adding a semi-colon (ie ';')
> to the end.
> >
> >Cheers
> >
> >
> >"Pradhan Balter" <pradhan@retlabgraphics.com> wrote in message
> >news:2674b01c46289$b7611110$a501280a@phx.gbl...
> >> I know this is an old question. Is there a way to display
> >> zero fields as blanks? I know I can do this with and If
> >> (x=0,""...etc,  but then if this cell is used in a
> >> subsequent formula, such as a sum, I get a VALUE error.
> >>
> >> Thanks,
> >> Pradhan
> >
> >
> >---
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.714 / Virus Database: 470 - Release Date:
> 2/07/2004
> >
> >
> >.
> >


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 2/07/2004


0
macropod (18)
7/9/2004 10:30:34 AM
Reply:

Similar Artilces:

Blanks as a condition of a statement
How is it possible to do a function based on if a cell is blank or not. Such as the following for cell a1: IF b1 is not blank then add b1-c1 else don't do anything in a1. I have tried but can't get anything to go. Thanks in advance, Lee =IF(B1<>"",B1-C1,"") -- HTH RP (remove nothere from the email address if mailing direct) <Stuck on Blanks> wrote in message news:4383062f.5bc.41@news2... > How is it possible to do a function based on if a cell is > blank or not. Such as the following for cell a1: > IF b1 is not blank then add b1-c1 else d...

Displaying a Meaningful Account Name
Trying 2006 Deluxe Trial - I'm wondering if Money can be set up to display the Account Number or Abbreviation in the Account List instead of the Account Name ? I have five "sub-savings" accounts at my bank to allow me to save for different goals (car repairs, tuition, Christmas, etc.). When I did the initial account download from my bank, Money assigned Account Names that don't correspond with the account numbers my bank uses. For example, Money renamed my "S2.3" account to "Savings-#1". As you can imagine, I have enough accounts that it will be ...

Receipt display too crowded
When I use the net display side by side with an image the font size for the total line crowds off the last digit. This is a problem if they are writing a check. The display is set at 1024x768 so the receipt area is 512x768 verticaly oriented. ...

add a column to be displayed
when i open my address book it lists - name, phone and e- mail for my contacts...how do i add the column for business name? You cannot configure the Outlook Address Book display. -- Russ Valentine [MVP-Outlook] "westy" <westy523@comcast.net> wrote in message news:03bb01c36832$efe6a6f0$a601280a@phx.gbl... > when i open my address book it lists - name, phone and e- > mail for my contacts...how do i add the column for > business name? but on my other computer it does have that column? >-----Original Message----- >You cannot configure the Outlook Address Book d...

Mail not automatically displayed in Outlook Inbox
Just Migrated to Exchange 2003. New unread messages do not display in the Inbox unless the user selects another Outlook (XP) folder (Contacts, etc) first. Any idea on how to fix this problem? Everything else runs fine. Thank you. "Melissa" <melissa@erntech.com> wrote: >Just Migrated to Exchange 2003. New unread messages do >not display in the Inbox unless the user selects another >Outlook (XP) folder (Contacts, etc) first. Any idea on >how to fix this problem? Everything else runs fine. You're having problems with UDP. The Exchange server sends a...

Excel 2003 doesn't display the proper equation with trendlines
Excel 2003 is not displaying the proper equation if you insert a trendline to data and click on the "display equation" check box. If you use the equation to generate "y" from the exact "x" values that you began with, you get a completely different curve than the original trendline displayed on the graph. For example: Use the "log" function in a worksheet to plot y=log(x) from x=1 to 100, plot the data, and fit a 2nd order polynomial to this data, clicking on "display equation". It won't be a good fit, but this is beside the point....

Charting a full year without zeros
Help! I want to chart results for the coming year where the data feeds in month-by-month. The problem is that the chart pluges to zero for the future months. How do I terminate the data at the current point (January to September) without having to reset the data ranges every month? Thanks -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25096 View this thread: http://www.excelforum.com/showthread.php?threadid=470385 Rob - Set up a dynamic chart, using dynam...

A one page report that prints an additional blank page
Hi I have a report that is 1 page and this is how it displays in print preview, however when I print I get a get the report and a blank page! Any suggestions on why this is. (I have not set the database up so it is possible that it may be set like this - if so how do I undo this). Hope you can help! Many thanks Emma -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200708/1 On Tue, 21 Aug 2007 17:39:22 GMT, Emlou85 via AccessMonster.com wrote: > Hi I have a report that is 1 page and this is how it displays in print > preview, ho...

How would I display a value in a cell without entering formula in.
Hi you may post your question as body of your message :-) -- Regards Frank Kabel Frankfurt, Germany "adame9176" <adame9176@discussions.microsoft.com> schrieb im Newsbeitrag news:A324F745-A3D2-491A-AF56-59F5C9727551@microsoft.com... > If you don't type it in, you would use VBA code to place it there. Gord Dibben Excel MVP On Thu, 7 Oct 2004 00:41:02 -0700, adame9176 <adame9176@discussions.microsoft.com> wrote: ...

Easiest way to print AND display data on screen.
I've got a project that involves displaying and printing data. All the data is readonly in Access 2007. All I have to do is display the data using various charts and lists. I'm trying to decide whether to use Access or Excel to display the data. The thing I like about Excel is that I can display the data on the screen and it can also be printed. It seems to me that if I do it in Access I will have to do double the work. I'll have to display charts on a form to show it on the screen but create a report to print it. Is this correct? Is there a way to print a form in Access 20...

FRx 6.7: Accounts Not Displaying In Row File
I have successfully created Financial Statements for two of our three companies. However, I’m having a problem with the third one. In creating the row file, I specify a range from account 30000 through 99999. Most accounts show up in the list. Some don’t. The following account string will be my example of an existing account, with a balance, that does not show up in the row file: 00000-30035-00000 (30035 is the main account) If I manually insert a row into the row file, type in the description, and click the down arrow for the GL Link, the account does not display in the drop down. Ho...

How to display interested Access's fields on the screen or print it out.
I have create many tables in Access database, and I want to print al fields in each table with some interested attributes such as fiel width, field type, field comments, etc for browsing . Although Acces supports this details in the window properties, but It shows only field per page that not convenient -- SanguanKPosted from - http://www.officehelp.i "SanguanK" <SanguanK.1wfmj1@NoSpamPleaze.com> wrote in message news:SanguanK.1wfmj1@NoSpamPleaze.com... > > I have create many tables in Access database, and I want to print all > fields in each table with some inte...

Turn off Zero values at the end of the chart
Please can someone tell me how to stop my charts plotting zero values at the end in cases where the chart runs for a longer period than my data? For example, my chart total date range is say Jan 05 to Dec 05, and I have several lines to plot, the longest one extending for the whole 12 month period, but others for shorter periods from say Jan to Jun 05, and Feb to Nov 05 etc. At the moment the way the source data is set up, is that for the line item that ends in June, there are zero values in the column for Jul to Dec 05 and my chart is plotting these zero values, however I want my line to ju...

Skip Over Blanks
I am trying to create an array that will show missed payments. I have it all figured out except it shows a zero on companys that have payed, and I would like to find a way to have it not even show a zero for those cells, and skip over them and keep listing the other companys. ...

Add gridlines (NOT print or display) for Excel 2007
I know how to add gridlines for Excel 2003. However, someone in their infinite wisdom (yeah?!) decided to make Office 2007 products difficult to use, hard to find icons, unable to set up personal toolbars with favorite icons, etc. I can't find how to ADD gridlines. Through a search of Help, they show you how to display/hide/print/not print, etc. but NOT how to add them. I copied part of a spreadsheet from a 2003 Excel file. BUT, when I add new rows, none of the new rows have gridlines. I can't find how to add thjem. Hi Press the Round Office button>Excel Options>Adv...

Display Name that E-Mail Recipients See
re: Outlook 2003 When I send e-mail to people, in the FROM FIELD, they only see my complete e-mail address. The e-mail address is NOT preceeded by a "friendly" display name, as one might expect. As per MS Office Online Assistance at http://office.microsoft.com/en-us/assistance/HA011507601033.aspx I have ensured that I have the friendly name I want showing in the YOUR NAME field under USER INFORMATION under TOOLS|E-MAIL ACCOUNTS. This does not solve the problem. I can temporarily solve it, per individual e-mail message, by selecting HAVE REPLIES SENT TO under MESSAGE OPTIONS a...

Quote for IWM returns zero
Using Money or Money Central to acquire a quote for iShares Russell 2000 Index (IWM) returns Zero. Others, like Yahoo Finance quote, return latest trade information. Why? Ken Further checking this morning I discover all my other ISHARES are working okay. Like ISHARE RUS MC INDX (IWR) is okay and iShares S&P 500 Index (IVV) is okay. Of all the ISHARES I have, iShares Russell 2000 Index (IWM) is the only one that is failing. As I said before iShares Russell 2000 Index (IWM) returns a zero, which of course tells Money to reduce this investment to zero. Was quite a shock to see this in...

avg formula and blank cells
What is the proper way when using the AVG function with cells formatted as numbers, to fill a blank cell so it won't make the avg lower, but rather be ignored in the AVG function? Thanks! --Randy Starkey __________ Information from ESET NOD32 Antivirus, version of virus signature database 4080 (20090515) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Excel ignores blanks cells when averaging. If you mean ignore cells uncertain a certain value. In Excel 2007, use AverageIf. In earlier versions you need to construct the formula using SumIf ...

Appointments "off scale" at top & bottom of display
In calendar, the Day View, when there are appointments earlier or later than the times displayed on the screen, the only indication is a tiny gray triangle on the far right side of the last visible appointment. Is there some way to make it more obvious that there are appointments on the list you cannot see? Thank you. You can't make the triangle more obvious but can use a larger time scale so more time is visible on screen. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Ou...

SOP Invoice not displayed in Inventory Transaction Enquiry window
Hi all I have a SOP Invoice with one SOP line. Its Posted. Its in the SOP history table. Nothing strange about it. Looks fine. I open Enquiry >> Inventory >> Item Transaction window...and type in the item number (thats on the SOP Invoice Line above)...and the Invoice is not listed... This means that if I add up the quantities on the transactions listed in the Item Transaction Enquiry window...they do not equal the quantity on hand value as per the Item Enquiry window I checked the IV10200 and IV00102 tables and the item movement from the invoice is in both. I ran the ...

Text box displaying result from a query
Hi On a form the user selects a part No in a combo box, else where on that form I want to use related fields to show information. I have made a query that filters on the combo box and the results are correct but when I select the new text box in input in the control source "=[Qu-JobSelectFrmCompAnnalisi]![Componet short Name]" all I get displayed is #Name? What am I doing wrong to get it to display the data I want? I am using access 97 , any advise is welcome. Thanks Jon Jon, If your query only returns one record, and is not bound to your form, then try: =DLOOKUP("...

Display value on a form dynamically
I have a form that needs to display values from an array on the form; how do I achieve the following? If item count in array is < 20 then print Val1 Val2 Val3 Val4 Val5 Val6 and so on If items in array is > 20 then print Val1 Val2 Val3 Val4 Val5 Val6 Val7 Val8 Val9 and so on The following code works fine and prints values in two columns butI need to modified it to print in three columns if nItemsCount > 20 { for (i = 1; i <= nItemsCount; i++) { if (i % 2) { nX = nX * 25; ...

Displaying Fractions
When I enter a fraction such as 3/6 or 8/12, excel automatically simplifies the fraction to read 1/2 or 2/3. How do I display the original fraction, ie, an unsimplified version that I can use in formulas throughout the spreadsheet? Thanks, Kim Out of curiosity - since the values are the same what difference does it make if you're just going to use them in formulas? 3/6 is the same as 1/2 mathematically. -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com "Kim" <Kim@discussions.microsoft.com> wrote in...

How can I choose non consecutive column/row data to display a char
Could someone tell me how I could choose data in say, column A & Column C to be displayed on the same chart ? I am dealing with the same header (months) but different data fields in columns B to Z. I would like to display charts by selecting column A & then each of the subsequent columns individually, rather that pasting the data of A after each column. ...

How not to display any pop up message for macro?
When macro imports an external links into sheets, it pops up a message is about the pasting area is larger then worksheet for confirmation, does anyone have any suggestions on how not to display any pop up message for running macro? Thanks in advance for any suggestions qryConnect = "URL;http://www.mmacycles.com" Set anySheet = Sheets("MMA") With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _ :=anySheet.Range("$A$1")) .Name = "o070104" .FieldNames = True .RowNumbers = False .Fil...