Suppressing categories with 0 values

Hi all

I'm trying to build a dynamic chart for sales data.  The output file contains two columns, A and B.  Column A contains categories, i.e. Apples, Oranges, Pears, etc.,. and B contains sales data, i.e. 5, 0, 10, etc.,. respectively.  The ouput data comes out monthly.  Some months will contain sales from all categories, some ten, some fifty.  I want the chart to only graph categories with non-zero values, i.e. if there are 100 categories and only 30 with sales numbers there is no point in showing the other 70 categories with zero values.  I can format column B with zero values to show #N/A or "", but either way the categories are shown on the graph.   I only know the autofilter feature to suppress "" (blank) cells so the chart doesn't show it, but this is cumbersome as the chart has to be update manually each time.  Any thoughts

Thanks

VT
0
anonymous (74722)
5/16/2004 2:56:04 AM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
481 Views

Similar Articles

[PageSpeed] 41

Hi
have a look at
http://andypope.info/charts/piezeros.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Confusious wrote:
> Hi all,
>
> I'm trying to build a dynamic chart for sales data.  The output file
> contains two columns, A and B.  Column A contains categories, i.e.
> Apples, Oranges, Pears, etc.,. and B contains sales data, i.e. 5, 0,
> 10, etc.,. respectively.  The ouput data comes out monthly.  Some
> months will contain sales from all categories, some ten, some fifty.
> I want the chart to only graph categories with non-zero values, i.e.
> if there are 100 categories and only 30 with sales numbers there is
> no point in showing the other 70 categories with zero values.  I can
> format column B with zero values to show #N/A or "", but either way
> the categories are shown on the graph.   I only know the autofilter
> feature to suppress "" (blank) cells so the chart doesn't show it,
> but this is cumbersome as the chart has to be update manually each
> time.  Any thoughts?
>
> Thanks,
>
> VT

0
frank.kabel (11126)
5/16/2004 8:03:01 AM
Reply:

Similar Artilces:

infopath with crm 3.0
Hi, i am using crm3.0 for 2 months so i have much to learn. crm3.0 forms are not so customizable, you cant build whats in your mind so here is my question, i want to design a page with infopath 2003 and want crm3.0 to get the data entered by several different users on different machines. considering i am totally new, i want to know the logic, and the procedure. Thank you, ...

Re : Excel Range of Values Amidst Characteristic Transitions
Re : Excel Range of Values Amidst Characteristic Transitions Enter an Excel worksheet ; now that the tabulation is prepared for (preferential) presentation, Column A is shown as follows :- 01 8.0 02 8.0 03 <A Blank Row> 04 8.0 05 <A Blank Row> 06 <A Blank Row> 07 9.0 08 <A Blank Row> 09 9.0 10 9.0 11 <A Blank Row> 12 6.5 13 6.5 14 <A Blank Row> 15 <A Blank Row> 16 <A Blank Row> 17 8.0 18 <A Blank Row> 19 8.0 20 <A Blank Row> 21 8.0 22 <A Blank Row> 23 6.5 24 <A Blank Row> 25 ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

Changing cell values through form
Hi... I'm not that good with access so I decided to look for help here. I have a table filled with drivers. Starting number, Driver name, and Number of laps are the columns. My Idea is to make a form that will list all the drivers, and add a value of +1 into the "Number of laps" cell to that driver when double clicked. That would be a sort of Lap counter form... Every suggestion is welcome. -- ::: www.ktvj-online.com - Motocros Portal ::: ::: www.forum.ktvj-online.com - Forum ::: ...

2nd try, parameter values
I'm having problems posting... sorry if this comes up twice. I have parameter values set to make things easier for the users... The prompt pulls Last Name or Emp #. If the Emp# is not available, is there a way to search by last name, including suffixes, but only entering the last name? or even just the first letter of the last name? On Thu, 19 Jul 2007 09:31:24 -0700, melinda.pluma@agedwards.com wrote: >I'm having problems posting... sorry if this comes up twice. > >I have parameter values set to make things easier for the users... > >The prompt pulls Last Name or...

Migrating Data from Act 2005 to CRM 3.0
Is it possible for me to export from Act 2005 to Outlook and then, export into CRM 3.0. Will this all me to keep the notes in Act 2005. ...

Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6360C.2372C770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I wanted to reference a cell value in my spreadsheet header and found = out how to do that using this function: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WS As Worksheet For Each WS In Worksheets ActiveSheet.PageSetup.RightHeader =3D _ Format(Worksheets("Time Period Info").Range("B3").Value) =20 Next WS End Sub However, this leaves my text for ...

