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.

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

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.


.

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

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.


.



.

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
.

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.


.



.

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
.

The code snippet you appropriately quoted has been superseded
by new code in the CloudFile link.
The CaxP formula has been added as a demo.
The code in the MediaFire link has not been edited.
It still copies formulas.

I am very sorry if I may look very naive when it comes to cloudfiles (this is
very new to me). To get the new file with the code you mentioned, am I
supposed to do anything other than clicking the link in the post above and
save the file ? The only file version I get from clicking all the cloudfile
links in the above posts is one file (same file; it that has only one summary
sheet that shows Hemoglobin [H_Value] data in pivottable, in addition to 3
sheets [GroupA, GroupB, GroupC], and I cannot seem to find the CaxP formula
demo mentioned)
Do I have the correct file link in the posts?
Again, I am sorry if I sound naive about this and THANK YOU very much for
your patience.

"Herbert Seidenberg" wrote:

The code snippet you appropriately quoted has been superseded
by new code in the CloudFile link.
The CaxP formula has been added as a demo.
The code in the MediaFire link has not been edited.
It still copies formulas.

.

I, and dozens of other people, downloaded the files from the above
links
and found the correct versions.
The version you mentioned (with GroupA)
has been deleted from the links days ago.
You probably have all 3 versions on your PC, but at different
directory locations.
Have some new blood at your facility try downloading.

I believe that the following snippet fixes my problem so that it would
pastspecial value:

..ListObjects(1).ListColumns(j).DataBodyRange.Copy

Sheets(ss).ListObjects(1).ListColumns(i).DataBodyR ange.Cells(u).PasteSpecial
(xlPasteValuesAndNumberFormats)
Application.CutCopyMode = False

The current function to get the percentage of table10[hemoglobin] with
values between Table11[lo] and Table11[high]is as follows:

=COUNTIFS(Table10[Type],[Type],INDIRECT("Table10["&Table12[#Headers]&"]"),"="&INDEX(Table11[Lo],rown),INDIRECT("Table10["&Table12[#Headers]&"]"),"<="&INDEX(Table11[Hi],rown))/COUNTIFS(Table10[Type],[Type])

The denominator in the above function currently counts all cells in the
column (even empty ones). For instance, in table10 shown below, for month of
Jan, % of hemoglobin values between 10 and 12 is 50%. How do I change the
denominator in the above function so that it count cells that only have
numbers (i.e do not count cell into the denominator for that month if cell is
empty; as such, the percentage would become 100%)?

Table10
Type Jan Feb Mar Apr May
albumin 3.5 3.5 3.3 3.5 3.1
aluminum 3.6 3.5 3.3 3.3 3.5
Aluminum 5
hemoglobin 10.7 10.8 11.3
hemoglobin 10.5 9.8 11.0 11.2

Thank you very much.



"Herbert Seidenberg" wrote:

The code snippet you appropriately quoted has been superseded
by new code in the CloudFile link.
The CaxP formula has been added as a demo.
The code in the MediaFire link has not been edited.
It still copies formulas.

.

I just saw your post about trying another computrer and I opened the link
from another computer and got the correct file. Thank you.
Any help with how to change the percentage function so that not count empty
cells in denominator is very much appreciated.
Thanks a lot again.

"Herbert Seidenberg" wrote:

I, and dozens of other people, downloaded the files from the above
links
and found the correct versions.
The version you mentioned (with GroupA)
has been deleted from the links days ago.
You probably have all 3 versions on your PC, but at different
directory locations.
Have some new blood at your facility try downloading.
.

Excel 2007 Tables
Consolidate.
Revised % formula to accommodate unavailable data.
http://www.mediafire.com/file/qedjmj...01_20_10b.xlsm

Excel 2007 Tables
Consolidate
Faster format
http://c0444202.cdn.cloudfiles.racks...01_20_10b.xlsm

Thank you very much..It worked perfectly.

"Herbert Seidenberg" wrote:

Excel 2007 Tables
Consolidate
Faster format
http://c0444202.cdn.cloudfiles.racks...01_20_10b.xlsm
.