charting under a condition 1

[westma]

Hello again. I have a line graph which plots data from column B. I need to add a series to this chart that plots only the 0's from column B, because I need to show the zero points with a different colored line. How can I do this? Thank you!

 

[SkipVought]

Hi,

I don't understand what you are going to do with ZEROS in a series???

Could you post your data and explain better what you want?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[westma]

Hi, sorry, I was just trying to go for a quick explanation.

Column B in my worksheet contains data, and my chart plots a simple line graph of all of the data. It is possible that some of the numbers in column B are zeroes. The purpose of the chart is to be able to see where the zeroes are located so that the data can be patched with different numbers. The original line simply does not plot the zeros, the line just drops to the axis. I would like to create another series that will basically check column B for zeroes and the plot a thick green line in the chart to make them easier to see.

Also, each point in column B corresponds to a date in column A. The user enters a month (in a different cell) to be plotted, and the chart displays all of the data in that month.

I tried inserting a name into the workbook and using the name as the values for the second series. The formula the name refers to is as follows:

=IF(AND(MONTH(Analysis!$A$13:A35403)=Analysis!$E$5,Analysis!$B$13:B35403=0),Analysis!$B$13:B35403)

but when I use the name in the series, it says my chart contains a link to an external reference. Can I simply change this formula to make it work?

Hopefully this is a better explanation.

 

[SkipVought]

What about this for the second series
[tt]
=if(b2=0,1,0)
[/tt]
then plot this column of data as column chart type

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[westma]

I am required to keep the plot on the same chart. I need some sort of condition formatting option for the chart. So that if the data were a 0, the line would be green, default elsewhere. But I don't think that there is conditional formatting for a chart. Do you have any other ideas on how to do this? Thanks again for your help!

 

[SkipVought]

I am required to keep the plot on the same chart.

I am not suggesting anything different! That series can be a Column Type on that chart. You SELECT the series in the chart, Chart/Chart Type and select Column.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[westma]

=if(b2=0,1,0)

then plot this column of data as column chart type

I understand from this suggestion to create another column and insert the formula in each cell. This would work, however, I am not able to do this because of the layout of my spreadsheet. I have to go through the column that I already have and find the zeroes. Sorry if I am not understanding correctly.

 

[SkipVought]

I am not able to do this because of the layout of my spreadsheet

Do you already have 256 columns of data an no room for another column?

Then put the column an ANOTHER SHEET!


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[westma]

OK, here's the scoop. Column A and Column B can change at any moment. That is, I insert data into the columns, which can range from taking up two rows to taking up 65536 rows, and my spreadsheet runs analysis on this data. If I were to use another column, I would have to insert a formula into EVERY cell in the column. When I do this, my file goes from 4.38 MB to 5.74 MB, and my boss would like file file to be about 3 MB. I can see that frustration is mounting, and I apologize, I am simply saying that inserting another column is not an option. I need to use the column that already contains the data.

I have been able to get the data to graph zeroes with a name referring to the following formula:

=IF(MONTH(Analysis!$A$13:INDIRECT("Analysis!"&Analysis!$J$35))=Analysis!$E$5,IF(Analysis!$B$13:INDIRECT("Analysis!"&Analysis!$J$36)=0,Analysis!$B$13:INDIRECT("Analysis!"&Analysis!$J$36)))

But for some reason this changes all of the data to 0's, and charts for all of the points rather than those that fall within the specified month.

From above E5 is the cell with a 1-12 for the month, J35 contains reference to the cell with the last data point in column A, and J36 contains the reference to the cell with the last data point in Column B

 

[SkipVought]

OK.

1) I Named a Range ZeroSeries with the formula
[tt]
=(Sheet1!$B$1:$B$19=0)
[/tt]

2) In the Chart/SourceData I Added a new series with the reference
[tt]
=Book2!ZeroSeries
[/tt]
3) Select the series in the Chart/Chart Type - change to Column

3) Select the series in the Chart/Format Selected Data Series - Axis Tab - Select Secondary Axis.

VOLA!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

You can make your source data ranges DYNAMIC using the OFFSET function faq68-1331 How can I rename a table as it changes size .

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

This is acutally a better formula to use in the Insert/Name/Define for ZeroSeries
[tt]
=(OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$A:$A),1)=0)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[westma]

Hey Skip, thank you for your inginuity in helping me make this work. I had to play with it a little bit to get it to look right, but your help got me on the right track, and I never would have gotten there on my own. Thanks again for all your help!