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 |
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 |
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 | | |
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 |
All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com