×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Data selection in plotting a Graph
5

Data selection in plotting a Graph

Data selection in plotting a Graph

(OP)
Hi all..

subject looks pretty simple,but....
problem is ....i have a spreadsheet in which,for a set of data ,graph has to be plotted.
The cells in which data range is there is bound to a condition.
If once i input the data in the sheet,corresponding to this ,the data range changes & the range should be selected and get plotted.

Did  i made it complicate.....
do suggest me solution on this issue.

Thank u

RE: Data selection in plotting a Graph

Named ranges?

RE: Data selection in plotting a Graph

(OP)
may b....
i will try to put tat prob in a simple way.....
data table (X,Y);
X=1,2,3,4,5.......(incremental)
Y=0.2,0.4,0.6.....(force factor,formula dependenet)
X values keeps on incrementing by +1 and terminates at  a particular value(conditional based).
Now the graph should be such tat it should select (X,Y)till X value terminates.Later cells should not be selected.

May be a macro sort,but i'm not able to give the shape....

RE: Data selection in plotting a Graph

There are two possible approaches.

>  If you make the plot range arbitrarily large, Excel will dump all the blank lines.  As you add data to the actual valid range Excel will add the new points to the plot.

>  If you copy the last line and insert the copy just before the original last line, Excel will expand the range automatically in the plot.  Then enter the new data into the current last line.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Data selection in plotting a Graph

2
Look at setting up dynamic range names.  The key is to use the offset function.  For example suppose you have data in in cells a1:a26 and you would like to plot the last 10 values in column A.  Then, create a formula name, say ChartData, where the refers to box is =OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A)-10,0,10,1).  Now the series formula in your chart uses the formula name as follows:
=SERIES(,,'Dynamic Range Example.xls'!ChartData,1)
Anytime you add more data to colum A your chart will change automatically.

RE: Data selection in plotting a Graph

3
Define two names (via Insert/Name/Define, or Ctrl-F3), for example x and y, with the following formula (instead of a static reference like Sheet1!$A$2:$A$5):
x: =OFFSET($A$2,0,0,COUNT($A:$A),1)
y: =OFFSET($B$2,0,0,COUNT($A:$A),1)
This assumes that your x-range starts at A2, and the y-range at B2, modify as you need.
Then, the series in your chart should have as x values range x, and y values range y, so you would type for the series formula:
=SERIES(,Sheet1!x,Sheet1!y,1)

that should do it. I think that Jon Peltier has something on this as well, google for dynamic range names and charts.

Cheers,
Joerd

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Data selection in plotting a Graph

A star for joerd.  You are a real guru. Who'd of thunk you could assign a name to a formula instead of a range.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Data selection in plotting a Graph

My apologies for not acknowledging cummings54 who had a similar suggestion.

Thinking about it some more, I guess I have heard of that.  On this forum someone posted the Chart Formula spreadsheet by Stephen Bullen.... generates a graph from a formula types into a spreadsheet without ever using any numerical data in cells.  You can adjust the x-axis range and the number of points effortlessly.  Seems kind of mystical to me, but now I remember it was built on the princple of named formulas.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Data selection in plotting a Graph

(OP)
Thanks buddies.....
the show has come to an end....Infact i was  bit unfamiliar with dynamic activities in excel,in the initial stages..but anyhow now it was pretty clear...
All ur help are greatly apreaciated.
especially for jeord,whose direction to turn to Jon Peltier ......it was really awesome.

Thank u all....

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources