Formula returns empty; chart plots zero; I want to skip

I am using formulas to populate the source data area for a chart. I am using 
percentages, so when the total (divide by) is zero, I want to have an empty 
cell. This will distinguish it from a real zero where the numerator is zero.
I am doing the division in another cell as =IF A2=0,"",A1/A2. In the cell I 
am plotting from (source data), I am using =Sheet1!A3, where Sheet1!A3 is 
where the previous formula is.
When I look at the cell in the source data, it is empty. If I copy and paste 
special values, it still looks empty, but plots a zero.
If I delete the cell, then it leaves a gap the way I have it set up in the 
Options.
How can I get this cell with the formula to skip in the chart instead of 
plotting a zero?
0
bluegar (1)
2/6/2007 11:43:00 PM
excel.charting 18370 articles. 0 followers. Follow

6 Replies
405 Views

Similar Articles

[PageSpeed] 36

On Tue, 6 Feb 2007, in microsoft.public.excel.charting,
bluegar <bluegar@discussions.microsoft.com> said:
>How can I get this cell with the formula to skip in the chart instead of
>plotting a zero?

The short answer is that we would all like to be able to do that, but 
Microsoft sez "tough luck!" There is no substitute for an actually blank 
cell.

The frustrating thing is that functions like AVERAGE() are completely 
capable of evaluating FALSE as "do not count this point" and not as 
zero, so that AVERAGE({2,4,FALSE,6}) is 4, not 3. If only chart series 
had been designed to do the same thing, or even designed to offer that 
as an option in the Tools.. Options.. Chart dialogue.

-- 
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
    which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
2/7/2007 12:18:08 AM
If the formula returns NA() instead of "", there will be no point plotted in 
a line or XY series. You don't get an actual gap; if the series has lines 
connecting the points, a line segment connects the points on either side of 
the gap. There are workarounds, which involve raw poultry parts and chants 
at full moon.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Del Cotter" <del@branta.demon.co.uk> wrote in message 
news:nW8kQMEArRyFFwKv@branta.demon.co.uk...
> On Tue, 6 Feb 2007, in microsoft.public.excel.charting,
> bluegar <bluegar@discussions.microsoft.com> said:
>>How can I get this cell with the formula to skip in the chart instead of
>>plotting a zero?
>
> The short answer is that we would all like to be able to do that, but 
> Microsoft sez "tough luck!" There is no substitute for an actually blank 
> cell.
>
> The frustrating thing is that functions like AVERAGE() are completely 
> capable of evaluating FALSE as "do not count this point" and not as zero, 
> so that AVERAGE({2,4,FALSE,6}) is 4, not 3. If only chart series had been 
> designed to do the same thing, or even designed to offer that as an option 
> in the Tools.. Options.. Chart dialogue.
>
> -- 
> Del Cotter
> NB Personal replies to this post will send email to 
> del@branta.demon.co.uk,
>    which goes to a spam folder-- please send your email to del3 instead. 


0
jonxlmvpNO (4558)
2/7/2007 1:02:56 AM
I am working with a stacked column chart, & when I use the NA() 

It actually adds #NA to the graph.

Am I SOL? or is there a way to get rid of the NA (or 0) values

"Jon Peltier" wrote:

