Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Options for three compartment analysis - Old BASIC program to Excel?
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 ) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I install the Data Analysis Toolkit from Office Basic CD? | Excel Discussion (Misc queries) | |||
On opening program get MS Visual Basic error | Excel Discussion (Misc queries) | |||
Monthly job timings analysis - automation options? | Excel Worksheet Functions | |||
A simple visual basic program | New Users to Excel | |||
best program to do purchasing analysis for multiple locations? | Excel Worksheet Functions |