How suppress chart points - cells with formulae returning null

I want to suppress chart points for a cumulative line graph of monthly 
telephone costs where the particular month's values have not yet been entered 
but there is a  formula in the cell which currently returns null but will 
return the value once it is posted on the accountmaster sheet.

0
sarcastix (1)
10/16/2008 3:02:16 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
824 Views

Similar Articles

[PageSpeed] 55

Hi,

Use NA(). The will cause the data marker to be omitted. Note the line will 
be interpolated between valid data points.
Something like this,

=IF( <test> , <value> , NA() )


Cheers
Andy

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"sarcastix" <sarcastix@discussions.microsoft.com> wrote in message 
news:C0179255-E8D3-4432-B428-DD2F7DAFCF79@microsoft.com...
>I want to suppress chart points for a cumulative line graph of monthly
> telephone costs where the particular month's values have not yet been 
> entered
> but there is a  formula in the cell which currently returns null but will
> return the value once it is posted on the accountmaster sheet.
> 

0
andy9699 (3616)
10/16/2008 3:11:33 PM
Set the formula to return NA() if you aren't in the current month.
-- 
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"sarcastix" wrote:

> I want to suppress chart points for a cumulative line graph of monthly 
> telephone costs where the particular month's values have not yet been entered 
> but there is a  formula in the cell which currently returns null but will 
> return the value once it is posted on the accountmaster sheet.
> 
0
10/19/2008 11:50:00 PM
Reply:

Similar Artilces:

Copy Formula with Reference Change
I have a workbook that will have 100+ sheets in it. I want to use a beginning number in a cell on the first sheet and then on the following sheets in the same cell have a formula that adds 1 to the number in the same cell on the previous sheet. For example, the first sheet has 497 in cell B2. In each successive sheet, I would have a formula in cell B2 that would be '=sheet1!B2+1' to give me 498, then 499, then 500, etc. I want to copy the formula to each successive sheet and have it automatically adjust the sheet it is referencing to be the previous sheet. However, when ...

Linking Formats Across Cells
anyone know if I can link formats across cells? For example, if I fill a cell with a color, can I link it to another cell that will do the same ? thanks. Try the FormatPainter, the little PaintBrush icon up in the tool bar...........click on your colored cell, then click on the icon, then click and drag on the cells you want to be the same........... Vaya con Dios, Chuck, CABGx3 "RB" <RB@discussions.microsoft.com> wrote in message news:FE8ABE47-2671-447C-8CC6-2D784EEECE00@microsoft.com... > anyone know if I can link formats across cells? For example, if I fill a cell ...

How do I connect cell color to bar chart color?
I have seen an excel spread sheet bar chart where the colors of the bars were connected to the color of the cell. I was not able to determine how that was accomplished and the author of the spread sheet no longer works for the company that sent me the data. Does anyone know how this can be done? Hi Bob, It's possible this was done using a vba macro. For a non VBA approach have a look at Jon's examples of conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy Bob Freerks wrote: > I have seen an excel spread sheet bar chart where the color...

Simulating a Zodiac with a chart
I want to make a circular chart similar to a Zodiac but I want to color sections similar to a pie chart. I have plotted this before using Sin/Cos of the degree but did not have the ability to color the pie shape pieces. Is this possible? So say I want from 360* (top dead center) to 90* (left quadrant) to be shaded, how would I do this and with what type of chart? Hi, Donut chart will provide the shaded circular sections. Cheers Andy gtslabs wrote: > I want to make a circular chart similar to a Zodiac but I want to > color sections similar to a pie chart. > I have plotted this bef...

Better Return Function
1. When Recall Transaction For Return, instead of bringing up everything that was purchased in return mode ( and having to delete the ones not being return), there should be an option to make the POS to ask the user to scan/enter the item to be return. Then the program has to check that the item scanned does exists in that transaction. 2. Returned item from that transaction should not be allowed to be return again. 3. In Recall Transaction For Return mode, have the ability to block user from giving credit/cash back to customer. 4. In Recall Transaction For Return mode, do not pref...

Stacked chart #3
Hi, I have income distribution functions for different countries and I am trying to stack these curves. The data looks in this way: Country 1 Country 2 x y x y 5 0.2 7 0.1 8 0.4 9 0.3 10 0.3 12 0.5 15 0.1 20 0.1 Where x is the mean income and y is the % of the population. My problem plotting the curves is that different countries have different range of x values. And I want to keep them when I am stacking the curves. When I use stacked line chart excel considers the x values as categorical ...

