Zero value and bar graph

Hello all,
I'm new to graphs and what I have is a date range (1-Jan-08 - 31-Jan-08) 
with a dollor amount in the next cell. The bar graph is picking up the "zero" 
or blank ("")cells and is ploting them on the graph. How can I have the graph 
not plot the "zero" or blank ("") values?
I did the flowing to the amounts:=IF(N18=0,NA(),N18)
and that remove the "$0" from the graph, but it's still pickup the dates.

Off2000

Thanks
0
pgarcia (6)
1/31/2008 8:08:03 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
305 Views

Similar Articles

[PageSpeed] 25

Hi,

The NA() trick only suppressed the point/bar/column.
It does not remove the point from the series.

To do that you can hide the row using autofilter and as long as the 
chart is set to Plot only visible cells the point should be removed.

If the chart object is in the same rows as the data you will need to 
unset the Move and size properties of the chartobject otherwise it will 
change size.

Cheers
Andy

pgarcia wrote:
> Hello all,
> I'm new to graphs and what I have is a date range (1-Jan-08 - 31-Jan-08) 
> with a dollor amount in the next cell. The bar graph is picking up the "zero" 
> or blank ("")cells and is ploting them on the graph. How can I have the graph 
> not plot the "zero" or blank ("") values?
> I did the flowing to the amounts:=IF(N18=0,NA(),N18)
> and that remove the "$0" from the graph, but it's still pickup the dates.
> 
> Off2000
> 
> Thanks

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
1/31/2008 8:28:32 PM
Reply:

Similar Artilces:

Find value in another column, change Interior.colorIndex
First post didn't work. I apologize if this is a duplicate. Hello there, I wrote the following code to find a value in column B if the cell in columnS has an Interior.color.Index = 44, transfer the color to the cell with same value in B. (calcLastRow declared already) Dim ColumnB As Range, SrcChk1 As Range Dim DestChk1 As String Dim DestChk2 As String Set ColumnB = Range("B14", "B" & calcLastRow) Range("N14").Select With ColumnB Do DestChk1 = (ActiveCell.Offset(0, 5).Interior.ColorIndex...

On Close assign a value
I would like to assign a value to a field when closing a form. I have three employees (buyers) that we manually assign records for them to work. I would like to do this automatically based on the ID# of the record. I have the idea of what to do, but not sure how to program it. In my table - tblreqs - I would like to automatically assign a buyer (# 10 or #26 or #29) based on the ReqHeaderID. On close, if ReqHeader ID ends in 1,4,7, or 0, Buyer = 26; If ReqHeaderID ends in 2,5,8 Buyer = 29; If ReqHeaderID ends in 3, 6, 9 Buyer - 10. Can someone tell me how to write this c...

How to change the values in xml for picklist
Under the accounts, I got a field country picklist and a state/province picklist. I wanted to have the country picklist onchange the state/province list will reveal the releveant states options. How am I going to that in xml? Which file should I change to alow the customization to work? Any solutions? Will the changes be brought forward throught migration? What are the consequences of the changes? Thanks for the help The solution provided will greatly benfiance all. Not in a supported way. However, Javascript does ive you the option to do this type of manipulation. You would proba...

Calling Lookup popup window and filter values based on parameters
Hi All, Need your all's help for the following. On one of the form we have four lookup controls in following order. Account, Project, Project Task, Incident. Last 3 are custom entities. We need a functionality where, - when i open a Account lookup, it gives me account only related to current crm user. - After that when i open a Project lookup it should give projects only relted to account we have selected. - In the same way ProjectTask should be as per Project - While case should be as per account. Whats the best way of doing it? Awaiting reply. Thanks and Regards, Darshan ...

Report Parameters forms value list set by VBA?
NOTE: I"m a VBA novice I have a set of reports that use a common Report Parameter form. When I first designed them it was convenient as the same set of filters were applicable to all the reports: Example: report for invoices, report for open orders, report for processing orders Filters were: Location, Manager & Employee Now they also want to filter by date range, which won't consistent between reports: IE. Invoices = 30, 60 or 90 days old vs Pending Orders = 10, 15, 30 days old I'd like to just add one more drop down box to the parameter report and have the value list ch...

related to PIE charts N BAR charts
hello everyone, i am using office 2007... actually i have drawn some PIE charts and BAR charts with almost seven variable each.. they are looking good in colors.. however i want the black n white printout .. which is not giving the clear on the percentage in graphs.. is there any way to feed the PATTERNS in graphs for black n white picture.. like in books we have seen those things.. Hi, If what you are asking is can you apply Patterns to charts as in 2003 and earlier, the answer is no - that feature has been removed. I suggest that you TRY a gradient fill instead and see if that meets your...

