Dont want to inlcude 0's in my chart

Here is my formula, When i creat my chart it shows all the blanks or 0 data 
on my chart . Is there a way to do it?  Please help. Thank you.

=SUM(IF(Log!$A$2:$A$129=$A26,IF(Log!$R$2:$R$129>=Data!J$1,IF(Log!$R$2:$R$129<=Data!J$2,1,0),0),0))



0
mmb (7)
8/7/2008 5:29:03 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
546 Views

Similar Articles

[PageSpeed] 48

Change the zeros in the formula to NA().

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


"mmb" <mmb@discussions.microsoft.com> wrote in message 
news:7686C3F7-A1E5-4779-BF9F-F90E7570FEF4@microsoft.com...
> Here is my formula, When i creat my chart it shows all the blanks or 0 
> data
> on my chart . Is there a way to do it?  Please help. Thank you.
>
> =SUM(IF(Log!$A$2:$A$129=$A26,IF(Log!$R$2:$R$129>=Data!J$1,IF(Log!$R$2:$R$129<=Data!J$2,1,0),0),0))
>
>
> 


0
jonxlmvpNO (4558)
8/7/2008 5:46:38 PM
Jon, Thank you, but it is not working.

"Jon Peltier" wrote:

> Change the zeros in the formula to NA().
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "mmb" <mmb@discussions.microsoft.com> wrote in message 
> news:7686C3F7-A1E5-4779-BF9F-F90E7570FEF4@microsoft.com...
> > Here is my formula, When i creat my chart it shows all the blanks or 0 
> > data
> > on my chart . Is there a way to do it?  Please help. Thank you.
> >
> > =SUM(IF(Log!$A$2:$A$129=$A26,IF(Log!$R$2:$R$129>=Data!J$1,IF(Log!$R$2:$R$129<=Data!J$2,1,0),0),0))
> >
> >
> > 
> 
> 
> 
0
mmb (7)
8/7/2008 8:58:21 PM
What does not working mean?

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mmb" <mmb@discussions.microsoft.com> wrote in message 
news:BFFA708A-810C-4A4D-A255-D6CB08A5BD80@microsoft.com...
> Jon, Thank you, but it is not working.
>
> "Jon Peltier" wrote:
>
>> Change the zeros in the formula to NA().
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "mmb" <mmb@discussions.microsoft.com> wrote in message
>> news:7686C3F7-A1E5-4779-BF9F-F90E7570FEF4@microsoft.com...
>> > Here is my formula, When i creat my chart it shows all the blanks or 0
>> > data
>> > on my chart . Is there a way to do it?  Please help. Thank you.
>> >
>> > =SUM(IF(Log!$A$2:$A$129=$A26,IF(Log!$R$2:$R$129>=Data!J$1,IF(Log!$R$2:$R$129<=Data!J$2,1,0),0),0))
>> >
>> >
>> >
>>
>>
>> 


0
bob.NGs1 (1661)
8/8/2008 9:22:14 AM
Yes, well, I answered too quickly. Since you have a complicated set of 
nested sums, the NA() will only lead to woe.

Keep this range as is, then make another range that refers to this, but use 
a formula like

=IF(A1=0,NA(),A1)

Then make your plot using this second range.

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


"mmb" <mmb@discussions.microsoft.com> wrote in message 
news:BFFA708A-810C-4A4D-A255-D6CB08A5BD80@microsoft.com...
> Jon, Thank you, but it is not working.
>
> "Jon Peltier" wrote:
>
>> Change the zeros in the formula to NA().
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "mmb" <mmb@discussions.microsoft.com> wrote in message
>> news:7686C3F7-A1E5-4779-BF9F-F90E7570FEF4@microsoft.com...
>> > Here is my formula, When i creat my chart it shows all the blanks or 0
>> > data
>> > on my chart . Is there a way to do it?  Please help. Thank you.
>> >
>> > =SUM(IF(Log!$A$2:$A$129=$A26,IF(Log!$R$2:$R$129>=Data!J$1,IF(Log!$R$2:$R$129<=Data!J$2,1,0),0),0))
>> >
>> >
>> >
>>
>>
>> 


0
jonxlmvpNO (4558)
8/8/2008 1:45:04 PM
JOhn, Thank you. 

