skip cells with zero values in chart (cells not empty)

I don't want to display zeros in my line chart.  The cells are not empty - 
they contain a formula.  I have used a simple if statement to hide a zero 
value - however the chart is still showing the datapoint as zero.
0
ifox (1)
5/29/2007 2:22:01 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
712 Views

Similar Articles

[PageSpeed] 28

What does your IF statement produce?  If it gives an empty string "", then 
it will be treated as zero.  Change the "" to NA().  If you want to hide the 
NA() values in the worksheet, you can do that by conditional formatting.
-- 
David Biddulph

"jhall@ifox" <jhall@ifox@discussions.microsoft.com> wrote in message 
news:3DFD839D-40AD-4696-B4FD-F3D263EB1713@microsoft.com...
>I don't want to display zeros in my line chart.  The cells are not empty -
> they contain a formula.  I have used a simple if statement to hide a zero
> value - however the chart is still showing the datapoint as zero. 


0
David
5/29/2007 3:59:03 PM
I have an IF statement
=IF('Resource Availability New'!D10<>"", 'Resource Availability New'!D10, 
0)/'L:\Project Control\Actuals\[Monthly Hours.xls]Sheet1'!N$2 

but I don't want it to plot the 0 values but the result returned from the 
statement is used further down the sheet in a sum formula to which I need to 
see the result instead of NA how can I do that?

Beverly

"David Biddulph" wrote:

> What does your IF statement produce?  If it gives an empty string "", then 
> it will be treated as zero.  Change the "" to NA().  If you want to hide the 
> NA() values in the worksheet, you can do that by conditional formatting.
> -- 
> David Biddulph
> 
> "jhall@ifox" <jhall@ifox@discussions.microsoft.com> wrote in message 
> news:3DFD839D-40AD-4696-B4FD-F3D263EB1713@microsoft.com...
> >I don't want to display zeros in my line chart.  The cells are not empty -
> > they contain a formula.  I have used a simple if statement to hide a zero
> > value - however the chart is still showing the datapoint as zero. 
> 
> 
> 
0
6/1/2009 4:02:06 PM
Set up two ranges in the worksheet. We're well past the age of COBOL, where 
we scrounged for every byte of file size.

One range keeps zeros for subsequent processing, and the other uses NA() for 
charting. Since both ranges are linked to the original source, they should 
stay synchronized.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Don't miss it! Only two weeks away!

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______


"Beverly Darvill" <beverlypdarvill@eaton.com> wrote in message 
news:91E13427-7BAE-4274-BC94-469582D3893B@microsoft.com...
>I have an IF statement
> =IF('Resource Availability New'!D10<>"", 'Resource Availability New'!D10,
> 0)/'L:\Project Control\Actuals\[Monthly Hours.xls]Sheet1'!N$2
>
> but I don't want it to plot the 0 values but the result returned from the
> statement is used further down the sheet in a sum formula to which I need 
> to
> see the result instead of NA how can I do that?
>
> Beverly
>
> "David Biddulph" wrote:
>
>> What does your IF statement produce?  If it gives an empty string "", 
>> then
>> it will be treated as zero.  Change the "" to NA().  If you want to hide 
>> the
>> NA() values in the worksheet, you can do that by conditional formatting.
>> -- 
>> David Biddulph
>>
>> "jhall@ifox" <jhall@ifox@discussions.microsoft.com> wrote in message
>> news:3DFD839D-40AD-4696-B4FD-F3D263EB1713@microsoft.com...
>> >I don't want to display zeros in my line chart.  The cells are not 
>> >empty -
>> > they contain a formula.  I have used a simple if statement to hide a 
>> > zero
>> > value - however the chart is still showing the datapoint as zero.
>>
>>
>> 


0
jonxlmvpNO (4558)
6/2/2009 1:11:20 PM
Reply:

Similar Artilces:

