Excel Homework (Computer Class)

Instructions

General Notes:
1	Be sure to review the class materials first. No one should be frustrated!
2	If you need additional training even after watching what's in D2L, please see Supplemental Excel instruction below.
3	Be sure you can see all 6 tabs below. Use the arrows to move left to right.
4	Tabs are: Instructions, Using Functions, Cell Formatting, Graphs, Absolute and Relative, Puppy Palace
5	Any questions, email your instructor and be specific with your questions.
Instructions for the "Using Functions" sheet:
1	Review and understand the example. (Note: Always start a function with an = as the first character.)
2	Enter a column of 5 numbers, in cells B27 to B31 (Pick any numbers you wish.)
3	In cell B33, enter a formula to add the individual 5 cells one at a time.
4	In cell B34, enter a formula to calculate the SUM using the SUM function of cell range B27 through B31
5	In cell B35, enter a formula to calculate the average, but adding the 5 cells and dividing by 5.
6	In cell B36, enter a formula to calculate the AVERAGE using the AVERAGE function of cell range B27 through B31
7	Copy cells, B27 to B36 and paste them to B38 to B47.
8	Insert a row, between B39 and B40 and enter a new value in the cell.
9	Note how one sum value and one average value is now incorrect but the other is updated correctly.
10	Review the other functions in the example, in column J, such as MIN, MAX, COUNT and TODAY
	MIN is the minimum value, MAX is the maximum value, COUNT is the number of items in the list and TODAY returns today's date.
11	In cells B50 through B52, enter the functions MIN, MAX, and COUNT using the cell range of your new list.
12	In cell B53, enter the function TODAY to show today's date.
13	In cell B54, enter another Excel function that you choose. You can press the Fx at the top of your spreadsheet to see what's available.
Note:	If you see #### in a cell, your column is not wide enough. Be sure to make it larger before turning in your assignment.
Instructions for the "Cell Formatting" sheet:
1	Copy cell B10 to L10 then make it BOLD
2	Copy cell B11 to L11 then make it ITALIC
3	Copy cell B12 to L12 then make it UNDERLINED
4	Copy cell B13 to L13 then make the text BLUE
5	Copy cell B14 to L14 then make the background of the cell GREEN
6	Copy cell B15 to L15 then make the font 14 points
7	Copy cell B16 to L16 then choose the font Times New Roman
8	Type 12.34 in cell L17 and -45.56 in M17 then make for format Currency with negative values in RED
9	Type 123.4567 in cell L18 then reduce the number of decimals showing to 2
10	Type 5 in cell L19 and 90 in cell M19. Using conditional formatting on both
	have values greater than 10 appear with light red fill and dark red text.
Instructions for the "Graphs" sheet:
1	Create a table, like the one on the Graphs sheet but with different items,
	with 3 rows and 3 columns. Put your table in cells A26 through D29.
2	Be sure to label the columns and rows and fill in the table with data.
3	Create three charts, like the examples on the Graphs sheet.
	Be careful that the graphs are correct, including titles.
Instructions for the "Absolute and Relative" sheet:
1	Copy the table in cells A21:D26, into cells A34:D39.
2	Examine the formulas then copy cells F10:H10 to F35:H35
3	Update the tax amount, in H35 to 5%
4	Update cells C35:C39 to point at the new value using the absolute cell reference pointing to H35.
Instructions for the "Puppy Palace" sheet":
	This sheet contains a number of errors.
	The error might be that a formula is wrong, a word
	is spelled incorrectly, the chart is not pointing at
	the right data or something else.
	You need to find and correct at least 5 errors by making the changes in the Kitten Kingdom spreadsheet.
	List the errors you've corrected in cells B61:B65
Supplemental Excel Instruction:
	Up and Running with Excel 2010
	Office 365 - Up and Running with Excel

http://www.lynda.com/Excel-tutorials/Up-Running-Excel-2010/123569-2.html?autoplay=true http://www.lynda.com/Excel-tutorials/Office-365-Up-Running-Excel/420300-2.html?srchtrk=index:1%0Alinktypeid:2%0Aq:excel%0Apage:1%0As:relevance%0Asa:true%0Aproducttypeid:2

Using Functions

