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

ThePianoman

New Member
Joined
Mar 15, 2011
Messages
15

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 -->
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top