Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate percent on numbers in different sheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate percent on numbers in different sheets
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 On Wed, 20 Jan 2010 13:12:05 -0800, wissam wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate percent on numbers in different sheets
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. "Gord Dibben" wrote: 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 On Wed, 20 Jan 2010 13:12:05 -0800, wissam wrote: 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. . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate percent on numbers in different sheets
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 "wissam" wrote: 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. "Gord Dibben" wrote: 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 On Wed, 20 Jan 2010 13:12:05 -0800, wissam wrote: 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate percent | Excel Discussion (Misc queries) | |||
Calculate Percent in a Column | Excel Discussion (Misc queries) | |||
How to calculate and then show up as a percent? | Excel Worksheet Functions | |||
calculate percent increase | Excel Discussion (Misc queries) |