> If the formula returns NA() instead of "", there will be no point plotted in 
> a line or XY series. You don't get an actual gap; if the series has lines 
> connecting the points, a line segment connects the points on either side of 
> the gap. There are workarounds, which involve raw poultry parts and chants 
> at full moon.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "Del Cotter" <del@branta.demon.co.uk> wrote in message 
> news:nW8kQMEArRyFFwKv@branta.demon.co.uk...
> > On Tue, 6 Feb 2007, in microsoft.public.excel.charting,
> > bluegar <bluegar@discussions.microsoft.com> said:
> >>How can I get this cell with the formula to skip in the chart instead of
> >>plotting a zero?
> >
> > The short answer is that we would all like to be able to do that, but 
> > Microsoft sez "tough luck!" There is no substitute for an actually blank 
> > cell.
> >
> > The frustrating thing is that functions like AVERAGE() are completely 
> > capable of evaluating FALSE as "do not count this point" and not as zero, 
> > so that AVERAGE({2,4,FALSE,6}) is 4, not 3. If only chart series had been 
> > designed to do the same thing, or even designed to offer that as an option 
> > in the Tools.. Options.. Chart dialogue.
> >
> > -- 
> > Del Cotter
> > NB Personal replies to this post will send email to 
> > del@branta.demon.co.uk,
> >    which goes to a spam folder-- please send your email to del3 instead. 
> 
> 
> 
0
tmirelle (3)
3/19/2007 11:29:08 PM
It does? So it does; that's annoying. Maybe there's something you can do 
with custom number formats, although a couple of minutes trying didn't 
produce anything for me. Here's Jon's format hints page:

http://peltiertech.com/Excel/NumberFormats.html

Otherwise you may have to just manually select the one label by single 
clicking twice, then deleting.

On Mon, 19 Mar 2007, in microsoft.public.excel.charting,
tmirelle <tmirelle@discussions.microsoft.com> said:

>I am working with a stacked column chart, & when I use the NA()
>
>It actually adds #NA to the graph.
>
>Am I SOL? or is there a way to get rid of the NA (or 0) values
>
>"Jon Peltier" wrote:
>
>> If the formula returns NA() instead of "", there will be no point plotted in
>> a line or XY series. You don't get an actual gap; if the series has lines
>> connecting the points, a line segment connects the points on either side of
>> the gap. There are workarounds, which involve raw poultry parts and chants
>> at full moon.

-- 
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
    which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
3/20/2007 8:39:54 AM
Found the solution in case it is helpful to others...

set data label format to:

General;;;

Thanks everyone for your help!

"Del Cotter" wrote:

> It does? So it does; that's annoying. Maybe there's something you can do 
> with custom number formats, although a couple of minutes trying didn't 
> produce anything for me. Here's Jon's format hints page:
> 
> http://peltiertech.com/Excel/NumberFormats.html
> 
> Otherwise you may have to just manually select the one label by single 
> clicking twice, then deleting.
> 
> On Mon, 19 Mar 2007, in microsoft.public.excel.charting,
> tmirelle <tmirelle@discussions.microsoft.com> said:
> 
> >I am working with a stacked column chart, & when I use the NA()
> >
> >It actually adds #NA to the graph.
> >
> >Am I SOL? or is there a way to get rid of the NA (or 0) values
> >
> >"Jon Peltier" wrote:
> >
> >> If the formula returns NA() instead of "", there will be no point plotted in
> >> a line or XY series. You don't get an actual gap; if the series has lines
> >> connecting the points, a line segment connects the points on either side of
> >> the gap. There are workarounds, which involve raw poultry parts and chants
> >> at full moon.
> 
> -- 
> Del Cotter
> NB Personal replies to this post will send email to del@branta.demon.co.uk,
>     which goes to a spam folder-- please send your email to del3 instead.
> 
0
tmirelle (3)
3/28/2007 1:43:41 AM
On Tue, 27 Mar 2007, in microsoft.public.excel.charting,
tmirelle <tmirelle@discussions.microsoft.com> said:
>> >I am working with a stacked column chart, & when I use the NA() It 
>> >actually adds #NA to the graph.  Am I SOL? or is there a way to get 
>> >rid of the NA (or 0) values
>
>"Del Cotter" wrote:
>> It does? So it does; that's annoying. Maybe there's something you can do
>> with custom number formats, although a couple of minutes trying didn't
>> produce anything for me.
>
>Found the solution in case it is helpful to others...
>
>set data label format to:
>
>General;;;

[*slaps forehead*] Of course!

Thanks for coming back with that.

-- 
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
    which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
3/28/2007 8:42:54 AM
Reply:

Similar Artilces:

Zeroing out data but leaving formulas.
I have a spreadsheet that I calculates monthly data and puts it into a year end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? You should be able to just delete the static data (the data that the formulas are using to make calculations). To toggle the formula view (see the formulas) use Ctrl+~ "clemrogan" wrote: > I have a spreadsheet that I calculates monthly data and puts it into a year > end management spreadsheet. How do I clear the cells of data (zero...

Excel QUOTIENT ; That's the matter with the formula ......
1. Enter the following formula into a worksheet cell :- 2. = QUOTIENT (36,16) + RQP 3. Where RQP (=26) is a Named Formula (well defined). 4. The given formula appears to calculate well until the partial evaluation in the Formula Bar gives #NAME? (that's apparently an error condition). 5. That means that when the function QUOTIENT is applied in a larger formula, it would stall thereof. 6. There are inevitably dithering moments at the expense of troublshooting. 7. Please share your experience. Regards. What's the definition of RQP? When I put 26 in a cell and name that cell RQP....

Pie Charts #10
I need a pie chart to show quarterly data for 5 different departments. Each quarter will display in it's own pie chart. Each of the 4 quarterly pie charts will be sized proportionately to the year end total. A fifth pie chart will display the TTL data for each department. Is this at all possible? Is there a better way to show such data? Qtr 1 Qtr 2 Qtr 3 Qtr 4 TTL Dept 1 10 30 20 15 75 Dept 2 15 25 25 20 85 Dept 3 20 20 15 30 85 Dept 4 25 15 8 30 78 Dept 5 30 10 19 35 94 100 100 87 130 417 Thank you in advance! V On Aug 6, 3:57=A0pm, valerie <vgar...@carletonls.com> wrote: &...

all grids to display the total number of records returned by the q
Currently only first (up to) 100 records are displayed with an indication that query results contain more pages, the only way to see how many records are in the list is to click next page button multiple times (if the total number of records is not too high). The customer is asking to have the bottom statusbar that shows "100 records shown" to show the total number of records returned by the view (or the search) query, e.g. records 201-300 of 2,456. Thanks, Sergei ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most ...

Format Chart based on data
I have built a chart from a simple spreadsheet of data. Here is an example of the data: Group1 13.77 Group2 15.86 Group3 12.11 I want the chart (bar style) to show each bar in Blue that corresponds to a value greater than 13. All other bars (<13) should be red. Is that possible? Also, I want to draw a line across the chart at 13 to show the "benchmark". http://peltiertech.com/Excel/Charts/ConditionalChart1.html http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTe...

Formula Help #26
How do I enter a formula that counts based on two different criteria. I column A I have names and in column B I have dates or blank cells. need a formula that counts if there is a date in a specified cell i column B. The formula I have now is =SUMPRODUCT(A1:A12="Scratch")*(B1:B12="?????")) What would I put in the ????? to make it count if the cell has a date -- Message posted from http://www.ExcelForum.com The only thing I can think of would be a two step process. In C1, us the formula = if(and(isnumber(B1),A1="Scratch"),1,0), and so on dow the column. Th...

Footnote in a Formula Cell
I am wondering if there is a way to add a footnote to a cell tha contains a formula. Everytime I try it gives me an error. I canno change the cell to a value beause the forumla in that cell is linked t another workbook. Please HEEELP -- Message posted from http://www.ExcelForum.com Learned this from MVP Debra Dalgleish: http://tinyurl.com/6rnm2 HTH Jason Atlanta, GA >-----Original Message----- >I am wondering if there is a way to add a footnote to a cell that >contains a formula. Everytime I try it gives me an error. I cannot >change the cell to a value beause the foru...

Can you have fixed cell reference when dragging/copying formulae?
When you drag or copy a formula which refers to another cell, then that cell's reference is updated when the formula is dragged or copied across a spreadsheet. Is it possible to 'fix' the reference of the outside cell such that the formula refers to a fixed cell irrespective of where the formula is copied to? For example a formual in cell B2 containing a reference to cell A1 will always refer to the cell immediately above and to the left when copied elsewhere. Is it possible for the formula to be forced to refer to cell A1 irrespective of where it is copied? You can use $a$...

