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
692 Views

Similar Articles

[PageSpeed] 24

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:

Rotating Charts
Hi All I'm looking to create a Chart which i will then paste into Word, but i need to rotate it so it sits on the page lenght ways. Is there a way of rotating the chart 90 degrees in either Word or Excel?? Thanks in advance Gary Once you paste it into Word as an object, you should be able to rotate the chart. An alternative that I prefer is to set that one page in Word to print in 'landscape' mode. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <OnvmmfWpEHA.556@tk2ms...

Plot value at top of chart
Is it possible to have the highest data point on a line graph plot at the very top of a chart? For example, if the highest data point is 90 then I would like highest value of the y axis to also be 90 placing the data point at the top of the chart instead of excel automatically adding a "cushion (like 10) to the y axis. The data points vary each time the chart is created so i can't fix the value at 90. Thanks, Joe M. Hi Joe, I was looking at this issue yesterday and came upon this macro. It set the high of the axis to the highest point of the data and also the low ...

How to suppress #VALUE!
After setting up functions, the spreadsheet appears ugly when there are empty input cells. Is there any method in suppressing the display of #VALUE! etc, say assigning white colour to these error words? Thanks! Fix the errors, dont just hide them, eg Instead of your_formula that appears to give you the error when an input cell is empty (assume it is A1), try =IF(A1="","",your_formula) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�-------------------...

HOW DO i CREATE 3 PIE CHARTS SIDE BY SIDE- FOR 3 SERIES OF FIGURES
INCOME ANALYSED 5 WAYS FOR EACH OF 3 YEARS. hOW DO I CREATE 3 PIE CHARTS SIDE BY SIDE FOR EACH OF THE 3 YEARS? Hi, You would have to create 3 separate charts. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Sarahh" <Sarahh@discussions.microsoft.com> wrote in message news:40AF3534-26A3-4B6A-ABF3-78EC397BA60E@microsoft.com... > INCOME ANALYSED 5 WAYS FOR EACH OF 3 YEARS. hOW DO I CREATE 3 PIE CHARTS > SIDE > BY SIDE FOR EACH OF THE 3 YEARS? ...

How to suppress "Entourage cannot find server" message when offline?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Greetings. I'm a frequent traveler, and I can only access my email when connected to a VPN. When I am offline, I get the following error every time I send an email. <br><br>&quot;Entourage cannot find the server. Verify the server information is entered correctly in the Account Settings, and that your DNS settings in the Network pane of System Preferences are correct. <br><br>Mail could not be sent. Error -3176&quot; <br><br>What I wan...

two value axes in an excel line chart
How do I create two value axes in excel? For example, I have two correlated sets of values: x y 0.65 4.7 2.01 4.02 2.98 2.99 3.78 1.89 4.33 0.21 I want to show x as a function of y, but want each axis to show integers for 0-6 (and end up with a single line that passes through each x,y coordinate). How do I do this? If by 2 value axis you mean you want numerical values on both the horizontal and vertical axes, select a XY Scatter chart. On Sat, 21 Nov 2009 15:51:02 -0800, neural_jam <neural_jam@discussions.microsoft.com> wrote: >How do I create two value axes...

Formulas excel
Adquiri o Office 2003 em Português. Estou com dificuldades na utilização das fórmulas no Excel, porque estava habituado há muitos anos a utilizá-las em Inglês. Haverá alguma ferramenta que permita mudar as fórmulas para Inglês -- Catani ...

counta formula
I want to divide a number by counta for certain cells (which would yield a number). For example, =10/counta(A1:A4) would equal 10/4 if cells A1 to A4 are filled. What I am stuck on is this: =10/counta(A1:A4 but exclude the cells from this range that equal any of the numbers in in the range B5:B8) Example: =10/counta(A1:A4) would be 10/4 but since A2 = B7, the formula gives me 10/3 because A2 is excluded from counta What should happen if all the numbers match? Try something like this: =10/SUMPRODUCT(--(ISNA(MATCH(A1:A4,B5:B8,0))),A1:A4) -- Biff Microsoft Exce...

