Excel

Please see the attachment. Thanks

C

ell References

04

agazines

ooks

pparel

3

0

5

ast

2

00

,633

ind the Sale Total for Apparel?

s after

iscount

Price

5

?

?

?

11

20
12
13
14
15
16
17
18
19

20

borrowed

nterest Rate

12

???

from first year

12,000

?????

?????

																																																															Relative Cell Referece:
Sale for September		20
	Region																								M			B			A
					North	$1,200	$2,	11	$1,250
			South	$1,	65	$2,450	$1, Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper		54
	E	$2,341	$3,200	$1,623
												West	$3,	44	$3,	8	$2,356
Total			$8	$11,563				?																												F
Absolute Cell Reference 2:
		Price		D
Product	AfterDiscountA	AfterDiscountB
Polo Shirt		$5	$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:
		12		13		14		15			16		17		18		19
Calculating Loan Payment	(assume monthly payment)
	Amount	$20,000
Annual	I	8.75%
No of Payments per year
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
19,200	Return from second year
	12,000	Return from third year
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

Scores
88
79
99

sales report. Enter formula in cell C22 to check if the employee reached the target.

Employee

A

000

??

B

??

C

??

D

??

E

??

F

??

??

00

??

I

??

Syntax

Scores

Return

A

99

B

79

C

65

D

54

F

for Pilots basketball games can be purchased according to the following schedule:

$8

8

16

Function

Tickets Price

$10

332 $8

$6

$5

Syntax

UNT and COUNTIF Function

32

s

54

apples 44
apples

Syntax

orange

Syntax

TIONS

AND, OR Functions
		Scores
	88
		79
		99
Write formula to check if A3>A4 AND A3
Write formula to check if A3>A4 OR A3
IF Function
				Syntax	IF(logical_test, [value_if_true], [value_if_false])
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
Use the same formula all the way down. If the target is reached the formula should say YES; otherwise NO
Target	$28,000
Sales	Target Reached?
	32
25000
12250
36500
31520
29000
G	32500
H		332
23000
Nested IF function
IF(logical_test,[value_if_true],if(logical_test,[value_if_true],[value_if_false]))
Grade	If score is
57	Enter IF formula to create letter grade	> 90
81-90
71-80
61-70
<61
	Tickets
Price/ticket for first 10 tickets		$10
Price/ticket for next 10 tickets (11-20)
Price/ticket for tickets > 20		$6
Compute ticket prices for following tickets bought by three individuals by using only one IF function
39
SUMPRODU		CT
Pilots Athletics have provided information about their ticket sales. Find their total revenue
540
456
779
Revenue
SUMPRODUCT(array1,array2,array3, …)
				CO
		apples
	orange
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
COUNT(column)
COUNTIF(column,criteria)
LOOKUP Function
Frequency	Color
4.14	red
4.19
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.
LOOKUP(lookup_value, lookup_vector)
TEXT FU	NC
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

Region Amount

M

M

Parent North $ 1,000

M

East $ 1,000

M

Parent East $ 1,000

F

TX

East $ 1,000

F

Fan East $ 1,000

F

Parent East $ 1,000

F

MI

Fan North $ 1,000

M

MA

Fan East $ 1,000

M

CO

Parent West $ 1,000

F

CO

Alumna West $ 1,000

M

Alumna South

M

West $ 2,000

F

49464

Fan East $ 2,000

F

MA 49464 04/25/04 Alumna East $ 2,000

M

NC

Unknown South $ 2,000

M

MA

Parent East $ 2,000

F

IN

Alumna East $ 2,000

F

05/03/04 Parent East $ 2,000

M

Fan East $ 2,000

F

CT

05/09/04 Fan East $ 2,000

M

Alumna East $ 2,000

M

NY

Alumna East $ 2,000

F

Fan West

M

CO

Fan West $ 2,500

M

IN

Parent East $ 2,500

F

South Bend IN

Alumna East $ 2,500

M

Toledo TX

Alumna East $ 2,500

F

Grand Rapids CT

Parent East $ 2,500

F

TX

Unknown East $ 2,500

F

Grand Rapids

Fan North $ 2,500

F

Unknown West $ 2,500

M

CA

Fan West $ 2,500

F

Romulus MI 48174

Fan North $ 2,500

F

IN

Alumna East $ 2,500

F

Grand Rapids MI

Parent North $ 2,500

M

CA

Alumna West $ 2,500

M

IN

Alumna East $ 2,500

M

Parent West $ 2,500

F

VT

09/15/04 Alumna East $ 2,500

M

MA

Fan East

F

CO

Alumna West $ 5,000

M

FL

09/26/04 Parent South $ 5,000

F

NY

Alumna East $ 5,000

M

WA

Parent West $ 5,000

F

WY

Unknown West

F

TX

Fan East $ 10,000

F

NY

Fan East $ 10,000

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

