excel

excel-a3-2015.xlsx

Home >Information Systems homework help >excel

ReMe

HW 3 has 4 problems, with 15 points total:
1. 3 pts. Library books: delete my notes and hints before print (to save paper)
2. 4 pts. Product availability lookup - given a product's name (product ID), and the number of units desired, find out if the onhand amount is available for the desired order
3. 4 pts. LA County Health Department - our old friend :-)
4. 4 pts. CSUN VPAC Student/faculty/public prices of each show depends on the different section of the theater - which can be looked up! But different show has different prices -- "IF": Which column's values to be returned depends on which show you're interested.
General requirements same as HW 1 - reiterated below:
	print values AND formulas -formula print first, then its corresponding value print;
	sufficient font size: 11-12 point fonts required, 13-point font appreciated;
	use parameters (rather than numbers directly); don't forget to consider MIXED REF if appropriate!!
	proper formatting - grid lines, $, %, etc.;
	exact use of the given spreadsheet - do NOT move cell contents; do NOT add your own cells/ranges;
	assemble all printouts in order.
	–violations subject to 2-point penalty EACH occurrence.
	Put your name (Last, Firt) on the upper-right corner
		(my suggesed cell is not rigid, as long as it's upper-right corner)

LibraryBook

LastName,

Firstname

ONE OF the solution logics (you may have a different one)

Name

Due Date

Return

Overdue (Y/N)

Fine

Fine Rate:

0.2

per day

Suggestion:

Adam

10/28/15

10/25/15

You can

Ben

10/12/15

10/14/15

draw a

David

10/18/15

Think about how to

flowchart

Candy

11/10/15

10/25/15

deal with these cases

to help you

Edward

10/20/15

organize

your logic:

Yes

(1) Write a formula each for columns E and F (IF formula)

(which means

(2) Write a VLOOKUP for the situation below

"not returned yet")

Yes

(But not returned

Key logic: *If* a book is NOT YET returned, compare TODAY's date with it's due date

is NOT necessarily

Hint: One solution logic could be -

late!! - depending on

(1) Examine first if the return date is blank, and if it is, check overdue or not

due date!)

comparing today's date (an Excel function; check Handout 1) with the due date…

(2) If the return date is not blank, compare the return date with the due date.

… …

Yes

… …

AND, to find out column D value, you need a lookup!

Is D blank?

Is Ret Date later than DueDate?

(You figure out...)

Is TODAY later than DueDate?

ProductLookup

	LastName,	FirstName
Product Name	On Hand	Unit Price
iPad 2	11	399.99
The new iPad	4	499.99
Asus Eee Pad	8	399.99
Samsung Galaxy Tab 10.1	6	399.99
Customer query:
Please enter product name:	# unit desired?	Availability
(User enter product name here)	(User enter # unit here)	(YOUR Formula, showing "Available" or "Mot available")
Logic: If the number entered for a specific product is more than the on-hand quantity of that product, then …
Hint: How do we know the on-hand quant of a product? - Answer: through a lookup with desired product name!
By nature this is an IF; but one of the values to be compared should be obtained with a LOOKUP.

HealthDept

				LastName,	FisrtName
	(1)			(2)
Ser No	Care Provider	Data Summed from Itemized Sheets	If (1) is not equal to corresponding (2), list the value of corresponding (2) here (Else leave it blank)	Care Provider	Data Reported by Provider
1	Bay View Rehab. Center	686,078.00		Homes for Life Service	10,000.00
2	Children's Bureau of Southern Bay	5,197,762.00		Olive Hilltop Treatment Centers, Inc	130,816.00
3	Children's Paradise Inc.	3,121,157.00		San Fernando Children's Center	587,063.00
4	Foothill Family Counseling	3,861,414.00		South Central Rehab Program	616,644.00
5	Hamburger Family Center	3,353,699.00		Bay View Rehab. Center	686,078.00
6	Homes for Life Service	100,000.00		Pacific Asian Psychiatric Services	853,400.00
7	Intercommunity Child Center	1,609,745.00		ProviCare Comm. Serv.	915,785.00
8	Los Angeles Unified School District	1,877,623.00		Intercommunity Child Center	1,609,745.00
9	Olive Hilltop Treatment Centers, Inc	130,861.00		Los Angeles Unified School District	1,876,623.00
10	Pacific Asian Psychiatric Services	853,400.00		Children's Paradise Inc.	3,121,175.00
11	ProviCare Comm. Serv.	915,785.00		Hamburger Family Center	3,353,699.00
12	San Fernando Children's Center	587,063.00		SHIELDS for Women Project, Inc.	3,734,250.00
13	SHIELDS for Women Project, Inc.	3,734,250.00		Foothill Family Counseling	3,861,414.00
14	South Central Rehab Program	616,644.00		Star View Adult Day Care Center	4,396,804.00
15	Star View Adult Day Care Center	4,396,804.00		Children's Bureau of Southern Bay	5,197,762.00
16	The Boys and Girls Support Society	6,358,914.00		The Boys and Girls Support Society	6,358,914.00
			"Corresponding (2)" means -
			the data of the same provider,
			as reported by that provider (from G column),
			according to (in the order/sequence of) the company name on the LEFT HALF

the nature of the problem is a comparison; but the comparison needs a lookup

VPAC-Price

CSUN VPAC Prices for Spring 2014		LastName,	FirstName
	23-Mar	28-Mar	4-Apr
	Scharoun Ensemble Berlin	Doc Severinsen & His Big Band	Diana Krall – Glad Rag Doll World Tour
Code	M23	M28	A04
Orchestra	$55	$75	$150
Parterre	$55	$65	$125
Loge	$55	$50	$90
Balcony	$55	$30	$65
Which show do you want to go to?		(Enter the code like "M23" here)
Which section of seats do you prefer?		(Enter name of the section here)
Your Price:		(Write your VLOOKUP with IF here)
Hint for the formula: The nature of the problem is a lookup, but this lookup depends on an "IF" -
	which date is the show you want to purchase, because you will have a different lookup
	according to the date. For example:
	If the date is M23, your loookup will return the B column value;
	If the date is M28, your loookup will return the C column value;
	If the date is A04, your loookup will return the D column value;
	- did I already give you the logic of the formula?