VBA, Conditional Statements with Multiple Criteria, or Pivot Table? Getting started?

[ThePianoman]

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, 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 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. 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 stdev, 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! I can send you the file to look at it if you wish. Just give me your email address. <!-- / message --><!-- attachments -->