This is a very quick tour of the Epi_Tools.XLSX spreadsheet. This is a file that actually contains a number of worksheets that will enable you to do a variety of epidemiologic or biostatistical calculations quite easily.

This is one of the worksheet which functions as a menu. The main menu down here and it has hyperlinks that will bring you to many of the functions that are contained within this file.

The worksheets are also are arrayed in tabs across the bottom here, and I find these to be a more convenient way to find things. There are some functions that are not included on this hyperlinked main menu, but if I go to one of the worksheets, they generally have a link somewhere on the sheet that will bring you back to that main menu if you desire.

There are a number of functions here. First, there are two sheets that actually demonstrate two ways of creating epidemic curves. These each have hyperlinks to a short video that explains how a particular technique works.

The alternate method is in EpiCurve2, and you can look at these videos at your leisure and see two ways of creating epidemic curves.

One of the more useful worksheets for students in epidemiology is this "CI One Group" which calculates the confidence interval for proportions or rates in a single group. There is an upper and lower function on this page. The top portion is for computing the confidence interval for single a prevalence or a single cumulative incidence, in other words a proportion. The lower portion is for computing the confidence interval for a single incidence rate. In each case, you can put your own numbers into the turquoise boxes, and it will automatically do the calculations for you. You can see that in row #9 here I have a numerator of 4 and a denominator of 8. If I were to change this to a numerator of 32 and a denominator or, say, 78 and then click outside the box, it will automatically recompute the estimated proportion, and it will calculate 90%, 95%, and 99% confidence intervals. So, obviously the 95% confidence intervals are going to be most useful for us. And it has, in fact, 4 rows that will to this, so you can do multiple calculations simultaneously. This confidence interval in one group would be used, for example, if you were looking at information from a case series, where you are not really comparing groups. You are just calculating confidence intervals for a single proportion, or in the case of an incidence rate down here below, if you wanted the confidence interval for single incidence rate, you could use this sheet. 

Most of the cells on the sheets are locked, so you cannot change the formulas inadvertently. However, any cells that are shaded with turquoise are cells for which you can change the numbers, and then you can see the effects that has on the calculations. Some of these worksheets are informational. This worksheet for Chi-squared tests enables you to do some calculations, but is primarily here to explain how the Chi- squared test works. The Chi-squared test is actually incorporated into some of the other sheets, such as the one for case-control studies, which has a turquoise 2 by 2 table in which you can enter the counts that you have for cases and controls and their exposure distribution.

This is a typical two by two contingency table that we've utilized in class. I'm going to change the number of exposed cases here from 20 to 45 and click somewhere outside the box in order to get it to recompute, and you can see that it automatically redoes all the calculations including the odds ratio, the p-value (using the Chi-squared test) and also the upper and lower limits of the confidence interval.

In cases where the Chi-squared test is inappropriate - for example, if we had very small numbers of cases and the expected numbers in any one of the cells under the null hypothesis would be less than 5, then the Chi-squared test is inappropriate, and one needs to use Fisher's Exact test. If that is the case, if the expected numbers under the null hypothesis are less than 5 in any one of these cells, this flag down here will show up, and there is a hyperlink to an online Fisher's Exact test that you can use as an alternative to the Chi-squared test. If this assumption is not violated, the flag goes away. There is another information sheet discussing the relationship between cumulative incidences and incidence rates.

The Cohort Studies worksheet is very useful. It also has an upper and lower portion. The top portion is for studies in which you are comparing cumulative incidence. The lower portion is used when you are comparing incidence rates. Once again, you have the ability to change the numbers in the turquoise shaded cells boxes and having changed those numbers, and then when you click on an empty cell, Excel will update the calculations of incidence rates in each group, the rate ratio, the rate difference, and their 95% confidence limits, the attributable proportion, the p-value, and even the population attributable fraction. There many other worksheets, but they that will be infrequently used for the course.

For example, there is this one called "Sample Size". If, for example, one were contemplating doing a study in which it was important to compare means, where without treatment the mean was 205 with a standard deviation of 30, and we anticipated that some treatment might reduce the mean to 180, this would compute the number of individuals you would need in each group in order to achieve a certain level of statistical power at any given, alpha level or p-value. The upper part does this if you are comparing means, and the lower one is for comparing proportions.

There is also a random number generator for randomization in a clinical trial. You can indicate how many groups you will be comparing in a clinical trial and this the second number the second box and you put any number into the second cell in order to "seed" the random number generator, and then, when you a click in another empty cell it generates a ransom group assignment. So, if I do this for a large number of subjects, it eventually randomly assign them to any one of three groups in this example. You can change the number of groups you want, and this sheet will randomly allocate people to that number groups. 

There are additional worksheets for direct standardization, standardized incidence ratios - things that will be addressed later on the course. I can also use these arrows to see more of the worksheets. There are additional sheets here that do probability calculations based on Poisson probabilities, binomial probabilities, and normal probabilities.

There is a worksheet that will provide you with a contingency table with which you can evaluate screening tests, calculating sensitivity, specificity, and positive and negative predictive values.

There's a sheet that describes how to do T-tests using Excel and, this one is demonstrating both paired and unpaired T-tests.

Another sheet demonstrates have to perform simple linear regression using Excel. Again, there is a box into which you can enter a limited amount of data, but the formulas for computing the slope, the intercept, the correlation coefficient, and the R-squared value, and even p-value for this linear function are all shown over here. You can replicate these formulas to suit your own purposes.

There is also a demonstration of how to do an analysis of variance, and then there are a number of other  tools that we may look at later in the course.