"mmb" wrote:

> Here is my formula, When i creat my chart it shows all the blanks or 0 data 
> on my chart . Is there a way to do it?  Please help. Thank you.
> 
> =SUM(IF(Log!$A$2:$A$129=$A26,IF(Log!$R$2:$R$129>=Data!J$1,IF(Log!$R$2:$R$129<=Data!J$2,1,0),0),0))
> 
> 
> 
0
mmb (7)
8/18/2008 11:10:00 PM
Reply:

Similar Artilces:

Need help bring over GP 7.0 data to new a GP 8.0 server.
I'm setting up a GP 8.0 Test Server with SQL 2000 SP3a installed. How do I bring my Great Plains 7.0 Data into the GP 8.0 envirnoment? Thanks in advance, The easiest way is the following: On the 7.0 server, detach DYNAMICS. Copy the DYNAMICS mdf and ldf file from the 7.0 to the 8.0 server. On the 8.0 server, attach and browse to the mdf that you copied. On the 7.0 server, attach DYNAMICS. Repeat for all databases. If you also need the sql logins from the 7.0 server, there is a TK on how to do this (uses a table called RecoveryMaster within Dynamics db). If you can go without the s...

cannot install Office 2001 Combo update 9,0,4
When I try to launch it on my G4, the Classic Environment is automatically launched. Then I get the following message: "The default location for this install cannot be found.This install will be cancelled." I have no trouble with other installs. Hi It sounds like you may have put your Office 2001 installation in a place other than the default location and the updater can't find it. Is that possible? -Jim Gordon Mac MVP All responses should be made to this newsgroup within the same thread. Thanks. About Microsoft MVPs: http://www.mvps.org/ Search for help with the free Googl...

Net Framework 2.0 is not supported with Microsoft CRM Connector fo
Has anyone been successful installing the 2.0 framework on the CRM server when using the GP connector? The documentation states: Microsoft .NET Framework 2.0 is not supported with Microsoft CRM Connector for Microsoft Dynamics GP Does this mean I cannot install the framework on the CRM server, or the integration server, or both? I'd like to upgrade our web server to SharePoint 3.0, but I cannot because CRM is on the same device. Is there a service pack to fix this problem, or has anyone found a way around it? ..NET 1.1 and 2.0 applications should be able to work on the same mac...

Help with Charts
:confused -------------------------------------------------------------------------------- I work in a Doctors surgery and I'm creating a Spread Sheet for select number of patients to monitor their blood results. I need about 30 charts on each patients sheet, To save me doing eac one seperately I have tryed to copy and paste them in to the nex patients details.... But all the charts refer to the previous patient details. Is there anyway of copying them so that they relate to th sheet they are copied in to? Thanx Alexandra Huckerby (fustrated and tired!! -- ~lexy -----------------...

CRM 4.0 records reverting to old state; caused by upgrade, rollup?!?
Hi all, A quick timeline before explaining my issue: - We installed CRM 3.0 a couple of years back. We imported old data from ACT! and used a custom drop down field with the value "Migrated from ACT!" to distinguish. - Users have put in a lot of work cleaning up those records, merging when necessary, assigning to right owners... - At some point we installed rollup 1 for 3.0 successfully. - Last year, mid-December, we upgraded to CRM 4.0 (I believe) successfully. - A month later, mid-January, we installed rollup 1 for CRM 4.0. - A few months later, some users were complaining about d...

Can not get Excel macros to save chart format -lost in space!
Help??? I've been searching this forum for 5 days trying to find a problem similar to what I am having without any luck. I have been trying to use Excel macro to record the creation, formating and saving of a simple bar chart against my Excel data range but the macros do not run for the chart. I am able to creat the charts okay but the macro craps out when I run it. I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" type errors as if it's not recognizing the chart that I just made. I am an advanced Excel person BUT not so with VB coding. ...

I want my Welcome Screen back!
I am using Windows XP Pro SP 3 I've had it setup for 4 users for the family members of my home and we use Fast Switching with the Welcome Screen (which conveniently allows you to see all 4 users, click on them to unveil a password field and then you can log in) Then two days ago I noticed some rogue "anti virus" software on there and quickly found instructions to remove it which including installing and using Malwarebytes Anti-Malware software. It scanned my PC, found dozens of problems, and I had it fix them. Then when I reboot my PC suddenly no longer has ...

