If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Plot Line in data table
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 |
#3
|
|||
|
|||
Plot Line in data table
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 |
#4
|
|||
|
|||
Plot Line in data table
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 |
#5
|
|||
|
|||
Plot Line in data table
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 |
Thread Tools | |
Display Modes | |
|
|