How do I use equation of a line to calculate x-values from known .

[lizzystien]

I have an xy scatter plot constructed and created a polynomial function for
the line. I want to use the equation obtained to calculate the expected x
values from 75 different y values without punching it all into a calculator.
I know there is a way to do this on excel, but it's been awhile, anyone
willing to be a teacher?

[bpeltzer]

You've got the linear equation of the form y=Ax + B. Type the value of A in
cell B1, the value of B in cell C1. In cells A2 through A76, enter the x
values given. In cell B2 enter the formula =A2*$B$1 + $C$1. Copy that
formula from B2 into cells B3 through B76.
(BTW, I'm assuming you're looking for an expected y for a given x; y is
generally set up as the dependent variable, and the equation you get from the
scatterplot will be constructed that way).

"lizzystien" wrote:

> I have an xy scatter plot constructed and created a polynomial function for
> the line. I want to use the equation obtained to calculate the expected x
> values from 75 different y values without punching it all into a calculator.
> I know there is a way to do this on excel, but it's been awhile, anyone
> willing to be a teacher?

[lizzystien]

Okay, I'm more than likely just very dense with all of this, but I'm still
having troubles. My equation is in the form y=Ax^2+Bx=C. For the graph that
I used to create the line, the y variable indicates absorbancies while the
x-values are mg of protiens. My 75 known values are absorbancies and I need
to predict the amount of protien for each aborbancy. Still haven't had much
luck, but thank you to bpeltzer for at least trying to teach the unteachable.

"lizzystien" wrote:

> I have an xy scatter plot constructed and created a polynomial function for
> the line. I want to use the equation obtained to calculate the expected x
> values from 75 different y values without punching it all into a calculator.
> I know there is a way to do this on excel, but it's been awhile, anyone
> willing to be a teacher?

[bpeltzer]

Okay, I missed the polynomial mention in your first post. Still, I think you
need to start by reversing the x's and y's so that you get an equation that
expresses protien as function of absorbency. Probably you can switch the two
columns and go through the chart process again. Once you've got an equation
of the form you showed, y=Ax^2 + Bx + C, where the y is unknown (amount of
protein), the steps aren't much different from before. Enter the values of
A, B and C in cells A1, B1 and C1, respectively. Enter the known variable's
values in cells A2 through A76. In B2, the formula is =a2 * $A$1^2 + a2 *
$B$1 + $C$1. Copy that formula from B2 to B3 through B76.
--Bruce

"lizzystien" wrote:

> Okay, I'm more than likely just very dense with all of this, but I'm still
> having troubles. My equation is in the form y=Ax^2+Bx=C. For the graph that
> I used to create the line, the y variable indicates absorbancies while the
> x-values are mg of protiens. My 75 known values are absorbancies and I need
> to predict the amount of protien for each aborbancy. Still haven't had much
> luck, but thank you to bpeltzer for at least trying to teach the unteachable.
>
> "lizzystien" wrote:
>
> > I have an xy scatter plot constructed and created a polynomial function for
> > the line. I want to use the equation obtained to calculate the expected x
> > values from 75 different y values without punching it all into a calculator.
> > I know there is a way to do this on excel, but it's been awhile, anyone
> > willing to be a teacher?

[B. R.Ramachandran]

Hi,

If y=ax^2+bx+c, then ax^2+bx+c-y = 0. So for any given y-value
(Absorbance), you can solve for the x-value (protein concentration) by using
the quadratic formula:

x = (-b + sqrt(b^2-4*a*(c-y)))/(2*a) or (-b - sqrt(b^2-4*a*(c-y)))/(2*a)

When you plug in the values of a, b, c, and y, only one of the above
equations would give a meaningful solution for x (the other one will give a
value for x that is physically absurd); my guess is that the first equation
will give the meaningful x-value for any y-value, but you should try.

So if you have 75 absorbance values in a column (say A2:A76), in B2 enter
the following formula (of course enter the actual values of a, b, and c from
the polynomial fit in place of "a", "b" and "c" in theformula)

=(-b+SQRT(b^2-4*a*(c-A2)))/(2*a)

and autofill the formula down to B76. If the values are not satisfactory,
try changing the "+" sign into "-" and see whether it helps).

Regards,
B. R. Ramachandran

"lizzystien" wrote:

> Okay, I'm more than likely just very dense with all of this, but I'm still
> having troubles. My equation is in the form y=Ax^2+Bx=C. For the graph that
> I used to create the line, the y variable indicates absorbancies while the
> x-values are mg of protiens. My 75 known values are absorbancies and I need
> to predict the amount of protien for each aborbancy. Still haven't had much
> luck, but thank you to bpeltzer for at least trying to teach the unteachable.
>
> "lizzystien" wrote:
>
> > I have an xy scatter plot constructed and created a polynomial function for
> > the line. I want to use the equation obtained to calculate the expected x
> > values from 75 different y values without punching it all into a calculator.
> > I know there is a way to do this on excel, but it's been awhile, anyone
> > willing to be a teacher?