Changing bar graph colours according to value
Is there any way to have the colours of the bars in a bar graph change according to value? Hi, Have a look at Jon's example. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy nikki8327 wrote: > Is there any way to have the colours of the bars in a bar graph change > according to value? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

.net 3.0 on GP 7.5?
I need to know if the .net 3.0 framework will "break" GP 7.5. .net 3.0 is necessary for another application which will be running on the same machine. I cannot find an answer on Partnersource or various other resources that I've looked into. 7.5 is not supported by Microsoft anymore... Any insight would be much appreciated! Dear, You can export the data from the first company using SmartList, and use Integration Manager or Macro's to import the transactions into the other company, please let me know if you need any help in either ways. Regards, -- Mohammad R. Daou...

It is a question about the Visual C++ 6.0 Enter(or Pro)
Hello. Environment : Visual C++ 6.0 Enter versions When I add approximately 1,500 sources to one DLL and build. vc60.idb becomes around 35MB, and the following errors are displayed, and build stops. \x1.cpp(19) fatal error C1073: Internal error involving incremental compilation(compiler file 'main.c', line 629) \x1.cpp(23) fatal error C1073: Internal error involving incremental compilation(compiler file 'main.c', line 629) When I delete vc60.idb, An error is not displayed, and build is performed till the last. Thanks, "teratera" <teratera@discussions.m...

Wennsoft and Great Plains 8.0
I have a client considering moving to GP 8.0 from 7.5. They have Wennsoft's project software. What is the community experience with Wennsoft 8.0? Good, Bad, Indifferent? What I'm looking for is information around the upgrade process, "gotcha's", customizations, etc. Thank you in advance. Robert Houdeshell ...

Downgrade from MOSS to WSS 3.0
Hi everybody, my scenario: 1. Windows 2008 Enterprise full installation. 2. SQL Server 2005 SP2 3. WSS 3.0 my question, can i do a downgrade from MOSS (Enterprise Feature - Publishing template) to WSS 3.0 normal site (Blank Site)? We developed an KPI application using Dundas Charts and Dundas Gauge and the site was created using the publishing template for MOSS. The customer has WSS 3.0, we never was inform us that they have wss 3.0 and we have to do the downgrade to install the application now. somebody knows about?. ------=_NextPart_0001_A1F38FB8 Content-Type: text/p...

hide #div/0
i need to hide #div/0 error for the following formula = PMT(E6/12,F6,E4) when result is =0 I used IF(ISERROR (PMT(E6/12,F6,E4),"",(PMT(E6/12,F6,E4)) IF(ISERROR(PMT(E6/12,F6,E4),"",(PMT(E6/12,F6,E4)) IF(ISERROR PMT(E6/12,F6,E4),"",(PMT(E6/12,F6,E4) IF(ISERROR (PMT(E6/12,F6,E4),"",(PMT(E6/12,F6,E4))) nothing is working what am i missing? > nothing is working Hi. It looks like you do not have a closing ")" for IsError > ...ISERROR (PMT(E6/12,F6,E4) Should be something like: ISERROR(PMT(E6/12,F6,E4)) = = = = = HTH...

search and return cell value
I hope that someone may be able to help. I have a worksheet that has 7 fixed columns and a variable number of rows. The rows can range from 1 to many. Cell A1 is dependent upon the value in the last row in column G. I can locate this with nested "if" statements or by creating a new column H that will test to see if the next row contains values. Is there any function that I can put in cell A1 that will locate the last row in column G that contains a value and return the value of this cell? Many Thanks Hi if you have no blank rows in between try =OFFSET($G$1,COUNTA(G:G)-1,0) -- Reg...

Dynamic Bar Chart Category Axis Problem
Hi I have created a dynamic bar chart, roughly based on Stephen Bullen's funchrt4. The number of categories can range from 2 to about 50 and ideally I would like the category axis names font to be Arial 10 at least. When the number of categories gets towards 50, the category axis names disappear altogether and the only way I have found to retrieve them is to manually reduce the font size to about 7 (dependent on the level of the zoom). This font size is excessively small for the majority of charts. Is there any way to get Excel2002 to keep the category axis names in all circumstan...