Example:	1							Other functions:
	2							1	MIN	minimum value in the list
	3							5	MAX	maximum value in the list
	4							5	COUNT	count of items in the list
	5							6/8/16	TODAY	current date
	15	<== calculate the sum by adding individual cells
	15	<== calculate the sum by using the sum function and a cell range
	3	<== calculate the average by adding individual cells and dividing by the number of cells
	3	<== calculate the average by using the average function and a cell range
		Now, what happens if copy the list then insert a row? (Inserted row 18, and put a 9 in the cell.)
		Your sum and average functions includes the new row automatically, but the calculations using individual cells don't.
		That's why it's always better to use functions and cell ranges rather than individual values.
	1
	2
	3
	9
	4
	5
	15	<== calculate the sum by adding individual cells
	24	<== calculate the sum by using the sum function and a cell range
	3	<== calculate the average by adding individual cells and dividing by the number of cells
	4	<== calculate the average by using the average function and a cell range
Instructions & Work Area for the "Using Functions" sheet:
			1	Review and understand the example. (Note: Always start a function with an = as the first character.)
			2	Enter a column of 5 numbers, in cells B27 to B31 (Pick any numbers you wish.)
			3	In cell B33, enter a formula to add the individual 5 cells one at a time.
			4	In cell B34, enter a formula to calculate the SUM using the SUM function of cell range B27 through B31
			5	In cell B35, enter a formula to calculate the average, but adding the 5 cells and dividing by 5.
			6	In cell B36, enter a formula to calculate the AVERAGE using the AVERAGE function of cell range A27 through A31
			7	Copy cells, B27 to B36 and paste them to B38 to B47.
			8	Insert a row, between B39 and B40 and enter a new value in the cell.
			9	Note how one sum value and one average value is now incorrect but the other is updated correctly.
			10	Review the other functions in the example, in column J, such as MIN, MAX, COUNT and TODAY
				MIN is the minimum value, MAX is the maximum value, COUNT is the number of items in the list and TODAY returns today's date.
			11	In cells B50 through B52, enter the functions MIN, MAX, and COUNT using the cell range of your new list.
			12	In cell B53, enter the function TODAY to show today's date.
			13	In cell B54, enter another Excel function that you choose. You can press the Fx at the top of your spreadsheet to see what's available.
			Note:	If you see #### in a cell, your column is not wide enough. Be sure to make it larger before turning in your assignment.

Cell Formatting

Cell Formatting:
Cell formatting describes the way the items in your spreadsheet appear and how they are treated by Excel.
For example, numbers can have 1, 2, 3 or more decimals, or can be dollars and cents, or dates.
Text can be bold, underlined, different colors and different sizes.
You can also apply a style to a table which means Excel makes the choices for you.
To apply different styles, colors, etc., you'll need to be on the HOME TAB
then highlight the cell (or cell range) and make the choice you need to to get the formatting you want.
Instructions for the "Cell Formatting" sheet:											Work Area for the "Cell Formatting" sheet:
1	Copy cell B10 to L10 then make it BOLD
2	Copy cell B11 to L11 then make it ITALIC
3	Copy cell B12 to L12 then make it UNDERLINED
4	Copy cell B13 to L13 then make the text BLUE
5	Copy cell B14 to L14 then make the background of the cell GREEN
6	Copy cell B15 to L15 then make the font 14 points
7	Copy cell B16 to L16 then choose the font Times New Roman
8	Type 12.34 in cell L17 and -45.56 in M17 then make for format Currency with negative values in RED
9	Type 123.4567 in cell L18 then reduce the number of decimals showing to 2
10	Type 5 in cell L19 and 90 in cell M19. Using conditional formatting on both
	have values greater than 10 appear with light red fill and dark red text.

Graphs

Graphs Sheet:
Graphs are very useful in illustrating what data means.
The table below has been used to create
a pie chart for one item, a bar graph, and a line graph.
(Scroll to the right to see all the graphs.)
Puppies sold per month
	January	February	March
Bulldog	1	2	6
Poodle	3	1	3
Pug	1	4	1
Instructions for the Graphs Sheet:
1	Create a table, like the one above but with different items, with 3
	rows, and 3 columns. Put your table in cells A26 through D29.
2	Be sure to label the columns and rows and fill in the table with data.
3	Create three charts, like the example above.
	Be careful that the graphs are correct, including titles.
Work Area for the Graphs Sheet:

Bulldog January February March 1 2 6 Poodle January February March 3 1 3 Pug January February March 1 4 1

