Hi,
I have data of blood test results for patients in an excel file, with each
patient's data being placed on one worksheet. For instance:
Data for PatientA are in worksheet called "PatientA" where cell A9 is
patient's Hemoglobin value

Data for PatientB are in worksheet called "PatientB", where cell A9 is
patient's Hemoglobin value
......
Data for PatientZ are in worksheet called "PatientZ"; cell A9 is patient's
Hemoglobin value

I need to create a summary sheet, where A9 in the summary sheet would give
me the percentage of among all patients (PatientA to PatientZ) where
Hemoglobin level is between 10 and 12

Any help would be appreciated.
Thanks.

What are some typical hemoglobin values? Are they intergers (whole numbers)?
Are they decimal numbers to 1 decimal place? Are they decimal numbers to 2
decimal places?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hi,
I have data of blood test results for patients in an excel file, with each
patient's data being placed on one worksheet. For instance:
Data for PatientA are in worksheet called "PatientA" where cell A9 is
patient's Hemoglobin value

Data for PatientB are in worksheet called "PatientB", where cell A9 is
patient's Hemoglobin value
.....
Data for PatientZ are in worksheet called "PatientZ"; cell A9 is patient's
Hemoglobin value

I need to create a summary sheet, where A9 in the summary sheet would give
me the percentage of among all patients (PatientA to PatientZ) where
Hemoglobin level is between 10 and 12

Any help would be appreciated.
Thanks.

Hemoglobin is decimal number to 2 decimal places,
The following is an example of the sheets I have (one sheet per patient; has
data from multiple months; has data on hemoglobin and other labs; I will need
to calculate percentage for other labs too [like percentage with ferritin
800], but I could adopt the help I get about Hemoglobin for other labs).
Thanks a lot.

Jan Feb Mar Apr May
Hemoglobin 9.52 9.50 11.12 11.65 11.10
HCT 28.2 28.5 32.0 33.4 33.9
Ferritin 750 535 415 482 486
Iron Sat 17.3 32.2 12.5 38.4 61.8
Potassium 5.5 4.6 4.8 5.5 5.9

Thanks

"T. Valko" wrote:

What are some typical hemoglobin values? Are they intergers (whole numbers)?
Are they decimal numbers to 1 decimal place? Are they decimal numbers to 2
decimal places?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hi,
I have data of blood test results for patients in an excel file, with each
patient's data being placed on one worksheet. For instance:
Data for PatientA are in worksheet called "PatientA" where cell A9 is
patient's Hemoglobin value

Data for PatientB are in worksheet called "PatientB", where cell A9 is
patient's Hemoglobin value
.....
Data for PatientZ are in worksheet called "PatientZ"; cell A9 is patient's
Hemoglobin value

I need to create a summary sheet, where A9 in the summary sheet would give
me the percentage of among all patients (PatientA to PatientZ) where
Hemoglobin level is between 10 and 12

Any help would be appreciated.
Thanks.


.

Ok, need some clarification...

You want the percentage of patients that have a hemo value =10 and <=12.

In the original post you said this value was located in cell A9 on each
patients sheet which implies a single value per patient.

In your follow-up you now say there is a hemo value for each month.

So, how would you calculate the percentage of patients with a hemo value
=10 and <=12 when there are multiple hemo values for each patient?

Let's assume this is your data:

Pat1: 9.52,9.50,11.12,11.65,11.10
Pat2: 8.99,9.99,10.01,12.01,11.52

What percentage of those 2 patients meet the criteria?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hemoglobin is decimal number to 2 decimal places,
The following is an example of the sheets I have (one sheet per patient;
has
data from multiple months; has data on hemoglobin and other labs; I will
need
to calculate percentage for other labs too [like percentage with ferritin
800], but I could adopt the help I get about Hemoglobin for other labs).
Thanks a lot.