View -> Sized With Window (for Excel charts): I want it back, plea
While I appreciate that not every function survives from one version to the next, this is one I sorely miss ! I use Excel 2003 at work, and 2007 at home. I can stand the constant bleating about compatability. I can even (barely) tolerate the highlight colors not matching. But this? Why oh why was it removed? Can you at least recommend a macro / VBA script patch to reurn this functionality to the program ? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" b...

Condtional format with formula
I have this formula =IF(ISNA(VLOOKUP(A17,'Data-FSList'!GEAC2000IntlFSList,2,FALSE)) =TRUE, "DONOR NOT VALID",VLOOKUP(A17,'Data-FSList'!GEAC2000IntlFSList,2,FALSE)) on cell B17. When the user selects a value in A17 from the drop down, it goes to sheet 'Data-FSList' to retrieve a value. If the value is not there it comes back in field B17 'DONOR NOT VALID' However when A17 contains no value from the drop down and is blank I do not want 'DONOR NOT VALID' to show in cell B17. Is there a way to add to the formula above if A17 is blank then...

Scatter plot
I have an XY scatter graph with some points in it. My data in the spreadsheet looks like this: [Title] [X-value] [Y-value] [size] Data 1 | 10 | 10 | 1 Data 2 | 33 | 44 | 5 What I want to do is to change the size of the data point according to the value I specify. Right now, I have to individually modify each point and add a number corresponding to the size. Is there anyway to take the size-value from the spreadsheet and change the size of the point in the graph? In the long run, I am also looking to modify the color. If the solution requires some VBA coding, I am up for that. Any sugges...

Is there a way to protect just one cell, not the whole sheet?
The books I read seem to indicate so, but say you first must turn on the 'Protect Sheet' option. When I do that, everything is protected. Any help much appreciated. Hi try the following: - first select all cells you don't want to protect - goto 'Format - Cells - Protection' and uncheck 'Locked' - now protect your sheet -- Regards Frank Kabel Frankfurt, Germany "Fred Exley" <fexly221@msn.com> schrieb im Newsbeitrag news:10hsd5t42ghdt65@corp.supernews.com... > The books I read seem to indicate so, but say you first must turn on the > &...

PA falta tipo moneda en ventana "Return from Proyect Entry"
La ventana de "Return from Proyect Entry" tiene que ser multimoneda ya que todo el sistema es Multimoneda. La ruta para acceder a esa ventana es Transacciones/Proyecto/PA Purchasing/Return from Proyect Entry. Ver incidente 8404987 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

Offsetting Chart data
I require to view data in chart format, the problem is as the data lines are so close I need to offset the data to view all on one chart for comparison. Can anyone help me with this?? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ What does that mean? "Offset the data?" If you want to see only a subset of the data, see the Dynamic Charts page of my site. You should be able to adapt one of the examples to your needs. -- Regards, Tushar Mehta, MS MVP -...

dynamic charts using named formulas
I have dates in the first row I have values in the second row I use tecnique of named formuls of offset function (tushar Methta) so that the chart is updated when data for new dates are added. now after some time I want to delete the older data If I delete the columns of older dates the named formula get messed up and I get error message that three is some wrong reference. actulally in the offset formula the starting cell is relaced by "REF" Then I have to redo the whole exercise any solutions. If the first row is some sort of header that doesn't get deleted, use it as the ba...

