calculate percent between 10.0 and 12.0 for data in multiple s
in [Functions]
|
Prev: How do I change date that is in All Caps to proper format?
Next: a cell contain formula, how to extract value of that cell
From: wissam on 23 Jan 2010 00:48 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" <wissam(a)discussions.microsoft.com> wrote in message > news:F9584E2B-62EC-4062-9EE0-CBEF3AF35723(a)microsoft.com... > > 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. > > > . >
From: T. Valko on 23 Jan 2010 11:57 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" <wissam(a)discussions.microsoft.com> wrote in message news:EF4D141A-A41C-4A36-A65A-ED2ACC364DF6(a)microsoft.com... > 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" <wissam(a)discussions.microsoft.com> wrote in message >> news:F9584E2B-62EC-4062-9EE0-CBEF3AF35723(a)microsoft.com... >> > 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. >> >> >> . >>
From: wissam on 24 Jan 2010 22:27 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" <wissam(a)discussions.microsoft.com> wrote in message > news:EF4D141A-A41C-4A36-A65A-ED2ACC364DF6(a)microsoft.com... > > 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" <wissam(a)discussions.microsoft.com> wrote in message > >> news:F9584E2B-62EC-4062-9EE0-CBEF3AF35723(a)microsoft.com... > >> > 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. > >> > >> > >> . > >> > > > . >
From: wissam on 24 Jan 2010 22:35 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/gjlkwmj2tqt/01_20_10a.xlsm > . >
From: T. Valko on 24 Jan 2010 23:27
Good deal. Thanks for feeding back! -- Biff Microsoft Excel MVP "wissam" <wissam(a)discussions.microsoft.com> wrote in message news:BDB33781-A92D-498D-87F7-458D84DA905E(a)microsoft.com... >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" <wissam(a)discussions.microsoft.com> wrote in message >> news:EF4D141A-A41C-4A36-A65A-ED2ACC364DF6(a)microsoft.com... >> > 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" <wissam(a)discussions.microsoft.com> wrote in message >> >> news:F9584E2B-62EC-4062-9EE0-CBEF3AF35723(a)microsoft.com... >> >> > 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. >> >> >> >> >> >> . >> >> >> >> >> . >> |