Jan Feb Mar Apr May
Hemoglobin 9.52 9.50 11.12 11.65 11.10
HCT 28.2 28.5 32.0 33.4 33.9
Ferritin 750 535 415 482 486
Iron Sat 17.3 32.2 12.5 38.4 61.8
Potassium 5.5 4.6 4.8 5.5 5.9

Thanks

"T. Valko" wrote:

What are some typical hemoglobin values? Are they intergers (whole
numbers)?
Are they decimal numbers to 1 decimal place? Are they decimal numbers to
2
decimal places?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hi,
I have data of blood test results for patients in an excel file, with
each
patient's data being placed on one worksheet. For instance:
Data for PatientA are in worksheet called "PatientA" where cell A9 is
patient's Hemoglobin value

Data for PatientB are in worksheet called "PatientB", where cell A9 is
patient's Hemoglobin value
.....
Data for PatientZ are in worksheet called "PatientZ"; cell A9 is
patient's
Hemoglobin value

I need to create a summary sheet, where A9 in the summary sheet would
give
me the percentage of among all patients (PatientA to PatientZ) where
Hemoglobin level is between 10 and 12

Any help would be appreciated.
Thanks.


.

I figured it out and code is shown below (might not be neat for the
professionals, but it gave me what I needed; what it gives me is the
percentage of patients who have (hemoglobin) Hb value of 10 to 12 among
patients who got Hb checked,; one percentage value per each month).

Sub GetHb10To12() €˜Hemoglobin (Hb) between 10 and 12
Dim CountAllPatients As Integer 'Count all patients
Dim CountNonMissingPatients As Integer 'Gives number of patients without
missing value for that month
Dim CountHb10To12 As Integer 'Gives number of patients who
have Hb value between 10 and 12
Dim i As Integer 'Will be used for moving from one
patient (sheet) to another
Dim j As Integer 'Will be used to move from one
month to another
Dim HBpercent10TO12 As Double 'Gives % of patients with Hb
10-12 among patients with nonmissing Hb

j = 2 'Month January data is placed in
row (9,2), cell B9
For j = 2 To 13 'To loop from month January
till December
CountAllPatients = 0
CountNonMissingPatients = 0
CountHb10To12 = 0

i = 3
For i = 3 To Worksheets.count 'To loop from one patient to
another
'Note: Sheet 1 is YearlySummary;
Sheet 2 is Percentages
CountAllPatients = CountAllPatients + 1
If Worksheets(i).Cells(9, j).Value < "" Then 'Cells(9,j) is where
Hb data present
CountNonMissingPatients = CountNonMissingPatients + 1
If (Worksheets(i).Cells(9, j).Value = 10) And
(Worksheets(i).Cells(9, j).Value <= 12) Then
CountHb10To12 = CountHb10To12 + 1
End If
End If
Next i

If CountNonMissingPatients < 0 Then
HBpercent10TO12 = 100 * CountHb10To12 / CountNonMissingPatients
Else
HBpercent10TO12 = -10
End If

If HBpercent10TO12 = -10 Then
Worksheets(1).Cells(9, j).ClearContents
Else
Worksheets(1).Cells(9, j).Value = HBpercent10TO12
End If

Next j

End Sub


"T. Valko" wrote:

Ok, need some clarification...

You want the percentage of patients that have a hemo value =10 and <=12.

In the original post you said this value was located in cell A9 on each
patients sheet which implies a single value per patient.

In your follow-up you now say there is a hemo value for each month.

So, how would you calculate the percentage of patients with a hemo value
=10 and <=12 when there are multiple hemo values for each patient?

Let's assume this is your data:

Pat1: 9.52,9.50,11.12,11.65,11.10
Pat2: 8.99,9.99,10.01,12.01,11.52

What percentage of those 2 patients meet the criteria?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hemoglobin is decimal number to 2 decimal places,
The following is an example of the sheets I have (one sheet per patient;
has
data from multiple months; has data on hemoglobin and other labs; I will
need
to calculate percentage for other labs too [like percentage with ferritin
800], but I could adopt the help I get about Hemoglobin for other labs).
Thanks a lot.