averaging a value between two serial dates
Hi all, I have two spreadsheets. The first spreadsheet contains two columns, one for start date/time (in serial format) and the second for stop date/time (in serial format). The second spreadsheet contains two columns. One containing a serial date/time and the other containing a meteorological value (eg. temperature). I want Excel to read the start and stop date/time from the first spreadhseet and then search the second spreadsheet for all temperature values which were recorded during this time frame. Lastly, I want Excel to average these values. Is this possible using a single Excel func...

vertical graphs
how do you make the x axis the dependent variable and the Y axis the independent variable so that the curves progress up instead of across? You're going to need to give us some more info. What kind of chart are you talking about? For instance, bars can go horizontal (Bar Chart) instead of vertical (Column Chart.) This doesn't sound too difficult, but we can't help much without some more specifics. Why not give us a couple of short columns of numbers and a better description of what you need the chart to look like. "jimF" <jimF@discussions.microsoft.com> wro...

How to remove Windows Search mention in URL bar IE8?
I have Dutch system, will try to describe it good :-) Windows XP Home SP3 Dutch IE 8 latest version When I typ something in the URL bar, a message appears in the URL bar, saying "Download Windows Search to improve results for history and favorits". First of all I do not see any use for that ... and 2nd, the new Windows Search is a monster of a search thing. It's just a ridiculous kind of search thing. So, how can I get that message gone? I do not want Windows Search or make use of it in IE8. Thanx in advance Menno Open Internet Explorer (only) to http...

If statement...copy values of cells on another worksheet.
I need a formula that looks at a drop down list on a different worksheet and if it is "Sold", then the data values from T2:AC2 would show up on the other worksheet. Is this possible? I figured it out. "heater" wrote: > I need a formula that looks at a drop down list on a different worksheet and > if it is "Sold", then the data values from T2:AC2 would show up on the other > worksheet. Is this possible? I still need help. I need it to look at S2:S86 (drop down list), if it is "Sold", then T2. I beleive I have to write a formula for e...

Automatic Graphs/ Dynamic Graphs
I have a list of test results, one row for each participant. (Each result is in a seperate column) I need to make a graph of all of their results when I click on their names. Obvously I cannot create a graph for each one, as there could be hundreds of participants, I need it to create a graph using relevent cell references for each person on the fly. I have seen this done in the past, but am unsure on how to accomplish this myself. Thankyou for any help you can give me Dan Hi, Maybe you used one of these sites previous to get your information. http://peltiertech.com/Excel/Charts/Dy...

Automate a graph/chart?
I am putting together a daily list of the change in prices of stocks, and what I want to do is make a graph on another worksheet that will update whenever enter a new date and amount is entered. Is this possible? If so...how? Thanks in advance Hi, You need to use named ranges in order for the chart to update automatically. See Jon Peltier's article on dynamic charting. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Chees Andy pugsly8422 wrote: > I am putting together a daily list of the change in prices of stocks, and > what I want to do is make a graph o...

divide by zero error on purchase order report
On a clean install of headquarters manager I get a divide by zero error on the standard purchase order report. Regardless of what date range I put in it generates the same error. Any one else had this problem? Thanks You have a purchase order in the system with an exchange rate of zero. Run the following SQL Statement from HQ Administrator: Select PONumber, POTitle, StoreID, ID, ExchangeRate from PurchaseOrder where ExchangeRate = 0 This will tell you which PO is causing the error, but fixing it will be a problem as there is really no PO Editing function in HQ. You could use a SQL Upda...

Windows media player track bar
can anyone pleeease tell me how the seek bar in the media player seek to any position on the slider ???!!!! ... as opposed to the default thumb movement to adjacent frequency ticks as in windows volume control ... i have a avi player with a seek bar .. in OnHScroll, when the user clicks on the slider, a TB_ENDTRACK message is sent but the thumb moves only to the next tick, so the thumb's current position is not set to current position of the mouse ... ANY HELP WILL BE APPRECIATED VERY MUCH ... thanx Convert the Cursor postion to the Coordinates of the Control. Then set the Scroll Positio...

how can i make a climate graph
i am using excel 2007 and i need to know how to make a graph with two y axes the bars being precepitaion and and the line being temperature. how will i do this Hi, Create a standard clustered column chart based on both sets of data. Select the Temperature series, right click and pick Change Series chart type... Select the line chart type. Select the new line series and right click, pick Format Data Series. Select Series Option, if not already active, and set secondary axis. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "spongebran" <spongebran@...

