excel formulas

excel.xlsx

ReadMe

IS 312 Spreasdsheet HW 4 (15 points)
1. 4 Pts	Extension of the "Product lookup" problem, adding the formula to estimate total charge. Basic logic is IF and mutiplication, but whom to compare and whom to multiply depends on VLOOKUP.
2. 4 pts	Use CombineA1 & CombineA2. Read the instructions closely.		IF+VLOOKUP cross sheet.
3. 7 pts	Use CombineB1 & CombineB2. Read the instructions closely.		IF+VLOOKUP cross sheet.
	By nature the last part of this problem is a cross-sheet lookup, but whether or not to conduct a lookup depends on whether or not a cell's value is the top performer's score.
Also, don't forget "old skills" such as mixed reference.
Font size, font size, font size!		12pts
Font size, font size, font size!
Note: The cross-sheet reference is in Tutorial 3, under the section title "Section 3-2: Workbook – Referencing across Worksheets"
A portion of the page is pasted below, to help you quickly locate the contents:

Last name	First name
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 data entry:		Your formulas in the two cells below:
Please enter product name:	# unit desired?	Availability	Charge
(Enter product name here)	(Enter # unit here)	(Show "Available" or "Not available")	(Charge if applicable)
		Formulas in C11 and D11
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!
Please do NOT move any cell's contents; please do NOT add/insert any row/column.

	Last name	First name	*** Print only THIS sheet (value AND formula); no print of next sheet - no formula there
Ser No	Care Provider	Data Summed from Itemized Sheets	Formula below: If the value in C column is the same as the CORRESPONDING value in the next sheet, leave the current cell blank; ELSE, display the corresponding value(NOT company!) from next sheet
1	Bay View Rehab. Center	686,078.00
2	Children's Bureau of Southern Bay	5,197,762.00
3	Children's Paradise Inc.	3,121,157.00
4	Foothill Family Counseling	3,861,414.00
5	Hamburger Family Center	3,353,699.00
6	Homes for Life Service	100,000.00
7	Intercommunity Child Center	1,609,745.00
8	Los Angeles Unified School District	1,877,623.00
9	Olive Hilltop Treatment Centers, Inc	130,861.00
10	Pacific Asian Psychiatric Services	853,400.00
11	ProviCare Comm. Serv.	915,785.00
12	San Fernando Children's Center	587,063.00
13	SHIELDS for Women Project, Inc.	3,734,250.00
14	South Central Rehab Program	616,644.00
15	Star View Adult Day Care Center	4,396,804.00
16	The Boys and Girls Support Society	6,358,914.00

Ser No	Care Provider (from another sheet)	Data Reported by Provider
1	Homes for Life Service	10,000.00
2	Olive Hilltop Treatment Centers, Inc	130,816.00
3	San Fernando Children's Center	587,063.00
4	South Central Rehab Program	616,644.00
5	Bay View Rehab. Center	686,078.00
6	Pacific Asian Psychiatric Services	853,400.00
7	ProviCare Comm. Serv.	915,785.00
8	Intercommunity Child Center	1,609,745.00
9	Los Angeles Unified School District	1,876,623.00
10	Children's Paradise Inc.	3,121,175.00
11	Hamburger Family Center	3,353,699.00
12	SHIELDS for Women Project, Inc.	3,734,250.00
13	Foothill Family Counseling	3,861,414.00
14	Star View Adult Day Care Center	4,396,804.00
15	Children's Bureau of Southern Bay	5,197,762.00
16	The Boys and Girls Support Society	6,358,914.00

Last name		First name								In columns K,L,M, write ONE formula to			In N,O, write an IF, with lookup,
PRINT:	1. Values of this sheet; 2. Formulas of this sheet (next sheet - no print needed)									display the NAME of student, if s/he			to find out a top performer's
		<These columns are all data>							<Formulas J~O>	is the max of the corresponding item			student org and position
	Name	E1	DB	E2	xL1	xL2	xLPj	FinE	SemesterTotal	xLPj-Max	FinE-Max	Semester-Max	StudentOrg	OfficerPosition
1	Dean	81	46	72	20	18	47	86
2	Marianna	53	48	81	19	7	39	83					Note (delete after reading it)
3	Indray	76	35	76	20	18	48	86					For columns N and O, you need
4	Elizabeth	61	43	80	14	10	28	66					to consider AND or OR in IF
5	Matthew	40	47	50	9	0	36	63
6	May	56	50	64	18	16	30	80
7	Tamara	25	48	44	19	7		34
8	Aaron	64	46	63	19	18	47	97
9	Wen	67	48	85	19	18	44	86
10	Kaihee	55	43	47		20	45	71
11	Peter	38	46	66	18	18	47	44
12	Na	44	47	40		20	47	65
13	Yue	82	43	96	20	17	47	112
14	Guadalupe	69	46	82	20	19	46	88
15	Nataliya	85	48	95	19	20	49	105
16	Robert	61	50	93	20	19	49	92
17	Leiby	76	43	66	17	16	46	91
18	Julia	81	48	73	19	19	49	89
19	Chuan	53	48	44	12	0	28	52
20	Ming	75	46	98	16	18	49	106
Possible:		100	50	100	20	20	50	120
% of B & above:										<==== *Formulas this row too!

Student	Org	Position
Dean	AA	President
Indray	AA	VP
Wen	AA	Secretary
Julia	Bay	President
Guadalupe	Bay	VP
Yue	Bay	Treasurer
Nataliya	MISA	President
Ming	MISA	Secretary
Larosa	MISA	Treasurer
Note: NOT all top performaers are student officer!
(so you may have the displayed value "N/A")