Using Dynamic Ranges for Excel Charts

Hi,

I am want to create a dynamic chart which will get ignore the #NA values in the table and it should not plot them.
Refer to the attached excel file for more details.
I want a graph which will only displays the values for "Mars, Jupiter, Venus, Pluto" and ignore the #NA values.

Thanks

Thanks but this might not work for me as I am looking for an automatic process.Is there a way in which the graph can automatically pick data excluding all #N/A values below "Pluto"? I believe using "Named Ranges" will solve the problem but I am not able to make it work.

This is the formula, that I use all the time

=OFFSET(CLIENT_Summary!$A$1,0,0,COUNTA(CLIENT_Summary!$A:$A),COUNTA(CLIENT_Summary!$A:$A))

I will adapt it to your usage following this post. You enter this formula in the Names manager, under the formulas tab on the ribbon. Basically you make a range by using an offset. You start at the first cell, then, from there add 0 rows and 0 columns, and adjust from the start the number of cells that are not blank (in your case the number of cells that are not NA), and adjust the vertical by the same.

I am getting very close with creating a name

ChartNoNA
=OFFSET($B$2,0,0,COUNTIF($B:$B,"<>"&NA())+1,COUNTA($2:$2)+1)

Just have to tweak the plus ones a bit, or start the range at another spot.

=OFFSET(Sheet1!$B$3,-1,0,COUNTIF(Sheet1!$B:$B,"<>"&NA())+1,COUNTA(Sheet1!$2:$2)+1)

Worked for me....named it ChartNoNA, see posted workbook.

Thanks!!!
Is there a way that it can be tweaked so that any legitimate (non #N/A)values that are added after Pluto are also picked by the graph?
Like there is another row titled "Earth" which has some non-zero values.

Can empty or #N/A series be deleted from the legend without using any VBA code?

Originally Posted by teylyn

Include the whole data table in the range and just delete the empty series from the legend.

I know..I was hoping for a more automatic process.

Hi,
If I delete the data series say "Pluto" the graph doesnot exclude it but shows a series "#N/A". Also if I add another series after Pluto it doesnot get picked up by the chart.
It will be great if you can help me resolve these problems.
I am just curious if the graph use the named ranges that you have created? I am asking because I coudn't see a reference.

Originally Posted by delforum

=OFFSET(Sheet1!$B$3,-1,0,COUNTIF(Sheet1!$B:$B,"<>"&NA())+1,COUNTA(Sheet1!$2:$2)+1)

Worked for me....named it ChartNoNA, see posted workbook.

Hi,
I modified the definition of the Named Range "ChartNONA" to OFFSET(Sheet1!$B$3,-1,0,Sheet1!$B$20,COUNTA(Sheet1!$2:$2)+1)

$B$20- capture the number of cell which are non blanks AND non #N/A.

So the named series is able to capture my requirement i.e when i add another series it includes that in the range and similarly when I removed a series with #N/A values it excludes that from the range.

Now I would like to use this named range in a graph. I provide Chart Data Range = 'Test.xls'!chartNONA
The graph pciks the required range and display it properly as per my requirement.

The problem is when I modify the range and provide new data series the chart doesnot pick that and if i click on select data and check the chart range it has become something similar to "'Sheet1'!$B$2:$AX$8"

The input that we have provided is gone

Anyway I can do a work around and fix this problem??

Thanks

Perfectly dynamic formula, you can add rows of planets, or dates of data. I used your formula modifications from test, and made them universal.

ChartNoNA range variable in the Names Manager

=OFFSET(Sheet1!$B$3,-1,0,COUNTA(Sheet1!$B:$B)-COUNTIF(Sheet1!$B:$B,"<>"&NA())+1,COUNTA(Sheet1!$2:$2)+1)

Just keep your blank spot at B3, and keep the start of your data where it is at, with the N/A's all at the end of the list


See the attached file.

I see your issue. When you add lines, and want to update the chart, you have to go back and put in the new range, by simply typing in ChartNoNA. The only way around this is running a macro. I created one by using record.

Please Login or Register  to view this content.

See the attached sheet, I made it run when you change something on the worksheet.

When you insert rows, it will give you a small error about #REF, but when you hit OK, the chart is updated.

You must enable macros security settings.

Hi,

I am sorry for bumping this old thread. I was trying to do this without using Macro and looks like there is a way to do this by using the SERIES formula and named ranges for "Chart Data Range".
I have created 3 named ranges

ChartNoNA: OFFSET(Sheet1!$B$3,-1,0,$b$20,COUNTA(Sheet1!$2:$2)+1) ---Capture the complete data which dont have a blank and #N/A time series.

ChartNoNA_Series: OFFSET(Sheet1!$B$3,-1,0,Sheet1!$B$20,COUNTA(Sheet1!$2:$2)+1) --- Captures only the series name (Mars, Jupiter, Venus, Pluto, etc.)

ChartNoNA_Values: OFFSET(Sheet1!$B$3,0,1,Sheet1!$B$19,48) ---- Captures the values for the abouve time series.

Now I am not sure how excatly I can use the above named ranges(along with others if required) to create a dynamic graph.

Any help on this will be greately appreciated !!

Thanks.