Chart gridlines over an area graph
Good morning! I have an excel chart with an area graph. When I add gridlines to the chart the lines do not show over the colored area portion of the graph. i found a button for droplines which added the Y axis lines, but have found nothing that will show the X axis lines through the graph. Any ideas are greatly appreciated. Have a GREAT day! brenda Hi, You will need to draw the gridlines yourself using additional data series. For horizontal gridlines you could use a series for each line, changing the y value for each series. Or a single series plotted as xyscatter. The x value woul...

Non-number value in Charts
I am trying to make a non-number value show up in a column chart. The chart is automatically showing any non-number value as zero. This is fine, however I would like the Display Data Labels to show the value that is displayed in the cell. How do I do this? I think you're actually wanting to display either series name, or category name. If you just want custom labels on individual data points/columns, you'll need to manually edit the labels yourself by selecting the value, then clicking again (aka, a slow double click). -- Best Regards, Luke M *Remember to click "yes&qu...

excel charts #4
Hi, I have 2 sets of data for times. I can plot the chart data onto an excel bar chart and see the results, no problem. However I'd like to compare the 2 sets of times. i.e. I'd like to have two bars, side by side for each of the times. i.e. A1=1 secs A2=2 secs B1=4 secs B2=5 secs Right now I can plot all the A1,B1, ... N1 values but I don't know how to put the results for A2, B2, ... N2 beside each of those for the A1, B1,...N1 etc. Am I right in thinking I just have to change the data range? Or do I need somethign else? i can use the wizard or whatever it takes to redraw th...

Return a name in the same row
I have a 2 sheet spreadsheet. I want a formula to match a cell in column A of sheet 1 to a matching cell in column a of sheet 2 and return a name form the same matching row in column C of sheet 2. I have searched for this formula and cannot find it. Please help. Scott Sheet 1 CustNum 1 9 9 9 9 10 10 11 11 11 11 11 14 14 30 38 38 40 50 50 50 50 Sheet 2 CustNum ShopNum LastName 1 5 Name one 2 5 Name two 5 53 Name three 7 57 Name four 8 8 Name five 9 9 Name six 10 10 Name seven 11 11 Name eight 12 340 Name nine 13 12 Name ten 14 13 Name eleven...