Formula Addition incorrect
This must be a simple error, as all I am doing is calculating sales tax. I have column B as Gross rents, column c with the formula =sum(B11_-Sum(b11/1.065). And column D with the net rent. Then I ask it to total all columns down. But the Column total from the calculations, does not add correctly. It has a 3 cent difference. Add the figures manually and the total is correct. I.e. Gross Rent Sales tax Net Rent 900 =Sum(b11)-sum(b11/1.065) 845.07 This column does not add down correctly. Can anyone suggest why. There are only 1...

Two different values in a single cell...
Hi, Is it possible to have two different values (coming from another sheet) in a single cell? For example, 0.87 (0.97) In that case 0.87 comes from a cell on sheet1 and 0.97 from a cell on sheet2. Thanks for the help. Patrick, Hi You can concatenate two results into one cell. This makes it inot a text cell, however, so you cannot do maths on it quite so easily. To join the info, just use the CONCATENATE function, or the & sign =A2&" " &A3 You may also need the TEXT funtion, depending on what format you want the result to be -- Andy. "Patrick" <an...

"IF" Formula
xl2000—Totaling time worked at each of three stores for a two-week pay period. What I have is Columns C, D, E, F, G, H, & I, are week one. Columns M, N, O, P, Q, R, S, are week two. I have, through data validation, dropdowns to select Store 1, Store 2, or Store 3, in row 7 of each of the described columns. The employee will select the store worked for that day, by column (C> Monday, D>Tuesday, E> Wednesday, etc). Then enter the time worked in the same column. In row 18 are the total hours for each day worked. Now I need to summaries the time worked for each store thro...

How do you ungroup charts in Powerpoint 07?
I'm trying to ungroup a chart in Powerpoint 07 like you would ungroup an object. You used to be able to do it in previous versions by right-clicking on the chart and then selecting ungroup. Can anybody help please? Thanks AK, Try this, select your chart and copy it. Home tab, click the list arrow next to Paste, select Paste Special and select Enhanced Metafile. Now you should be able to ungroup the pasted graphic. -- Luc Sanders MVP - PowerPoint "AKGray66" <AKGray66@discussions.microsoft.com> schreef in bericht news:F42ED3C3-80F8-46BC-890F-B...

Rename name so formulas show name change
Excel 2003 Cell A1 is named MyCell. Cell A2 has formula "=MyCell". I rename cell A1 in the toolbar to NewCell. Cell A2 still has the formula "=MyCell". How do I rename cell A1 to NewCell and have it automatically change cell A2 to the formula "=NewCell"? Thank you for your help. Download Jan Karel Pieterse'e Name Manager here: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp -- Kind regards, Niek Otten Microsoft MVP - Excel "John" <no.email@no.email.com> wrote in message news:ORne6k3$GHA.4592@TK2MSFTNGP03.phx.gbl... | Excel 2003 |...

Bullet point formatting
I would like make it single spacings through out my page. When bullet point list is selected, an extra row is created after the text. To explain, I typed 5 words which start at the left side. Then I pressed return. At that point, it is single spacing. Then I select 'Bullets' and automatically an extra row is inserted. I've checked Paragraph Properties and all are set to '0' and single spacing. Please can you advise. Thank you in advance. ...

publish chart to IIS Server
Right now i can publish my report to an interactive htm file. How do I publish it to say an IIS Server ? ...

TO CREATE A LINE CHART WITH RATE IN Y AXIS & REAL TIME IN X AXIS
i want to create a line chart in excel for rate vs time as y & x axis data. the chart should be an automatic charting as, when the particular time (10,10.30,11,11.30 ...)of system time reaches the data in one cell (rate ) should be taken automatically for charting the chart for 10am to 11.30pm. the only thing is how to check logically if the system time reached or not. Put the times in one column and the rates in the next, then create an XY chart. I can't tell a lot from your post, but if you expect Excel to determine a rate from the time, it will not. You have to write some fo...

text in Bullets/points in cell
How can one write the text in bullets or points in the cell in excel 2007. Hold the ALT key, then on the NUMERIC KEYPAD, type 0149, then let go of the ALT key -- you'll have your bullet •<== works here, too! "kamal" wrote: > How can one write the text in bullets or points in the cell in excel 2007. ...

Unable to edit formulae
I've been trying to edit formulae on my machine, especially VLookups, but for some reason, they are appearing with small boxes instead of commas, which means I can't edit them. Any ideas what's happening? Does this mean that when you're changing an existing formula you see the boxes? Or do you see the boxes when you're typing a formula from scratch? I'm gonna guess the first. There's a windows setting that could be messed up. Open up the regional settings applet under control panel in winXP: windows start button|settings|control panel|regional and language op...

Want to find max in a range then return a name from a cell
The spreadsheet has 5 people each person has a list describing thei sales performance. Each has their highest sale, highest over cost sale etc. I have a table with a describtion highest sale, highest over cost sal etc. I want the cell beside each title to calcute who has the highest figur and place their name in this cell. :confused: I have a spreadsheet with lists containing highest sale highest average sale etc. What I want to do is create a formula tha will find the max value within a list please note these values ar spread out and do not run on the spreadsheet side by side or one aft...

Excel 2003: Cell rounds 16 digit numbers- How to advoid rounding?
When I enter a 16 digit credit card number, excel rounds the last digit to 0; How do I format to reatin the exact number I entered (Have formatted cells to numbers, no decimal places now.) Hi, Format as text or precede your number with an apostrophe; it won't show up in the cell. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Max T." wrote: > When I enter a 16 digit credit card number, excel rounds the last digit to 0; > How do...