Nursing

Unit 3 Assignment 2 Example

In this example, I work through Unit 3 Assignment 2 by hand. You need to implement this in Excel, but hopefully this example will clearly illustrate what needs to be done.

Remember to use formulas instead of working out the calculations yourself and entering the results directly into the spreadsheet. Use cell references when possible to avoid having to reenter values more than once in your spreadsheet. Following these principles will make your spreadsheet reusable. If you want to use the spreadsheet for another situation, you will not have to change much, if anything, other than the data.

Set Up

The first thing you need to do in this assignment is to choose three categories of assignments. As an example, I will pick the categories speeches, group projects, and essays.

Next, pick a weight (which is a percentage) for each category. It does not matter what you pick, but the weights should all be positive and add up to 100%. I will choose 50% for speeches, 20% for group projects, and 30% for essays.

Next, pick at least three grades for every category. Make the grades numbers from 0 to 100. In this example, I will pick three speeches, four group projects, and five essays and assign the grades as in the following table:

	Speeches		Group Projects		Essays
	50%		20%		30%
Speech 1	76	Group Project 1	33	Essay 1	82
Speech 2	88	Group Project 2	77	Essay 2	23
Speech 3	92	Group Project 3	87	Essay 3	69
		Group Project 4	94	Essay 4	100
				Essay 5	42

Do not use the categories, weights, and grades that I pick. Pick your own.

Steps to Calculate the Grade

1. Take the average of the grades in each category. In your spreadsheet, you should use the AVERAGE function to do this or use SUM and maybe COUNTIF.

Average of speech grades: 85.33

Average of group projects grades: 72.75

Average of essay grades: 63.2

2. Multiply the average of the category by the category weight. Notice that I have converted the percentages to decimals in order to multiply (that is 50% = 0.5, 20% = 0.2, 30% = 0.3)

Speech Average * Speech Weight = 85.33 * 0.5 = 42.67 Group Project Average * Group Project Weight = 72.75 * 0.2 = 14.55 Essay Average * Essay Average Weight = 63.2 * 0.3 = 18.96

3. Finally, add up the values from Part 2 to get the final grade.

Final Grade = 42.67 + 14.55 + 18.96 = 76.18

One way to set up the final results in your spreadsheet is indicated in the table below.

	Speeches		Group Projects		Essays
	50%		20%		30%
Speech 1	76	Group Project 1	33	Essay 1	82
Speech 2	88	Group Project 2	77	Essay 2	23
Speech 3	92	Group Project 3	87	Essay 3	69
		Group Project 4	94	Essay 4	100
				Essay 5	42
Average	85.33		72.75		63.2		Final Grade
Weighted Grade	42.67		14.55		18.96		76.18

In the blue colored cells, enter the weight as a decimal number (0.5, 0.2, and 0.3 in my example and then change the format to display as a percentage.

In the orange colored cells, use the AVERAGE function and cell references (not actual values) to average the grades in each column.

In the green colored cells, use cell references to the averages (orange cell) and weights (blue cell) in the column. Do not just type in the values themselves or work this out and type in the actual number. Make Excel do all of the hard work for you.

Use the SUM function and cell references to add up all the weighted grades to give the final grade.