charts

[brin]

I have a line chart that shows data ok but shows a continuous flat line when the data stays at the same value ie:

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=71 border=0 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17 x:num="31044">$31,044.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="31349">$31,349.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="31099">$31,099.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="31099">$31,099.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="30639">$30,639.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="30639">$30,639.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="30639">$30,639.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="30639">$30,639.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="30639">$30,639.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="30639">$30,639.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="30639">$30,639.00 </TD></TR></TBODY></TABLE>
Is there a way to stop the data plotting at the 30,639 level and to not show a flat line?
I have tried putting in a space but it shows a zero and the plotted line drops to zero:
=IF(Z19=Z18,"",W19)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[VoG]

Try

=IF(Z19=Z18,NA(),W19)

 

[brin]

Thanks VoG
I tried that but still get zero

 

[brin]

VoG
Thanks for your help
I tried your suggetion on a separate sheet and it worked, so I think there must have been data in some of the cells that it was referencing
So I created a separate column of data and then applied the formula and it worked well
Problem solved

 

[shemayisroel]

In the data you will notice #NA

To make this not visible in your data use conditional formatting, change it to formula is and type

Example in A1

=ISNA(A1), can copy down or across.

Format font colour to white of your background is white etc

 

[brin]

Thanks
I will try that, however I have the data in a hidden column now as it was difficult to use the source cells due to the effect on other parts of the spreadsheet

 

[shemayisroel]

Thanks
I will try that, however I have the data in a hidden column now as it was difficult to use the source cells due to the effect on other parts of the spreadsheet

No probs, if you hide the data and are charting off that data it will make your charts not chart anything???

 

[brin]

I hadn't thought of that
thanks

 

[shemayisroel]

A way around it is to create the data required on a seperate sheet and then hide your entire sheet.

But the best way is to try and structure your origional data to be able to chart it

 

[brin]

Hi all
I found a way to show hidden data on the charts
By doing this I can keep my data that I dont want others to see on the same sheet as my other calculations and still get the chart to show the data:

<TABLE class=MsoNormalTable style="WIDTH: 100%; mso-cellspacing: 0in; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width="100%" border=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-row-margin-right: 8.5pt"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 100%; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width="100%">Display hidden worksheet data in a chart <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-cell-special: placeholder" width=11>
</TD></TR><TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 7.5pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 7.5pt; PADDING-BOTTOM: 6.25pt; BORDER-LEFT: #ece9d8; WIDTH: 100%; PADDING-TOP: 6.25pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width="100%" colSpan=2>

You can quickly include hidden worksheet data in a chart by changing the following option.<o></o>​

1. Select the chart in which you want to plot hidden worksheet data. <o></o>
2. On the Tools menu, click Options. <o></o>
3. On the Chart tab, under Active chart, clear the Plot visible cells only check box.

This worked for me

<o></o>
</TD></TR></TBODY></TABLE><!--@@FEEDBACKWIZ@@-->