Overlapping bars within chart
Sample scenario: Let's say in each year, I have this many cats: 2010: 6 2011: 8 2012: 20 Out of those cats, let's say that the following number have been neutered: 2010: 1 2011: 4 2012: 18 Is there a way for me to create a form of bar chart that shows the total number of cats per year as a "background" bar, with the total number of neutered cats in each year as a skinnier "foreground" bar that is a different color and overlays the background bar? I just figured this out on my own. As an FYI to anyone else, here was my solution: 1. Plot the ...

Chart Title or Text Box Linked to cell & sheet
I need to create charts from data collected via a com port. As i stands, the data is written to sheet 1. Chart 1 is ready to plot dat as it is written. Once the data is complete, I copy sheet 1 and char 1. This results in sheet 1(2) and Chart 1(2). All references to shee 1 on the newly created chart 1(2) automatically changes to refer t sheet 1(2). This applies to data ranges, Chart Title, Axis names. When creating text boxes refering to sheet1!$A$1, the reference doe not automacially change to sheet 1(2)!$A$1. Can this be done? If not, can I create multiple chart titles? Any help o...

Timeline bar chart
Hello, I am trying to create a bar chart based on key dates in a project. There are 20 different part numbers, all with individual timelines. Some parts of the process take many days, some one, etc. I want a bar chart that signifys these key dates, as well as shows a bar running throught the course of each individual aspect (ie, 12/2 - 12/8). I am having trouble doing this with dates. Any help would be greatly appreciated. -- shane476 ------------------------------------------------------------------------ shane476's Profile: http://www.excelforum.com/member.php?action=getinfo&u...

Keep Pivot Table custom chart type
I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The chart looks great when I create it. The problem is when the user does anything to the chart (filters the data, adds a new flield of data, etc.), the chart changes to where both data series are displayed as bar charts and the second axis is gone. I can't find an event that fires when they modify the chart, so I don't know that I can programatically change it back. Is there a way to keep it from changing in the first place? I'm using Excel 2003. Thanks! Loss of formatting is a known prob...

Rotating a line chart?
Is it possible to rotate a line chart so it looks like a vertical chart rather than a horizontal chart? And if so, once it is rotated, can one still interact with the chart and it's data? Thank you. Visit Excel Tips and Cases http://www.tushar-mehta.com/excel/tips/index.html and scroll down to 'Invert and rotate charts' The direct link http://www.tushar-mehta.com/excel/tips/0201-chart%20invert%20and% 20rotate.pdf is almost certain to change in the near future. The above applies to a XY Scatter chart. Hopefully, you can use that instead of a line chart. -- Regards, Tusha...

surveys in ms crm 4.0
we want to develop a survey application in ms crm 4.0 . IS there any way to do it ??? If yes then how ?? thanks in advance If this is a "permanent survey" like a customer satisfaction survey sent after each resolved case, then you can create a custom entity and link it to the relevant entity (case in this example). Secondly, create a custom aspx application which collects this survey information and uses the CRM web services to create a record for that. "nafees ahmed" wrote: > we want to develop a survey application in ms crm 4.0 . IS there any way to > do ...

Simple charting question
I don't believe I'm having so much trouble with this. I'm trying to make a graph with Temperature (Celesius) as the X-axis and Baseball Speed (Km/hr) as the Y-axis. When I highlight the columns below and try to make a bar graph - 0 44.1 1 47 3 44.7 4 43.5 5 50.2 13 59.9 I get two series plotted or shown seperately. I would like to get one of the following: ...

Combining Stack bar and Line Charts
I'm trying to display 2 sets of data within the same chart; I want to display yr 1 data in a stacked bar format and yr 2 data in a line format. Both data sets would use the same axis; month and volume. I need to distinctively display each mo/yr together to show any increases or decreases in volume. Any suggestions you have are appreciated! -- tibor ------------------------------------------------------------------------ tibor's Profile: http://www.exceltip.com/forum/member.php?action=getinfo&userid=156 View this thread: http://www.exceltip.com/forum/showthread.php?threadid=1...

