Proportional XY Line Chart

[kjoiner]

Hello,

I am plotting a torque curve for the design of a hairspring to counterbalance a roll up door. Because the door rolls up on itself, the radius changes. I am plotting torque versus degrees of rotation. The degrees of rotation are taken from how many degrees the wheel must rotate to raise the door 1 slat. As the doors roll up, there is a point where the radius will "jump" up to a new dimension when the previous layer stacks up under the new one. Therefore there are several groupings of incremental angles. When plotting this curve, excel takes all of these angle and torque data points and places them on a grid. The problem is that one grid difference may be a 29 degree increment one place and a 41 degree increment somewhere else on the x axis. This leads to a non-proportional chart. I have Excel create a trend line as a best fit curve for figuring out a linear spring rate. If the X axis is off, so is the trend line.

What I'm looking for is a way to have Excel display the X axis data on a proportional grid. Is there a way to do this?

Thanks,

Kyle

 

[MintJulep]

I think your problem will be solved if you use the

XY (Scatter)

chart type rather than the Line chart type.

 

[kjoiner]

MintJulep,

Thanks for the tip! I think that worked. Maybe you can help me with another question. My y intercept for the trend line is at y=40. Is there any way to extrapolate the trend line so that I can find out how many degrees negative the value goes to 0? I'll use this to determine the preload I need to apply to the spring.

Thanks again,

Kyle

 

[melone]

You could add a trendline (and display the equation). Right click on the data series -> Add Trendline and pick the type of trendline. Then click on the Options tab and click on the Display Equation on Chart box. If you want, you can even display the R² value (goodness of fit).

Hope this helps!

 

[kjoiner]

Melone,

Thanks for the tip on the equation. The equation gives me the spring rate and a more accurate intercept than I could eyeball from the chart. Dividing the intercept by the spring rate will give me the preload. I also found where I could have Excel forcast the trendline backward until it reaches zero. Having the formula let me punch in how many units to forecast [or "backcast" in my case ]. Is there a way for Excel to calculate the exact number of units so that the trendline reaches zero base on the the formula it generates?

Thanks again,

Kyle