Outcome Of Both Vertical And Lateral Gene Transfer
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

BIO 102 Lab 03: Chemical Digestion and Nutrition
To submit, print this document, complete all lab activities, take a photo of your bile experiment, and answer the review questions. Scan your lab pages using the free phone app AdobeScan, and upload your PDF and your bile photo to Canvas.
· Please turn in only pages with completed work (ie., omit pages such as page 1 that only provide instructions)
· Please write your name on the 1st page you submit
If you have a disability that makes it difficult to complete this lab, please contact your instructor.
Copyright © 2016 by Mary Vander Maten and Jill Caporale.
OBJECTIVES
After completing this lab, you should be able to:
Provide an example of how an enzyme is named.
Explain the functions of enzymes during digestion.
Note the effect of bile salts on fats.
Complete a personal diet assessment
Explain Body Mass Index and its relationship to health.
INTRODUCTION
Enzymes are an important group of proteins that enable cells to carry out the chemical reactions needed to support life at temperatures that the cells can tolerate. Enzymes are organic, or biological, catalysts. They speed up chemical reactions that would otherwise occur at much slower rates and can be used over and over many times. The chemical that the enzyme affects is called the substrate. An enzyme is named by adding “–ase” to the name of the substrate it acts upon. For example, the enzyme that speeds up hydrolysis, or digestion, of fats is called lipase and the enzyme that breaks down cellulose is called cellulase.
Part 1: DIGESTION
•Mouth: The Digestion of Starch
Amylase is a protein that is an enzyme produced by the salivary glands and pancreas. Recall that starch is a complex carbohydrate composed of many glucose molecules bonded together. Amylase begins starch digestion in the mouth and hydrolyzes the starch into less complex carbohydrates, such as maltose, a disaccharide. Starch digestion continues in the small intestines.
Starch âź¶ Simple Sugars
via Amylase
•Stomach: The Digestion of Proteins
Protein digestion is initiated in the stomach by proteases such as pepsin and continues in the small intestine.
Protein âź¶ Amino acids
via Pepsin
Although there is some enzyme activity in the stomach, much of the work is mechanical. Searching online, find diagrams or photos of the human stomach’s interior. (Figure 33.8 in your textbook does a poor job clearly showing the stomach interior). Specifically pay attention to the illustrations of the stomach’s interior surface to observe the rugae lining the inside of the stomach.
•Small Intestine: The Primary Site of Digestion and Absorption
Don’t let the name “small intestine” deceive you. The name describes its relative circumference to the large intestine. In contrast, the length of the small intestine is about six meters whereas the length of the large intestine is about one and a half meters.
Most digestion and absorption takes place in the small intestine. Carbohydrate and protein digestion are completed in the small intestine. Lipid and nucleic acid digestion are initiated and completed. Although digestion occurs in the small intestine, some enzymes necessary for the digestion are produced by the pancreas.
The enzymes produced in the pancreas are transported to the duodenum by the pancreatic duct. Bile, produced in the liver and stored in the gall bladder, is delivered to the small intestine to aid in fat digestion. Bile is an emulsifier but is not an enzyme. It enables a more rapid enzymatic breakdown of lipids by increasing surface area of fats (by creating smaller droplets).
Microscopic Investigation of the small intestines
Look at a slide of columnar cells in the small intestine.
https://www.dartmouth.edu/
1. The brush border at the distal end of the columnar cells form the upper layer of the microvilli.
How do you think microvilli increase absorption?
2. You will notice some of the columnar cells contain small globules of mucus. These cells are called goblet cells. Why do you have cells in your intestines that produce mucus? (Isn’t mucus just for your nose?)
3. How many feet are in 6 meters? How many feet in 1.5 meters? Why are these numbers important in this lab? (For reference, the Chevy Suburban SUV is 5.7 m long)
Effect of Bile on Fats
Materials for at home demonstration:
2 clear drinking glasses
Dry mustard (see *Note below)
Spoon
Timer
Safety goggles recommended for all at-home experimentation/demonstration
Water
Into each glass, place both 3 Tablespoons of vegetable oil and 3 Tablespoons of distilled water (both glasses should have oil and water). You will be using dry mustard to substitute for bile. Add a pinch of dry mustard into glass B2. Stir the mixtures for one minute. Carefully observe both glasses for 3–4 minutes. Record your results in Table -2.
2-2 Effect of bile salts on fat dispersal.
Tube | Observation (size of fat droplets) |
B1 (oil + water) | |
B2 (oil + water + dry mustard) |
The mustard acts an emulsifier. Dispersing the fat globules of the oil into smaller droplets.
Spices like dry mustard are used in the preparation of salad oil are used not only for taste, but to break the oil into smaller particles, thus keeping the salad dressing as a suspension. Bile, emulsifies fat in the small intestines, dispersing the fat into smaller droplets thereby creating more surface area for lipases to work, which speeds up the digestion of fats.
*Note: If you do not have dry mustard, you can use an eighth of a teaspoon of regular liquid yellow mustard
Part 3: Stomachache
We have all experienced the gastrointestinal discomfort and sometimes pain caused by a bout of diarrhea. Make an educated guess regarding the source of the pain. Observe a diagram of the human digestive system. (Refer to your copy of the textbook Campbell: Biology in Focus 2nd edition it is located on page 693). Although we commonly refer to such pain as a stomachache, where is the likely emanating? Explain your reasoning.
NUTRITION AND HEALTH –Â How healthy are you?
Obesity increases risk of diabetes, high blood pressure, heart disease, stroke, arthritis, and even some cancers. Chronic conditions such as diabetes and high blood pressure make COVID-19 outcomes worse, resulting in either a longer disease course or in ~3% of all COVID patients, death.
Activity 1: Personal Assessment
There are 2 ways to see if your weight is in a healthy range.
1) One method is to measure your waist circumference. It is best if women have a waist size under 35 inches and men under 40 inches. Find a tape measure and measure your waist. _________
2) The body/mas index (BMI) is another way to see if your weight is in a good range. The National Institutes of Health (NIH) has a quick BMI check here:Â https://www.nhlbi.nih.gov/health/educational/lose_wt/BMI/bmicalc.htm
Your BMI _____________
Activity 2: You are a health care worker
You are the nurse or physician for these patients. Determine the BMI and circle the correct weight range:
Patient 1 Male, 5’10” 200 pounds
BMI: ________
Underweight/Normal/Overweight/Obese
Patient 2 Female, 5’ 5” 110 pounds
BMI: _________
Underweight/Normal/Overweight/Obese
When can BMI be misleading? It does not allow for variations in muscle mass, gender, or body (ie., skeletal) frame.
Patient 3 Male college football player in top physical shape: 6’ 2” 245 pounds (average weight of NFL player)
BMI: _________
Underweight/Normal/Overweight/Obese
Is the B.M. Index representative in this case? __________________
Activity 3: Fat versus Muscle
Fat characteristics: less dense, less metabolically active
Muscle characteristics: more dense, more metabolically active
Will it be easier to maintain a target weight if your body contains more muscle or more fat in the body?
Why?
Fat tissue (like all tissue) is composed of living cells that require energy to function. Explain how this fact might impact an obese person’s efforts to lose weight?
Activity 4: Calculating Calories needed to survive
The calorie is a measure of the amount of energy contained in a substance. (To observe the chemical energy in a food being transformed into visible heat energy, look for videos online entitled “burning a sugar cube”). Hence, all foods have caloric value, even “junk” foods. To determine the number of calories in a given food, they are actually calories are measured by burning the food in a special container and measuring the heat released. (You may have used a device similar to this in chemistry class, a bomb calorimeter.)
How many calories do you need each day? It depends on your gender, age, and activities.
Here are approximate examples of Calories burned/hour for a 150-pound person:
Sleeping | 40 Calories per hour |
Sitting | 80 Calories per hour |
Standing | 120 Calories per hour |
Walking | 280 Calories per hour |
Swimming or dancing | 450 Calories per hour |
Running | 600 Calories per hour |
1. Calculate daily calories needed for a 150-pound person if s/he sleeps 8 hours, sits 10 hours, stands 3 hours, walks 2 hours, and dances 1 hour in each 24-hour period: _________________________
2. Calculate calories for your own typical day:
Activity | Time | Calories |
3. What are obstacles to encouraging people to engage in more physical activity? What could be done to encourage more exercise in busy daily schedules?
Activity 5: What kind of Calories are best?
A healthy diet can make a big difference in overall fitness. According to the CDC (Centers for Disease Control), “poor nutrition contributes to many costly diseases, including obesity, heart disease, and some cancers.” (www.cdc.gov/nutrition)
Here are some recommended numbers, from U.S. Guidelines (https://health.gov/our-work/food-nutrition/2015-2020-dietary-guidelines/guidelines/appendix-7/ ), for a normal diet of 2200 calories per day:
10-30% protein, or 110 grams (20% of 2200 = 440 calories divided by 4 = 110 grams*)
25-35% fat, or 73 grams (30% of 2200 = 660 calories divided by 9 = 73 grams**)
45-65% carbohydrates, or 275 grams (50% of 2200 = 1100 calories divided by 4 = 275 grams*)
*There are 4 Calories per gram of protein or carbohydrate.
**There are 9 Calories per gram of fat.
You can check calories and grams on food packaging, or by looking online
Food examples
1. We likely consume large quantities of sugar in our diets. For example, a can of Coke contains 42 grams of sugar.
a. Which is true about this amount of sugar as related to the daily requirement for carbohydrates?
It is 5%
It is 15%
It is 25%
b. The American Heart Association recommends no more than about 30 grams of simple sugar a day.
(https://www.heart.org/en/healthy-living/healthy-eating/eat-smart/sugar/added-sugars)
c. Is this simple sugar the best carbohydrate to consume? Why or why not? __________________________________________________________
d. List food sources of complex carbohydrates: _________________________________
_________________________________
_________________________________
_________________________________
2. Many of us may also consume too much fat. For example, a small serving of French fries at a fast food restaurant has about 230 calories and contains 44% fat (11 grams).
Here are some other sources of fats. Which do you eat?
Nuts and seeds Fatty fish Avocado
Cheese Eggs Butter and oils
3. What about salt? The sodium in NaCl can cause problems such as hypertension in some individuals (this will be discussed when studying the circulatory system). Processed foods are typically high in salt.
If you want simple help with your diet, check out MyPlate at https://www.choosemyplate.gov
Review Questions:Â (to be completed and submitted)
1. Define the term “substrate”:
2. Explain the role of enzymes in chemical digestion:
3. a) Test your knowledge of naming enzymes. What is the enzyme that works on the following substrates?
Substrate: lipids. Enzyme: _______________________
Substrate: peptide. Enzyme: _______________________
Substrate: lactose. Enzyme: _______________________
b) You may have heard of a friend or family member being “lactose intolerant”. This is caused by the individual lacking an enzyme to digest the sugar most commonly found in milk. What is your guess of the name of this enzyme? Doing some independent online research, what happens when a person is incapable of digesting this milk sugar, lactose? Why do you think that evolutionary change has removed the ability of grown adults to digest milk sugars?
4. a) Bile is made in the liver, stored in the gallbladder and is secreted into the small intestines to assist with digestion of fats through its action as an emulsifier. What is an “emulsifier”?
b) What is the difference between and emulsifier and an enzyme?
c) Performing online research, what are everyday examples of emulsified items that you eat/drink?
5. Explain the function of bile:
6. How do you think microvilli work to increase absorption?
7. What cells in your intestines produce mucus?
8. For each of the following substrates, state the locations of its chemical digestion occurs in the body, which type of enzyme is involved, and what units these molecules are broken down into.
Organic Molecule | Location of digestion | Enzyme involved | Monomer units |
Carbohydrate | |||
Fats (triglyceride) | |||
Proteins |
9. The Body Mass Index uses __________ and _____________ to see if a person’s weight is healthy.
10. Fill in the chart below.
Type of Tissue | More or less dense | More or less metabolic activity |
Fat | ||
Muscle |
11. Number these activities in order of activity, starting with the highest (you may rank activities as tied for equal activity, denoting them as, for example, “2-tied”):
___ walking ___ sitting ___ running ___ riding a bicycle
___ sleeping ___ swimming ___ standing ___ using a skateboard
Why did you rank a specific activity as highest, and why?
12. Most processed foods have too much (check all that apply)
___ fresh fruit ___ salt ___ fresh vegetables
___ water ___ whole grains ___ fat
___ simple sugar ___ protein ___ leafy vegetables
1
Bio 102 Lab 03: Chemical Digestion and Nutrition
How to Proceed
Transcription and Translation – Introduction
Be sure that you have read over our online lecture this unit on DNA and read pp 177 to 181 in your book before starting. DNA can be a complex concept to grasp, and there is a lot of terminology to keep straight. These first two exercises will focus on transcription and translation, the two processes responsible for taking the information embedded in our DNA and using it to create a protein.
There are segments in our DNA called genes that code for the proteins needed to carry out cellular functions. These genes are a sequence of nucleotides; adenine (A), thymine (T), cytosine (C) and guanine (G) and the specific sequence of these nucleotides is what conveys the information needed to produce a given protein. In humans, the smallest gene is 252 nucleotides long, whereas the largest is more than 2 million nucleotides long! The genetic code is used to decipher the sequence of nucleotides into a sequence of amino acids. The code uses a series of three-nucleotide sequences called codons. Each different codon codes for an amino acid and it is this specific sequence of amino acids that determines what protein is formed.
DNA is found in our nucleus, yet our proteins are synthesized in the cytoplasm. A gene must first be transcribed into a form that can leave the nucleus. Transcription is the process in which a sequence of DNA used to synthesize a complementary strand of messenger RNA (mRNA). This mRNA acts a template and is used to translate the original DNA sequence into a protein, based on the information in its codons and the Genetic Code.
For example, the DNA sequence ATG-CGT-TAG-CGT-ATTC would be transcribed into the mRNA sequence UAC-GCA-AUC-GCA-UAA. Then, using Fig 10.11 on p 180 in your book, you can determine that this mRNA would be translated into the amino acid sequence Tyrosine-Alanine-Isoleucine-Alanine-Stop.
In Exercise 1, you will have the opportunity to demonstrate your understanding of transcription and translation. You will be using the following website; be sure that you are able to access and use the site:
University of Utah. No date. Transcription and Translation
https://learn.genetics.utah.edu/content/basics/transcribe/Â (Links to an external site.)
In Exercise 2, you will apply what you learned in Exercise 1 and evaluate the effect that different types of mutations have on the outcome of transcription and translation. You’ll want to review these mutations on pp 186-187 of your book and in our online lecture on DNA before starting. You will be using the following website; be sure that you are able to access and use the site:
McGraw Hill. No date. Virtual Lab: DNA and Genes
http://glencoe.mheducation.com/sites/dl/free/0078802849/383936/BL_26.html (Links to an external site.)
Finally, in Exercise 3, you will complete a series of calculations to determine the probability of a mutation occurring within a gene that results in a change in protein structure. These are straight-forward math calculations; do not let them overwhelm you.
UNIT 5 EXPERIMENT ANSWER SHEETÂ Please submit to the UNIT 5 Experiment SUBMISSION LINK no later than Sunday midnight.
SUMMARY OF ACTIVITIES FOR UNIT 1 EXPERIMENT ASSIGNMENT
· Experiment 5 Exercise 1 – Transcription and Translation
· Experiment 5 Exercise 2 – Translation and Mutations
· Experiment 5 Exercise 3 – Mutation Rates
Experiment 5 Exercise 1: Transcription and Translation
This exercise will ensure that you have a good understanding of the processes of transcription and translation. To get started, go to the following website:
University of Utah. No date. Transcription and Translation
http://learn.genetics.utah.edu/content/molecules/transcribe/
Procedure
A. Read over the information on the first screen and click on the click here to begin to proceed.
B. On the next screen transcribe the give DNA strand.
Table 1. Transcription of the DNA sequence (1.5 pts).
RNA |
C. Once you have finished transcribing the DNA, you will then translate the RNA sequence. Follow the instructions on the screen.
Table 2. Translation (1.5 pts)
Codon | Amino Acid | |
Codon 1 | ||
Codon 2 | ||
Codon 3 | ||
Codon 4 | ||
Codon 5 | ||
Codon 6 |
Experiment 5 Exercise 2: Translation and Mutations
Now that you know how to transcribe DNA and translate the mRNA message, let’s take a look at the different types of mutations that might disrupt this process. Review pp 186-187 in your book before beginning. In this exercise you will need to use the following website:
McGraw Hill. No date. Virtual Lab: DNA and Genes http://www.glencoe.com/sites/common_assets/advanced_placement/mader10e/virtual_labs_2K8/labs/BL_04/index.html
Read over the information in the Mutation Guide and close it when you are done. Note that there are several pages; you will need to click on Next to proceed through the Guide. If you want to review this material, you can click on the Mutation Guide button. You are going to run a series of simulations in which an mRNA sequence and its corresponding amino acid sequence is provided. You will be told what type of mutation you will you apply (= Mutation Rule) and you will have to determine the new, mutated mRNA and the resulting protein sequence.
Procedure
A. Click on the Mutate button to get started.
B. Find the Mutation Rule (lower left corner) and enter it into Table 3 below (see the Example provided).
C. Drag the appropriate nucleotides to build the new, Mutated mRNA sequence. If you make a mistake building the new mRNA sequence, drag the correct nucleotide and place it on top of the incorrect one (you cannot actually remove a nucleotide).
D. Once you have generated your Mutated mRNA sequence, you now need to build your Mutated amino acid sequence by matching the appropriate amino acid with each codon. Click on Genetic Code Chart to see the code or you can use Figure 10.11 on p 160 in your book.
NOTE: If you add a STOP codon, do NOT add any more amino acids after it!
E. Once you have finished, click on the Check button. If you are correct, then continue with Step F. If you had errors, you will have to Reset the simulation and start over with Step A. Here is what the results look like for the example provided:
F. When you have been successful, enter the Original mRNA sequence and the Original amino acid sequence in the Table below. Then enter the Mutated mRNA and Mutated protein sequence.
G. Click on Reset and repeat Steps A through F four more times so that you end up with FIVE replicates. Do not reuse the same Mutation Rule and do not use the rule used in the example (“the 4th A becomes a C”). If you get the same Mutation rule twice, Reset the simulation and run again.
Do NOT use the same Mutation rule as shown in the example and do NOT use the same Mutation Rule twice!
Table 3. Mutation rules, mRNA sequences and amino acid sequences (10 pts).
Rep | Mutation Rule and Sequences | |
E
X A M P L E |
Mutation rule: The 4th A becomes a C | |
Original mRNA sequence | AUG CAC ACG GUG CGA GGG AGU CUG | |
Original amino acid sequence | Met (Start) – His – Thr – Val – Arg – Gly – Ser – Leu | |
Mutated mRNA sequence | AUG CAC ACG GUG CGCÂ GGG AGU CUG | |
Mutated amino acid sequence | Met (Start) – His – Thr – Val – Arg – Gly – Ser – Leu | |
Consequence | Substitution appears to have had no effect; Arg Arg | |
1 | Mutation rule: | |
Original mRNA sequence | ||
Original amino acid sequence | ||
Mutated mRNA sequence | ||
Mutated amino acid sequence | ||
Consequence | ||
2 | Mutation rule: | |
Original mRNA sequence | ||
Original amino acid sequence | ||
Mutated mRNA sequence | ||
Mutated amino acid sequence | ||
Consequence | ||
3 | Mutation rule: | |
Original mRNA sequence | ||
Original amino acid sequence | ||
Mutated mRNA sequence | ||
Mutated amino acid sequence | ||
Consequence | ||
4 | Mutation rule: | |
Original mRNA sequence | ||
Original amino acid sequence | ||
Mutated mRNA sequence | ||
Mutated amino acid sequence | ||
Consequence | ||
5 | Mutation rule: | |
Original mRNA sequence | ||
Original amino acid sequence | ||
Mutated mRNA sequence | ||
Mutated amino acid sequence | ||
Consequence |
Questions
1. What is a silent mutation? Did you see any examples of this in your mutations above? If so, which mutation rule(s) generated it? Cite your sources (2 pts).
2. What is a missense mutation and how does it differ from a nonsense mutation? Did you see examples of either of these types of mutation and if so, which mutation rule(s) generated it? Cite your sources (2 pts).
3. What is a frame-shift mutation and why are they so damaging? Did you see any examples of this in your mutations above? If so, which mutation rule(s) generated it? Cite your sources (2 pts).
4. Find a genetic disorder that develops as a result of one of the types of genetic mutations we have examined in this exercise. Identify the disorder and briefly describe the mutation responsible. Cite your sources (3 pts).
Experiment 5 Exercise 3: Mutation Rates
We learned in our second exercise that not all mutations have an observable effect. Yet the risk of a mutation being damaging is fairly significant, so it is important to understand the probability of them occurring. In this exercise, we are going to calculate the probability of a mutational event within a gene. You are given the necessary information below to complete the calculations. Do not let them overwhelm you; this is simple math, so think things through.
Assume that:
· there are approximately 3,000,000,000 base pairs in the mammalian genome (genes constitute only a small portion of this total)
· there are approximately 10,000 genes in the mammalian genome
· a single gene averages about 10,000 base pairs in size
Questions
1. Based on the assumptions above, in the mammalian genome, how many total base pairs are in all the mammalian genes? Show your math (2 pts).
2. What percentage (%) of the total genome does this represent? Show your math (2 pts).
3. What is the chance (%) that a random mutation will occur in any given gene? Show your math (2 pts).
4. Only 1 out of 3 mutations that occur in a gene result in a change to the protein structure. What is the probability that a random mutation will change the structure of a protein? Show your math (2 pts).
UNIT 1 Experiment Grading Rubric
Component | Expectation | Points |
Experiment 5 Exercise 1 | Demonstrates an understanding of the process of transcription and translation (Table 1 and 2). | 3 pts |
Experiment 5 Exercise 2 | Correctly implements the proper mutation and transcribes the mRNA correctly (Table 3). | 10 pts |
Demonstrates an understanding of the different types of mutations and their consequences (Questions 1-4). | 9 pts | |
Experiment 5 Exercise 3 | Correctly calculates the necessary information (Questions 1-4). | 8 pts |
TOTAL | 30 pts |
Updated April 2015
A Skills Approach: Excel 2016 Chapter 3: Using Formulas and Functions
1 | Page Skill Review 3.2 Last Updated 3/19/18
Skill Review 3.2 In this project you will edit a worksheet to compute student grades and grade statistics. Be sure to save your work often!
Skills needed to complete this project: • Using Date and Time Functions
• Using CONCAT to Combine Text
• Formatting Text Using Functions
• Creating Formulas Using Counting Functions
• Using Formula AutoComplete to Enter Functions
• Displaying and Printing Formulas
• Naming Ranges of Cells
• Using the Logical Function IF
• Using the Function Arguments Dialog to Enter Functions
• Working with Named Ranges
• Finding Minimum and Maximum Values
• Calculating Averages
• Finding Data Using the VLOOKUP Function
• Checking Formulas for Errors
1. Open the start file EX2016-SkillReview-3-2. The file will be renamed automatically to include your
name. Change the project file name if directed to do so by your instructor, and save it. Click the Enable
Editing button in the Message Bar at the top of the workbook so you can modify it.
2. Take a look at the two sheets. The first sheet contains the students’ names and their scores. The second
sheet will be used to look up the letter grade for each student.
3. On the Scores worksheet, enter a function in cell B3 to display the current date and time.
a. If necessary, click the Scores worksheet tab. Click cell B3.
b. On the Formulas tab, in the Function Library group, click the Date &Time button.
c. Click NOW.
d. Click OK.
4. The first column should display the full student name. Use CONCAT to combine the values from the
First Name and Last Name columns.
a. Click cell A10.
b. On the Formulas tab, in the Function Library group, click the Text button, and select CONCAT.
IMPORTANT: If you do not see CONCAT in the list, use CONCATENATE function instead.
c. Click cell C10 to enter the cell reference in the Text1 argument box.
d. Press TAB to move to the Text2 argument box.
e. Type ” ” to place a space between the first and last names.
f. Press TAB to move to the Text3 argument box.
Step 1 Download start file
A Skills Approach: Excel 2016 Chapter 3: Using Formulas and Functions
2 | Page Skill Review 3.2 Last Updated 3/19/18
g. Click cell B10 to enter the text reference in the Text3 argument box.
h. Click OK. The completed formula should look like this: =CONCAT(C10,” “,B10)
5. Add the PROPER function to the formula so student names do not appear in all uppercase.
a. Double-click cell A10 to edit the formula.
b. Create a nested formula by typing PROPER( between the = symbol and CONCAT.
c. Type another ) at the end of the formula.
d. Press ENTER. The completed formula should look like this:
=PROPER(CONCAT(C10,” “,B10))
e. Copy the formula from cell A10 to A11:A26 to fill the list of student names. Use any method you
want.
6. Count the number of students to calculate the class size.
a. Click cell B2.
b. Type =COU
c. Double-click COUNTA in the Formula AutoComplete list.
d. Click cell A10 and drag to cell A26.
e. Press ENTER. The completed formula should look like this: =COUNTA(A10:A26)
7. Display your formulas to check for accuracy.
a. On the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
b. When you are ready to continue, hide the formulas and display formula values by clicking the Show
Formulas button again.
8. Define a named range for the total possible points up to the class drop cut-off point.
a. Select cells D7:R7.
b. In the Name box, type: PossiblePtsMid
c. Press ENTER.
9. Find out which students have a grade below C at the cut-off point for dropping the class. Enter an IF
function in cell S10 to check if the student’s total points divided by the total possible points through the
midterm is less than 70% (the lowest percentage for a C grade). Use SUM functions within the IF
function. Be sure to use the range name you just defined for possible points. If the student is below a C
grade, display Warning! in the cell; otherwise leave the cell blank.
a. Click cell S10.
b. On the Formulas tab, in the Function Library group, click Logical.
c. Click IF.
d. If necessary, move the Function Arguments dialog so you can see the worksheet data.
e. In the Logical_test argument box, type: SUM(D10:R10)/SUM(PossiblePtsMid)<70%
f. In the Value_if_true argument box, type: Warning!
g. In the Value_if_false argument box, type: “”
A Skills Approach: Excel 2016 Chapter 3: Using Formulas and Functions
3 | Page Skill Review 3.2 Last Updated 3/19/18
h. Click OK. The completed formula should look like this:
=IF(SUM(D10:R10)/SUM(PossiblePtsMid)<70%,”Warning!”,” “)
i. Fill the IF function in cell S10 down for all students. Use any method you want. There should be
three students with Warning! in the Class Drop Cut-Off Point column.
10. Find the highest score for each assignment.
a. Click cell D4.
b. Type =MAX( and then click cell D10 and drag to cell D26.
c. Press ENTER. The completed formula should look like this: =MAX(D10:D26)
d. Copy the formula across the row to cell AB4. Use any method you want. Be sure to leave cell S4
blank.
11. Find the lowest score for each assignment.
a. Click cell D5.
b. Type =MIN( and then click cell D10 and drag to cell D26.
c. Press ENTER. The completed formula should look like this: =MIN(D10:D26)
d. Copy the formula across the row to cell AB5. Use any method you want. Be sure to leave cell S5
blank.
12. Calculate the average score for each assignment.
a. Click cell D6.
b. Type =AV and then double-click AVERAGE in the Formula AutoComplete list.
c. Click cell D10 and drag to cell D26.
d. Press ENTER. The completed formula should look like this: =AVERAGE(D10:D26)
e. Copy the formula across the row to cell AB6. Use any method you want. Be sure to leave cell S6
blank.
13. Compute the students’ total points. Enter a SUM function in cell AC10 to add all the points across for
the first student.
a. Click cell AC10.
b. Type =SU and then double-click SUM in the Formula AutoComplete list.
c. Click cell D10 and drag to cell AB10.
d. Press ENTER. The completed formula should look like this: =SUM(D10:AB10)
e. Copy the formula from AC10 through cell AC26. Use any method you want.
14. Enter a formula to compute the percentage for the first student. Divide the student’s total points by the
total possible points. You will be copying this formula, so make sure the reference to the total possible
points uses an absolute reference.
a. In cell AD10, enter the following formula to calculate the percentage: =AC10/$AC$7
b. Copy the formula from AD10 through AD26. Use any method you want.
A Skills Approach: Excel 2016 Chapter 3: Using Formulas and Functions
4 | Page Skill Review 3.2 Last Updated 3/19/18
15. The grade scale is stored in the Grades worksheet. Before calculating students’ final grades, create a named
range to use in the formula.
a. Click the Grades sheet tab.
b. Select cells B4:C8.
c. Type GradeScale in the Name box.
d. Press ENTER.
16. Now you are ready to create a lookup formula to display each student’s final letter grade.
a. Return to the Scores sheet, and click cell AE10.
b. On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select
VLOOKUP.
c. Click cell AD10 to enter it in the Lookup_value argument box.
d. Type GradeScale in the Table_array argument box.
e. The rates are located in the second column of the lookup table. Type 2 in the Col_index_num
argument box.
f. In this case, you do not want to specify an exact match, as the percentage grades do not match the
grade scale percentages exactly. An approximate match will return the correct letter grade.
g. Click OK. The completed formula should look like this: =VLOOKUP(AD10,GradeScale,2)
h. Fill down for all students. Use any method you want.
17. Before closing the project, check your workbook for errors.
a. On the Formulas tab, in the Formula Auditing group, click the Error Checking button.
b. If errors are found, use the error checking skills learned in this chapter to find and fix the errors.
18. When Excel displays a message that the error check is complete, click OK.
19. Save and close the workbook.
20. Upload and save your project file.
21. Submit project for grading.
Step 2
Upload & Save
Step 3 Grade my Project
Hi there! Click one of our representatives below and we will get back to you as soon as possible.