Automate calculations based lookups of multiple criteria

forum.ozgrid.com/index.php?attachment/36982/

The above file contains some clarification as to what I need, but I will explain here as well:

I work in a hospital doing clinical genetic research on pediatric patients, primarily Neurofibromatosis type 1 patients. For several of the studies we are performing, we do a few different types of bone scans: DXA, pQCT, and QUS. We then extract the data from the machines (see "MachineDataOutput" tab in attached file), which is easily pasted into Excel in a very straightforward and usable format. The raw data is basically a few different types of measurements, in the form of small numbers with several decimal places, and includes the patient's gender ("M" or "F"), and their age (typically 3-20).

I then have to convert these numbers into a zscore (see "OldCalcSheet" tab) for statisticians to use to interpret the data for publication. Right now I am using a basic spreadsheet with simple, embedded formulas to individually calculate each measurement outp from the machine. The problem I have is that the measurement is entered into the spreadsheet based on 2 variables: gender and age. Each gender has it's own table of data, divided into rows based on their age (3, 4, 5, etc). Each age has a set mean and standard deviation (indicated as "SD" in the spreadsheet"). So I have to first locate the correct table based on gender, then the correct age, and input the value to get the zscore. One at a time. This is very tedious, as I get large amounts of patient's data at one time.

What I want to create is a spreadsheet that I can paste large amounts (about 150 or so patient's worth) of data at once, click a button, and let Excel calculate them for me- evaluating each patient's gender, refers to the correct table, then evaluates their age, refers to the correct mean and SD, then uses the formula to calculate the result. Make sense? This would save researchers a lot of time, allowing for focus on other areas. I have very basic HTML experience, which is somewhat similiar to VBA, but not enough that I can do it on my own. I have an average knowledge of Excel, but I don't know where to start. Should I use VBA, a pivot table, or complex IF-THEN-ELSE statements? Either way, if someone would give me the steps, some sample code, or even alter the attached sheet that would be wonderful. The more the better, and the more detailed the explanation the better off I'll be in creating future similiar spreadsheets. Thanks in advance for the help!

Re: Automate calculations based lookups of multiple criteria

AAE: I apologize for the misleading title. I struggle to come up with proper forum headings. Thanks for the improved heading. It definetely fits my problem a little more closely.

Herbds7: Thanks for putting those pivot tables together for me. I actually have posted to a few other forums on the subject, and a few others have sugested pivot tables as well. I guess what I don't understand is how I can use a pivot table to automate the calculations based on gender and age. Each mean/standard deviation is specific to that gender and age, meaning I have to plug in the machine-output data one value at a time, after having mached it up to the specific age & gender. I suppose I should just say that what I want is a spreadsheet similiar to the one I attached "CalcSheet", that allows me to simply copy and paste the data into the correct column, click a button, and let Excel do the calculations based on the two criteria, age and gender. I will be continuously receiving new data to calculate zscores with, so I can see creating new pivot tables each time being somewhat inconvenient. This is why I believe I need a macro instead. I would be very interested in anyone's thoughts on this. I just need to get a basic layout idea, and maybe some sample code to get me started. I am inexperienced with VBA, but I learn very quickly. Try me.

Re: Automate calculations based lookups of multiple criteria

Ok, so sorry about the delay in response. My email was supposed to be set up to let me know if anyone responded. Thanks for helping me out with this. I see what you mean about the auto-updating tables, and it makes sense now. I should've taken a few more minutes to check it out. I like the concept of what you're doing. I can see a few issues based on what I need:

- What happens if there are 2 or more individuals of the same gender/age?
- The idea of this new spreadsheet was to allow the new calculated zscores to appear next to the MachineDataOutput, so that it stays in order. Technically the machine also gives us an ID# associated with each patient (which I mistakenly didn't include in the sample machine output), so it needs to be organized in such a way that I can easily paste it back with their raw data the machine gives. I just need a spreadsheet with an underlying Macro that allows me to simply copy and paste the data (Machine Data Output) into a spreadsheet with the categories already set up (CalcSheet), click a button, and let it do the calculations for me. I've already started setting up arrays with all of the gender/age-specific standard deviations and means stored in them. I just need the proper if-then statements, loops, lookups, etc. to do the calculations. Let me know if you can do all this with pivot tables, or if you can help me with this macro idea. Thanks!

Re: Automate calculations based lookups of multiple criteria

Thanks for doing this. This actually looks like it might work. I will tweak it here and there and see what I can do. Thanks!!!