Plot Line in data table

I have a table of data

8 9 10 11 12 13 14 15
1.00 28 32 35 40 47 59 71 82
1.50 42 47 53 60 71 88 106 123
2.00 56 63 70 80 94 115 141 184
2.50 70 79 83 100 115 147 176 206
3.00 84 95 105 120 141 176 212 247
3.50 98 111 125 140 165 206 247 288
4.00 112 128 140 160 188 235 282 329
4.50 126 142 158 180 212 265 317 370
5.00 140 158 175 201 236 294 353 411
5.50 154 174 193 221 259 323 388 452
6.00 168 189 211 241 283 353 423 493


I want to be able to plot a line through this for a specific value. Is this
possible. eg if i want a line for 120, excel will find best fit line through
chart. I want this to overlay on actual values as above.

Any help much appreciated.

Thanks

Sorry, the top row and left columns arnt included. Correct table below.
28 32 35 40 47 59 71 82
42 47 53 60 71 88 106 123
56 63 70 80 94 115 141 184
70 79 83 100 115 147 176 206
84 95 105 120 141 176 212 247
98 111 125 140 165 206 247 288
112 128 140 160 188 235 282 329
126 142 158 180 212 265 317 370
140 158 175 201 236 294 353 411
154 174 193 221 259 323 388 452
168 189 211 241 283 353 423 493

Thanks

"Daniel Chalmers" wrote in message
...
I have a table of data

8 9 10 11 12 13 14 15
1.00 28 32 35 40 47 59 71 82
1.50 42 47 53 60 71 88 106 123
2.00 56 63 70 80 94 115 141 184
2.50 70 79 83 100 115 147 176 206
3.00 84 95 105 120 141 176 212 247
3.50 98 111 125 140 165 206 247 288
4.00 112 128 140 160 188 235 282 329
4.50 126 142 158 180 212 265 317 370
5.00 140 158 175 201 236 294 353 411
5.50 154 174 193 221 259 323 388 452
6.00 168 189 211 241 283 353 423 493


I want to be able to plot a line through this for a specific value. Is
this
possible. eg if i want a line for 120, excel will find best fit line
through
chart. I want this to overlay on actual values as above.

Any help much appreciated.

Thanks

Daniel Chalmers wrote:
28 32 35 40 47 59 71 82
42 47 53 60 71 88 106 123
56 63 70 80 94 115 141 184
70 79 83 100 115 147 176 206
84 95 105 120 141 176 212 247
98 111 125 140 165 206 247 288
112 128 140 160 188 235 282 329
126 142 158 180 212 265 317 370
140 158 175 201 236 294 353 411
154 174 193 221 259 323 388 452
168 189 211 241 283 353 423 493

I want to be able to plot a line through this for a specific value.
Is this possible. eg if i want a line for 120, excel will find best
fit line through chart. I want this to overlay on actual values as
above.

I'd use a surface plot, using the contour sub-type. Select the data table,
use the chart wizard, select a surface chart and the contour sub-type.

To put a line at a specific value (i.e. 120), select the legend and from the
menu click Format | Selected legend | Scale tab. You can put 120 in the
major unit box to get lines at 120, 240, 360, ....

Dave
dvt at psu dot edu

Daniel -

What type of chart is it? If it's a contour chart, you can adjust the
scaling so the boundaries include 120. For instance, with your data, I
double clicked on the Legend in a contour chart, and adjusted the scale
so the major spacing was 60. One of the areas went from 60 to 120, and
the next went from 120-180. Not exactly what you want, though.

You can analytically determine the points of the boundary, if you don't
mind some heavy lookups and interpolation. Your data fits in A1:I12. I
put the target value into A17, and this formula in B17:

=INDEX($A$2:$A$12,MATCH($A17,B$2:B$12,1))+(INDEX($ A$2:$A$12,MATCH($A17,B$2:B$12,1)+1)-INDEX($A$2:$A$12,MATCH($A17,B$2:B$12,1)))/(INDEX(B$2:B$12,MATCH($A17,B$2:B$12,1)+1)-INDEX(B$2:B$12,MATCH($A17,B$2:B$12,1)))*($A17-INDEX(B$2:B$12,MATCH($A17,B$2:B$12,1)))

Ain't she a beauty! And no, I didn't draw this up all at once, I built
pieces of it in some cells, made sure they were right, then combined
them. Then I dragged this across to fill B17:I17. These are the values
in the first column (1, 1.5, 2, 2.5, etc) where each column's data
crosses 120. You could draw a set of these curves on an XY Scatter chart.

This works here for data that monotonically increases down the column of
values; you'd have to adjust it for decreasing data, and undulating data
will give you fits. A more rigorous approach might do some least
squares fitting, and would take into account interpolation between
adjacent columns, not just down the rows.

Also, these XY Scatter series cannot be combined with a contour chart.
Bummer.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Daniel Chalmers wrote:
I have a table of data

8 9 10 11 12 13 14 15
1.00 28 32 35 40 47 59 71 82
1.50 42 47 53 60 71 88 106 123
2.00 56 63 70 80 94 115 141 184
2.50 70 79 83 100 115 147 176 206
3.00 84 95 105 120 141 176 212 247
3.50 98 111 125 140 165 206 247 288
4.00 112 128 140 160 188 235 282 329
4.50 126 142 158 180 212 265 317 370
5.00 140 158 175 201 236 294 353 411
5.50 154 174 193 221 259 323 388 452
6.00 168 189 211 241 283 353 423 493


I want to be able to plot a line through this for a specific value. Is this
possible. eg if i want a line for 120, excel will find best fit line through
chart. I want this to overlay on actual values as above.

Any help much appreciated.

Thanks

Are you plotting a LINE or XY chart?
Which are the x-values in your data?
Bernard

"Daniel Chalmers" wrote in message
...
I have a table of data

8 9 10 11 12 13 14 15
1.00 28 32 35 40 47 59 71 82
1.50 42 47 53 60 71 88 106 123
2.00 56 63 70 80 94 115 141 184
2.50 70 79 83 100 115 147 176 206
3.00 84 95 105 120 141 176 212 247
3.50 98 111 125 140 165 206 247 288
4.00 112 128 140 160 188 235 282 329
4.50 126 142 158 180 212 265 317 370
5.00 140 158 175 201 236 294 353 411
5.50 154 174 193 221 259 323 388 452
6.00 168 189 211 241 283 353 423 493


I want to be able to plot a line through this for a specific value. Is
this
possible. eg if i want a line for 120, excel will find best fit line
through
chart. I want this to overlay on actual values as above.

Any help much appreciated.

Thanks