Adding % to Bar Chart
I have a bar chart for resource analysis and I want to add a % to the chart. I calculate it in a cell. Is there anyway to add a textbox and put the value of a cell in it? You can add custom data labels based on worksheet calculations. Use one of these utilities to create the labels: Rob Bovey's Chart Labeler, http://appspro.com/Utilities/ChartLabeler.htm John Walkenbach's Chart Tools, http://www.j-walk.com/ss/excel/files/charttools.htm - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com...

naming formula #2
In naming formulas if the formula contains a cell address this address is taken as absolute address in the orginal formula. On the other hand if the formula(not the name) is copied into a new cell the cell addresses of the formula are relative. can I not use formula name to show relative addresses. I tried using R1C1 style . that also does not help. I hope my doubt is clear. I made a name (testFormula) that refered to this formula: =Sheet1!$D$12+Sheet1!$A$1 and when I put =testformula anywhere, I got what "=Sheet1!$D$12+Sheet1!$A$1" evaluated to. I think you want to use relativ...

Excel 2007: Default Chart Template
Hello; I posted this back in July in the wrong forum ("General Questions"); since I didn't get a response, and the problem persists, I'll post it in this forum. When our lab computers were running XP and Office 2003, I could easily set up the default chart type to XY Scatter, with a few extra modifications, so that all students would create the same type of chart. Now that we have Vista and Office 2007, the process does not seem to work properly. I created a template for an XY Scatter chart ("Scatter with only Markers"), added a title and axis labels, forma...

Suppressing
I have a report that has a table with basically one grouping. I have sever rows of the same group and another table inside each of the rows. So basically I have 25 rows od the same group with a different table inside eavh row. So table inside of a table. My problem is that whenever one of the group tables has no data, it still shows whitespace and I cant get it to suppress when its blank. Any ideas? hi, can you set the width=0 and then set the visible for that row=false. Regards VB "Ben Watts" wrote: > I have a report that has a table with basica...

Pyramid charts #2
Example: I have 100 people with five different age groups (under 20, 21-40, 41-60, 61-80, >80). I would like to create one pyramid using the data to create layers in the pyramid. How do I do that? You have to fake it with negative values. Let the data be age pop let this start in A1 20 100 so these are A2 and B2 40 80 60 60 Use data like this age pop1 pop2 20 -50 50 formulas for numbers =-B2/2 and =B2/2 40 -40 40 60 -30 30 Make a stacked Bar Chart and edit to taste best wishes -- Bernard Liengme www.stfx.ca...

Chart setting
First excuse me for my bad english :-) I realized a chart with a set of numbers on the X-axix. The problem is that the interval between each number is not constant, while the chart represents each of them at the same distance, as they were text labels. How to solve? I must use only experimental data so I cannot add not real data. It's a line chart wich represents the pH of a solution (strong acid / base titration). Thanks in advance, Andrea. Your English is great. You have made a Line chart when you really need an XY chart. Microsoft selected poor names for these chart type. To corre...

Sales Return for Non Inventory Items
Hi I was not able to do a Sales Return for Non Inventory items. Is there are a setup in GP to enable this? Or Can you suggest a workaround? Regards Reem Reem, Make sure you have the "Enter Non-Inventoried Items" checkmark selected under Sales Order Processing Setup Options. Go to Tools > Setup > Sales > Sales Order Processing, click on the Options button. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Reem Jacob" wrot...

Formula links in file
We have a master data file that about 50 other related spreadsheets have formula links to. Permission to this master file has now been restricted for many people so when they open some of the other 50 files the links don't work to master file. Can we cut and paste (rather than just copy) the master file to another location and will the links follow the file to the new location? If not, is there a bulk way of changing the addresses in the formulas to the new location of the master file. Ideally for all 50 at once or at worst for all tabs in each file at once. Thanks R...

textures in charts
I am familiar with the textures that come with excel. Are there some other downloadable textures for excel charts out there? I'm not aware of any texture created specifically for Excel charts, but there are tons of graphic files on the Web that are intended to be used as web site background images. Many of them might work quite well for chart textures. Start with a Google search for "web background images." John Walkenbach For Excel tips, macros, & downloads... http://j-walk.com/ss "DougC" <dcrumley@edamerica.org> wrote in message news:377201c37bbf$544a...

Hiding Data Points in a chart
I have formula that is producing a blank cells, zeros, and valid data. I only want the valid data to display on the chart, but my graphs are picking up the other cells, evern though the result is zero or blank because of the formula. Is there a way to chart this information in a line graph without the blank cells or the zeros, when they are not hard coded and are formulas? =IF(OR(your_formula="",your_formula=0),NA(),your_formula) -- David Biddulph "Brandon" <Brandon@discussions.microsoft.com> wrote in message news:5560225E-0097-4447-A6FB-F15C530E4B5D@microsof...

Return date of an action
Hi, I have a gantt chart which has columns headed by dates A B C D E F 1 10/12 17/24 24/12 31/12 1/7 2 Do A 3 Finish B 4 Start C 5 6 7 I would like to put a formula in colum F to show the date that the action in the row will be completed by. The real worksheet has 52 columns in it so using an IF statement isn't an option. Each row will only have one entry in it. Thanks for any help Ben 9 10 So simple but I would never have got there. Thanks very much ...

Formula for table lookup
I need to be able to enter a value into two different cells and return a value from a table to match a certian value from that table. two cell values sheet 1 A1= 1" A2= 75# if you go to 1" and across to 30# you get 23.99 this is the value I would like to have returned. The table is on sheet 2 of the same workbook. It's not really a table its just data in cells. Trap Size 25# 30# 75# 1/2" 3.31 3.83 8.4 3/4" 9.2 10.64 23.35 1" 20.74 23.99 52.65 1 1/4" 26.59 30.76 67.51 1 1/2" 36.79 42.55 93.39 2" 57.48 66.49 145.93 2 1/2" 82.72 95.7 210.02 3...

Suppress X Axis Scaling
I have a table I’m charting. There are 15 textual descriptions in a column and 6 date field headings. When I grab that table and switch the “Series In: to rows, the chart builds correctly – to some degree. Though there are only 6 contiguous date field headings, the dates are not. Is there a way to suppress the X Axis Scaling to show only the dates associated with this table, not adding filler dates that do not exist for this data? Sincerely, Arturo D'oh! An apostrophe at the beginning of the date does the trick… "Arturo" wrote: > I have a table I’m charting. Th...

Copying an Array Formula
How do you copy an array formula from one cell to another -- montag ----------------------------------------------------------------------- montagu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1522 View this thread: http://www.excelforum.com/showthread.php?threadid=38759 montagu Wrote: > How do you copy an array formula from one cell to another? 1. Go to your original cell 2. Ctrl-C 3. Go to your other cell where you want the formula copied 4. Ctrl-V Regards. -- BenjieLop ------------------------------------------------------------------------...

Knowing What Point is Selected on a Chart
Hello, Try this again. No response before. May not be possible. I would like to select a single point on a chart, then select a button that will call the following subroutine: AddCommenttoPoint(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long) The elementID is XlSeries, Arg1 is the first series collection. The problem I have is Arg2. I want that to be the data point that is selected on the series. How do I determine which data point is selected on a series collection using code? The Selected property does not work with the series collection. Thanks a lot. Bill ...

creating chart
i am new to excel and need to learn how to create charts. does anybody have any good tutorials? try this site: http://www.bappit.com/Excel.html it has a tutorial on creating excel charts. they are a new site so if they dont have what you need then complete the contact form and they will get abck to you. emendoza1 wrote: >i am new to excel and need to learn how to create charts. does anybody have >any good tutorials? A couple I can think of that will be useful. Jon Peltier's site http://peltiertech.com/Excel/Charts/index.html Tushar Mehta's site http://www.tushar-meht...

Can you enter a formula in a chart data series?
I am referencing a column of data into a chart. How do I perform an operation on the data before it is entered into the chart? For example, I want the row of data to be divided by 1000, and then charted. HELP! This is driving me crazy! -- Shirley Hi, The simplest way is to use a helper column and formula to alter your data and then chart that. You can not directly apply formula to the data series within the chart. Cheers Andy Shirley wrote: > I am referencing a column of data into a chart. How do I perform an > operation on the data before it is entered into the chart? For...