Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel to not plot emty cells
I have a line chart created from a series of data that is calculated
using some formulae. Sometime the data is not applicable for a particular time period so the formula looks something like:- =if(A1=7,"",B1) -- this is not the actual formula but basically if it's not applicable then I set the cell to double quotes. My problem is that Excel plots these double quotes as a zero whereas I would really like to show a gap in the line. I know there is a preference setting to do this and I have it set correctly. If I just type in some dummy data and skip a value the chart plots correctly i.e. produces a gap in the line. Is there anything I can set in my formula to get Excel to plot a break in the line? Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel to not plot emty cells
Paul,
Set the cell to #N/A instead of "" to get the desired result on your chart "Paul Finer" wrote: I have a line chart created from a series of data that is calculated using some formulae. Sometime the data is not applicable for a particular time period so the formula looks something like:- =if(A1=7,"",B1) -- this is not the actual formula but basically if it's not applicable then I set the cell to double quotes. My problem is that Excel plots these double quotes as a zero whereas I would really like to show a gap in the line. I know there is a preference setting to do this and I have it set correctly. If I just type in some dummy data and skip a value the chart plots correctly i.e. produces a gap in the line. Is there anything I can set in my formula to get Excel to plot a break in the line? Many thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel to not plot emty cells
On 2007-03-09 09:07:06 +0000, David said:
Paul, Set the cell to #N/A instead of "" to get the desired result on your chart "Paul Finer" wrote: I have a line chart created from a series of data that is calculated using some formulae. Sometime the data is not applicable for a particular time period so the formula looks something like:- =if(A1=7,"",B1) -- this is not the actual formula but basically if it's not applicable then I set the cell to double quotes. My problem is that Excel plots these double quotes as a zero whereas I would really like to show a gap in the line. I know there is a preference setting to do this and I have it set correctly. If I just type in some dummy data and skip a value the chart plots correctly i.e. produces a gap in the line. Is there anything I can set in my formula to get Excel to plot a break in the line? Many thanks Wow, that was quick! Thanks a million...it worked a treat! Cheers Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Excel to not plot emty cells
Using =NA() instead of "" causes the graph to go from the previous point to the next.
-- Kind regards, Niek Otten Microsoft MVP - Excel "Paul Finer" wrote in message news:2007030908521816807%paulfinerNO@SPAMmaccom... |I have a line chart created from a series of data that is calculated | using some formulae. | Sometime the data is not applicable for a particular time period so the | formula looks something like:- | | =if(A1=7,"",B1) -- this is not the actual formula but basically if | it's not applicable then I set the cell to double quotes. | | My problem is that Excel plots these double quotes as a zero whereas I | would really like to show a gap in the line. | | I know there is a preference setting to do this and I have it set correctly. | If I just type in some dummy data and skip a value the chart plots | correctly i.e. produces a gap in the line. | | Is there anything I can set in my formula to get Excel to plot a break | in the line? | | Many thanks | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete emty file names from "recent documents" Excel | Excel Discussion (Misc queries) | |||
remove emty rows in a table | Excel Worksheet Functions | |||
Remove emty values from a array | Excel Programming | |||
Last cell and first emty???!!! | Excel Programming | |||
emty excel frame | Excel Programming |