how do i "de-dupe?" rows by a column value?
I have a complex operation to perform. My spreadsheet has rows that indicate items (control number in column A) linked to titles (control number in column B). A title can have multiple items in the spredsheet. I want to number the titles and assign random numbers to them so that I can get two random samples. I also want to have my random numbering retained in the spreadsheet as a whole, so I can see all the items assigned to my samples. How can I "dedupe" column B, assign a random number to each title control, then transfer that data to my "undeduped" spreadshe...

OnTimer() Not Being Called after timer is set up (VS C++ 6.0)
OK, here is what I have done. I am looking to use a timer to complete some refresh operations every 2 seconds. However, I cannot get my OnTimer()function to be called. I set up a timer with the following line of code: SetTimer(1, 2000, 0); Then, I have the Ontimer function, which I created from ClassWizard as a message handler for WM_TIMER. void CProbeTalkMenu::OnTimer(UINT nIDEvent) { m_szStatus += "*"; UpdateData(); CDialog::OnTimer(nIDEvent); } OnTimer() is never called. I am afraid that I am failing to do something grossly important. Can anyone explai...

Business Portal Order Management Item Categories Query how to chan
Hi , I am looking for some guidelines to how to change the query of the Item Categories web part on Orders page in Business Portal. This web part displays a list of Item Categories, defined in Catalogue Maintenance. by default it shows Category ID ( name of Categories ). my clients want to display the Description of category. is there a way to change the Query behind this web part ? please advise. Thanks Syed ...

Display value on a form dynamically
I have a form that needs to display values from an array on the form; how do I achieve the following? If item count in array is < 20 then print Val1 Val2 Val3 Val4 Val5 Val6 and so on If items in array is > 20 then print Val1 Val2 Val3 Val4 Val5 Val6 Val7 Val8 Val9 and so on The following code works fine and prints values in two columns butI need to modified it to print in three columns if nItemsCount > 20 { for (i = 1; i <= nItemsCount; i++) { if (i % 2) { nX = nX * 25; ...

Form using a query to look up values
SELECT Residents.[Last Name], Reciepts.Date, Reciepts.[Street Number], Reciepts.[Street Name], Reciepts.Amount FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street Name]); I have a couple of combo boxes in my form that uses information from this query to populate a table. The query allows me to type in the last name in a box on my form, and then fill in the street number and street name using the drop down box. My issue is that once I fill in the form once, and go to the next line, th...

Smart List version 7.0
GlacierIs there any way to see inventory or sales transactions in smart list that include the serial # of the product? ...

Query for random values multiple records
I have a table of names (lots of names! Too many to write individual queries for...). I want three random non-repeating numbers between 1 and 7 for each of these names. Output example: Name1 3 Name1 2 Name1 5 Name2 1 Name2 5 Name2 4 From reading on the boards I was able to come up with a way to generate output such as this: Name1 3 Name1 2 Name1 5 Name2 3 Name2 2 Name2 5 However, that will not work for what I want the random numbers for. I need a different set of each random non-repeated number set for each name. Thanks! On Mon, 16 Apr 200...

Select case syntax for continuous range values?
I know you can use Select case statements to cover a range of integer values, such as: Case 100 To 500 Case 501 to 650 etc However, I'm not clear on how to work with non-integer values to ensure that every possible value is covered, while ensuring no overlap in categories. With an IF statement I might use: If x >100 and x <=500 elseif x >500 and x <=650 which would properly assign a value of 500.4395 to the second condition Is there a way to do this with select case? Or is my best option to try to use more decimals than the data might need, e.g. C...

Negative Values in Stacked Bar Charts
I'd like to include negative values in stacked bar charts, with the total stacked value equal to the sum of each of the subtotals. However, when I tested this, it appears that Excel just shows the negative components at the bottom of the bar, below zero, but the stacked total still seems to reflect the sum of the positive components. Is there a way of showing a stacked bar the way I'm trying to do it? Todd Hi, In order to plot the chart how you want you would have to make the negative values positive. You may need to explain further why you need a negative value should be...

Summing column that includes #Error values
I have asked this before but it's come back to haunt me. I have a calculated value column in a query that contains some #Error values. This generates an error when attempting to run a query which is a sum on that column. How can I get around this - ie. make it ignore the #Error values when summing stuff? What is causing the errors? MH "will" <will@stream3.co.uk> wrote in message news:ekig4a5nHHA.3968@TK2MSFTNGP06.phx.gbl... >I have asked this before but it's come back to haunt me. I have a >calculated value column in a query that contains some #Err...