Do not want zero values to plot in a chart

If I have a chart -(line chart) for a spreadsheet and there are some cells that are zero as a result of a formula what can I do to not have those zero values plot on the chart?  I have included the condition with the formula to have it display as a blank instead of a zero, but the value of the formual still plots

Thanks for any input

Michelle
0
anonymous (74722)
2/4/2004 2:26:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
267 Views

Similar Articles

[PageSpeed] 18

Hi Michele
try the following in your formula:
=IF(A1=0,NA(),A1)
the #NA reult is not plotted in your chart. If you like you can hide
these errors with conditional formats

Frank


MichelleT wrote:
> If I have a chart -(line chart) for a spreadsheet and there are some
> cells that are zero as a result of a formula what can I do to not
> have those zero values plot on the chart?  I have included the
> condition with the formula to have it display as a blank instead of a
> zero, but the value of the formual still plots.
>
> Thanks for any input.
>
> Michelle


0
frank.kabel (11126)
2/4/2004 2:31:22 PM
>-----Original Message-----
>If I have a chart -(line chart) for a spreadsheet and 
there are some cells that are zero as a result of a 
formula what can I do to not have those zero values plot 
on the chart?  I have included the condition with the 
formula to have it display as a blank instead of a zero, 
but the value of the formual still plots.
>
>Thanks for any input.
>
>Michelle
>.
>

If therre is only one set of data it is easy.

Select the chart and choose Tools, Options, Cart and tick 
the Plot Visible Cells Only radio button.

Then hide the column or rows with zero

Regards
Peter
0
2/4/2004 2:41:39 PM
Reply:

Similar Artilces:

Grabbing a value that is related to another :)
Hello , this is my 1st post & question , but i think it will be easy fo you to answer :) I am also new in excel (i have programmed in asp,php,sql etc but neve in excel...i thought it would be better to look in a forum than in book for my 'prob') so let me explain exactly what i want to do: Sheet 1: i have 1 column with 10 cells (a1:a10) that have a text inside (smal text like DVD-RW) in the 2nd column i have 10 cells (b1:b10) with numbers the number in each cell is related to the text in the cell next to it for example the number in B3 cell is related to the A3 text ... In a...

How do I stop using data source no longer needed for charts?
HOW DO I DELETE AN AUTOMATIC LINK which is no longer there? I have copied a chart from one workbook to create a new workbook. Now it always says this wkbk contains automatic links to information in another workbook. Grace - You can unlink a chart from its source data. This web page describes several ways: http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Grace wrote: > HOW DO I DELETE AN AUTOMATIC LINK which is no longer there? &g...

Dropdown cell containing only the unique values from a column
Hi there, I have been looking for an answer on this one for some time now: How can I create a Dropdown cell containing only the unique values from a column (without using extra cells, but using vba is ceratinly an option). Is it maybe an idea to create a module/macro that can filter a list and return the unique items separated by a semi-colon? thx, Seansan Hi quite difficult without using a helper column. You may use the following function which returns an array of unique values for a specified range (posted by Myrna Larson some time ago): Function MakeArray(rng As Range) As Variant ...

Formatting of phone numbers- want to delete +1
I bought a new Microsoft 7 based computer and restored my .pst files to Office 2007 on it. Now all of my phone numbers are formatted with a +1 in front of the 10 digit number. I have researched this issue for 3 days and have not found a solution that works to delete this +1 which I believe is the country codde. Does anyone have a fix for this? The option not to add the country code to new phone numbers has always been here: Actions > Call Contact > New Call... > Dialing options There are very few if any reasons to do so, however. -- Russ Valentine "carolina...

timeline plotting
I am using excel to create a timeline to compare when different people were born (and died) and when significant events happened. I have worked out how to do the births and deaths part in a bar chart, but so far, the only way I have worked out how to indicate when a significant event happened, I have to do it manually by drawing a line at the approximate date. Is there anyway I can get excel to do it automatically for me so it is more accurate? If it makes it any more clearer I suppose I am trying to do something like a budget analysis - the constant line to be the breakeven and the...

Bell Curve Chart
I have to create a bell curve chart, this is a one time thing. I would rather learn myself, does anyone know how? Hi have a look at http://www.tushar-mehta.com/excel/charts/normal_distribution/index.htm -- Regards Frank Kabel Frankfurt, Germany Lwilson wrote: > I have to create a bell curve chart, this is a one time thing. I > would rather learn myself, does anyone know how? ...

