Selective Display of Data on a Chart

DanExcel

New Member
Joined
Mar 6, 2002
Messages
29
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top