Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Obtain the Y value on an Excel XY scatter chart

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Excel
View previous topic :: View next topic  
Author Message
Bocfus
New Member


Joined: 26 Sep 2007
Posts: 13
Location: Bennettsville, SC

PostPosted: Tue Oct 30, 2007 4:47 pm    Post subject: Obtain the Y value on an Excel XY scatter chart Reply with quote

This month we were asked how to obtain the Y value on
an Excel XY scatter chart, given that the corresponding
X value was known. There are two approaches to this.
The easiest is to use an Excel add-in that has been
created especially to do this. The second way is to
generate an equation which accurately describes your
data, and use that to calculate your values.

1. Using an Excel Add-In
An Excel add-in contains code that adds extra
features to Excel. Excel is shipped with a variety
of add-ins, and there are many third-party add-ins
available. One which solves our problem is provided
by Tushar Mehta at

http://www.tushar-mehta.com/excel/software/interactive_chart_display/i
ndex.html

This creates a dialogue box which when you enter an
X value tells you the corresponding Y value, and
vice versa. It also draws dotted horizontal and
vertical lines to the Y and X axes from the
specified point.

Another add-in is provided by Advanced Systems
Design and Development at
http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm
This provides functions to interpolate, extrapolate
and curve fit data rapidly. Use the Interpolate
function to find the Y value at a given X value.

2. Generating an Equation from your XY Scatter Chart
On your XY Scatter chart insert a trendline that
fits your data. Right-click the data series and
select Add Trendline. Click the options tab and
choose to display both the equation and the
R-squared value on the chart. The R-squared value
shows how closely your data fits the equation. A
value of 1 indicates an exact fit. Values far
away from 1 indicate that your calculations
won't be accurate and you should try another
trendline.

Copy the right-hand part of the equation into a
cell, replacing the x with a reference to the cell
holding your known X value. For example, for a linear
trendline the equation Excel displays might be
y = 1.97x + 0.1. If your known X value was in
cell H8, you would enter:
=1.97*(HCool+0.1.

For more on data acquisition and control using Excel see
http://www.windmill.co.uk/excel.html
http://www.windmill.co.uk/xlchart.html
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Excel All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group