Easy way to reference a specific cell in a new worksheet
This project is for week over week sales. A new worksheet is added every week. The current week's sales are in Column E for every salesperson and the previous week's sales are in Column F. I would like the Column F to automatically reference Column E on the previous worksheet every time a new worksheet is added by copying the previous worksheet. It's not that hard to work around it now, I would just like to save time and automate the process. Is this possible? Let me know if what I am attempting to do does not make sense. TIA Hi without VBA this is not possible -- ...

Conditional chart formating
I need to format my charts so the color of the data points change depending on the value amount. My tabels are already set up and cannot be change or have anything new added due to the amount and delicacy of data. Is there a macro I can use. Perhaps a formula I can put somewhere? Hi, Ed Ferrero has a vba example. It's for a column chart but the code will be very similar. Instead of interior fill you will be doing marker stuff. eg: .MarkerBackgroundColorIndex = 50 .MarkerForegroundColorIndex = 50 Conditional Chart Formatting example. http://www.edferrero.com/charti...

adding a skip pattern
Does MS Access refer to "skip patterns" by a different name? Is there any relatively simple way to include skip patterns in an Access form? Example: if the answer to question 1 = no, then skip to question 6. In a previous post, the reply below was given, but I must confess that I am not so swift to fully understand. Can someone provide a bit more detail: "You'd used the afterupdate event on a given control then use the setfocus method to move the cursor to another control and use the Enabled control property to control whether or not other controls remain active or...

Validation cell in UDF?
I want to use a user-defined function as part of a cell validation routine. Is there a way from within the function to determine what cell is being validated? Does anyone have examples of something similar? Thanks, -- Greg Greg, Try Application.Caller.Address -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Greg Allen" <gregory.allen@sierraatlantic.com> wrote in message news:e1zFRUYfIHA.1204@TK2MSFTNGP03.phx.gbl... >I want to use a user-defined function as part of a cell validation routine. > Is there a way ...

changing the cell information
Hi I have files I tried to change the cell information to another cel that have to be the blank for a row that contains director name in cel A and for the movie titles I have to have director names instead of th title names.Please help me, the data in cell B sholuld have to be, A 1 D:B.Jane 2 T:Back to the future 3 T:Aliens 4 T:X-files 5 D:Adam Mathew 6 T:Core 7 T:Titanic B 1 (empty) 2 D:B.Jane 3 D:B.Jane 4 D:B.Jane 5 (empty) 6 D:Adam Mathew 7 D:Adam Mathew thanks -- Message posted from http://www.ExcelForum.com ...

use IF to return a truly blank cell
I often use if statements to return empty cells, for example: =IF(a1=0,"ERROR","") The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents. Is there a way to designate, in such a formula as above, to return a TRULY empty cell? Thanks -- cwinters ------------------------------------------------------------------------ cwinters's Profile: ht...

Skip if empty
Everyone, I'm sure this is an easy fix, but it is just beyond me. Hopefully, one of you is going to make me feel stupid. I have an excel 2003 workbook that is a template for a merge document in Word. I need it to be able to handle 10 different sets of information starting in column B and ending in column J. I have all the information for the Word document in sheet1 of the workbook, and I'm going to merge from sheet2. I'm not always going to have all 10 of the columns filled with information. so I need to be able to skip code when I run out of records so that when I merge them...

Cell contents displayed on toolbar
I lost the view of the cell contents on the toolbar above the worksheet and don't know how to get it back. Can someone tell me how to do this? It is driving me crazy This is my first time and I don't know how to receive feedback so I posted my question again with my e-mail. Hi Menu View > Formula Bar (or something similar, I have a translated version here) -- HTH. Best wishes Harald Followup to newsgroup only please "Walt" <wlamon@wyvernltd.com> skrev i melding news:099a01c3b037$9466c9c0$a501280a@phx.gbl... > I lost the view of the cell contents on the too...

How do I average a range of cells when one cell contains #N/A
I perform a lookup where the results could populate one cell or as many as 15 cells with number results. The cells that do not result in numbers have #N/A. I want to average the fifteen cells but only the cells with numbers. How do I get it to ignore the #N/A when performing the average? =AVERAGE(IF(NOT(ISNA(A1:A15 )),A1:A15)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "hongkonglt" <hongkonglt@discussions.microsoft.com> wrote in message news:5EF99B0B-230F-47FE-A795-56C4E3B84C46@microsoft.com... > I perform a lookup where the re...