Jan Feb Mar Apr May
Hemoglobin 9.52 9.50 11.12 11.65 11.10
HCT 28.2 28.5 32.0 33.4 33.9
Ferritin 750 535 415 482 486
Iron Sat 17.3 32.2 12.5 38.4 61.8
Potassium 5.5 4.6 4.8 5.5 5.9

Thanks

"T. Valko" wrote:

What are some typical hemoglobin values? Are they intergers (whole
numbers)?
Are they decimal numbers to 1 decimal place? Are they decimal numbers to
2
decimal places?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hi,
I have data of blood test results for patients in an excel file, with
each
patient's data being placed on one worksheet. For instance:
Data for PatientA are in worksheet called "PatientA" where cell A9 is
patient's Hemoglobin value

Data for PatientB are in worksheet called "PatientB", where cell A9 is
patient's Hemoglobin value
.....
Data for PatientZ are in worksheet called "PatientZ"; cell A9 is
patient's
Hemoglobin value

I need to create a summary sheet, where A9 in the summary sheet would
give
me the percentage of among all patients (PatientA to PatientZ) where
Hemoglobin level is between 10 and 12

Any help would be appreciated.
Thanks.


.



.

Good deal. Thanks for feeding back!

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
I figured it out and code is shown below (might not be neat for the
professionals, but it gave me what I needed; what it gives me is the
percentage of patients who have (hemoglobin) Hb value of 10 to 12 among
patients who got Hb checked,; one percentage value per each month).

Sub GetHb10To12() 'Hemoglobin (Hb) between 10 and 12
Dim CountAllPatients As Integer 'Count all patients
Dim CountNonMissingPatients As Integer 'Gives number of patients
without
missing value for that month
Dim CountHb10To12 As Integer 'Gives number of patients who
have Hb value between 10 and 12
Dim i As Integer 'Will be used for moving from
one
patient (sheet) to another
Dim j As Integer 'Will be used to move from one
month to another
Dim HBpercent10TO12 As Double 'Gives % of patients with Hb
10-12 among patients with nonmissing Hb

j = 2 'Month January data is placed
in
row (9,2), cell B9
For j = 2 To 13 'To loop from month January
till December
CountAllPatients = 0
CountNonMissingPatients = 0
CountHb10To12 = 0

i = 3
For i = 3 To Worksheets.count 'To loop from one patient to
another
'Note: Sheet 1 is YearlySummary;
Sheet 2 is Percentages
CountAllPatients = CountAllPatients + 1
If Worksheets(i).Cells(9, j).Value < "" Then 'Cells(9,j) is where
Hb data present
CountNonMissingPatients = CountNonMissingPatients + 1
If (Worksheets(i).Cells(9, j).Value = 10) And
(Worksheets(i).Cells(9, j).Value <= 12) Then
CountHb10To12 = CountHb10To12 + 1
End If
End If
Next i

If CountNonMissingPatients < 0 Then
HBpercent10TO12 = 100 * CountHb10To12 / CountNonMissingPatients
Else
HBpercent10TO12 = -10
End If

If HBpercent10TO12 = -10 Then
Worksheets(1).Cells(9, j).ClearContents
Else
Worksheets(1).Cells(9, j).Value = HBpercent10TO12
End If

Next j

End Sub


"T. Valko" wrote:

Ok, need some clarification...

You want the percentage of patients that have a hemo value =10 and <=12.

In the original post you said this value was located in cell A9 on each
patients sheet which implies a single value per patient.

In your follow-up you now say there is a hemo value for each month.

So, how would you calculate the percentage of patients with a hemo value
=10 and <=12 when there are multiple hemo values for each patient?

Let's assume this is your data:

Pat1: 9.52,9.50,11.12,11.65,11.10
Pat2: 8.99,9.99,10.01,12.01,11.52

