Dynamic Range for Scatter Graph

[Hereward]

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

 

[Hereward]

Thats correct rows 7,6,8

There will always be the same amount of data for each of the rows, (e.g. d6-z6/d7-z7/d8-z8) so for x at the moment it is displaying the correct values; the count and each of the "y" variables value at that count. However that is because I have limited it to extend only to the last numeric value in the row. If I extend it anymore, the count goes up. It is not plotted but the "x" axis extends the count by however many total cells there are in the row regardless of whether or not they have #N/A.

 

[Hereward]

[/URL][/IMG]


Here is what happens if I extend the values across the whole row if my explanation is unclear.

 

[Jon Peltier]

But what are those X values supposed to be? Simply the count, that is, the point number? If that is all they are, then you can omit the X values and Excel will just use the set of counting numbers from 1 to the number of Y values.

Assuming that's what is going on, we can set up the following Names in the worksheet.

Go to the Formulas tab of the ribbon, click Define Name, and define the following Names, all scoped to the active sheet:

Name: RowSix
RefersTo:
=OFFSET(Sheet1!$D$6,0,0,1,COUNT(Sheet1!$6:$6))

(where Sheet1 is the name of your worksheet. Be sure to use single quotes around the sheet name if needed.)

Name: RowSeven
RefersTo:
=OFFSET(Sheet1!$D$7,0,0,1,COUNT(Sheet1!$7:$7))

Name: RowEight
RefersTo:
=OFFSET(Sheet1!$D$8,0,0,1,COUNT(Sheet1!$8:$8))

Make your chart using an arbitrary subset of the data in rows 6 through 8. Make a new chart. I suspect your existing one has too tortured a history to be useful.

For example, my three series have these three series formula:

=SERIES(,,Sheet1!$D$7:$N$7,1)
=SERIES(,,Sheet1!$D$6:$N$6,2)
=SERIES(,,Sheet1!$D$8:$N$8,3)

Open the Select Data dialog, select Series1 in the list, and click Edit. In the Series Y Values box, change
=Sheet1!$D$7:$N$7
to
=Sheet1!RowSeven

Similarly in the other two series, change
Sheet1!$D$6:$N$6 to Sheet1!RowSix
and
Sheet1!$D$8:$N$8 to Sheet1!RowEight

The series formulas now look like
=SERIES(,,Sheet1!RowSeven,1)
=SERIES(,,Sheet1!RowSix,2)
=SERIES(,,Sheet1!RowEight,3)

Sometimes Excel even lets you change the series formulas directly, but that doesn't always work.

Now as the data extends, the formulas that return either the values or #N/As update, and the chart reflects the updated data range.

 

[Hereward]

That has worked perfectly thank you very much.

I have read up on named rows but couldn't quite grasp it until now.

Your help is immensly appreciated,

Thank you.