How do I select individual cells for a chart
I am trying to select individual cell values for my chart but the dialogue box will only allow a certain number of characters therefore I cannot select all the cells I want. Help!! One of the most important tasks in making a chart is to properly prepare your data. One data feature that improves charting is having all plotted cells together in a single range. If it's not a dynamic selection of cells, you could select the cells (use ctrl+click to select multiple discontiguous ranges), then paste them into an empty range. If your range is changeable, note that by default Excel charts...

Pivot Chart issues
PIVOT TABLE Create a pivot table showing the highest and lowest total in �94 by territory.. I have a worksheet set up by east south central west territories. I have a column which has the total sales in 1994.. i however cannot get this pivot chart to work right. can anyone help? -- spezialize ------------------------------------------------------------------------ spezialize's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28628 View this thread: http://www.excelforum.com/showthread.php?threadid=483340 Send me a copy to rightemboyo@hotmail.com and describ...

Retrieve value from parameter in bound query
I have a report bound to a query that has a parameter filter on a date field. Is there any way to retrieve the value that the user enters into the parameter dialog box? I've tried accessing the Parameter object from the QueryDef, but the Value property is always empty during Report_Open or Report_Activate (the former occurs before the dialog is presented anyway). I also can't just construct the query SQL with VBA, as the query references multiple queries all with the same named parameter (I like how nested parameters don't require multiple entries by the user if they are ...

csv file drops zeros at beginning of a set of Numbers
I have programs that only import the csv format. When I create the file with a text field using number such as 0101, 0321 if I close and reopen the file it drops the zero. How do I correct this? Lisa Three ways 1) If all the 'numbers' are the same length (4 characters) then via Format>Cells...Number>Custom, type 0000 2) Pre-format the cells to text and it will retain the leading zeroes. 3) rename the file with no extension or a *.txt one and then when you open it Excel will invoke the import wizard, choose delimited, comma and then in the third stage of the wizard, mar...

Can't view chart in MSN Money
Hi, Recently trying out MSN Money but can't able to view charts for all counters. Some can some no history data available. When I tried other provider can view. Please help. Thank you. In microsoft.public.money, MSN Money <MSN Money@discussions.microsoft.com> wrote: > > >Recently trying out MSN Money but can't able to view charts for all >counters. Some can some no history data available. When I tried other >provider can view. Please help. There are different levels of information on various securities. For example, the "pink sheets" stocks will...

Cell's literal value
Using Excel97 I have a data set downloaded from another program. It has several columns filled with text values. I would like to combine this text strings into one column. What is the best way to do this? Is there a way I can refer to the cell's literal value rather than as a reference to the cell and save the literal value into a new cell? Thanks in advance. Andy -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- You can use a formula for...

I don't want outlook as my default
When trying to send an attachment in Word I get a message to change my profile in the Mail icon in the control panel. I would rather my mail to be sent from my hotmail or ISP email but not sure what to do. Tried hotmail but still received error message. Need help! In news:A7AF4CB7-5A4B-4EE4-8034-EAA419DD6C4C@microsoft.com, cinnamon <cinnamon@discussions.microsoft.com> typed: > When trying to send an attachment in Word I get a message to change my > profile in the Mail icon in the control panel. I would rather my > mail to be sent from my hotmail or ISP email but not sur...

Area chart date format
I have an area chart that plots quarterly data. I want to show only years on the x-axis, so I changed the interval to 1 year and the number format to 'Custom yyyy'. However this is shortening my dates, so 2020 becomes 202. Bizarrely, 2021 and 2031 show up in full, but other dates are only shown by their first three digits. Any ideas? I suspect the space allotted for each label isn't quite long enough, so the last character is truncated. I know all digits are the same width, but sometimes, especially when the font size is small, "1" is narrower than the rest, whic...

count values in report
I have a report that I've been constantly working on and have posted several questions to this particular report. I'm at a new stage and seem to be stuck, again! This is what I need: I have a report that will display data using a crosstab query. The text boxes on my report are all unbound because it's an 12 month rolling report. There is immense code behind the report to produce all of my data correctly. Currently my report shows: Item Desc Mth1 - Mth12 Totals Everything displays correctly, however I want to add logic in my report to add data to my new field cnt. I want this ...

creating a chart with auto fillin of missing date points?
I have dates in column A and a value in B. I want to create a line graph showing values across ALL dates using the points I have actual dates for as the end points for each line segment. How do I get Excel 2003 to do that? Thanks! Select the chart Choose Tools>Options On the Chart tab, for 'Plot empty cells as', select Interpolated Click OK Rob wrote: > I have dates in column A and a value in B. I want to create a line graph > showing values across ALL dates using the points I have actual dates for as > the end points for each line segment. How do I get Excel ...

