T
Tanya Falbel
I have some biochemical assay data to fit into three compartments
and a kludgy old BASIC program that fits the data to the percentage
of each compartment with the lowest variance, written by someone else,
long ago.
I can either incorporate the BASIC program into a spreadsheet -
translating into another programming language or writing a macro
(I have no idea how to do this) - or use some existing matrix
functions
or a more elegant solution using some statistical functions beyond my
current knowledge of stats. I used to write long, involved programs in
C and Pascal long (>20 years) ago on mainframe computers, but now
I don't know how to even write and run the simplest program on
my Mac laptop. The short question for this group is - how to get Excel
to do this, and if it requires a longer application programming
solution,
then where do I find such a person - I'll look here on the U-Wisconsin
campus, but I have a feeling that this might be pretty easy for Excel
experts, if not, please tell me so and I'll find myself an application
programmer to hire.
If this is the wrong group to present this problem, please let me
know a better place to post, if you know of one -- thanks!
Tanya ([email protected])
Details as follows:
I'll describe the relatively simple problem, and include my version
of pseudocode for the BASIC program.
I have measurements for three cellular compartments: chloroplast,
cytosol, vacuole, and I want to know where my favorite chemical is
located. I have fractionated my material into n parts,today, n=6,
and want to know where my chemical is located, relative to the
marker activities. The solution will be of the form j% in chloroplast,
k% in cytosol, and m% in vacuole.
The BASIC program steps through all possible percentages,
for j=1 to 100
for k=1 to (100-j)
m=100-j-k (so j+k+m=100%)
sum up array of measurements
for i=1 to n (n=6 today)
compare variance between actual measurement of
my chemical to the one that would be predicted
based on a particular j,k,m (where we are in the loop),
store variance (sum of (predicted-measured)^2)
in a variable called var.
Next n
Keep track of variance and find minimum
when var > varminimum keep looping,
when var < varminimum, reset var = varminimum
keep track of jmin,kmin,mmin that gave this minimum,
then continue looping
next k
next j
output final jmin, kmin, mmin
That's one way to do it.
Here's a comma delimited matrix of sample measurement
data for the marker enzymes:
fraction #, chloroplast mkr, cytosol mkr, vacuole mkr
1, 1.05, 98.15, 27.67
2, 2.22, 189, 73
3, 0.65, 94.1, 53.3
4, 0.15, 57.7, 42.5
5, 0.06, 27.2, 22.3
6, 0.03, 93.2, 98.4
you can see that the three "compartments" kind of smear together in
this fractionation procedure
then we need to fit my favorite chemical compound to compartments,
measured in the 6 fractions as follows:
1, 696
2, 1026
3, 651
4, 640
5, 768
6, 786
That's it
and a kludgy old BASIC program that fits the data to the percentage
of each compartment with the lowest variance, written by someone else,
long ago.
I can either incorporate the BASIC program into a spreadsheet -
translating into another programming language or writing a macro
(I have no idea how to do this) - or use some existing matrix
functions
or a more elegant solution using some statistical functions beyond my
current knowledge of stats. I used to write long, involved programs in
C and Pascal long (>20 years) ago on mainframe computers, but now
I don't know how to even write and run the simplest program on
my Mac laptop. The short question for this group is - how to get Excel
to do this, and if it requires a longer application programming
solution,
then where do I find such a person - I'll look here on the U-Wisconsin
campus, but I have a feeling that this might be pretty easy for Excel
experts, if not, please tell me so and I'll find myself an application
programmer to hire.
If this is the wrong group to present this problem, please let me
know a better place to post, if you know of one -- thanks!
Tanya ([email protected])
Details as follows:
I'll describe the relatively simple problem, and include my version
of pseudocode for the BASIC program.
I have measurements for three cellular compartments: chloroplast,
cytosol, vacuole, and I want to know where my favorite chemical is
located. I have fractionated my material into n parts,today, n=6,
and want to know where my chemical is located, relative to the
marker activities. The solution will be of the form j% in chloroplast,
k% in cytosol, and m% in vacuole.
The BASIC program steps through all possible percentages,
for j=1 to 100
for k=1 to (100-j)
m=100-j-k (so j+k+m=100%)
sum up array of measurements
for i=1 to n (n=6 today)
compare variance between actual measurement of
my chemical to the one that would be predicted
based on a particular j,k,m (where we are in the loop),
store variance (sum of (predicted-measured)^2)
in a variable called var.
Next n
Keep track of variance and find minimum
when var > varminimum keep looping,
when var < varminimum, reset var = varminimum
keep track of jmin,kmin,mmin that gave this minimum,
then continue looping
next k
next j
output final jmin, kmin, mmin
That's one way to do it.
Here's a comma delimited matrix of sample measurement
data for the marker enzymes:
fraction #, chloroplast mkr, cytosol mkr, vacuole mkr
1, 1.05, 98.15, 27.67
2, 2.22, 189, 73
3, 0.65, 94.1, 53.3
4, 0.15, 57.7, 42.5
5, 0.06, 27.2, 22.3
6, 0.03, 93.2, 98.4
you can see that the three "compartments" kind of smear together in
this fractionation procedure
then we need to fit my favorite chemical compound to compartments,
measured in the 6 fractions as follows:
1, 696
2, 1026
3, 651
4, 640
5, 768
6, 786
That's it