If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Zero is a dropdown Take 2
I tried this earlier and nothing happened. I'm setting up a chart with several series instead of a data range (wow, looks like I know what I'm talking about. LOL) and I'm doing a line graph. Problem is that when I have zero's or an N/A it drops down and then comes back up. I'm looking to have it just stop and then start back up the next week, instead of dropping down but don't know how to do that or even what to call it or else I would search for it. -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#2
|
|||
|
|||
Zero is a dropdown Take 2
DKY:
There are several possible conditions that can cause your chart to drop to zero. 1. Your data cell has a zero (0). 2. Your data cell is blank. You can use Tools Options Chart Plot Empty Cells as (Not Plotted - Leave Gaps) 3. Your data cell has a formula like = If (A1 = "", "", 99) or = If(A1="",Na(),99) 4. Your data cell has text instead of a number (Excel plots text as zero's) The specific fix depends on your situation. If you have empty cells (no formulas or values), then the tools Options Charts Plot empty Cells as choice will solve problem If you have zeros, Excel will plot them as zero unless you remove them or use a formula like = If(A1=0,Na(),A1) If you have cells with string (you mentioned N/A), then Excel will plot as 0. There are many web posts on plotting missing data. You may want to start with this one: http://processtrends.com/pg_charts_missing_data.htm ...Kelly "DKY" wrote in message news I tried this earlier and nothing happened. I'm setting up a chart with several series instead of a data range (wow, looks like I know what I'm talking about. LOL) and I'm doing a line graph. Problem is that when I have zero's or an N/A it drops down and then comes back up. I'm looking to have it just stop and then start back up the next week, instead of dropping down but don't know how to do that or even what to call it or else I would search for it. -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#3
|
|||
|
|||
Zero is a dropdown Take 2
Actually I have a formula in there that looks like this =IF(D10"",1-(E10/D10),"") and when I go to Tools Options Chart Plot Empty Cells as is already set to (Not Plotted - Leave Gaps) So, I would think that it would not plot it. I even tried deleting the formula and it still plots as zero. -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#4
|
|||
|
|||
Zero is a dropdown Take 2
DKY
Excel treats cells with formulas as Not Empty. The Not Plotted (Leave Gaps) doesn't work because the cell is not empty. When you have formulas, the fix is to use NA() instead of "". Excel will place a #N/A in those cells. Excel recognizes NA() and interpolates through the #N/A cells. Your formula should look like: = IF(D10"",1-(E10/D10),NA()) If you want gaps in your data series instead of interpolation, then you can replace the formulas with values by copying data and using Edit - Paste Special Values then remove all #N/As. ....Kelly "DKY" wrote in message news Actually I have a formula in there that looks like this =IF(D10"",1-(E10/D10),"") and when I go to Tools Options Chart Plot Empty Cells as is already set to (Not Plotted - Leave Gaps) So, I would think that it would not plot it. I even tried deleting the formula and it still plots as zero. -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#5
|
|||
|
|||
Zero is a dropdown Take 2
I used the formula and it gives me the #N/A but the chart still has a line that shoots down to zero in the middle of it. I'm not sure what I'm doing wrong here, I even got rid of the formula, line's still there. I then put a #N/A in the cell, line's still there. I don't get it. I have the option selected in the options that if its blank then don't plot but when the cell is empty it still draws a line down and plots it at zero. I'm so lost. -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#6
|
|||
|
|||
Zero is a dropdown Take 2
Does the fact that I'm plotting several different series in one chart matter? The reason I ask is that one of the series works where it doesn't plot but the other two don't -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#7
|
|||
|
|||
Zero is a dropdown Take 2
Hi:
Just a hunch: could it be that you have (1) a hidden row in your data range with zero, text or blank value AND (2) you have unchecked "Plot visible cells only" in Tools/Options/Chart? Henk "DKY" wrote: Does the fact that I'm plotting several different series in one chart matter? The reason I ask is that one of the series works where it doesn't plot but the other two don't -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#8
|
|||
|
|||
Zero is a dropdown Take 2
Okay, Here's what I've got Not plotted (leave gaps) Checked Plot visible cells only UnChecked Chart sizes with window frame Checked Show names Checked Show values Checked each of my three series are from three different sheets and there are no values where it should be a no value, not even a function. Two of my three are doing what they're supposed to be doing but one is dropping down to the zero's for some reason. I don't understand, could there be something wrong with the sheet itself? -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#9
|
|||
|
|||
Zero is a dropdown Take 2
I made a new sheet for the one series and replotted it, it works now. I don't know what the problem was but thanks for the help everyone. -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
#10
|
|||
|
|||
Zero is a dropdown Take 2
I wld try checking "Plot visible cells only". Hit F9 if you set yr
calculation to manual and if this solves your problem you have hidden rows out there with zero, text or blank values, giggling in the dark. It may not seem very likely, but all you can do is checking all possible causes Kelly mentioned carefully one by one. In particular check Kelly's fourth option. If your cell is FORMATTED as text (check Cell/Format/Number), a number may very well be TREATED as text and this can be non-obvious as you entered it as a number and you see it as a number, but Excel treats it as a text label. I hope this helps. Henk "DKY" wrote: Okay, Here's what I've got Not plotted (leave gaps) Checked Plot visible cells only UnChecked Chart sizes with window frame Checked Show names Checked Show values Checked each of my three series are from three different sheets and there are no values where it should be a no value, not even a function. Two of my three are doing what they're supposed to be doing but one is dropping down to the zero's for some reason. I don't understand, could there be something wrong with the sheet itself? -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=535942 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
offer dropdown options based on another dropdown | Conor | General Discussion | 2 | January 13th, 2006 04:28 PM |
Help a newbie: Populating dropdown lists on a form using data from anothr dropdown list | DAVID | General Discussion | 1 | June 14th, 2005 12:21 AM |
Lag on dropdown action, when values change? | Larry | General Discussion | 2 | March 22nd, 2005 01:03 PM |
dynamically create unlimited dropdown box | jyang2005 | General Discussion | 0 | November 4th, 2004 09:35 PM |