Excel

excel_homework_template_1.xlsx

Home >Computer Science homework help >Excel

Cell References

Relative Cell Referece:
Sale for September 2004
Region	Magazines	Books	Apparel
North	$1,200	$2,113	$1,250
South	$1,650	$2,450	$1,545
East	$2,341	$3,200	$1,623
West	$3,442	$3,800	$2,356
Total	$8,633	$11,563	?	Find the Sale Total for Apparel?
Absolute Cell Reference 2:
Prices after Discount
Product	Price	AfterDiscountA	AfterDiscountB
Polo Shirt	$55	$49.50	?	Find the Prices After DiscountB?
Jeans	$42	$37.80	?
Watch	$85	$76.50	?
Discount A	10%
Discount B	35%
Mixed Cell Refereces to calculate the 11X20 multiplication table:
11	12	13	14	15	16	17	18	19	20
12
13
14
15
16
17
18
19
20
Calculating Loan Payment			(assume monthly payment)
Amount borrowed			$20,000
Annual Interest Rate			8.75%
No of Payments per year			12
Total no of payments			60
Calculate amount due each payment			?????
Calculating Net Present Value
Data	Description
9%	Annual discount rate.
-50,000	Initial cost of investment
18,000	Return from first year
19,200	Return from second year
12,000	Return from third year
12,000	Return from fourth year
14,500	Return from fifth year
Formula	Description (Result)
?????	What is NPV of this investment?
?????	What is NPV if there is a 5000 loss in year 6?

Useful Functions

AND, OR Functions
Scores
88
79
99
	Write formula to check if A3>A4 AND A3<A5 is a true statement or not
	Write formula to check if A3>A4 OR A3<A5 is a true statement or not
IF Function
Syntax	IF(logical_test, [value_if_true], [value_if_false])
Scores
88
79
99
	Write IF function to check score in cell A13 is higher than that of cell A14. If so, the formula should say YES; otherwise NO
The following example provides Employee sales report. Enter formula in cell C22 to check if the employee reached the target.
Use the same formula all the way down. If the target is reached the formula should say YES; otherwise NO
Target	$28,000
Employee	Sales	Target Reached?
A	32000	??
B	25000	??
C	12250	??
D	36500	??
E	31520	??
F	29000	??
G	32500	??
H	33200	??
I	23000	??
Nested IF function
Syntax	IF(logical_test,[value_if_true],if(logical_test,[value_if_true],[value_if_false]))
Scores	Grade			If score is	Return
57		Enter IF formula to create letter grade		> 90	A
99				81-90	B
79				71-80	C
65				61-70	D
54				<61	F
Tickets for Pilots basketball games can be purchased according to the following schedule:
Price/ticket for first 10 tickets			$10
Price/ticket for next 10 tickets (11-20)			$8
Price/ticket for tickets > 20			$6
Compute ticket prices for following tickets bought by three individuals by using only one IF function
8
16
39
SUMPRODUCT Function
Pilots Athletics have provided information about their ticket sales. Find their total revenue
Tickets	Price
540	$10
332	$8
456	$6
779	$5
Revenue
Syntax	SUMPRODUCT(array1,array2,array3, ...)
COUNT and COUNTIF Function
apples	32
oranges	54
apples	44
apples	86
	how many data values exist?
	Number of cells with apples in cells A61 through A64
	Number of cells with a value greater than 55 in cells B61 through B64
Syntax	COUNT(column)
	COUNTIF(column,criteria)
LOOKUP Function
Frequency	Color
4.14	red
4.19	orange
5.17	yellow
5.77	green
6.39	blue
	Look up 6.39 in column A, and returns the value from column B that is in the same row.
	Look up 4.14 in column A, and returns the value from column B that is in the same row.
Syntax	LOOKUP(lookup_value, lookup_vector)
TEXT FUNCTIONS
Barack	Obama
Barack Obama
What are the left 4 characters of the name?
Right 4?
Trim spaces
No. of characters?
5 characters starting at space 2
Combine first and last name with a a space
Make all name capital
END

Pivot Table

