Organic & Biochemistry Laboratory Manual
Chemistry 107L revised 2011
Table of Contents
Experiment
Page
Principles for Safety in the Chemical Laboratory.................................................................2 Laboratory Notebooks...........................................................................................................6 1,2,3,4... Counting Carbons to name that Organic Compound...........................................10 3-D Modeling of Organic Molecules..................................................................................20 Synthesis of Aspirin .............................................................................................................30 Characterization of Aspirin Synthesis Product Using TLC..................................................35 Characterization of Aspirin Synthesis Product Using NMR and IR....................................40 Saponification, a Sudsy Situation........................................................................................53 Identification of Carbohydrates .........................................................................................59 Testing For Vitamin C .......................................................................................................65 Isolating DNA in the Kitchen ............................................................................................72 Protein Identification Lab ..................................................................................................77 Using Excel to Graph Data.................................................................................................84 Protein Quantitation Using the Lowry Method................................................................. 93 Appendix I: Notes on Laboratory Glassware and Equipment ..........................................100 Appendix II. Notes on Significant Figures ........................................................................107
1
Principles for Safety in the Chemical Laboratory Safe practices in the chemical laboratory are of prime importance. A student should consider it an essential part of his or her educational experience to develop safe and efficient methods of operation in a lab. To do this, one must acquire a basic knowledge of properties of materials present in the lab, and one should realize the types of hazards that exist and the accidents and injuries that can result from ignorance or irresponsibility on the part of the student or a neighbor. Regulations 1. Wear safety goggles at all times while in the laboratory. 2.
Report all accidents to the instructor or lab assistant immediately.
3.
NEVER eat, drink, chew, or smoke in the laboratory.
4.
NEVER leave an experiment unattended. Inform the instructor or lab assistant if you must leave the lab.
5.
After the experiment is completed, turn all equipment off, making sure it is properly stored, and clean your area. Failure to comply with these regulations is cause for immediate dismissal from lab.
Precautions 1.
Approach the laboratory with a serious awareness of personal responsibility and consideration for others in the lab.
2.
Become familiar with the location of safety equipment, such as acid-base neutralizing agents, eye wash, fire extinguisher, emergency shower, broom & dustpan and broken glass container.
3.
Pay strict attention to all instructions presented by the instructor. If something is not clear, do not hesitate to ask the instructor or lab assistant.
4.
Clean up all chemical spills immediately.
2
5.
Be aware of all activities occurring within a reasonable proximity of yourself since you are always subject to the actions of others.
6.
To avoid contamination of community supplies, do not use personal equipment such as spatulas in shared chemicals and replace all lids after use.
7.
Avoid unnecessary physical contact with chemicals; their toxic properties may result in skin irritation.
8.
Use all electrical and heating equipment carefully to prevent shocks and burns.
9.
NEVER handle broken glassware with your hands; use a broom and a dust pan.
10.
Wash your hands at the end of the laboratory.
Personal Attire Choice of clothing for the laboratory is mainly left to the discretion of the student. Because of the corrosive nature of chemicals, it is in your best interest to wear comfortable, practical clothing. Long, floppy sleeves can easily come into contact with chemicals. A lab coat is suggested to help keep clothes protected and close to the body. Accessories also need consideration. Jewelry can be ruined by contact with chemicals. Open toed shoes do not adequately protect one against chemical spills. If hair is long enough to interfere with motion or observation, it should be tied back. Remember that your clothes are worn to protect you.
Assembling Equipment Equipment should be assembled in the most secure and convenient manner. Utility clamps are provided to fasten flasks, etc., to ring stands. This keeps top-heavy or bulky equipment away from the edge of the bench where it can be knocked easily off . Consider the safe location of the hot plate. Keep it away from the bench edge to minimize chances of contact with the body. If the aspirator is being used, locate your apparatus near the sink or center trough for convenience.
Handling Glassware Laboratory glassware is usually fragile, and if it is not properly handled, serious injuries may result Do not force glass tubing or thermometers into a rubber stopper. Lubricate the tubing or thermometer with glycerol or water, wrap it in a towel, and gently insert it into the stopper by using pressure in a lengthwise direction while rotating it. Always grasp the tubing near the stopper. When removing the tubing, remember to protect your hands with a towel. If there are difficulties with this procedure, ask for the instructor's assistance. Apparatus that can roll should be placed between two immobile objects away from the edge of the bench. Chipped or broken glassware cannot be used. There are special receptacles in the lab for these waste materials. After the experiment is completed, all glassware should be emptied, rinsed, cleaned 3
and returned to your drawer. The equipment in your drawer is used by several groups during the week. Each group must pay attention to cleaning and returning equipment to the drawer or the next group will not have necessary items to complete the experiments.
Acids and Bases In this lab sequence, you will come in contact with several acids and bases. As with all chemicals, caution must be taken to prevent contact with the skin. When handling these chemicals, keep hands away from the eyes and face until they have been thoroughly washed. If an acid or base comes in contact with your skin, flush the area with large quantities of clean, cold water. Eyes are extremely sensitive. Use the eye wash provided in the laboratory, or wash with water for at least 10 minutes. Again, the instructor must be notified immediately. To insure your safety, neutralize acid or base spills before cleaning them up. Boric acid is available to neutralize base spills, and carbonate powder is provided to neutralize acids.
Before leaving lab on the first day please 1) sign the provided sheet indicating that you have read and understand the lab safety guidelines (see page 5) and 2) sketch the lab indicating all of the safety features and their locations 3) have your instructor check and initial your sheet before you turn it in and 4) sign and hand in the waiver/release form (page 9) indicating you understand ; the dangers involved in wearing contact lens in a chemistry lab, release Black Hills State University from liability, and consent to receive medical treatment in the event of an injury during this lab.
4
Organic 7 Biochem Survey Chemistry Lab CHEM 107L I have read and understand the safety guidelines for working in the chemistry lab. Name
Date
Signature Please sketch the laboratory below and indicate the location of all safety items in the lab. Also indicate first aid kit and phone locations in relation to the lab. Then have your instructor check and initial before turning in.
Instructor initials
5
Laboratory Notebooks You are required to use a notebook in lab to record all primary data and observations. You should prepare your notebook each week before coming to lab by writing the title of the experiment on a new numbered page, summarizing relevant equations from the lab manual, and starting calculations involving molar masses, etc. Take note of theoretical ideas and special instructions given by your instructor at the start of each experiment. Your notebook should be a complete record of your work in lab. You or other chemists should be able to understand the notes in the future, not just during the current experiment Good note taking in lab is a valuable skill that you can learn with a little effort and practice. Guidelines to be Followed: 1. Always bring your notebook with you to lab. You will be graded on the completeness of your previous note taking and your preparation for the current experiment. You may use your notebook during a lab quiz. 2. Number the pages sequentially and reserve space at the beginning for a table of contents. 3. Take your notebook to the balance, etc. and record values directly in it - not on loose scraps of paper. 4. Specify each measured quantity by name and include the units. 5. If you make a mistake in your notebook, simply draw a solid line through the error and write the correction nearby. 6. Tables greatly simplify data entry; they should be set up before coming to lab. 7. Write down all observations such as color and phase changes - don't rely on your memory. Before coming to lab this notebook should have entered in it • a lab title and page reference (where in the note book this lab is written up) on a table of contents page • numbered pages • Lab Title, date and lab partners name/s at top of first page for that lab • objective for the lab • answers to pre-lab questions (showing work) • a short procedure for the lab (you can cut and paste from the lab handout) • any reaction equations 6
• •
chemical formulas and calculation of molecular masses/weights which will be needed for calculating the results from lab any work that can be done ahead of time will greatly improve your ability to get through the lab in a timely manner
After lab is finished the notebook should contain • raw data from experiment (this includes copies of all graphs!) • set ups for calculations • all final answers • answers to the post lab questions (showing work) • any and all observations made during the lab - observations can be: color, lack of color, temperature, physical state (solid, liquid, gas), a mistake you made in the procedure and how you dealt with it. • all information needed for your report sheets should be in your notebook first and then transferred to the report sheet. That way report sheets are a bit neater and easier to grade. • last of all you need to write up a short conclusion for the lab - your conclusion should address the objectives of the lab - did you achieve the objective? Was it easy to do the lab? What parts were difficult?
If your lab notebook is kept up to date and everything gets written in it you will find quizzes and the final exam much easier as both are open lab notebook!!
7
Attention: Students are advised against wearing contact lenses while observing or participating in science laboratory activities. While hard contact lenses do not seem to aggravate chemical splash injuries, soft contact lenses absorb vapors and may aggravate some chemical exposures, particularly if worn for extended periods.
Please take your contact lenses out prior to entering the laboratory. Contact Lens Administrative Policy and Waiver Form Students are advised against wearing contact lenses while observing or participating in science laboratory activities. While hard contact lenses do not seem to aggravate chemical splash injuries, soft contact lenses absorb vapors and may aggravate some chemical exposures, particularly if worn for extended periods. You are asked to please remove your contact lenses prior to entering the laboratory. Please fill out and sign the waiver/medical release form on the next page indicating that you understand and accept responsibility for wearing contacts in lab. If you do not use contact lens, this form is also; a liability release and a release form for allowing medical treatment in cases of injury. 8
Waiver of Liability, Indemnification and Medical Release I am aware of the dangers involved in wearing contact lenses in a science laboratory setting. On behalf of myself, my executors, administrators, heirs, next of kin, successors, and assigns, I hereby: a.
waive, release and discharge from any and all liability for my personal injury, property damage, or actions of any kind, which may hereafter, accrue to me and my estate, the State of South Dakota, and its officers, agents and employees; and
b.
indemnify and hold harmless the State of South Dakota, and its officers, agents and employees from and against any and all liabilities and claims made by other individuals or entities as a result of any of my actions during this laboratory.
I hereby consent to receive any medical treatment, which may be deemed advisable in the event of injury during this laboratory. This release and waiver shall be construed broadly to provide a release and waiver to the maximum extent permissible under applicable law. I, the undersigned participant, acknowledge that I have read and understand the above release. Name ______________________________________ Age _____________ Signature ___________________________________ Date ____________
9
1, 2, 3, 4..... Counting Carbons to Name that Organic Compound or The simplified use of the IUPAC (International Union of Pure and Applied Chemistry) Nomenclature Objectives:
learn to use IUPAC Nomenclature to name organic compounds from structural drawings Learn to draw the structure of organic compounds from their IUPAC name
Now that you are embarking on your journey to learn about Organic and Biochemistry it is important that you learn the basics of the chemical language used to describe organic compounds. Since there are millions of different compounds it is important that each should have a different name. The IUPAC has devised a formal system of nomenclature (naming) with rules for naming organic compounds which will be the focus of our lab today. In order to employ this system you will need to memorize some of the basics: 1. Parent or root name - how many carbons in the longest chain (or ring if it is a cyclic compound) meth - one carbon eth - two carbons prop - three carbons but - four carbons pent - five carbons 2.
hex hept oct non dec
- six carbons - seven carbons - eight carbons - nine carbons - ten carbons
Suffix (ending of the name) used in the parent or root name for the different types of compounds ane - indicating an alkane (a saturated hydrocarbon with no double or triple bonds) ene - indicating an alkene (an unsaturated hydrocarbon which contains a carbon to carbon double bond) 10
yne - indicating an alkyne (an unsaturated hydrocarbon which contains a carbon to carbon triple bond) ol -
3.
indicating an alcohol (in an organic compound an alcohol is an -OH group)
Prefixes (indicate substituent groups attached to the parent compound) Methyl Ethyl Propyl ChloroBromo-
These lists of parent names, suffixes and substituent prefixes are by no means complete. You will learn more throughout the semester. Today we just want to introduce you to the basics. Basic Rules for Naming Organic Compounds 1.
Count the carbons in a. the longest continuous chain or b. Ring if it is a cyclic compound then select the appropriate root name. Example A
longest chain is 10 carbons giving a root name of - dec- for the 10 carbon chain 11
Example B ring contains 6 carbons giving a root name of - cyclohexcyclo indicating the ring and hex for the six carbons in the ring
II.
Select the appropriate suffix to indicate the type of organic compound, alkane, alkene, alkyne, etc. Both Examples, A and B, are alkanes so the parent names would be: Example A - decane Example B - cyclohexane
III.
IV.
Now name the substituent groups Example A
has a side chain made up of two carbons, an Ethyl group
Example B
has two side chains made up of two carbons, two Ethyl groups
Now indicate the position of the substituent groups by numbering the carbons in the chain or ring sequentially starting: for alkane chains - at the end closest to the side chain for rings - at the carbon where the substituent closest to the beginning of the alphabet attaches for alkenes alkynes, and alcohols at the end closest to the multiple bond or -OH group
Example A
5-Ethyldecane
Example B
1,4-Diethylcyclohexane
12
V.
If you have two or more different substituents they are listed in alphabetical order (ignoring numerical prefixes like di- , tri-, etc. that are used to indicate multiple identical substituents). Example C
This is an alkane with a 6 carbon chain and two substituent groups, a methyl group and an ethyl group so it would be named 3-Ethyl-3-methlyhexane
VI.
If you have a compound with a double bond then you have the possibility of geometrical isomers due to the fact that double bonds don’t freely rotate.
Example D This compound has a four carbon chain with a double bond between the 2 nd and 3 rd carbons and the hydrogens that are attached to the double bonded carbons are on opposite sides. It would be named: trans-2-butene
Example E This compound has a four carbon chain with a double bond between the 2 nd and 3 rd carbons and the hydrogens that are attached to the double bonded carbons are on same side. It would be named: cis-2-butene
Note: you can also have geometrical isomer in cycloalkanes with two substituent groups present.
13
Naming Organic Compounds From the short hand structural formulas below draw out the compound showing the placement of all atoms and covalent bonds. Then using the IUPAC rules for naming organic compounds name the following compounds. #
shorthand structure
complete structure showing all atoms, bonds and appropriate carbons numbered
1
2
3
4
14
name
5
6
7
8
9
15
10
11
12
Drawing the Structure of Organic Compounds from their IUPAC name For each of the following organic compound names draw the structure indicating all atoms and bonds. (it will be easier if you start at the right with the root or parent name which will indicate the type of compound, alkane, alkene, alkyne or alcohol, and the number of carbons in the longest chain/ or in the ring if it is a cyclic compound.) 13.
3-methylhexane
14.
4-ethyl-3,5-dimethylnonane
16
15.
4-methyl-cyclopentene
16.
3-chloro-1-propanol
17.
6-bromo-2-methyl-2-hexanol
18.
5,5-diethyl-3-heptyne
19.
1-cyclohexyloctane
20.
1-chloro-3-methyl-cyclopentane
helpful hints: carbon forms 4 covalent bonds, oxygen forms 2 covalent bonds, hydrogen and the halogens, chlorine, bromine, fluorine form one bond. 17
Names
Section
Nomenclature Report Sheet 1.
Naming Organic Compounds from their Structures
In the space below draw the complete structure of the organic compound indicating all atoms and bonds and name it using the IUPAC Nomenclature. #
Complete Structure
name
2
5
8
11
18
2.
Drawing Organic Compound Structure from their IUPAC Names
In the space provided below draw the complete structures of the compounds from their names. Include all atoms and bonds.
3-methylhexane
6-bromo-2-methyl-2-hexanol
5,5-diethyl-3-heptyne
1-chloro-3-methyl-cyclopentane
Questions: 1.
Cis and trans refer to
2.
Name two types of organic compounds where this type of isomer is found.
isomers.
19
3-D Modeling of Organic Molecules Objectives: In this experiment you will: build models of organic molecules from their empirical formulas and/or name build and name different isomers of compounds with the same empirical formula examine a model of DNA Background The structures of molecules and solids are largely responsible for their physical and chemical behaviors. Compounds with similar empirical formulas may have very different properties because the positions of the atoms and the electronic charge distributions may be very different. Therefore, chemists often write pseudo-structural formulas for a molecule to indicate precisely how the atoms are connected. For example, we often write CH 3 COOH instead of C 2 H 4 O 2 for acetic acid. A physical model provides even more details about the spatial relationship of the atoms in a compound. A model may allow us to view the overall 3-D shape (spherical, flat, rod-like, etc.) of a molecule, to appreciate how flexible it is, or to gauge the distance between atoms that are not directly bonded to each other. Models also enable us to predict how entire molecules interact with each other in a condensed phase. In any event, we must not take the meaning of models too literally, because models never provide a perfect description of real compounds. There are several different types of pieces in your plastic model kits. They are color coded, the black pieces represent carbon, red oxygen, white hydrogen, and blue pieces represent nitrogen. You will notice that there are different numbers of bond attachment points on the black pieces, four bonds indicates all single bonds from the carbon atom giving tetrahedral shape, three bonds - giving a trigonal planar shape, and two bonds giving a linear shape. The red oxygen and blue nitrogen also come with different numbers of bonds depending on its hybridization. Hydrogen atoms are usually indicated by simply leaving the end of the model piece unconnected. Other terminal atoms in a structure, such as halogens, may be shown using colored balls. Lone pairs of electrons are often not shown in a model, but they must be taken into account when establishing the geometry around a central atom. Complex molecules containing multiple center atoms are built by recognizing the shape of each center atom, assembling that center, and then connecting the centers together according to a Lewis structure or drawing of the molecule. When a molecular model is built, the free rotation about single bonds may allow the model to be twisted into a variety of conformations. If single bonds link atoms into a ring, the conformations are limited because the bond angles at each center are fixed. Multiple bonds are rigid and fix the shape of that part of a molecule. Therefore, atoms bound to a center with a double bond may be connected in different, non-interconvertable ways. Molecules that differ only by this kind of connectivity are called geometrical isomers. 20
The shape of a molecule and the types of atoms it contains determines whether that molecule is polar or non-polar. In a large molecule, it is often possible to recognize a smaller unit containing a group of atoms bonded in a specific way. The sub-unit causes a particular chemical behavior of that molecule, so the set of atoms is called a functional group. Very large “macromolecules ” often contain repeating patterns of simple units; in polymers, these units are called monomers. Procedure 1.
The first step in constructing your models will be to draw Lewis structures for each molecule. A Lewis structure is basically a drawing of the arrangement of the atoms in the molecule and the covalent bonds (pairs of electrons shared by atoms) joining the atoms together along with the placement of any nonbonded electron pairs. This is done by adding up the valance electrons provided by each atom in the molecule. The number of valance electrons is easily determined from the group number the element occupies on the periodic table. Divide the total number of electrons by two to get the number of electron pairs you have for forming bonds or nonbonded pairs. (See the General Guidelines for drawing Lewis structures.) Example:
to draw a Lewis structure for CH 4 add up valance electrons
C group IV has four electrons H group I has one electron
1carbon atom x 4 electrons = 4 4 hydrogen atoms x 1electron = 4 Total 8
Then divide total number of electrons by 2 to calculate the number of electron pairs 8/2=4 Using carbon as the central atom arrange the hydrogen atoms around the outside with a single bond between carbon and each hydrogen.
Since this Lewis structure shows Carbon with four hydrogens bonded to it you will need to select the black model piece with four bond attachment points on it to build the 3-D model.
21
2.
Count the number of electron regions around each of the central atoms, remember single bonds, nonbonded electron pairs, and multiple bonds each count as one region. Refer to the VSEPR summary sheet to determine which model pieces to use.
3.
Assemble your model of the molecule and have your instructor initial your report sheet.
Work in teams of two or three. Construct models of each of the molecules listed on the report sheet. Have your instructor check your models and answer the questions. MODEL STRUCTURES YOU WILL BUILD 1. Alkanes: These are simple hydrocarbons with sp 3 -hybridized carbon atoms (atoms with four electron regions around them.). Alkanes may be gases, liquids, or solids. They are often used as fuels. 2. Alkenes: These are hydrocarbons containing carbon-carbon double bonds (sp 2 hybridized carbon, with three electorn regions around them) do not rotate. Geometrical isomers are possible. Alkenes, such as ethylene (H 2 C=CH 2 ) and vinyl chloride (ClHC=CH 2 ) may be polymerized. 3. Alkynes: These are hydrocarbons containing carbon-carbon triple bonds (sphybridized, with two electron regions around them) 4. Rings: Hydrocarbons may form ring compounds with either sp 3 or sp 2 carbons. (Parent name prefix cyclo-) The differing shapes and flexibility of these compounds gives them very different physical and chemical properties. 5. Functional groups and parent name suffix: Alcohols (-OH) -ol, carboxylic acids (COOH) -oic acid, esters (-COO-) -oate, amines (-NH 2 ) -amine, and aldehydes (-CHO) -al are distinguished by the presence of a characteristic group of bonded atoms (-XX).
MODELS TO VIEW DNA deoxyribose guanine
cytosine
thymine
22
cytosine
General Guidelines for Drawing Lewis Structures 1. Lewis structures are only useful for covalent bonding. Don’t use for ionic compounds 2. Count the total number of valence electrons all the atoms of the molecules. Do not count core electrons. Also remember to add electrons if the molecules is an anion or subtract electrons if it is a cation. 3. Arrange atoms symmetrically, with the least electronegative atom in the center and the more electronegative atoms around the outside. F and H should always be on the outside with a single bond. Cl, Br, and I are also usually around the outside with single bonds. 4. C, N, O, and F, obey the octet rule. C, N, and O are frequently involved in double or triple bonds. 5. In radicals (molecules with an odd number of electrons) use pairs of electrons for each bond, then place the unpaired electron on the least electronegative atom or in a multiple bond. 6. If a single arrangement of atoms can have double bond(s) in more than two equivalent locations you have resonance forms present. 7. Nonmetals from the third or higher period may have an expanded octet, if they are the central atom in a structure. Place extra lone pairs on this atom. In cases of multiple possible structures the one with the lowest formal charge is best. 8. Be, B, and Al may form compounds with incomplete octets. In this case they need to be the central atom. 9. Begin to recognize groups of atoms that are frequently bonded together in different structures. Use these as building blocks to speed up you solution to any complicated structure 10. When finished check you final structure by double checking the total number of valence electrons.
23
Summary of VSEPR Orbital and Molecular Geometries # electron regions and hybridization 2 (sp)
# non-bonding pairs
Orbital Geometry
Bond angles Molecular geometry
Example
0
linear
180
linear
BeCl2
3
(sp 2 )
0 1
trigonal-planar trigonal-planar
120 120
Trigonal planar V-shaped
BF 3 SO 2
4
(sp 3 )
0 1 2
tetrahedral tetrahedral tetrahedral
109
(This will show your data graphed out X values from column A and Y values from column B)
Step 9
left click on Next>
this window allows you to put a title on your graph and label the X and Y axis. Exercise I, for the chart title enter: Weight of Growing Mice Over Time, for the value (X) axis enter: average weight in grams, for the value (Y) axis enter: age in days
Step 10
left click on gridlines in this window you can select to have gridlines printed on your graph (gridlines make it very easy to read values off your graph.) For this exercise select major and minor gridlines for both the X and Y axis.
Step 11
left click on Legend
in this window you can have a legend printed on your graph for each series of data. For our purposes deselect show legend so no legend will be shown.
Step 12
left click on Next>
in this window you decide where to put your graph. Select As an object in: Sheet 1 (this will place your graph on the spreadsheet with your actual data) 85
this drops your graph onto your spreadsheet (at this point you may want to change the view to make it easier to adjust graph on page. If so select the View menu at top of page, select zoom, then select 75%)
Step 13
left click on Finish
Step 14
left click on the Chart Area to select it (you will see little filled squares around the perimeter of the chart once it is selected)
Step 15
left click and hold to drag the graph to where you want it on the sheet. For Exercise I drag it down below the data in columns A & B and place the graphs left edge on the left margin of the sheet.
Step 16
left click and hold on the lower right corner (you should get a double ended diagonal arrow) and drag the corner out and down to fill up the page with the graph. To preview the page select the sheet by left clicking on the shaded cell to the left of the A column label then select File and print preview. (If you don’t select the sheet the preview will only show you the graph, not your data table or header.) If you are happy with the placement of the graph on the sheet, close window to return to chart.
Now your graph is pretty well set up and all that is left is fine tuning to make it eaier to read values from
Finishing up the graph To set X and Y axis scales place mouse pointer on the X axis of graph (it will say value (X) axis) right click and select format axis, then select the scale portion of the window at top, for Exercise I set minimum to 0, maximum to 14, major gridlines to 2 and minor gridlines to 0.2 (If you wanted to rescale the y axis you would put the pointer on the Y axis and go through the same steps.) Getting rid of the Shading in the Plot Area select plot area from tool bar, or place mouse pointer in shaded plot area not touching any lines or data points (should say plot area) right click and select Format Plot Area, left click. This window allows you to adjust shading/color of plot area. Select the white color then click on OK. (Sometimes if gridlines are too close together this is difficult to do, just stretch graph out get rid of gray then push it back to the original size)
86
To adjust placing of graph title or axis labels left click on the area of interest < to move area left click and hold while you drag area to where you want it. < to correct spelling or add text left click in the text to be changed to drop the text cursor < to change font size right click to get into the format window
To Have a line of best fit plotted place mouse pointer on one of the data points on graph right click left click on trendline in Type window select linear left click on Options select display equation on chart left click on OK (This will plot the best fit line on your graph and print the equation for the line on the graph. The equation for a line is y=mx+b, where y and x are the axis values, m is the slope and b is the y-intercept.) To Print Your Spreadsheet < select the spreadsheet by left clicking in the shaded cell to the left of the cell labeled A (if you don’t select the whole sheet it will only print your chart) < select file < left click on print preview < if preview is what you want left click on print
Directions for the Office 2007 version of Excel Exercise I. Step 1
find a computer with Microsoft Excel 2007 installed on it
Step 2
select Excel and open the program
Step 3
under the ‘Page Layout’ Tab select Size - Letter 8.5" x 11" Orientation- Portrait Margins - Custom Margins Top: 1 Bottom: 1 Left: .75 Right .75 Header: Exercise I with your names OK 87
Step 4
Enter the following column titles and data into columns A & B on the Excel spreadsheet. (See data table below) You can pull the columns wider by placing the mouse pointer on the column line and clicking and holding the left button while you drag the column wider. (Note: Excel will by default use data in column A for X values and data from column B for Y values in a scatter plot. And if you have data in columns A, B, C, & D highlighted it will plot three series using A as the x value for all three and B, C, and D as y values)
Average weight grams
Age in days
1
1
2
2
4
4
6
6
8
8
10
10
Step 5
left click on the top left cell, while holding the mouse button down move the mouse over the bottom right cell and release the mouse button. The data table should now be outlined with a heavy black line and should have a gray background to show it has been selected.
Step 6
left click on the Insert tab
Step 7
left click on ‘Scatter’ in the charts section
Step 8
left click on the top left chart sub-type (this one shows only data points no lines) A plot of your data will now appear and the format of the buttons at the top of the screen will revert to the default Excel buttons. While you have a workable plot you need to clean up the plot and add important features like titles for the plot and the axes
Step 9
left click anywhere in the plot. Note the buttons at the top of the screen change as Excel shifts to ‘Chart Tools’ mode
88
Step 10
left click on the Layout Tab left click on the Chart Title icon in the Labels section left click on the ‘Above Chart’ icon There will no be a text box on your chart that says ‘Chart Title’ Left click in the box and replace the words ‘Chart Title’ with Weight of Growing Mice Over Time
Step 11
left click on the Axis Titles button under the Layout tab Under Primary Horizontal Axis Title Click ‘Title Below Axis’ and replace the text ‘Axis Title’ with average weight in grams,
Step 12
In a similar manner label the Y axis age in days.
Step 13
left click on the gridlines icon in the Axes section Set both Primary Horizontal Gridlines and Primary Vertical Gridlines to Major & Minor Gridlines (gridlines make it very easy to read values off your graph.)
Step 14
left click on the Legend icon in the Labels section and select ‘None’ to remove the legend.
Step 15
left click on some white space on your graph, and move it to a good spot on your Excel spreadsheet so you can print out the data and the graph on a single piece of paper. For Exercise I drag it down below the data in columns A & B and place the graphs left edge on the left margin of the sheet.
Step 16
left click and hold on the lower right corner (you should get a double ended diagonal arrow) and drag the corner out and down to fill up the page with the graph. To preview the page select the sheet by left clicking on the shaded cell to the left of the A column label then select File and print preview. (If you don’t select the sheet the preview will only show you the graph, not your data table or header.) If you are happy with the placement of the graph on the sheet, close window to return to chart.
Now your graph is pretty well set up and all that is left is fine tuning to make it easier to read values from
Finishing up the graph To set X and Y axis scales place mouse pointer on the X axis of graph right click and select format axis, for Exercise I find the maximum row, click on the ‘fixed’ button and set the 89
maximum to 14. Next adjust the Y axis. The scale here is OK, Note: in the format axis menu you can set location of axis labels, format type of number, and select font and size. Depending on the size of your plot you may want to change the format of the number (under Format Axis - Number option) or the size of the font (Window that comes up when you first right click on the axis)
To adjust placing of graph title or axis labels left click on the area of interest < to move area left click and hold while you drag area to where you want it. < to correct spelling or add text left click in the text to be changed to drop the text cursor < to change font size right click to get into the format window
If you need to have a line of best fit plotted place mouse pointer on one of the data points on graph right click left click on Add trendline In the Trendline Options Window Click on the ‘Linear’ button select display equation on chart box left click on CLOSE (This will plot the best fit line on your graph and print the equation for the line on the graph. The equation for a line is y=mx+b, where y and x are the axis values, m is the slope and b is the yintercept.) To Print Your Spreadsheet < select the spreadsheet by left clicking in the shaded cell to the left of the cell labeled A (if you don’t select the whole sheet it will only print your chart) < left click on the Windows Office icon in the upper left hand corner < move your mouse over the print icon, and then click on Print Preview < if preview is what you want left click on print
90
Now that you have worked your way through one graph complete Exercise II on your own.
Exercise II. Accurately calculating amounts of insulin in blood samples is sometimes done by running an assay which uses a radioactive isotope of iodine, I125. A standard curve is prepared using samples containing known amounts of insulin. Then the known samples and the unknown blood samples are assayed in the same manner and radioactive emissions are counted on a scintillation counter. Data for the known amounts of insulin is graphed and a line of best fit is generated to set up a standard curve from which amounts of insulin in the unknown samples can be calculated. Below you will find a table of data from an imaginary Insulin Assay. Using Excel and chart wizard set up a spread sheet with a data table and graph of this standard curve. Remember to set up the scale and gridlines so that you can read insulin concentrations for unknown samples off your graph to +/- 2 microgram. (Remember that when reading a scale your first uncertain digit, thus your last significant digit is the one you estimate between the lines.) I125 cpm
Insulin micrograms
167
50
298
100
443
150
721
200
1670
500
2445
750
When you have printed both of the spreadsheets with data tables and graphs for Exercise I and Exercise II answer the following questions using your finished graphs. Post Lab Questions: 1.
What is the slope of the line from your graph in Exercise I?
2.
What is the y intercept in your graph from Exercise I?
3.
Using your graph what is the average weight of a mouse that is five days and twelve hours old?
4.
If a young mouse weighs 2.75 grams from your graph, how old is it?
5.
What is the slope of the line in your Standard Curve for Insulin graph, Exercise II?
6.
What is the y-intercept for your Standard Curve for Insulin graph, Exercise II?
91
Names
Section
Report Sheet Excel Graphing Lab
Exercise I Slope of line Y-intercept
Staple a copy of your graph for Exercise I to this report sheet.
Exercise II Slope of line Y-intercept Staple a copy of your graph for Exercise II to this report sheet.
1.
How many micrograms of insulin are in a blood sample that has 1250cpm (counts per minute) when assayed? (Use your graph from Exercise II)
2.
If the volume of the blood sample used in the assay is 0.200ml, what is the concentration of insulin in the blood sample in question #1 in micrograms/milliliter?
3.
Where would you go to adjust the orientation of the paper for your graph? (Landscape or portrait)
4.
What do you need to do to print your graph only, without your data table?
92
Protein Quantitation Using the Lowry Method Objectives:
