Calculate percent on numbers in different sheets

[wissam]

Hi, I have data on patients in excel file, with each patient's data placed on
a separate sheet. For instance: PatientA has data on worksheet "PatientA"
where cell A9 is hemoglobin data ... PatientZ has data on worksheet
"PatientZ" where cell A9 is hemoglobin data.
I have a summary worksheet called "Summary". I need cell A9 in the "Summary"
sheet to give me the percentage of patients whose Hemoglobin value is between
10 and 12. Any help is appreciated. Thanks.

 

[Gord Dibben]

Separate sheets for each patient is always a pita when it comes to summary
sheet.

For ease of summarizing copy all patients to one sheet and simply filter for
those will hemoglobin between 10 and 12


Gord Dibben MS Excel MVP

 

[wissam]

Thank you Gord very much for your response.
I think that what I am looking for is a bit more complicated, and
copying/pasting into one sheet might not solve my problem. Each patient data
sheet we have contains data across multiple months, and contains data on
multiple other labs (not only Hemoglobin). The following is a truncated
example of a patient sheet (we have multiple other labs that we track; I need
to calculate percentage on other labs, not only hemoglobin; for instance,
percent of patients with ferritin above 800,.....).

Jan Feb Mar Apr May Jun
Hgb 9.50 9.50 11.10 11.60 11.10 8.90
HCT 28.2 28.5 32.0 33.4 33.9 27.2
Ferritin 750 535 415 482 486 468
Iron Sat 17.3 32.2 12.5 38.4 61.8 43.1
Potassium 5.5 4.6 4.8 5.5 5.9 5.8

I have used VBA in Microsoft Access , but never in Excel. I was wondering if
a code in Excel might help me (if I get help with Hemoglobin, I would use it
as a guidance for how to get the values I need for other labs, like
calcium,...). The code I need would give me the following:

1) Get the total number of patients I have (as count of all excel sheets I
have minus 1, since I have a summary sheet). Call this variable
"TotalPatientCount".

2) Get the number of patients with Hemoglobin value (in cell A9 of all the
sheets) between 10 and 12; call this "Count". I presume this would be done
via a loop, something like:
Count =0
For i = 2 To TotalPatientCount
With Worksheets(i)
If .Range("A9") >= 10 and <=12 Then
Count = Count + 1
end if
end with
next i
end loop

3)Percentage would be 100*count/TotalPatientCount

Any help in writing the above code would be very much appreciated.

 

[wissam]

I figured it out and code is shown below (might not be very efficient code
for the professionals, but it gave me what I needed).

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)
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(6, j).ClearContents
Else
Worksheets(1).Cells(8, j).Value = HBpercent10TO12
End If

Next j

End Sub