Create a pivot table for the following data in a new worksheet which should summarize data the way shown in the following picture.
For the pivot table, (1) Add a design of your choice, (2) Add a calculated filed called "Match" equal to the 10% of amount
, and (3) Add a pivot chart of your choice
Contributor	Gender	Street	City	State	Zip	Letter Sent	Status	Region	Amount
Mr. Douglas Viereck	M	4005 West Madison Road	Toledo	TX	43617	02/06/03	Parent	East	$ 1,000
Mr. Ken Hodge	M	6 Bayberry Pointe Drive	Topaz	MI	49925	12/21/03	Parent	North	$ 1,000
Mr. Gregory Olson	M	1925 Bridge Street	Halfway Corner	MA	48441	01/05/04	Fan	East	$ 1,000
Mr. Ray Suchecki	M	Pond Hill Road	Monroe	VT	48161	02/13/04	Parent	East	$ 1,000
Ms. Susan Mouw	F	3408 Gateway Boulevard	Sylvania	TX	43560	02/18/04	Alumna	East	$ 1,000
Ms. Gretchen Fletcher	F	2819 East 10 Street	Mishawaka	IN	46544	02/24/04	Fan	East	$ 1,000
Ms. Doris Reaume	F	82 Mixi Road	Bootleg	ME	49945	03/18/04	Parent	East	$ 1,000
Ms. Shirley Woodruff	F	8408 E. Fletcher Road	Clare	MI	48617	03/27/04	Fan	North	$ 1,000
Mr. Wayne Bouwman	M	400 Salmon Street	Ada	MA	49301	04/01/04	Fan	East	$ 1,000
Mr. John Rohrs	M	37 Queue Highway	Lacota	CO	49063	04/04/04	Parent	West	$ 1,000
Ms. Michele Yasenak	F	95 North Bay Boulevard	Jenison	CO	49428	04/11/04	Alumna	West	$ 1,000
Mr. Ronald Kooienga	M	15365 Old Bedford Trail	Eagle Point	FL	49031	04/15/04	Alumna	South	$ 2,000
Mr. Donald Bench	M	2874 Western Avenue	Drenthe	WY	49464	04/17/04	Unknown	West	$ 2,000
Ms. Janice Stapleton	F	2840 Cascade Road	Zeeland	WV	49464	04/25/04	Fan	East	$ 2,000
Ms. Joan Hoffman	F	4090 Division Street NW	Borculo	MA	49464	04/25/04	Alumna	East	$ 2,000
Mr. Shannon Petree	M	3509 Garfield Avenue	Romulus	NC	48174	04/26/04	Unknown	South	$ 2,000
Mr. Joe Markovicz	M	1366 36th Street	Roscommon	MA	48653	05/02/04	Parent	East	$ 2,000
Ms. Bridgit Feeney	F	5013 North Cliff Avenue	LaPorte	IN	46351	05/03/04	Alumna	East	$ 2,000
Ms. Dawn Parker	F	1935 Snow Street SE	Saugatuck	NH	49453	05/03/04	Parent	East	$ 2,000
Mr. Carl Seaver	M	5480 Alpine Lane	Selkirk	VA	48661	05/09/04	Fan	East	$ 2,000
Ms. Deborah Wolfe	F	2140 Edgewood Road	Five Lakes	CT	48446	05/09/04	Fan	East	$ 2,000
Mr. James Cowan	M	114 Lexington Parkway	Alto	NY	49302	05/17/04	Alumna	East	$ 2,000
Ms. Rebecca Van Singel	M	56 Four Mile Road	Grand Rapids	NY	49505	05/27/04	Alumna	East	$ 2,000
Ms. Jennifer Lewis	F	3333 Bradford Farms	Copper Harbor	CA	49918	06/08/04	Fan	West	$ 2,500
Mr. Walter Reed	M	150 Hall Road	Kearsarge	CO	49942	06/16/04	Fan	West	$ 2,500
Mr. Toby Stein	M	431 North Phillips Road	South Bend	IN	46611	07/01/04	Parent	East	$ 2,500
Ms. Barbara Feldon	F	230 South Phillips Road	South Bend	IN	46623	07/03/04	Alumna	East	$ 2,500
Mr. Gilbert Scholten	M	3915 Hawthorne Avenue	Toledo	TX	43603	07/13/04	Alumna	East	$ 2,500
Mr. Donald MacPherson	F	701 Bagley Street	Grand Rapids	CT	49571	08/09/04	Parent	East	$ 2,500
Ms. Julie Pfeiffer	F	3300 West Russell Street	Maumee	TX	43537	08/28/04	Unknown	East	$ 2,500
Ms. Curtis Haiar	F	10 Sycamore Street	Grand Rapids	WI	49509	08/31/04	Fan	North	$ 2,500
Ms. Jean Brooks	F	44 Tower Lane	Mattawan	WA	49071	09/02/04	Unknown	West	$ 2,500
Mr. Janosfi Petofi	M	11456 Marsh Road	Shelbyville	CA	49344	10/08/04	Fan	West	$ 2,500
Ms. Nancy Mills	F	2890 Canyonside Way	Romulus	MI	48174	10/27/04	Fan	North	$ 2,500
Ms. Tara Jerentowski	F	109 East Monroe Avenue	Elkhart	IN	46515	10/31/04	Alumna	East	$ 2,500
Ms. Pam Leonard	F	915 South Creek Drive	Grand Rapids	MI	49587	11/22/04	Parent	North	$ 2,500
Mr. Jeffrey Hersha	M	8200 Baldwin Boulevard	Burlington	CA	49029	11/25/04	Alumna	West	$ 2,500
Mr. Clifford Merritt	M	4004 West 41st Street	Goshen	IN	46526	12/12/04	Alumna	East	$ 2,500
Mr. Joseph Allen	M	101 South Plains Ave	Omaha	NE	68101	09/15/04	Parent	West	$ 2,500
Mrs. Wendy Williams	F	4545 Main Street	Montpelier	VT	05602	09/15/04	Alumna	East	$ 2,500
Dr. Mark Bickford	M	P.O. Box 100011	Boston	MA	02134	09/24/04	Fan	East	$ 5,000
Ms. Anne Logan	F	101 South University Ave.	Denver	CO	80210	09/26/04	Alumna	West	$ 5,000
Rev. Philip Johnson	M	555 Parker Street	Miami	FL	33010	09/26/04	Parent	South	$ 5,000
Ms. Mary Beth Mauch	F	P.O. Box 6677	New York	NY	10015	09/27/04	Alumna	East	$ 5,000
Mr. Peter Burton	M	777 Orchard Ave.	Seattle	WA	98117	10/05/04	Parent	West	$ 5,000
Ms. Helen Gooch	F	P.O. Box 43000	Jackson	WY	83001	10/06/04	Unknown	West	$ 10,000
Mrs. Suzanna Lianos	F	P.O. 1121	Meredith	TX	03253	10/10/04	Fan	East	$ 10,000
Mrs. Jane Everritt	F	160 Vittum Hill Rd.	Massapequa	NY	11758	10/13/04	Fan	East	$ 10,000

What if Analysis

Your Home payment - Goal Seek
Loan Amount	$250,000
Annual Interest	6%
Number of months	240
Your monthly payment	$1,791.08
		Let's say you want to pay only $ 1500 per month.		Let's say you want to pay only $ 1500 per month.
		How much interest rate you should pay?		How many months you should pay?
			Show the goal seek results in this work sheet above