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

[ThePianoman]

Austin.DXA.Calculator.xls

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!

[MarvinP]

Hi ThePianoMan and welcome to the forum.

You need Pivot Tables. Taking your Sheet1 data, I've created a Pivot Table by simply dragging and dropping column heads into the different boxes of the Pivot Table list dialog box.

Pivot tables has Sums, Counts, Standard Deviations as a few of it's summation possibilities. You can also filter out Gender or Age or groups of ages.

Find my attached with a very simple Pivot Table example showing a possibility. You need to study a few days on Pivot Tables and get back to us on what you need to accomplish.

Also - are you using 2003 or 2007 or 2010 Excel as there are more options for Pivot Tables in the newer versions of Excel.

[ThePianoman]

Thanks for the quick response. I'm using Excel 2007 (I assumed my profile would give that info, guess not). I have been studying up on pivot tables, but I have no need to sum up any data. What I need to accomplish is the ability to simply copy and paste the exported data from the machine, and write some form of VBA macro that calculates the zscores based on age & gender. The gender/age-specific means and stdev's would have to be stored in some way for the macro to reference them when it evaluates the exported data (see example in the spreadsheet I posted). It seems counter-productive to create a pivot table with each set of exported data I receive, rather than just making a spreadsheet with a macro that is programmed to automatically calculate the pasted in data. Does that make sense? Correct me if I'm wrong, because I'm certain you have much more experience. I just assumed that pivot tables are not capable of performing the conditional type of calculations I need.

[MarvinP]

You posted an .xls so 2003 excel was the file format, even though you claimed 2007 excel in your profile.

Are the Z scores determined from the data or are you getting them from another source? Z scores aren't a hard problem, we can do them either way. Deep in Pivot Tables are Calculated Items and Show Values As which might be very quick, based on your need.

What I was trying to do was eliminate the intermediate steps and go straight to your answer.

Macros are one method but I'm thinking Pivot Tables are simpler. If you know VBA and can follow code then be more specific on your needs. BUT - I'd like you to consider Pivots first.

[ThePianoman]

Sorry for the confusion. I use Excel 2007 but the spreadsheet attached was sent to me as a 2003 file and I edited it with 2007, and forgot to save as 2007.

I would be happy to consider pivot tables. If you would explain to me how it would work with what I need, that would be helpful.

Sorry I assumed I was being fairly specific in my post and the spreadsheet. I already have the formulas to calculate the zscores (they are in the spreadsheet in the "zscore" columns). Perhaps if I explained the process a little more clearly, referencing the attached spreadsheet. I am currently using "OldCalcSheet" to get the zscores one at a time.

Each "Measured Value" (see "OldCalcSheet") I export from the machine has to be calculated seperately at this point. I look at the patient's gender corresponding to the exported measured value, then find the correct gender-specific table. Then I look at the patient's age. Then using the "Measured Value" column in that row from the correct table, I input the value and it gives me the zscore from the formula already embedded in it.

This is tedious work. What I want (see "CalcSheet") is to be able to simply cut and paste the exported data (see "MachineDataOutput") into a spreadsheet, and click a button to activate the macro. Here's what the macro should do:

1) First it would need to determine "M" or "F". This would reference it to the correct table (because each gender and age have specific means and stdev's- already in the spreadsheet- see "OldCalcSheet")
2) Once referred to the correct table, the macro would evaluate the age from the pasted data, then select the correct MEAN and STDEV for that gender/age to use in the formula for calculating the zscore.
3) Some sort of loop would have to be included so Excel will cycle through all of the data in the rows/columns. This is the purpose of creating the new spreadsheet. Rather than input one patient at a time, do mass amounts at once.

I consistently get new data, so that's why I say I don't understand the purpose of creating a new pivot table with each new set of data (as they cannot be updated). I can follow VBA code well enough that if I were given several samples of code for each step, I could compile the macro on my own. Thanks for being patient with me.