Thanks:  0
Likes:  0

# Thread: Dynamic Range for Scatter Graph

1. ## Dynamic Range for Scatter Graph

Hi,

I am trying to develop a dynamic range for a scatter graph from an index that selects a row of data from the raw data below for comparison. My formula at the moment looks like what you can see below; however I can't quite get it to work. Is there something obvious I am missing?

=SERIES('Total Report'!\$C\$6,'Total Report'!=OFFSET(\$D\$5,0,0,MAX(IF(ISNUMBER(5:5),ROW(5:5)-3,0)),1)),'Total Report'!=OFFSET(\$D\$6,0,0,MAX(IF(ISNUMBER(5:5)-3,0)),1)),1)

Regards,

Hereward

2. ## Re: Dynamic Range for Scatter Graph

are you comparing 1 row with the whole data - what are you trying to achieve ?

3. ## Re: Dynamic Range for Scatter Graph

Your SERIES formula can only contain references to ranges or hard-coded values.

You can define Names that include the OFFSET formulas, and reference these names in the SERIES formula. See Dynamic Charts for a tutorial.

4. ## Re: Dynamic Range for Scatter Graph

Hi, All thank you for the advice.

I was comparing one row against another row and plotting it onto a scatter graph. I managed to have the scatter graph working by using the following:

=IF(ISBLANK(B32),#N/A,INDEX(B32:B137,\$B\$5))

and then the scatter plot is completed for with the following:

=SERIES('Total Report'!\$C\$7,'Total Report'!\$D\$6:\$XFD\$6,'Total Report'!\$D\$7:\$XFD\$7,1)

This has allowed me to create a scatter graph that only plots cells with numbers in. However the trouble I have now is that I am trying to create a line graph for the same pieces of data. I want to compare three different rows of data and I can indefinitely extend 2 of the data sets however the first one, which I am assuming the line chart creates the "count" still counts #N/A as a value and therefore I end up with all the data right up against the Y-axis.
Is there a reason this method works for the scatter graph and not the graph measuring the value by count?

Thank you for any feedback

5. ## Re: Dynamic Range for Scatter Graph

If it makes it easier to understand, the new graph is not plotting the points as "0" it is automatically adjusting the axis to be the full count all the way up to "XFD". Is there anyway to ensure the x-axis range is determined by the the total number of cells not containing "#N/A".

=SERIES('Total Report'!\$C\$6,,'Total Report'!\$D\$6:\$JM\$6,1)

=SERIES('Total Report'!\$C\$7,,'Total Report'!\$D\$7:\$XFD\$7,2)

=SERIES('Total Report'!\$C\$5,,'Total Report'!\$D\$5:\$XFD\$5,3)

As you can see 2,3 I can plot all the way to "XFD" however if I do the same to 1 the x-axis changes the count all the way to XFD. To clarify the amount of data will change on daily or even hourly basis therefore I cannot set a range as there will inevitably be blanks or data excluded.

I hope I have explained myself well enough.

6. ## Re: Dynamic Range for Scatter Graph

What data are you using for your X values? If you leave that blank in the series formula, Excel will use the counting numbers (1, 2, 3, ...), and it isn't smart enough to only assign counting numbers to points that have numbers for Y values.

Actually, I don't really understand what your data is doing. Could you show a screen shot of the data, and what you're trying to plot?

7. ## Re: Dynamic Range for Scatter Graph

[IMG][/IMG]

Hi Jon,

Here is a screen shot. What I am trying to do is related to the graph in the middle. C5,C6 and C7 onwards is the data. At the moment there are about 272 entries and the rest of the row is filled with #N/A in accordance to the previously mentioned criteria. The Scatter graph will plot all numbers and only numbers depending the amount of values in the data rows ignoring N/A. However the middle where I extend the first series to the end of the sheet will count every cell including N/A is there anyway to generate the count based only on cells with values?

8. ## Re: Dynamic Range for Scatter Graph

Okay, this is a little more clear.

Are the #N/A values located anywhere in the row, or are the numbers all first and #N/A after you run out of numbers?

Are the same rows always plotted?

I think you're making this harder on yourself than you have to, and I also think this is one of those cases where VBA is not needed.

Are your X and Y values shown at least in part in the screenshot? What is the address of the cell containing the value 0.69? (You forgot to capture the row and column headings.)

9. ## Re: Dynamic Range for Scatter Graph

Are the #N/A values located anywhere in the row, or are the numbers all first and #N/A after you run out of numbers?

The latter, all numbers first, followed by N/A.

Are the same rows always plotted?

The same rows are always plotted.

Are your X and Y values shown at least in part in the screenshot? What is the address of the cell containing the value 0.69? (You forgot to capture the row and column headings.)

The value 0.69 is D6, but this is indexed from B37 which can be see in the bottom of the screen shot, which is derived from this (which is in D6):
=IF(ISBLANK(B32),#N/A,INDEX(B32:B137,\$A\$5))

I hope that makes it clear, and thank you for you help.

10. ## Re: Dynamic Range for Scatter Graph

All right. The blue, red, and green data in the chart seem to correspond to rows 7, 6, and 8. Is this correct?

And, what should the chart use for X values?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•