Hello all,
I am currently working on a pretty big Excel file (2007 on Windows XP work computer) for my medical institution and have come into a scenario that seems a bit too complex for me.
My workbook is being used to input the normal weights and measures of children based on their age and sex. Currently, the workbook includes Sheet 1 titled "Autopsy Form-Both Sides"; from there I have 14 sheets that separate normal means and standard deviations for different organs, their height, and their weight based on their ages and sex.
Sheet names:
Male Day Organs
Female Day Organs
Male Month Organs
Female Month Organs
Male Year Organs
Female Year Organs
Male Weight 0-3
Female Weight 0-3
Male Weight 4-18
Female Weight 4-18
Male Length 0-3
Female Length 0-3
Male Length 4-18
Female Length 4-18
The only information that I will input is on Sheet 1 and there are three criteria I need it to match before finding the answer. There is one blank for the number age (P4), one for the text age (Q4), and one for the sex (R4) that I need Excel to look up and match with the tables on the other sheets.
For example, cell F9 will be the normal body weight for the child. I will type: 4 (P4), 'year (Q4), 'M (R4). The normal body weights for 4-18 yo males are found in Sheet 6, 'Male Weight 4-18', and looks like this:
[TABLE="width: 225"]
<TBODY>[TR]
[TD]Age (yr)
[/TD]
[TD]Weight (M + SD)
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]17 ± 3.3
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]18.5 ± 4
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]20.5 ± 4.5
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]23 ± 5.8
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]25 ± 7
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]28 ± 8.8
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]31.5 ± 10.5
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]35 ± 12.3
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]39.5 ± 14.3
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]45 ± 16
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]50.5 ± 17
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]56.5 ± 18
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]62 ± 19
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]66.5 ± 19.8
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]69 ± 20.8
[/TD]
[/TR]
</TBODY>[/TABLE]
*Note, all of this information is formatted as a table. Not sure if that is an issue or not.
With the help of whatever formula that we end up using, I need the final answer in cell F9 on Sheet 1 to populate as 17 ± 3.3.
As a final problem I may run in to, the male and female "day" tables are not separated into individual cells but look like this:
[TABLE="width: 491"]
<TBODY>[TR]
[TD]Age (Days)
[/TD]
[TD]Brain
[/TD]
[TD]Liver
[/TD]
[TD]Heart
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]277.1 ± 68
[/TD]
[TD]68.8 ± 5.1
[/TD]
[TD]13.2 ± 1.2
[/TD]
[/TR]
[TR]
[TD]2-30
[/TD]
[TD]383.3 ± 34.5</SPAN>
[/TD]
[TD]140.1 ± 15.1</SPAN>
[/TD]
[TD]25.4 ± 4.6</SPAN>
[/TD]
[/TR]
[TR]
[TD]31-60
[/TD]
[TD]474.5 ± 42.7
[/TD]
[TD]148.4 ± 27.2
[/TD]
[TD]26.4 ± 4.5
[/TD]
[/TR]
[TR]
[TD]61-90
[/TD]
[TD]595.3 ± 51.7</SPAN>
[/TD]
[TD]186.4 ± 15.1</SPAN>
[/TD]
[TD]27.4 ± 2.7</SPAN>
[/TD]
[/TR]
[TR]
[TD]91-120
[/TD]
[TD]614.1 ± 42.9
[/TD]
[TD]176.1 ± 17.2
[/TD]
[TD]30.4 ± 6.2
[/TD]
[/TR]
[TR]
[TD]121-150
[/TD]
[TD]665.2 ± 77.5</SPAN>
[/TD]
[TD]215.5 ± 33.9</SPAN>
[/TD]
[TD]28.3 ± 2.2</SPAN>
[/TD]
[/TR]
[TR]
[TD]151-180
[/TD]
[TD]713.8 ± 61.7
[/TD]
[TD]269.1 ± 56.3
[/TD]
[TD]38 ± 6.5
[/TD]
[/TR]
</TBODY>[/TABLE]
Will Excel be able to pick these ranges out or should I input each day separately so that it is easier to look through?
Any help would be greatly appreciated!
-Amanda
I am currently working on a pretty big Excel file (2007 on Windows XP work computer) for my medical institution and have come into a scenario that seems a bit too complex for me.
My workbook is being used to input the normal weights and measures of children based on their age and sex. Currently, the workbook includes Sheet 1 titled "Autopsy Form-Both Sides"; from there I have 14 sheets that separate normal means and standard deviations for different organs, their height, and their weight based on their ages and sex.
Sheet names:
Male Day Organs
Female Day Organs
Male Month Organs
Female Month Organs
Male Year Organs
Female Year Organs
Male Weight 0-3
Female Weight 0-3
Male Weight 4-18
Female Weight 4-18
Male Length 0-3
Female Length 0-3
Male Length 4-18
Female Length 4-18
The only information that I will input is on Sheet 1 and there are three criteria I need it to match before finding the answer. There is one blank for the number age (P4), one for the text age (Q4), and one for the sex (R4) that I need Excel to look up and match with the tables on the other sheets.
For example, cell F9 will be the normal body weight for the child. I will type: 4 (P4), 'year (Q4), 'M (R4). The normal body weights for 4-18 yo males are found in Sheet 6, 'Male Weight 4-18', and looks like this:
[TABLE="width: 225"]
<TBODY>[TR]
[TD]Age (yr)
[/TD]
[TD]Weight (M + SD)
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]17 ± 3.3
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]18.5 ± 4
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]20.5 ± 4.5
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]23 ± 5.8
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]25 ± 7
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]28 ± 8.8
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]31.5 ± 10.5
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]35 ± 12.3
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]39.5 ± 14.3
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]45 ± 16
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]50.5 ± 17
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]56.5 ± 18
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]62 ± 19
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]66.5 ± 19.8
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]69 ± 20.8
[/TD]
[/TR]
</TBODY>[/TABLE]
*Note, all of this information is formatted as a table. Not sure if that is an issue or not.
With the help of whatever formula that we end up using, I need the final answer in cell F9 on Sheet 1 to populate as 17 ± 3.3.
As a final problem I may run in to, the male and female "day" tables are not separated into individual cells but look like this:
[TABLE="width: 491"]
<TBODY>[TR]
[TD]Age (Days)
[/TD]
[TD]Brain
[/TD]
[TD]Liver
[/TD]
[TD]Heart
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]277.1 ± 68
[/TD]
[TD]68.8 ± 5.1
[/TD]
[TD]13.2 ± 1.2
[/TD]
[/TR]
[TR]
[TD]2-30
[/TD]
[TD]383.3 ± 34.5</SPAN>
[/TD]
[TD]140.1 ± 15.1</SPAN>
[/TD]
[TD]25.4 ± 4.6</SPAN>
[/TD]
[/TR]
[TR]
[TD]31-60
[/TD]
[TD]474.5 ± 42.7
[/TD]
[TD]148.4 ± 27.2
[/TD]
[TD]26.4 ± 4.5
[/TD]
[/TR]
[TR]
[TD]61-90
[/TD]
[TD]595.3 ± 51.7</SPAN>
[/TD]
[TD]186.4 ± 15.1</SPAN>
[/TD]
[TD]27.4 ± 2.7</SPAN>
[/TD]
[/TR]
[TR]
[TD]91-120
[/TD]
[TD]614.1 ± 42.9
[/TD]
[TD]176.1 ± 17.2
[/TD]
[TD]30.4 ± 6.2
[/TD]
[/TR]
[TR]
[TD]121-150
[/TD]
[TD]665.2 ± 77.5</SPAN>
[/TD]
[TD]215.5 ± 33.9</SPAN>
[/TD]
[TD]28.3 ± 2.2</SPAN>
[/TD]
[/TR]
[TR]
[TD]151-180
[/TD]
[TD]713.8 ± 61.7
[/TD]
[TD]269.1 ± 56.3
[/TD]
[TD]38 ± 6.5
[/TD]
[/TR]
</TBODY>[/TABLE]
Will Excel be able to pick these ranges out or should I input each day separately so that it is easier to look through?
Any help would be greatly appreciated!
-Amanda