detect cell value
Wat do I use to detect cell contain the word "Actual" and assign the row to a variable which I can use latter on? Hi you may explain with some more detail what you mean with 'variable assignment' and what you want to do later on -- Regards Frank Kabel Frankfurt, Germany "crapit" <littlecramP@yahoo.com.sg> schrieb im Newsbeitrag news:ea7B#8u1EHA.2540@TK2MSFTNGP09.phx.gbl... > Wat do I use to detect cell contain the word "Actual" and assign the row to > a variable which I can use latter on? > > I want to use it to hide the entire ro...

VB.NET powershell Pipeline.Invoke: pass $NULL as value
Hi all, I need to be able to pass the Powershell $null value as a parameter to a powershell pipeline invocation: EG: Mycomand = new command("set-mailbox") MyCommand.Parameters.add("Identity",sCN) MyCommand.Parameters.add("AcceptMessagesOnlyFrom", <I need the NULL value here>) Anything I've tried has resulted in a Microsoft.Exchange.configuration.Tasks..... Or I need some other way to clear this attribute from VB.NET (2008) Pardon the minimal code, but every other aspect of the pipline invocation works just fine. The only thing...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

How to produce a chart based on number of cells not values in the cells
Hi I'm using Excel 2007. I have a requirement to base a chart on the number cells rather than the values in the cell. For example, here are some values I might enter against Monday to Friday: 1,3,1,1,2 In this example, I want my (pie) chart to show that 3/5 are value 1, 1/5 is value 2 and 1/5 is value 3 (i.e. the pie chart should have 3 sections). What excel does, is split the chart into 8 sections, and shows each individual value. Any suggestions on how I might achieve this, Thanks for any help Colin You need to do some calculations in the sheet first, then plot the results: http...

Formatting zeros as dashes
I have a worksheet with the data formatted as currency with no decimal places. I would like all my zeros to show up as dashes. For example, no $ 0 or $ (0). I understand that if the number is not exactly zero, it won't use the dash, ie. a .25 will show up as a 0... but this is what I would like to override. I also don't want to actually change the value in the cell using the rounding functions. Simply put, how do I get it to choose the format based upon the display accuracy of the value versus the true value? Ann: If you think about it there is no way for Excel t...

Nagivation Bar Properties Box ( Pub 2003)
The instruction to change the nav. bar is not working. I am attempting to change the email addess from ms email to my email addr by changing the properties of the nav. bar. ------------ THE INSTRUCTION--------------- Change a link on a navigation bar You can change the destination of a link. You can also change the text that displays on the navigation bar. Select the navigation bar that you want to change. On the Format menu, click Navigation Bar Properties, and then click the General tab. Under Links, click the link that you want to change, and then click Modify Link. In the Modify L...

Cell Values do not recalculate unless you press f9
Hi, When using excel 2003 i have cells where the value is calculated by a formula, if i change one the others used to update fine but no will only update if i press F9. Any ideas what is causing and how to resolve? Thanks John In message 1176985547.592431.243990@l77g2000hsb.googlegroups.com, John.Dand@googlemail.com <John.Dand@googlemail.com> Proclaimed from the tallest tower: > Hi, > > When using excel 2003 i have cells where the value is calculated by a > formula, if i change one the others used to update fine but no will > only update if i press F9. > > An...

How do I select the last 5 non zero values in a row & get an avg?
In excel I want to get the last 5 non zero values from a row (or column) and get an average. On Wed, 24 Aug 2005 11:27:01 -0700, "Larry L" <Larry L@discussions.microsoft.com> wrote: >In excel I want to get the last 5 non zero values from a row (or column) and >get an average. For Rows: =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1))) For Columns: =AVERAGE(TRANSPOSE(OFFSET(A1,LARGE((A1:A65535>0)*(ROW(A1:A65535)),{1,2,3,4,5})-1,0))) These are array formulas so after typing them in, hold down <ctrl><shift> while hitting...

New Issue...Graphs
How would I write a function or a module to tell a month that is displayed in the table as a number to display on the graph as abbreviations such as Oct, Nov, Jan, etc? -- Message posted via http://www.accessmonster.com there is a handy function called MonthName() MonthName( number, abbreviate?) example: MonthName(3, TRUE) = Mar MonthName(3, FALSE) = March MonthName(3) = March Thanks Ghetto, I think I will be able to use that so let me take my question to the next step. I have a graph based on a query. The data the query is grabbing for "month" is n...

Count Cells Within 10 of a Cell Value
I need a formula that calculates how many out of a range of cells are within 10 of another cell. For instance, I have cells F3 through U3. I want to know how many of them are either 10 above or 10 below the value in cell V3. If cell V3 equals 20, than I want to know how many are between 10 and 20 and how many are between 20 and 30. Ideally, this would be in the same formula but two seperate formulas is also acceptable. Look in the help index for SUMIF. You can combine -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Missbrooke06" &...