how can I fill labels in a chart, so that the grid lines don't in.
(Excel:) How can I fill labels in a chart, so that the grid lines of that chart do not interfere with the text in those labels? Hi, Double click the data labels and set the Patterns Area colour to be the same as the plotarea colour. Cheers Andy peterk wrote: > (Excel:) > How can I fill labels in a chart, so that the grid lines of that chart do > not interfere with the text in those labels? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Peter - Or you could double click the gridlines and change their color to the faintest gray in the palette (you've alr...

copy formula from worksheet to worksheet
When I copy formulas from one worksheet to another the pasted formula includes the path and file name of the worksheet it was copied from. At times I can edit it out, but sometimes the formulas get to long and are cut. How can I copy and paste without the path and file name in the formula. Thanks for any help One way Use Edit>Replace Replace "=" with " =", which converts formulas to text. copy and paste the cells Use Edit/Replace then to replace " =" with "=" To make them formulas again -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 S...

Is there a way to create a standard OHLC bar chart in Excel?
Hi, Does anyone know if it is possible to create a standard open high low close bar chart in Excel? I don't want the candlestick form they have but rather a format similar to the high-low-close included in Excel that also uses the open price. I want the bars to look something like: | |_ | _| | Thank you in advance for your help, Jon http://www.peltiertech.com/Excel/Charts/stock.html "jhorvath@cfxasset.com" <jhorvath@cfxasset.com@discussions.microsoft.com> wrote in message news:A4A48B44-A8...

no shipping for zero tendered
att Microsoft When we processing a transection that the tender is zero (like even exchange) the invoice will not show up in the EDIT SHIPPING window, so we can process shipping for this items?? Any solutions?? ...

Adding Formulas to Subtotal rows
I have a large amount of data on a spread sheet which has been sorted and subtotaled (using the “Subtotal” function on the data menu). On each subtotal row I would like to have formulas that use the results of that row’s subtotals (ie divide the value of one subtotal by the value of another subtotal on the same row). At the moment I am creating the formulas on one subtotal row and then copying them to each of the remaining subtotal rows – one row at a time. This seems to be cumbersome – is there a faster way? Thanks Hi one way: use a formula like the following =IF(ISNUMBER(FIND("Tota...

Zero Values on a Pie Chart
Hi Everyone! I am doing a pie chart and I can't seem to get the chart to NOT display "zero" values on the pie. I have tried everything and still cannot get it to not display it. I went to 'Tools' then 'Options' then to 'Chart', where it says "Plot empty cells as:" it is gray and I am not able to select anything. It has already pre-selected "Not plotted (leave gaps)". I don't know if this is why it keeps showing them on the pie or not? The "Plot visible cells only" is checked. Can anyone help me? Also is there a way ...

I need help with a table formula/mass editting
I have a spreadsheet (.csv) with 1600 products/rows and several columns. One of the columns that has the product descriptions also contains 1600 rows. In each row under that 'description' column is a formula/code to input a chart/table with product details to our web site in a chart form. I have many new products that I need to place on the web site with this layout. I need to know how to optimize that particular column to add specific details to the chart formula for the 1600 products. For instance, I need the chart to be the same on each product but with different ...

Im wanting to down load a file where i can go in and make my own .
Im wanting to know what file i can download so i can make my own business cards There are a multitude of templates within Publisher. Always post what version you are using. Hey David, missed you... Everyone said you abandoned us to go to Serif. How are things down under? I hear the Aussie PM is still on Bush's side... -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "��MS-Publisher��" <brains@here.com> wrote in message news:OVYylLGqEHA.3712@TK2MSFTNGP15.phx.gbl... > There are a multitude of templates wit...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Automatic Updating Charts
Just wondering if I could automatically change a chart. For example I do a number of chart for every month, obviously days of the months changes so on a month where there are 31st, there isnt a problem. The problem is when i have a month which only has 30 or 28 days. This leaves the last 3, 2 columns zero but are still on the chart but are shown as zero. Does anyone know how to automatically adjust the source data? perhaps with an if statement? Thanks tushar mehta and Jon peltier have excellent solution on this. you have to make the source as dynamic array see their webpages by sear...

How do you set up a formula ( as for a check book.)
How do you set up a formula ( as for a check book.? Hi You will have to give more detail on what you want to do? Do you want to create a payment register? Do you wnat to create a bank register, where you can record both income and expenditure? -- j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa "judygroves" wrote: > How do you set up a formula ( as for a check book.? check out: http://office.microsoft.com/en-us/templates/TC010186511033.aspx -- Gary''s Student "judygroves" wrote: > How do you set up a formula ( as fo...

Can you have a static file location in a formula?
I would like to have a static file location that does not change if email the workbook to somebody. Is this possible? Here is an example of what I would like to do. =IF(L8=TRUE,'C:\Documents\Football\Week 3\[Pool Score Sheet.xls]Scorin Tab'!$B3,IF('C:\Documents\Football\Week 3\[Pool Score Sheet.xls]Scorin Tab'!$B3=0,1,0)) I would like the C: Documents ... to never change. I will be emailin this sheet so I want to formula to refer to my computer and not th recipent. Please help -- TwEaKFrEa ----------------------------------------------------------------------- TwEaKFrEa...

Multisheet formula
How can I pull a particular cell on each sheet onto a completely separate worksheet. ex. Sheet1c4, sheet2c4, without having to type each particular sheet name in? In the target cell, type =, then click the other tab and then cell in that sheet, and Excel fills it in for you. -- HTH RP (remove nothere from the email address if mailing direct) "Todd Nelson" <ToddNelson@discussions.microsoft.com> wrote in message news:89C86FA1-E858-449B-9AF8-BE251EBC2FD6@microsoft.com... > How can I pull a particular cell on each sheet onto a completely separate > worksheet. ex. She...