how to compare formula's values?
I have a few columns which contain formulas. These yield results that I want to compare dynamically, cell by cell, not by copy and pasting, but the identity operator, "=", does not recognize any of these cells as equal even where their values are the same because the cells actually contain various and different functions. How can you compare the value of one cell to the value of another, where both values are results of the formulas, without programming? Can Excel do this? The data (simplified) Column A contains a binary value, either "w" or "q". Column B con...

How to add a name column to scatter chart's data label?
I have the following data and want to create a scatter chart. I am using either 2003 or 2007. Person X1 X2 a 2 4 b 8 2 c 9 3 d 1 5 e 8.5 1 (The range is =3DSheet1!$A$1:$C$6) I want to put the values (a, b, c, d, e) from the Person column next to the data point. For example, d for the (1,5). The objective is to put the a, b, c on the data point. In my last attempt, I set up the series value for X in an unconventional way =96 I included the Person column in it - "=3DSheet1!$A $2:$B$6" But the problem is that X value becomes (a, 2), (...

Reading csv file and parsing comma delimited values in Array
I am reading a .CSV file using FileSystemObject, and one of my field (std_num ) which has null values for the first 15 rows but have a number value after 16 so how can I read that correctly? Dim myarray() As String Dim temp_str As String Dim SET1 As String Dim STD_num As Integer temp_str = "A1,NTC,H1,Unknown,Undetermine" ' First 15 rows temp_str = "A1,NTC,H1,Unknown,20.3, 25.34" ' 16th row ...

Expressing time values
I'm running into a problem tryin to express time values properly. What I want to achieve seems simple: I have various time values and I want to add them and express the result in minutes and seconds. For example, in A1, I have 00:00:46, in B1 I have 00:00:20, in A2 I have :00:00:14, in B2 I have 00:00:19, etc. I want to add the value in A1 to each value in column B (separately), then add A2 to each value in Column B, and repeat for each value I have in column A. I know how to do everything except get Excel to treat my times as absolute time values instead of time of day. When I add ...

I am wanting to start a Good News Newspaper. How do I start?
Please provide me with any help and/or advice for starting up a Newspaper. Thanks and God bless. Creating a publication http://office.microsoft.com/en-us/publisher/CH062559031033.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "AMhere4U" <AMhere4U@discussions.microsoft.com> wrote in message news:0AFFE797-46C9-4864-8CC5-ECC1FF895B7F@microsoft.com... > Please provide me with any help and/or advice for starting up a Newspaper. > > Thanks and God bless. > > Please provide me with any help and/or...

Problem with Chart Title Formatting
I have what I thought would be a very simple task. I want the first line of a Chart title to be a large font size, while making the second line a smaller font size. I wrote the VBA code listed below. I thought all I would need to do is use the Characters property to change each part of the title. I did so and everything seemed to work OK until I decided to try and format the 2nd line using italics. By inserting the italics line (which is currently commented out), excel chages the font size to the same size as the 1st line (20 in this case) Does anybody see an obvious mistake? Coul...

2 or more text values related to the same text value
I have a database in which 2 or more text values (names) from one table may be related to the same singluar text value from another table (a title). When I query by title to find the names associated with that title, if there is more than one name associated with that title it returns two rows of results, with the same exact details except for the two names. Is there a way to design a report such that if 2 names will be returned given one title, to show only one set of results with both names together? I hope this question makes sense. Thank you, Debbie "Debbie S." <Deb...

combination chart #8
How can I get a combo chart with 3 series - one as a vertical bar/column, and 2 series as lines, with 2 y axes? I only seem to be able to get one series in line format, while the first two insist on viewing as vertical bars, regardless of whether they are assigned to the primary or secondary y axis. Right click each series and set the chart type property of the series seperately. alas wrote: > How can I get a combo chart with 3 series - one as a vertical > bar/column, and 2 series as lines, with 2 y axes? > I only seem to be able to get one series in line format, while the > fir...

QueryTable Becomes Values after Refresh
In Excel 2003 I have several sheets with QueryTables that become values after they are refreshed, meaning they can no longer be refreshed. If I look at the defined names for the sheet the refers to value looks like this "= Dataquery!$A$1:$Q$22456" it has spaces after the equals sign. The sheet name does not contain spaces. Is there a cure for this? Regards, Don Buchanan ...