# Data by runout

VIEWS: 43 PAGES: 2

• pg 1
```									Exercise 24                                    Variation of exercise 23 (Estimation of the increase in incidence rate of TB among HIV-
positive persons compared to HIV-negative individuals)
Given are data on HIV-prevalence among adults as well as HIV-prevalence among TB-patients. Data are from 11 districts in Kenya.
1 Type a sensible value for the incident rate ratio, R , into cell D15. Something like 2 or 3 is a good value.
, where A=proportion of adults infected with HIV, R=incidence rate ratio for TB among those with
2       Use the equation
and without HIV, and T=proportion of TB-patients infected with HIV
Note that all of the values of HIV-prevalence among adults are >0. To force the fitted line through
0/0 we put 0 in D17. Thus, type into cell D18: ‘=B18*D\$15/(1+B18*(D\$15-1))’
3       Copy the expression in D18 down to cell D28.
4       Set cells in the range E18:E28 to the squares of the difference between the proportion with tuberculosis (C18:C28) and the fitted values (D18:D28).
5       Set cell E29 to be equal to the sum of the values in column E.
6 We can now use the Excel function <Solver> to find the value of R that gives the best fit. Click on <Tools> <Solver>, enter E29 into <Target cell>, click on <Equal to
min>, and enter D15 in the box labelled <By changing cells>. Click on <Solve>. This will vary the value of R until the sum-of-squares is a minimum.

7 Draw a graph of the observed values of HIV-prevalence in adults and among TB-patients using a scatter plot. To draw the fitted line, right-click in one of the corners,
<Source data>, <Series>, <Add>, choose for y-values: D17:D28, (x-values are B17:B28), <OK>. Then right-click on one of the data points, <Format data series>,
<Pattern>, for "Line" choose "Automatic" or "Custom" (and make your choice of format), for "Marker" choose "none".
8       What is the best estimate of the incident rate ratio?

Rate ratio                4.59                                                               1.2
11 districts in Kenya
HIV in    HIV in TB             Square of
Setting              adults    patients     Fitted   difference                  HIV prevalence in TB patients   1.0
0.000
Kitui                  0.037      0.337
Meru                   0.069      0.197                                                                          0.8
Nyeri                  0.076      0.279
Kitale                 0.079      0.118
Kisii                  0.080      0.256
0.6
Kakmega                0.123      0.333
Nakuru                 0.132      0.411
Mombasa                0.146      0.450
0.4
Busia                  0.215      0.712
Kisumu                 0.250      0.608
Chulaimbo              0.259      0.608
SS
0.2

0.0
0.0               0.1             0.2     0.3

6976f742-eda0-4513-a01a-9f44fe04580e.xls                                -   Data
Exercise 24                                     Variation of exercise 23 (Estimation of the increase in incidence rate of TB among HIV-
positive persons compared to HIV-negative individuals)
Given are data on HIV-prevalence among adults as well as HIV-prevalence among TB-patients. Data are from 11 districts in Kenya.
1 Type a sensible value for the incident rate ratio, R , into cell D15. Something like 2 or 3 is a good value.
, where A=proportion of adults infected with HIV, R=incidence rate ratio for TB among those with
2       Use the equation
and without HIV, and T=proportion of TB-patients infected with HIV
Note that all of the values of HIV-prevalence among adults are >0. To force the fitted line through
0/0 we put 0 in D17. Thus, type into cell D18: ‘=B18*D\$15/(1+B18*(D\$15-1))’
3       Copy the expression in D18 down to cell D28.
4       Set cells in the range E18:E28 to the squares of the difference between the proportion with tuberculosis (C18:C28) and the fitted values (D18:D28).
5       Set cell E29 to be equal to the sum of the values in column E.
6 We can now use the Excel function <Solver> to find the value of R that gives the best fit. Click on <Tools> <Solver>, enter E29 into <Target cell>, click on <Equal to
min>, and enter D15 in the box labelled <By changing cells>. Click on <Solve>. This will vary the value of R until the sum-of-squares is a minimum.

7 Draw a graph of the observed values of HIV-prevalence in adults and among TB-patients using a scatter plot. To draw the fitted line, right-click in one of the corners,
<Source data>, <Series>, <Add>, choose for y-values: D17:D28, (x-values are B17:B28), <OK>. Then right-click on one of the data points, <Format data series>,
<Pattern>, for "Line" choose "Automatic" or "Custom" (and make your choice of format), for "Marker" choose "none".
8       What is the best estimate of the incident rate ratio?

Rate ratio                4.59                                                               0.8
11 districts in Kenya
HIV in    HIV in TB             Square of                                                                                                            Busia
Setting              adults    patients     Fitted   difference                  HIV prevalence in TB patients
Kisumu
0.000                 0.000
Kitui                  0.037      0.337      0.150      0.035
0.6
Chulaimbo
Meru                   0.069      0.197      0.255      0.003
Nyeri                  0.076      0.279      0.273      0.000                                                                                             Mombasa
Kitale                 0.079      0.118      0.282      0.027
Kisii                  0.080      0.256      0.285      0.001                                                                                    Nakuru
0.4
Kakmega                0.123      0.333      0.393      0.004                                                                  Kitui
Nakuru                 0.132      0.411      0.412      0.000                                                                                                 Kakmega
Nyeri
Mombasa                0.146      0.450      0.440      0.000
Busia                  0.215      0.712      0.557      0.024                                                                                    Kisii
Kisumu                 0.250      0.608      0.605      0.000                                                    0.2             Merui
Chulaimbo              0.259      0.608      0.617      0.000
Kitale
SS         0.094

0.0
0.0                   0.1             0.2                         0.3