What percentage of those 2 patients meet the criteria?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hemoglobin is decimal number to 2 decimal places,
The following is an example of the sheets I have (one sheet per
patient;
has
data from multiple months; has data on hemoglobin and other labs; I
will
need
to calculate percentage for other labs too [like percentage with
ferritin
800], but I could adopt the help I get about Hemoglobin for other
labs).
Thanks a lot.

Jan Feb Mar Apr May
Hemoglobin 9.52 9.50 11.12 11.65 11.10
HCT 28.2 28.5 32.0 33.4 33.9
Ferritin 750 535 415 482 486
Iron Sat 17.3 32.2 12.5 38.4 61.8
Potassium 5.5 4.6 4.8 5.5 5.9

Thanks

"T. Valko" wrote:

What are some typical hemoglobin values? Are they intergers (whole
numbers)?
Are they decimal numbers to 1 decimal place? Are they decimal numbers
to
2
decimal places?

--
Biff
Microsoft Excel MVP


"wissam" wrote in message
...
Hi,
I have data of blood test results for patients in an excel file,
with
each
patient's data being placed on one worksheet. For instance:
Data for PatientA are in worksheet called "PatientA" where cell A9
is
patient's Hemoglobin value

Data for PatientB are in worksheet called "PatientB", where cell A9
is
patient's Hemoglobin value
.....
Data for PatientZ are in worksheet called "PatientZ"; cell A9 is
patient's
Hemoglobin value

I need to create a summary sheet, where A9 in the summary sheet
would
give
me the percentage of among all patients (PatientA to PatientZ) where
Hemoglobin level is between 10 and 12

Any help would be appreciated.
Thanks.


.



.

Excel 2007 PivotTable
Consolidate, Value Filter.
Real numbers only.
With macro.
http://c0444202.cdn.cloudfiles.racks...01_20_10a.xlsm

Excel 2007 Tables
% of patients per test, per month, per limits
http://www.mediafire.com/file/gjlkwm...01_20_10a.xlsm

This looks great!
I will study it.
Thanks a lot.
Wissam

"Herbert Seidenberg" wrote:

Excel 2007 Tables
% of patients per test, per month, per limits
http://www.mediafire.com/file/gjlkwm...01_20_10a.xlsm
.

The code is brilliant and worked like a magic on my database. I have a small
problem, though:
I have a raw in the patient sheets that contains a formula to return a value
(example: takes calcium level and multiplies it by phosphorous to get a
product value CaxP, which is a number of 2 decimals). Is there an easy fix to
the code to pastes the value into the summary sheet Table10 rather than the
content of the cell itself (which is a formula)?
Thanks a lot
Wissam

"Herbert Seidenberg" wrote:

Excel 2007 Tables
% of patients per test, per month, per limits
http://www.mediafire.com/file/gjlkwm...01_20_10a.xlsm
.

Excel 2007 Tables
Consolidate table values.
http://c0444202.cdn.cloudfiles.racks...01_20_10a.xlsm

Excel 2007 Tables
Consolidate table values.
Faster version.
http://c0444202.cdn.cloudfiles.racks...01_20_10a.xlsm

Thanks a lot for the response.

Is it possible to adjust the code in your database (% of patients per test,
per month, per limits; link
http://www.mediafire.com/file/gjlkwm...01_20_10a.xlsm)

so that it would paste-special the value generated by a function in the cell
instead of copy/paste the cell content itself . Currently the code exports
the actual function to the summary sheet table (which generates a meaningless
value) rather than exporting the actual value from patient sheet's table.
I believe the code step that I am referring to is :

.ListObjects(1).ListColumns(j).DataBodyRange.Copy _
Sheets(ss).ListObjects(1).ListColumns(i).DataBodyR ange.Cells(u)

Thanks a lot for your help.


"Herbert Seidenberg" wrote:

Excel 2007 Tables
Consolidate table values.
Faster version.
http://c0444202.cdn.cloudfiles.racks...01_20_10a.xlsm
.