How to calculate % in summary sheet from cell A9 in multiple sheet

[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"
(Sheet2) where cell A9 is hemoglobin data ... PatientZ has data on worksheet
"PatientZ" (Sheet50) where cell A9 is hemoglobin data. I have a summary
worksheet called "Summary" (Sheet1). I need cell A9 in the "Summary" (Sheet1)
sheet to give me the percentage of patients whose Hemoglobin value is between
10 and 12 (that is percentage of A9 values in Sheet1 to Sheet50 with value
between 10 and 12). Any help is appreciated. Thanks.

 

[Gary''s Student]

I would definitely make a local table on the summary sheet. Say in Z1 we
enter:

=INDIRECT("Sheet" & ROW(A1) & "!A9") and copy down thru Z50

Then all we need is:

=SUMPRODUCT((Z1:Z50>=11)*(Z1:Z50<=12))/50 and format as percentage.

 

[wissam]

Thank you very much for your response.

This might help if I have only one lab test (hemoglobin) to look at and
fixed number of patients. The number of patients (and thus sheets) changes
frequently. I also have multiple other lab results to look at (like calcium,
sodium,....).

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 neat for the
professionals, but it gave me what I wanted).

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
CountNonMissingPatients = CountNonMissingPatients + 1
If (Worksheets(i).Cells(9, j).Value >= 10) And
(Worksheets(i).Cells(9, j).Value <= 12) Then 'Cells(9,j) is where Hb data
present
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