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 said:
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 said:
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
:
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
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.
.
.
