Excel VLOOKUP; VLOOKUP + IF; IF with dates

excel-a4-2015.xlsx

ReMe

HW 4 has three problems, TWO BASIC requirement and ONE BONUS, with 12+3 points total:
1. 5 pts. IS 312 Excel Boosting Rules - nested IF.
2. 6 pts. Diagnostic of Hi/Lo blood pressures - nested IF with thresholds.
3. 4 pts. The above scenario, on different sheets - cross-sheet reference.
General requirements same as HW 1 - reiterated below:
	sufficient font size: 11-12 point fonts required, 13-point font appreciated;
	print values AND formulas - formula print first, then its corresponding value print;
	use parameters (rather than numbers directly);
	proper formatting - grid lines, $, %, etc.;
	exact use of the given spreadsheet - moving cell contents are NOT allowed;
	assemble all printouts in order.
	–violations subject to 2-point penalty EACH occurrence.

	IS 312 Excel-Boosting Rule Enforcement								Last name		First name
Name	Exl1	Exl2	ExlProj	E1	E2	Tot	Sem%	ExcelTot	Exl%	Adjusted Sem%
Adam	9	18	37	63	107	234	81%	64	80%
Alexander	8	12	27	79	89	215	74%	47	59%
Chris	14	20	39	66	102	241	83%	73	91%
Cristina	9	11	29	62	97	208	72%	49	61%
David	12	20	33	63	84	212	73%	65	81%
Edwin	11	12	23	63	85	194	67%	46	58%
Kaley	12	20	37	60	84	213	73%	69	86%
Kevin	12	20	38	60	99	229	79%	70	88%
Matthew	12	13	31	88	115	259	89%	56	70%
Possible	15	20	45	90	120	290	100%	80	100%
Main principle: Excel % must NOT be significantly lower than the Semester % (Sem%)											Your formula
	Excel% can be slightly lower than Sem% (within 10 percentage points, such as -
	Sem% = 80%, Exl% = 70.5%, OK, since 70.5% is WITHIN 10 percentage points lower than 80%
Rule 1:	If Exl% is lower than Sem% by 15% or more, the Adjusted Sem% will be Sem% minus 10 percentage points;
Rule 2:	If Exl% is lower than Sem% by 10% or more (but not as much as 15%), the Adjusted Sem% will be Sem% minus 6 percentage points.
Rule 3:	If Exl% is not lower than Sem% for 10%, the Adjusted Sem% will be the same as Sem%;
Note: All the "%" in this problem are percentage POINTS; the consequence of this interpretation is:
	You will ONLY have addition/subtraction, but NOT multiplication/division in your formula.
	In other words, if A is 5 percentage points more than B, that means A=B+5%, not A/B=105%
	One example: A = 55%, B = 50%, then the difference of A and B is 5 percentage points (although that is 5/55=9% in proportion)

		BloodPressure Dianosis
			Last name here	First namehere
Reference:	120	80	Diagnosis
Measured:	Systolic	Dialatic	Systolic	Dialatic
Patient 1	111	75
Patient 2	105	75
Patient 3	104	80
Patient 4	126	86
Patient 5	120	69
			ONE formula, that can be copied to the above
			range, for both columns.
Rule:	Measured blood pressure -
	- can be lower than the reference by 10 (0~10 lower are OK);
		beyond this would be "Low";
	- can be higher within 5 (0~5 higher are OK)
		beyond this would be "High";
	So the words "Hi", "Low", or "OK" will be displayed
	in the range of D6:E10
Hint: IF, and mixed references
Type of logic: IF, with threshold

				(Last name)	(first name)
	Systolic	Dialatic
Patient 1
Patient 2
Patient 3			1. Write ONE formula for both B-column and C-column, you look up
Patient 4			the diagnosis of each pateint on systolic and dialatic pressure.
Patient 5			Copy the two formulas down their respective columns.
Average
Maximum
	2. Write a formula to calculate the average for systolic and dialatic pressure.
	3. Write a formula to calculate the maximum for systolic and dialatic pressure.
Note:	The abive three formulas must use ONLY the cells in the previous sheet
	- the "HiLoBP" sheet, RATHER THAN THE DATAON THE CURRENT SHEET.