Puppies Sold

Bulldog January February March 1 2 6 Poodle January February March 3 1 3 Pug January February March 1 4 1

Puppies Sold

Bulldog January February March 1 2 6 Poodle January February March 3 1 3 Pug January February March 1 4 1

Absolute and Relative

Example for Absolute and Relative References
Relative and absolute cell references
Relative cell references are like C1, A10, D14, etc.
Absolute cell references are like $D1, E$11, $F$4
The difference is that when a cell containing an absolute cell reference is copied, the formula stays the same,
but for relative cell references, when you copy and paste, the cell row and/or column is updated.
	January	Tax	Total Cost
Shoes	$ 22.00	$ 2.20	$ 24.20		Tax on purchases is:		10%
Shorts	$ 33.00	$ 3.30	$ 36.30
Shirts	$ 25.00	$ 2.50	$ 27.50
Totals:	$ 80.00	$ 8.00	$ 88.00
Next, see what happens when the table above is copied into cells A21:D26. Examine the formulas.
You'll notice most formulas are updated with new row numbers but the reference to the 10% tax value
remains the same, as $H$10. That's why the tax amount is calculated correctly.
	January	Tax	Total Cost
Shoes	$ 22.00	$ 2.20	$ 24.20
Shorts	$ 33.00	$ 3.30	$ 36.30
Shirts	$ 25.00	$ 2.50	$ 27.50
Totals:	$ 80.00	$ 8.00	$ 88.00
Instructions for the Absolute and Relative Sheet
Now, copy the table in cells A21:D26, into cells A34:D39.
Examine the formulas then copy cells F10:H10 to F35:H35
Update the tax amount, in H35 to 5%
Update cells C35:C39 to point at the new value using the absolute cell reference pointing to H35.
Work Area for the Absolute and Relative Sheet

Puppy Palace

Instructions for Puppy Palace
The table and chart below, for Puppy Palace Monthly Sales, is correct. The second table and chart, for Kitten Kingdom, has at least 5 errors.
Please make at least 5 of the corrections needed, using the Puppy Palace Monthly Sales as your example. List the errors you've corrected in cells B61:B65.
Puppy Palace Monthly Sales
	January	February	March	April	May	June	July	Aug	Sep	Oct	Nov	Dec
Poodles	1	5	4	1	4	1	5	1	1	2	3	1
Great Danes	2	1	3	2	3	2	4	2	3	3	1	2
Pugs	3	2	2	2	2	1	3	3	3	3	2	3
Yorkies	4	3	1	3	1	2	2	1	3	1	1	2
Monthly Totals:	10	11	10	8	10	6	14	7	10	9	7	8
Yearly Total (January to December):		110						Monthly Average:		9
Kitten Kingdom Monthly Sales
	January	February	March	April	May	June	July	Aug	Sep	Oct	Nov	Nov
Siamese	11	12	11	1	3	3	11	12	1	3	3	3
Maine Coon	12	2	12	9	4	4	1	1	11	2	2	4
Rag Doll	14	11	13	8	3	2	11	11	3	1	3	1
Domestic Short Hair	13	11	14	7	2	2	4	2	13	2	3	2
Monthly Tootals:	11	36	50	25	5	11	27	26	28	8	11	3
Yearly Total (January to June):		165						Monthly Average:		14
Describe the errors corrected - list at least 5 errors:
	1
	2
	3
	4
	5

Puppy Palace Monthy Sales

Poodles January February March April May June July Aug Sep Oct Nov Dec 1 5 4 1 4 1 5 1 1 2 3 1 Great Danes January February March April May June July Aug Sep Oct Nov Dec 2 1 3 2 3 2 4 2 3 3 1 2 Pugs January February March April May June July Aug Sep Oct Nov Dec 3 2 2 2 2 1 3 3 3 3 2 3 Yorkies January February March April May June July Aug Sep Oct Nov Dec 4 3 1 3 1 2 2 1 3 1 1 2

Siamese January February March April May June July Aug Sep Oct Nov Nov 11 12 11 1 3 3 11 12 1 3 3 3 Maine Coon January February March April May June July Aug Sep Oct Nov Nov 12 2 12 9 4 4 1 1 11 2 2 4 Rag Doll January February March April May June July Aug Sep Oct Nov Nov 14 11 13 8 3 2 11 11 3 1 3 1