Add names to scatter plot chart
I have three columns of info in my excel spreadsheet. column 1 has names, column 2 has numbers and column 3 has numbers. I can create the scatter plot chart just fine, but what I am having a problem with is that I cannot get the names from column one to show next to the points on the scatter plot. How is this done? -- oberon.black ------------------------------------------------------------------------ oberon.black's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26732 View this thread: http://www.excelforum.com/showthread.php?threadid=471758 I'm gu...

Chart
Chart - Title Widths Does anyone know how to find the width of a title/axis title? ...

Change Display Order in Charts
I have an X-Y chart with a cloud of points and a line. When the chart plots, the line is always hidden behind the cloud of points. Can I bring the line to the top so it becomes visible? I tried making the line first and last, but it always ends up behind the cloud. Thank you. Lines for a given chart group (group of data series) are plotted behind points of that same chart group in an XY chart. However, you can change the chart group by changing the axis on which the chart group is plotted. Make the plot with one series formatted as markers only and another as lines only, double cli...

CRM 4.0: Deleting system views
In CRM 4.0 I cannot find a way to hide or delete system views (Accounts: No Orders in Last 6 Months). In CRM 3.0, you could work around this by sharing with a "dummy" user, which would hide it from the rest of the users. I cannot find such a work-around in 4.0; has anyone else? Hi Mike, You can read my blog on how to hide it using a Plugin. http://msdynamicscrm-e.blogspot.com/2008/02/hiding-view-in-crm-40-using-plug= -in.html Hope this helps! Darren Liu, Microsoft CRM MVP Crowe http://www.crowecrm.com On Apr 18, 1:38=A0pm, MikePSU <mike.szyme...@gmail.com> wrote: > I...

combining charts from two spreadsheets
I hope someone can help me with this: I have two charts. The one thing these charts have in common are the addresses (the charts are from two different sources). On one chart, I will have monthly sales for the addresses. On another chart, I will have how much each location paid in expenses. I would like to combine these charts into one chart. The addresses on one chart will correspond to the addresses on the other chart and from their, I would like the other information to fall into place. Is this possible? Thanks for any help. this is totally out of the blue, but are you related to...

Bar chart with XY on secondary axis
I have four quarters of data that I display as a bar chart. I want to add additional data superimposed on each (XY) as a secondary axis. However, the XY coordinates are displayed on a single vertical axis in the center of the bars rather than on each related bar. Data: (BAR) 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr A 7.1 2.3 4.5 8.7 B 10.5 1.5 6.7 4.8 C 14.6 4.0 7.9 11.1 (XY) 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr A 25 55 67 ...

Re: Money 5.0-Error Updating Stock Quotes
When trying to update stock quotes via the internet, it no longe recognizes the stock symbols (this began happening sometime i Feb/March 2004). I've used the update feature in ver 5.0 for ~ 8 year without problems and have not changed any settings. Anyone have solution (other than the obvious....upgrade!) - tbridg ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message593303.htm In microsoft.public.money, tbridge wrote: ...

Creating multiple charts
I am new to creating charts etc in Excel. I am compiling all the accident information for my church and I would like to have charts linked to this data to give a visual presentation of all the statistics collected. Can anyone tell me in plane english how I do this from a single large spreadsheet which contains all the data. select the data you want to use for the chart, click on the chart wizard icon on the toolbar & follow the wizard. -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink ...

In charts, is it possible to change data references to relevant?
Ive created a basic bar chart, but the speadsheet in which the data is held is constantly being updated. This is ruining my graph as the data references are absolute. Is there a way of changing the source data references to relevant? ive tried "f4" when in the graph "Source data", "values" option, but this does not work. Ive tried deleting all of the $ from the data, but they apear again when the dialogue box is closed. Charts use absolute references. But you can also use a reference to a dynamic name: http://peltiertech.com/Excel/Charts/Dynamics.html ...

labelling points in xy chart
I am using an XY chart as a simple map. The data are in three columns of a worksheet - X coordinate, Y coordinate and Point ID. On my chart I want to plot the points and have each labelled with the point ID. At the moment I can only make the labels show either the X coordinate value (if I select Show label in the Data Labels tab) or the Y coordinate value (if I select Show value). How can I automatically link the label text to the column with Point IDs? Grateful for any help. Hi have a look at the following add-ins which will do this for you as Excel does not support this automatica...