Hello all,
I would like to create a dynamic Excel template to tabulate data gathered from Bradford-Coomassie assays--scientific tests that ascertain the concentration of certain proteins in solutions prepared from cultured cells. We fill ninety-six-well plates with prepared solutions, mix in chromagen (a coloring agent), then place the whole thing in a machine which refracts light of a specific wavelength through the samples and gives us a whole bunch of information which we then manipulate in Excel to give us readings in microgram/microliter.
It isn't difficult to do the majority of these equations, for they are almost all simple sums and averages. But what is difficult is correcting for dilutions using the equation generated by a series of control samples. I know this is a rather specialized task, but I was thinking that, if I explain it well enough, some of the more skillful programmers might be able to point me in the right direction.
Here is the procedure:
1. Five sets of three (total: fifteen) control data points are selected. The first set is pure water, the last pure protein isolate; those inbetween form a concentration gradient, so that we know how to correct for the peculiarities of the machine and the material we're using. We then graph these fifteen points on a scatter plot and generate a trendline. I'll return to this later.
2. We test up to ten different samples simultaneously, but for the sake of simplicity I'll explain the procedure for a single one. We will create our solution and then pour three plates at one dilution and three plates at another, giving us a total of six plates per sample.
3. In Excel, each set of three points is averaged; then comes the tricky part, which I think would pose the only real challenge in writing a program: we use the x-coefficient and the b-value from the trendline (in standard slope-intercept form) to calculate the x value for each average. The equation looks like this: (AVG-b)/m.
4. The two new x-values are divided by 2.5 and 5 respectively (the volume of the solution, in microliters, comprised of our cell samples).
5.These quotients are then multiplied by 25 (the total volume of solution in our wells).
6. Finally, the two products are averaged, yielding our final value: protein concentration in micrograms/microliter.
It's pretty easy to work all of the multiplication and division into a template: all it takes is some copying and pasting. What I don't know how to do, however, is make a program independently generate a line of best fit and plug that information into the template. Can anyone lend a hand?
I would like to create a dynamic Excel template to tabulate data gathered from Bradford-Coomassie assays--scientific tests that ascertain the concentration of certain proteins in solutions prepared from cultured cells. We fill ninety-six-well plates with prepared solutions, mix in chromagen (a coloring agent), then place the whole thing in a machine which refracts light of a specific wavelength through the samples and gives us a whole bunch of information which we then manipulate in Excel to give us readings in microgram/microliter.
It isn't difficult to do the majority of these equations, for they are almost all simple sums and averages. But what is difficult is correcting for dilutions using the equation generated by a series of control samples. I know this is a rather specialized task, but I was thinking that, if I explain it well enough, some of the more skillful programmers might be able to point me in the right direction.
Here is the procedure:
1. Five sets of three (total: fifteen) control data points are selected. The first set is pure water, the last pure protein isolate; those inbetween form a concentration gradient, so that we know how to correct for the peculiarities of the machine and the material we're using. We then graph these fifteen points on a scatter plot and generate a trendline. I'll return to this later.
2. We test up to ten different samples simultaneously, but for the sake of simplicity I'll explain the procedure for a single one. We will create our solution and then pour three plates at one dilution and three plates at another, giving us a total of six plates per sample.
3. In Excel, each set of three points is averaged; then comes the tricky part, which I think would pose the only real challenge in writing a program: we use the x-coefficient and the b-value from the trendline (in standard slope-intercept form) to calculate the x value for each average. The equation looks like this: (AVG-b)/m.
4. The two new x-values are divided by 2.5 and 5 respectively (the volume of the solution, in microliters, comprised of our cell samples).
5.These quotients are then multiplied by 25 (the total volume of solution in our wells).
6. Finally, the two products are averaged, yielding our final value: protein concentration in micrograms/microliter.
It's pretty easy to work all of the multiplication and division into a template: all it takes is some copying and pasting. What I don't know how to do, however, is make a program independently generate a line of best fit and plug that information into the template. Can anyone lend a hand?
