Dynamic Range for Scatter Graph

Hereward

New Member
Joined
Jul 15, 2015
Messages
12
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
sdfghyui_zpsn4d0t0q0.jpg
[/URL][/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?
 
Upvote 0
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.)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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