plotting unknowns on a chart

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have a table of info which has values (of a protein
concentration in this case) and then the recorded results
(light absorbance) next to each one. I also have
five 'unknown' concentrations, for which i have measured
the absorbance. By plottng on a graph i wish to be able
to see accurately what the concentration is for each of
my unknowns. I seem to get halfway there, and then fail.
If anyone can help before i haev to get my graph paper
out i'd appreciate it!

Richard
 
Richard,

Not sure if this will completely answer your problem, it's only
my suggestion...

I'm assuming that since concentration is the "unkown" it is
being plotted on the y-axis.

Create a trendline on your graph (Right click a point and add
trendline).
Right click the trendline and choose format trendline, under the
options tab check of "display equation on chart".
Use this equation to calculate theoretical y-values
(concentrations for each of the unknown points)

This will give you plottable data so you can plot the theoretical
values of the concentrations of the measured absorbances.

Of course next I would suggest that you use statistical methods
to calculate an errors for each of the calculated points. Then
you can add these to your graph by:
Right clicking your new series and choose "Format Data
Series", under the Y Error Bars choose Both and enter your
calculated error in the Fixed Value field.
If you don't want to calculate your error, I would suggest using
at least 1 standard deviation.

Dan E
 
Hi Richard,
It is better to use functions rather than Trendline to get the actual values
of the fit - otherwise you have to be careful to use enough sig figs. Others
will tell you that the functions in Excel are flawed but much
experimentation has shown that for 99% of scientific work this is not the
case.

I'll assume your conc. data to be in A2:A22 and the absorbance in B2:B22
Assuming your fit is a linear one. in a cell (let's say K1) enter
=SLOPE(B2:B22,A2:A22)
In K2 enter =INTERCEPT(B2:B22,A2:A22)

Now we have y = mx + b as the fit. To compute x when we know y, rearrange to
give x =(y-b)/m
Lets say the first unknown is in D2, in E2 use =(E2-$K$2)/$K$1 Use
absolutes for the slope and intercept so you can copy down the column for
other unknowns.

I am assuming your data fits Beer's Law so is linear. If not e-mail me (my
personal address) and I'll show how to use LINEST.

Now for a plug; if you want to compute confidence levels that is too much to
do in an e-mail but it is all in my book - see www.stfx.ca/people/bliengme

Best wishes
Bernard
 
Thanks to both of you for your help. Inow only wish the
quality of the data i collected was a little better!

Richard
 
If the data more accurately fit a piece-wise linear model, you might
want to check the 'Interactive Chart' add-in available from my web
site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top