Moving from cell to cell is very slow!!
in a machine with all office updates and xp sp2, when using excell (2000) if you move from cell to cell either to edit data or just to format a group of cells it takes about a minute. even in a new empty excel spread sheet. is there a setting or maybe one of the updates cause the program to do a search or something to delay the switching between cells? any help would be appreciated. "Skoal" <Skoal@discussions.microsoft.com> wrote in message news:E1585839-B4AC-4EBB-AA13-222459E306E8@microsoft.com... > in a machine with all office updates and xp sp2, when using excell (...

Cursor over data point on Excel Chart
For an x-y graph in Excel where the x values are dates and the y values are stock data, I have formatted it so I see the dates along the horizontal x axis. All good so far, but when I place my cursor pointer over a data point, it flashes up the numeric value for the date, then the price point like this: (37934, 1254) I wonder if there is a formatting technique to force it to show the date in date format. It would be easier for me to identify a particular date since I space out the date labels along the axis and can't determine with precision the exact date of a point I've placed th...

Getting a cell address and using it
I have a spreadsheet containing production information. I want to b able to search by date (can do this using VLOOKUP), and extrac information from the same row as that date. However, I also want to be able to extract the previous 10 entries a well, for comparison purposes. ie I enter a date and find that it is in B95, and the piece of info want is in C95. I also want to be able to pull out C86-94 and place i all into a new sheet. I'd be grateful for any help offered. Thank -- dudar ----------------------------------------------------------------------- dudara's Profile: http://w...

Changing Values from a form based on a union query
I have created four separate input forms for sales reps to enter information for an admin person. I want to keep the forms (and the tables associated with them separate). Each of these individual forms is based on a query such as LetterRequestJoeQuery which is then based on the table LetterRequestJoe. I then created a union query that combines all four individual queries and then a form based on this union query. The idea was to give an admin person a view of all entries and to be able to edit them. But I have discovered that you cannot change the information on a form based on a uni...

If cell equals, give total of another cell #2
I have a formula that looks likes this: =IF(Historical!I10="X","Historical!A10","") If true, it returns "Historical!A10" How do I write this formula to give me the value of A10 and not the name of the cell? -- Lori =IF(Historical!I10="X",Historical!A10,"") Vaya con Dios, Chuck, CABGx3 "Lori Burton" wrote: > I have a formula that looks likes this: > > =IF(Historical!I10="X","Historical!A10","") > > If true, it returns "Historical!A10" > > How do I writ...

Combining text and cell value to create a sentence
In Excel 2000 I have something like the following: A B C D Inc/Dec 1 April May YTD Goal 2 $75 $50 $10 $200 decreased I'd like to form a sentence such as Recoveries decreased from May to April. I've tried "="Recoveries " &Text(E2) " from" &Text(B1) "to " &Text(A1) But keep getting "too many arguments". Any suggestions, or is this possible? Simply =Recoveries " & E2 &" from "& B1 & " to " & A1 the T...

How to create a formula that results in a blank cell if required
I have a spreadsheet that is to be populated during the course of the year. I have a column which totals the number of days worked on a particular task and have copied the formula down the sheet for the number of entries required. Obviously, as the sheet is currently unpopulated, I do not want a whole load of zeroes listed in this column when the other cells in the sheet (which are used in the calculation) are currently empty. ie) what I have is (as a simple example) A B C ------------------------------- Hours Total Hrs Total Days 37 37 5 ...

