how to calculate unknown x-values

[aquamarine85]

Hello

I have a standard curve in Excel from known DNA concentrations (x-values) and their respective absorbance values (y-values). I then have a series of y-values from which I need to calculate unknown x-values to work out the corresponding y DNA concentrations. How do I do this please? This is the Excel spreadsheet

[MrShorty]

Unfortunately, I can't download your sample file.

What kind of curve is a "standard curve"? Usually I think of a polynomial. And, if this a spectrophotometric measurement, they usually want the instrument output to be linear (y=ax+b). If it is a linear function, it should be very easy to do the algebra and solve for x [x=(y-b)/a], assuming you are given the coefficients a and b.

If you are not given the coefficients, it should be relatively easy to obtain the coefficients using any of the regression tools in Excel (I prefer the LINEST function).

[aquamarine85]

The file I uploaded seemed to open when I clicked on it but maybe this only works for me?!

The standard curve is polynomial and a linear one so you are correct that I will be using y=mx + b. How do I enter this as a function/formula/equation in Excel please so it's easy to compute several values quickly?

Thank you

[MrShorty]

The file I uploaded seemed to open when I clicked on it but maybe this only works for me?!

Probably you and anyone else running 2007 and later. Some of us are stubborn old dogs and haven't yet been convinced to spend the money on the newer versions. So it isn't a problem with your file, it's a problem on my end.

Here's how I would enter this into a spreadsheet:

1) Put the values of m and b into cells A1 and B1
2) In A3, enter an x
3) In B3, enter the formula =$A$1*A3+$B$1
4) In D3, enter a y
5) In E3, enter the inverse formula =(D3-$B$1)/$A$1

You can stop at this point if you want. At this point, you can enter different values of x into A3 and get the corresponding y values just as fast as you can enter values into A3. (Or D3 if you are going to use the inverse function.)

Inevitably, I like to see several values at the same time (usually charting them as well), so I would continue:

6) Select row 3, copy, then past into rows 4 through ??. The dollar signs make those cell references "absolute", and the cell references without dollar signs are "relative." Each copy of the formula will correctly locate m and b, while also correctly choosing the relative location of the x value for that copy of the formula.
7) Enter the desired values for x and y into A3 through A? and D3 through D?.

[aquamarine85]

Thanks but when you say enter x into A3, which x value do I use?

[MrShorty]

The A B pair of cells will be used to calculate absorbance (y) at a given DNA concentration (x). So you put the known DNA concentration into A3, and B3 will calculate the expected absorbance for that concentration.

The D E pair of cells is for the inverse function. You put an absorbance in D3, and E3 will calculate the expected DNA concentration at that absorbance.

Somewhere along the way, I decided I wasn't certain which way you wanted to run the calculation, so I suggested a way to do both. If you have absolutely no need to calculate the absorbance at a given DNA concentration, delete the A B pair of cells and just use the D E cells to calculate DNA concentration at a given absorbance.