ICT IN BIOLOGY: USING EXCEL AS A DATA BASE DATABASE OF FEMALE REPRODUCTIVE HORMONES Data provided by Professor Paul Biscof Département de Gynécologie et d'Obstétrique Hôpital Universitaire de Genève Use the database that contains the data from 20 healthy women volunteers aged 20 to 35 years old in Geneva Switzerland. These women had not been taking oral contraceptives for at least 3 months before the start of the study. Daily morning blood samples were taken on starting on the first day of their menstrual cycle (determined by the beginning of their menstrual bleed = menses) and continued to the first day of their next menstrual bleed. Six hormones were measured in the blood samples: FSH LH Prolactin Oestradiol Progesterone Testostrone UI = International Units pg = picograms ng = nanograms The data was complemented by ultrasound scans of the volunteers’ ovaries to determine the stage of the follicles. Using an Excel Database Open the excel file and save it onto your computer's desktop. Open MSDATA.xls from your desktop. Remove and empty dividing lines between volunteers (for example, line 29 and line 30) Select the line(s, right click and select delete. Repeat for other empty lines between the information for the twenty volunteers. Return to line 1 of the database and highlight cells A to H Go to Data, Filter, and then Auto filter Small downward-pointing arrows will appear to the right in cells A to H in line 1 When you click on any one of the arrows you will obtain the following menu Sort Ascending Sort Descending (All) (Top 10….) Custom Followed by a list of items entered in the cells below the title cell. Go to A1 Volunteer, click on the arrow and select 3 You have now filtered all of the information concerning volunteer number 3. Notice that the arrow in A1 is now a different colour (in this case, blue). This indicates to you that a filter has been used in this column. You should also notice that the number of filtered entries is indicated in the left had corner of the Excel window. In this example “33 of 579 records found”. To return to the complete database, click on the coloured arrow (in this case in A1) and select ALL. DATABASE OF FEMALE REPRODUCTIVE HORMONES: Using the Custom option This is very useful if you want to compare results, for example those of volunteer 3 and volunteer 7. Return to the complete database. Click on the arrow in A1 and select Custom. A pop-up window will appear. Enter to information as shown below (and then click “ok”). You have now filtered the information from volunteers 3 and 7 only. Now go to cell B1 and click on the arrow Select day 8 You can now compare the results from day 8 from the two volunteers selected. Notice that two arrows in line 1 are now coloured (in this case, blue). This means that you have to click on both arrows, selecting ALL in each case, to return to the complete database. DATABASE OF FEMALE REPRODUCTIVE HORMONES: Exercise Start with the complete database. Filter day 8 from B1 Now click on the arrow in line E1 and select Custom When the pop-up box appears, enter “is greater than or equal to” and “10” 12 records are filtered Now click on the arrow in column F1 and select Sort Descending This allows you to compare the oestradiol levels (pg/ml) with high prolactin levels (≥ 10 ng/ml) during day 8 of the menstrual cycle from the 20 volunteers. Questions: Answer the following questions on your own paper. 1) How many volunteers show progesterone levels greater than 1ng/ml on day 9 of menses? 2a) Which volunteer shows FSH levels over 10UI/L during 5 days? 2b) Are these days consecutive? Investigation 1 The follicle ruptures about 36 hours after the LH peak. Plot the levels of FSH & LH against time for volunteer #1. insert directions here). What do you notice? Record observations on your paper. Please print this graph as you will turn it in with your other answers. Now, look through a few sample volunteers in the database in order to determine the lowest concentration that LH rises to in the peaks. After you think you are close, select LH, Filter, Custom. In the dialogue box enter “greater or equal to” and enter your figure. Press Enter. You should now have a list of the volunteers whose level of LH went above your selected value. If you have two or more values from the same volunteer you will need to raise the value that you are using. If, after filtering, you have five or less than five volunteers, you will need to lower the value you are using. You will probably find that some of the volunteers are missing after filtering; it’s ok. The peaks for LH will occur on a particular day in the menstrual cycle. Ovulation occurs 36h later. Copy the Days from the LH filtered mensis data and paste it under the data base. In order to determine the point of ovulation, in the column to the right, use the following formula to add 1.5 days: =(click on day cell+1.5) and then “enter.” Establish the mean and standard deviation for the day of ovulation for this set of volunteers and record this information on your paper. (Reminder: =Average(select data); =STDEV(select data) The rhythm method is a method of contraception which relies on calculating the “safe period”. This is the period when there is no viable oocyte in the fallopian tube. Oocytes are receptive to sperms for about 24h after ovulation and then die after 3 days. Authorities vary in their estimations of how long sperm cells remain viable in the uterus. Figures for sperm viability vary from as little as 1 day to as long as 7 days inside the woman after ejaculation. Questions: Answer on your own paper. 1) According to this data, what range of days in a woman’s cycle are “unsafe” and the woman should avoid sexual intercourse? 2) Using the evidence from this data base, evaluate the rhythm method of contraception.