2 protected cells
We suddenly have 2 more protected cells on the 12th page of our book. I have tried all kinds of things to drop the protection of these 2 cells. I have tried all kinds of things & looked @ help but no banana. Help. TIA -- _________ MMAP HQ Unprotect the sheet. Select those 2 cells. Click on Format - Cells - Protection tab. UnCheck the Locked box. HTH Otto "mmaphq" <mmaphq@mmap.org> wrote in message news:%23FL3ClDMHHA.420@TK2MSFTNGP06.phx.gbl... > We suddenly have 2 more protected cells on the 12th page of our book. I > have > tried all kinds of things to ...

Conditional Cell protection #2
I would like to blank and protect certain cells depending on a value in one cell (A1). If, e.g. the value in cell A1 is 6, then the users are only allowed to enter values in the first 6 weeks for that year (=first 6 columns in the table). In order to avoid problems I would like to erase all existing data in columns 7--> and then lock these cells. Any ideas how to do this? ...

How do I copy a cell + it's formula from one workbook to another?
For reasons i won't go into, i have to transfer hundreds of cells from one workbook to another. each one of these cells contains a formula ex: "=SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$D$2:$D$573="X",Production!$Q$2:$Q$573,0))),0))-SUM(IF(Production!$I$2:$I$573="HM37",IF(Production!$H$2:$H$573="AP",IF(Production!$S$2:$S$573="36H",IF(Production!$E$2:$E$573="X",Production!$Q$2:$Q$573,0))),0))" the new workbook is an identical...

file skipped
HI,All The following file is being skipped, should I be concerned..???? Backup - \\USFIN2KEX1\D: Data Unable to open the item \\USFIN2KEX1\D:\exchsrvr\imcdata\queue.dat - skipped. thanks Do online, exchange aware backups of the store. Do not do flat-file backups of the exchsrvr dirs and you wont see that error. :) On Sun, 20 Jun 2004 10:35:33 -0400, "Darren D" <Darren@somewhere.com> wrote: >HI,All >The following file is being skipped, should I be concerned..???? >Backup - \\USFIN2KEX1\D: Data >Unable to open the item \\USFIN2KEX1\D:\exchsrvr\imcdata\queue.da...

VBA, total number of cells with text
Hi all, I want to put in L1 the total number (in Dutch "totaal aantal") of cells in B1 to B200 and H1 tot H200 that contain text (there can be nothing else in columns B and H). I tried Range("L1").Select ActiveCell.FormulaR1C1 = _ "=""totaal aantal = ""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")" but it results in aan errormessage (something like "error caused by the application" (roughly translated from Dutch). Why? Should I use specialcells in ste...

Chart width as a variable
I want to create a chart in which there are two numerical variables, on would be height and one would be width. Could someone please let me kno how to do this? Thanks a lot -- ErictheFerri ----------------------------------------------------------------------- ErictheFerrit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2522 View this thread: http://www.excelforum.com/showthread.php?threadid=38714 Eric - Put the desired horizontal variable (width?) in one column, and the corresponding vertical variable (height?) into the next column. Select this data, star...

Prevent equation from skipping data when referencing import data?
I am referencing data that is being imported from access in table on a seperate worksheet. When the data is refreshed, only the last new entry is placed in my seperate table, not all of them. How do I fix this? ...

Hide column with locked cells
I have a worksheed that a users input data useing data validation. There are cloumns on the sheet that display data based on selections made in adjacent columns. The cells in these 'display column" contain formulas that I need to protect but I also want to enable the user to hide the display column to decluter if they need to. As best I can teel you cannot hide/unhide a column with locked cells. Is there a method or work around to do this? BTW Im using Excel 2007 On Jun 12, 2:49=A0pm, Robert H <robert.hatc...@l-3com.com> wrote: > I have a worksheed that a users input dat...

Lines are hidden on line chart
Is there any way to differentiate lines that are on top of each other? That is, they have similar data at some point on the chart. I've got a time-scale line chart - so it's a step chart and some of the data for the two lines I've got are the same. On the chart, all you see is one line - I'm just trying to determine if there is a way to differentiate them ... thanks. Coinciding lines are overlapping by default. Line widths are limited in Excel (will be btter in Excel 2007) and this is likely not a sensible option. Maybe you can use clearly discriminating dots (a.g...