Mr. Douglas Viereck

4005 West Madison Road

Toledo

TX

43617

02/06/03

Parent

East

$ 1,000

Mr. Ken Hodge

6 Bayberry Pointe Drive

Topaz

MI

49925

12/21/03

Mr. Gregory Olson

1925 Bridge Street

Halfway Corner

MA

48441

01/05/04

Fan

Mr. Ray Suchecki

Pond Hill Road

Monroe

VT

48161

02/13/04

Ms. Susan Mouw

3408 Gateway Boulevard

Sylvania

43560

02/18/04

Alumna

Ms. Gretchen Fletcher

2819 East 10 Street

Mishawaka

IN

46544

02/24/04

Ms. Doris Reaume

82 Mixi Road

Bootleg

ME

49945

03/18/04

Ms. Shirley Woodruff

8408 E. Fletcher Road

Clare

48617

03/27/04

Mr. Wayne Bouwman

400 Salmon Street

Ada

49301

04/01/04

Mr. John Rohrs

37 Queue Highway

Lacota

49063

04/04/04

Ms. Michele Yasenak

95 North Bay Boulevard

Jenison

49428

04/11/04

Mr. Ronald Kooienga

15365 Old Bedford Trail

Eagle Point

FL

49031

04/15/04

$ 2,000

Mr. Donald Bench

2874 Western Avenue

Drenthe

WY

49464

04/17/04

Unknown

Ms. Janice Stapleton

2840 Cascade Road

Zeeland

WV

04/25/04

Ms. Joan Hoffman

4090 Division Street NW

Borculo

Mr. Shannon Petree

3509 Garfield Avenue

Romulus

48174

04/26/04

Mr. Joe Markovicz

1366 36th Street

Roscommon

48653

05/02/04

Ms. Bridgit Feeney

5013 North Cliff Avenue

LaPorte

46351

05/03/04

Ms. Dawn Parker

1935 Snow Street SE

Saugatuck

NH

49453

Mr. Carl Seaver

5480 Alpine Lane

Selkirk

VA

48661

05/09/04

Ms. Deborah Wolfe

2140 Edgewood Road

Five Lakes

48446

Mr. James Cowan

114 Lexington Parkway

Alto

NY

49302

05/17/04

Ms. Rebecca Van Singel

56 Four Mile Road

Grand Rapids

49505

05/27/04

Ms. Jennifer Lewis

3333 Bradford Farms

Copper Harbor

CA

49918

06/08/04

$ 2,500

Mr. Walter Reed

150 Hall Road

Kearsarge

49942

06/16/04

Mr. Toby Stein

431 North Phillips Road

South Bend

46611

07/01/04

Ms. Barbara Feldon

230 South Phillips Road

46623

07/03/04

Mr. Gilbert Scholten

3915 Hawthorne Avenue

43603

07/13/04

Mr. Donald MacPherson

701 Bagley Street

49571

08/09/04

Ms. Julie Pfeiffer

3300 West Russell Street

Maumee

43537

08/28/04

Ms. Curtis Haiar

10 Sycamore Street

WI

49509

08/31/04

Ms. Jean Brooks

44 Tower Lane

Mattawan

WA

49071

09/02/04

Mr. Janosfi Petofi

11456 Marsh Road

Shelbyville

49344

10/08/04

Ms. Nancy Mills

2890 Canyonside Way

10/27/04

Ms. Tara Jerentowski

109 East Monroe Avenue

Elkhart

46515

10/31/04

Ms. Pam Leonard

915 South Creek Drive

49587

11/22/04

Mr. Jeffrey Hersha

8200 Baldwin Boulevard

Burlington

49029

11/25/04

Mr. Clifford Merritt

4004 West 41st Street

Goshen

46526

12/12/04

Mr. Joseph Allen

101 South Plains Ave

Omaha

NE

68101

09/15/04

Mrs. Wendy Williams

4545 Main Street

Montpelier

05602

Dr. Mark Bickford

P.O. Box 100011

Boston

02134

09/24/04

$ 5,000

Ms. Anne Logan

101 South University Ave.

Denver

80210

09/26/04

Rev. Philip Johnson

555 Parker Street

Miami

33010

Ms. Mary Beth Mauch

P.O. Box 6677

New York

10015

09/27/04

Mr. Peter Burton

777 Orchard Ave.

Seattle

98117

10/05/04

Ms. Helen Gooch

P.O. Box 43000

Jackson

83001

10/06/04

$ 10,000

Mrs. Suzanna Lianos

P.O. 1121

Meredith

03253

10/10/04

Mrs. Jane Everritt

160 Vittum Hill Rd.

Massapequa

11758

10/13/04

What if Analysis

Let’s say you want to pay only $ 1500 per month.

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.
How much interest rate you should pay?	How many months you should pay?
Show the goal seek results in this work sheet above