Selective Display of Data on a Chart

[DanExcel]

Summary: Is there a way to make a chart not display "0" values?

Details: I have a chart that display all the data in a column. I want it to stop displaying at the row that has a zero in it. So, if my Y-values are in column D, and everything under D300 is 0, I want the chart only to display values upto the 300th row. I am willing to do this within "normal" excel or with visual basic.

Any suggestions? Please let me know if the question is unclear.

-DanExcel

[Damon Ostrander]

Hi DanExcel,

There are myriad ways of accomplishing this, but the easiest is probably just to modify your formulas or whatever produces the data to leave the cells empty rather than writing zeroes in them. If a formula returns the zero you could modify it thus:

=IF(your formula=0,"",your formula)

to yield an empty cell rather than a zero. Of course, the chart options must be set to not plot empty cells (Tools -> Options -> Charts tab).

[Mark W.]

Better make that...

=IF(D2,D2,#N/A)

Use this formula to produce your Y values from the values in column D.

Damon, a cell containing "" isn't empty.

[DanExcel]

Damon and Mark,

I appreciate both of your replies. I had actually already tried Damon's suggestion, and found that it would still plot the values as "0". Using #N/A works better because it does not plot the values at all.

It does, however, still expand the graph scale so that it will fit all of the accomoate all of the cells used in the clumn, as if they had data. If you know somehow to make it actually scale to the data that is there, that would be helpful.

Thank you for your input. Let me know if I can clear anything up.

-DanExcel

[Mark W.]

If you know somehow to make it actually scale to the data that is there, that would be helpful.

Thank you for your input. Let me know if I can clear anything up.

-DanExcel

This can be accomplished using dynamic ranges. Are you familiar with 'em?

Take a look at http://www.mrexcel.com/board/viewtop...ic=267&forum=2.

[ This Message was edited by: Mark W. on 2002-04-03 10:05 ]

[DanExcel]

I am not familiar with them, and I didnt find anything in excel help for "dynamic range" (although I may not know what to look for). Can you direct me to a reference?

Thank you

-DanE

[Mark W.]

Here's one...

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

You can also Search this board for a myriad of examples.

[DanExcel]

Ah - I didnt realize that the OFFSET function was what you meant by dynamic ranges. I understand this function (I think), however, when I try to use it in the chart dialog box for the X or Y values of a series, it gives an "Invalid Function" error. Can this dynamic range be used in defining a series in the chart dialog box, or does it need to be in VB?

Also, fyi, there is a similar post on dynamic ranges entitled "Charting Ranges" (started by Dazed_). The example formula the post is:

=OFFSET('sheetname'!$B$5,0,0,COUNTA('sheetname'!$B:$B)-1,1)

I'm not sure if it was intended for use in the chart dialog box, tho.

Thank you,

-DanE

[Mark W.]

On 2002-04-03 13:39, DanExcel wrote:
Ah - I didnt realize that the OFFSET function was what you meant by dynamic ranges. I understand this function (I think), however, when I try to use it in the chart dialog box for the X or Y values of a series, it gives an "Invalid Function" error. Can this dynamic range be used in defining a series in the chart dialog box, or does it need to be in VB?

Also, fyi, there is a similar post on dynamic ranges entitled "Charting Ranges" (started by Dazed_). The example formula the post is:

=OFFSET('sheetname'!$B$5,0,0,COUNTA('sheetname'!$B:$B)-1,1)

I'm not sure if it was intended for use in the chart dialog box, tho.

Thank you,

-DanE

You'd create local (sheet) defined names that refer to these offsets and use the defined names in your chart's SERIES functions...

=SERIES(,Sheet1!X_Range,Sheet1!Y_Range,1)

If you data is organized like...

{"X","Y"
;1,10
;2,20
;3,30}

Sheet1!Y_Range could simply refer to...

=OFFSET(Sheet1!X_Range,,1)

If the above sample data is in Sheet1!A1:B4 then Sheet1!X_Range would refer to...

=OFFSET($A$1,,,MATCH(9.99999999999999+E307,$A:$A))

Now, when you add {4,40} to row 5 the chart containing the SERIES function shown above would automatically incorporate the data point, (4,40).

[ This Message was edited by: Mark W. on 2002-04-03 14:57 ]

[DanExcel]

Thank you again for your assistance -- I have been lead to another roadblock, however... The COUNTA() function is counting the #N/A cells... So the graph still displays a much larger range than necessary. Is #N/A entered literally as it is? Or is there a special way to enter it?

Thank you,

-DanE