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
|
|||
|
|||
Can a cell be ignored by both a chart and a function?
In order for a chart to ignore a cell (i.e., not treat it as zero), I enter
the #N/A error (by typing or returning the function =NA()) in the cell. But this will not work if I want functions such as STDEV(), MAX(), etc., to ignore the cells; they will return an error if any cell in the argument range is an error. For functions to ignore a cell, I must enter (or my formula must return) a null string (="") in the cell. Is there a way I can I satisfy the requirements of both charts and functions to ignore a cell (without needing to maintain two separate columns, and with as least monstrous a formula as possible)? Any insights appreciated. |
#2
|
|||
|
|||
Can a cell be ignored by both a chart and a function?
On Sun, 22 Jul 2007, in microsoft.public.excel.charting,
hmm said: Is there a way I can I satisfy the requirements of both charts and functions to ignore a cell (without needing to maintain two separate columns, and with as least monstrous a formula as possible)? Any insights appreciated. Not to my knowledge. Which I agree is annoying. There is no reason why Excel should not give you the option in the Tools.. Options.. Chart dialogue to ignore FALSE, as functions like AVERAGE(), etc. ignore FALSE. Instead, charts treat FALSE as zero. Your only real option is to maintain two ranges of cells, one for calculation and table presentation, and one for charting. The one for charting can turn all instances of FALSE or "" to N/A using a formula, so that they will not appear in a graph. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#3
|
|||
|
|||
Can a cell be ignored by both a chart and a function?
I tried this and it worked
Set cell A1 to 1, B1 to 2, C1 to 3, etc. In the cell that you want to have ignored, if possible, enter the following if statement =IF(A1="A",1,"") I did a simple auto sum and it ignored it. God Bless Frank "hmm" wrote: In order for a chart to ignore a cell (i.e., not treat it as zero), I enter the #N/A error (by typing or returning the function =NA()) in the cell. But this will not work if I want functions such as STDEV(), MAX(), etc., to ignore the cells; they will return an error if any cell in the argument range is an error. For functions to ignore a cell, I must enter (or my formula must return) a null string (="") in the cell. Is there a way I can I satisfy the requirements of both charts and functions to ignore a cell (without needing to maintain two separate columns, and with as least monstrous a formula as possible)? Any insights appreciated. |
#4
|
|||
|
|||
Can a cell be ignored by both a chart and a function?
Now plot it with a line or XY chart and you'll understand the initial
question. The "" is treated as a zero value with a corresponding data point. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Frank Pytel" wrote in message ... I tried this and it worked Set cell A1 to 1, B1 to 2, C1 to 3, etc. In the cell that you want to have ignored, if possible, enter the following if statement =IF(A1="A",1,"") I did a simple auto sum and it ignored it. God Bless Frank "hmm" wrote: In order for a chart to ignore a cell (i.e., not treat it as zero), I enter the #N/A error (by typing or returning the function =NA()) in the cell. But this will not work if I want functions such as STDEV(), MAX(), etc., to ignore the cells; they will return an error if any cell in the argument range is an error. For functions to ignore a cell, I must enter (or my formula must return) a null string (="") in the cell. Is there a way I can I satisfy the requirements of both charts and functions to ignore a cell (without needing to maintain two separate columns, and with as least monstrous a formula as possible)? Any insights appreciated. |
#5
|
|||
|
|||
Can a cell be ignored by both a chart and a function?
Jon;
I haven't tested it but you are more than likely absolutely correct. I remember having a similar problem. Instead of using the =N/A() I made the values equal to the last credible value in the data set. I haven't finished that one yet, but I hope it will work well. If you have time and wouldn't mind, woul you be so kind as to read the following post? a href="http://www.microsoft.com/wn3/aspx/notifauth.aspx?url=http://www.microsoft.com/office/community/en-us/default.mspx%3fdg%3dmicrosoft.public.excel.chartin g%26mid%3d10b38c31-799c-410e-9bf5-5636d532c668" target="_blank"bRead and rate the response/b/a I'm on my way there now to reply to a response. I am an idiot and need all the help I can get with these math and excel formulas. Thanks for correcting me. Frank "Jon Peltier" wrote: Now plot it with a line or XY chart and you'll understand the initial question. The "" is treated as a zero value with a corresponding data point. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Frank Pytel" wrote in message ... I tried this and it worked Set cell A1 to 1, B1 to 2, C1 to 3, etc. In the cell that you want to have ignored, if possible, enter the following if statement =IF(A1="A",1,"") I did a simple auto sum and it ignored it. God Bless Frank "hmm" wrote: In order for a chart to ignore a cell (i.e., not treat it as zero), I enter the #N/A error (by typing or returning the function =NA()) in the cell. But this will not work if I want functions such as STDEV(), MAX(), etc., to ignore the cells; they will return an error if any cell in the argument range is an error. For functions to ignore a cell, I must enter (or my formula must return) a null string (="") in the cell. Is there a way I can I satisfy the requirements of both charts and functions to ignore a cell (without needing to maintain two separate columns, and with as least monstrous a formula as possible)? Any insights appreciated. |
Thread Tools | |
Display Modes | |
|
|