Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
When using an IF-formula, my diagram treats empty cells as zero
I have prepared a line-diagram that will show quarterly information for the
last year and the coming five years. Since data only is available for three quarters at this point of time, I want the diagram to show those three quarters now and nothing more and then automatically add an additional quarter when I have filled in that information in the input sheet. In order to get this functionality, I have tried to use two different IF-formulas: =IF(O32="n.a.","",O32) and =IF(O32="n.a.",,O32) My problem is that the Excel-diagram treats the empty cells in the spread sheet (those cells containing any of the IF-formulas above but lack any information) as zero, and do not treat them as €śtrue empty cells€ť (even though they are empty in the spread sheet). And yes, I have instructed the diagram not to plot empty cells by using Tools/options/Chart/ Plot empty cells as - not plotted. My question is now €“ can I in any way make those cells €śempty€ť in the sense that Excel will not plot those cells that are empty? i.e. is there any other writing that I can use in my IF-formula above in order to achieve this? Or is there any other way that I can get the same result, i.e. make the diagram plot another quarter automatically when the data is added in the input sheet but dont plot any future quarters? I would really appreciate a solution on this one and I know that some of my colleagues also are struggling with the same issue. Best regards, /Lars |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
When using an IF-formula, my diagram treats empty cells as zero
How about
=IF(O32="",NA(),O32) HTH Kostis Vezerides Lars F wrote: I have prepared a line-diagram that will show quarterly information for the last year and the coming five years. Since data only is available for three quarters at this point of time, I want the diagram to show those three quarters now and nothing more and then automatically add an additional quarter when I have filled in that information in the input sheet. In order to get this functionality, I have tried to use two different IF-formulas: =IF(O32="n.a.","",O32) and =IF(O32="n.a.",,O32) My problem is that the Excel-diagram treats the empty cells in the spread sheet (those cells containing any of the IF-formulas above but lack any information) as zero, and do not treat them as "true empty cells" (even though they are empty in the spread sheet). And yes, I have instructed the diagram not to plot empty cells by using Tools/options/Chart/ Plot empty cells as - not plotted. My question is now - can I in any way make those cells "empty" in the sense that Excel will not plot those cells that are empty? i.e. is there any other writing that I can use in my IF-formula above in order to achieve this? Or is there any other way that I can get the same result, i.e. make the diagram plot another quarter automatically when the data is added in the input sheet but don't plot any future quarters? I would really appreciate a solution on this one and I know that some of my colleagues also are struggling with the same issue. Best regards, /Lars |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
When using an IF-formula, my diagram treats empty cells as zer
It worked great - thank you so much!
/Lars "vezerid" wrote: How about =IF(O32="",NA(),O32) HTH Kostis Vezerides Lars F wrote: I have prepared a line-diagram that will show quarterly information for the last year and the coming five years. Since data only is available for three quarters at this point of time, I want the diagram to show those three quarters now and nothing more and then automatically add an additional quarter when I have filled in that information in the input sheet. In order to get this functionality, I have tried to use two different IF-formulas: =IF(O32="n.a.","",O32) and =IF(O32="n.a.",,O32) My problem is that the Excel-diagram treats the empty cells in the spread sheet (those cells containing any of the IF-formulas above but lack any information) as zero, and do not treat them as "true empty cells" (even though they are empty in the spread sheet). And yes, I have instructed the diagram not to plot empty cells by using Tools/options/Chart/ Plot empty cells as - not plotted. My question is now - can I in any way make those cells "empty" in the sense that Excel will not plot those cells that are empty? i.e. is there any other writing that I can use in my IF-formula above in order to achieve this? Or is there any other way that I can get the same result, i.e. make the diagram plot another quarter automatically when the data is added in the input sheet but don't plot any future quarters? I would really appreciate a solution on this one and I know that some of my colleagues also are struggling with the same issue. Best regards, /Lars |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
When using an IF-formula, my diagram treats empty cells as zero
I'd try:
=IF(O32="n.a.",na(),O32) And maybe use format|conditional formatting to hide those errors in the worksheet. Lars F wrote: I have prepared a line-diagram that will show quarterly information for the last year and the coming five years. Since data only is available for three quarters at this point of time, I want the diagram to show those three quarters now and nothing more and then automatically add an additional quarter when I have filled in that information in the input sheet. In order to get this functionality, I have tried to use two different IF-formulas: =IF(O32="n.a.","",O32) and =IF(O32="n.a.",,O32) My problem is that the Excel-diagram treats the empty cells in the spread sheet (those cells containing any of the IF-formulas above but lack any information) as zero, and do not treat them as €śtrue empty cells€ť (even though they are empty in the spread sheet). And yes, I have instructed the diagram not to plot empty cells by using Tools/options/Chart/ Plot empty cells as - not plotted. My question is now €“ can I in any way make those cells €śempty€ť in the sense that Excel will not plot those cells that are empty? i.e. is there any other writing that I can use in my IF-formula above in order to achieve this? Or is there any other way that I can get the same result, i.e. make the diagram plot another quarter automatically when the data is added in the input sheet but dont plot any future quarters? I would really appreciate a solution on this one and I know that some of my colleagues also are struggling with the same issue. Best regards, /Lars -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Only Empty Cells AFTER Cells with Data | Excel Worksheet Functions | |||
remove error value when formula exists for empty cells | Excel Worksheet Functions | |||
continuing formula when cells are empty | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Empty Cells, Spaces, Cond Format? | Excel Discussion (Misc queries) |