Question

Using If and LookUp Formulas

Formatting Grades

Input boxes in tan

Output boxes in yellow

Given data in blue

Answers in red

Professor Streterstein is a bit absentminded. His instructor’s grade book is a mess, and he

would like your help cleaning it up and making it easier to use. In Professor Streterstein’s

course, the maximum possible points a student can earn is 750. The following table displays

the grade equivalent to total points for the course.

Professor Streterstein's Course Points

Total Points

Calculated Grade

0

F

450

D

490

C-

535

C

560

B-

600

B

635

A-

675

A

Help Professor Streterstein rework his grade book.

Perform the following:

1

Add a column in the grade book for final grade next to the total points earned column.

2

Use the VLookup Function to automatically assess final grades based on the total points

column.

3

Using the If Function, format the workbook so each student’s grade shows a pass or fail—

P for pass, F for fail—based on the total points.

		Professor Streterstein Course
	Student name	Exam 1	Exam 2	Quizz 1	Quiz 2	Quiz 3	Project 1	Project 2	Project 3	Project 4	Total Points Earned	Pass/Fail
	Achey, Sarah	76	100	38	43.5	50	87	48.5	73.72	86	602.72
	Acosta, Angelica	100	77	50	48	38.5	96	37.345	97	66.22	610.065
	Albannay, Bader	87	100	43.5	49	50	98	48.5	84.39	86	646.39
	Alkelabi, Khalid	8	87	4	2	43.5	4	42.195	7.76	74.82	273.275
	Alnusf, Ali	96	78	48	37.5	39	75	37.83	93.12	67.08	571.53
	Alo, Juanita	98	100	49	32	50	64	48.5	95.06	86	622.56
	AlSabt, Sadon A KH A	75	99	37.5	43.5	49.5	87	48.015	72.75	85.14	597.405
	Anderson, Chester	87	98	43.5	48	49	96	47.53	84.39	84.28	637.7
	Ballantine, David	96	92	48	22.5	46	45	44.62	93.12	79.12	566.36
	Barr, Sandor	64	96	32	26.5	48	53	46.56	62.08	82.56	510.7
	Barrett, Sarah	53	75	26.5	33.5	37.5	67	36.375	51.41	64.5	444.785
	Behbehani, Mohammad	45	93	22.5	39	46.5	78	45.105	43.65	79.98	492.735
	Beier, Susanna	78	8	39	33	4	66	3.88	75.66	6.88	314.42
	Berg, Yashika	4	100	2	41.5	50	83	48.5	3.88	86	418.88
	Blackett, Christopher	66	4	33	49	2	98	1.94	64.02	3.44	321.4
	Cabander, Frida	98	83	49	38.5	41.5	77	40.255	95.06	71.38	593.695
	Callaghan, Carolyn	67	87	33.5	44.5	43.5	89	42.195	64.99	74.82	546.505
	Ceballos, Manuel	89	96	44.5	28	48	56	46.56	86.33	82.56	576.95
	Chen, Yu-Chi	83	100	41.5	42.5	50	85	48.5	80.51	86	617.01
	Colombin, Meghan	56	45	28	21.5	22.5	43	21.825	54.32	38.7	330.845
	Connelly, Brianna	43	78	21.5	28	39	56	37.83	41.71	67.08	412.12
	Cooper, Alexander	77	85	38.5	44	42.5	88	41.225	74.69	73.1	564.015
	Corson, Bailey	88	74	44	23	37	46	35.89	85.36	63.64	496.89
	Cunningham, Brett	46	72	23	39	36	78	34.92	44.62	61.92	435.46
	Dietz, Alex	56	66	28	39	33	78	32.01	54.32	56.76	443.09
	Dubose, Fallon	78	98	39	45	49	90	47.53	75.66	84.28	606.47
	Elenbaas, Andrew	78	74	39	49.5	37	99	35.89	75.66	63.64	551.69
	Feuer, Jacob	90	77	45	11.5	38.5	23	37.345	87.3	66.22	475.865
	Flaks, Molly	23	88	11.5	22.5	44	45	42.68	22.31	75.68	374.67
	Friend, Jason	99	75	49.5	38	37.5	76	36.375	96.03	64.5	571.905
	Grzanowski, Michael	76	89	38	39	44.5	78	43.165	73.72	76.54	557.925
	Harkavy, Alexander	78	78	39	28	39	56	37.83	75.66	67.08	498.57
	Harris, MacGregor	45	46	22.5	33.5	23	67	22.31	43.65	39.56	342.52
	Hartman, Brian	56	65	28	21.5	32.5	43	31.525	54.32	55.9	387.745
	High, Alexander	43	76	21.5	49.5	38	99	36.86	41.71	65.36	470.93
	Hobbs, Zachary	67	78	33.5	44.5	39	89	37.83	64.99	67.08	520.9
	Holobinko, Brittney	99	87	49.5	43.5	43.5	87	42.195	96.03	74.82	622.545
	Keely, Sara	89	99	44.5	49	49.5	98	48.015	86.33	85.14	648.485
	Kendall, Richard	98	76	49	41	38	82	36.86	95.06	65.36	581.28
	Kowalski, Kyle	85	100	42.5	37	50	74	48.5	82.45	86	605.45
	Kuo, Cheng-Jung	87	98	43.5	33.5	49	67	47.53	84.39	84.28	594.2
	Lenox, Matthew	82	78	41	41.5	39	83	37.83	79.54	67.08	548.95
	Markham, Julie	67	64	33.5	44.5	32	89	31.04	64.99	55.04	481.07
	Martin, Elizabeth	83	56	41.5	37	28	74	27.16	80.51	48.16	475.33
	Martinez, Javier	89	53	44.5	50	26.5	100	25.705	86.33	45.58	520.615
	Mashburn, Amy	74	43	37	37	21.5	74	20.855	71.78	36.98	416.115
	McCain, Donovan	74	99	37	47	49.5	94	48.015	71.78	85.14	605.435
	McCauslin, Megan	100	67	50	50	33.5	100	32.495	97	57.62	587.615
	McCormick, Evan	94	87	47	4	43.5	8	42.195	91.18	74.82	491.695
	McLaughlin, Dorsey	8	67	4	24	33.5	48	32.495	7.76	57.62	282.375
	Mellen, Jonathan	74	100	37	36	50	72	48.5	71.78	86	575.28
	Miller, Sara	100	89	50	50	44.5	100	43.165	97	76.54	650.205
	Nestor, Brian	48	89	24	37	44.5	74	43.165	46.56	76.54	482.765
	O'Connor, Justin	72	100	36	37	50	74	48.5	69.84	86	573.34
	O'Donnell, Devon	100	56	50	27	28	54	27.16	97	48.16	487.32
	Oliveria, Kyle	74	100	37	44	50	88	48.5	71.78	86	599.28
	Ollom, Ross	88	100	44	43.5	50	87	48.5	85.36	86	632.36
	Olwig, Thomas	54	43	27	32.5	21.5	65	20.855	52.38	36.98	353.215
	Parra, Matthew	65	56	32.5	33.5	28	67	27.16	63.05	48.16	420.37
	Phan, Thao	67	78	33.5	44.5	39	89	37.83	64.99	67.08	520.9
	Pitto, James	89	90	44.5	43.5	45	87	43.65	86.33	77.4	606.38
	Pologar, Trenton	74	100	37	37.5	50	75	48.5	71.78	86	579.78
	Popalisky, Leslie	87	100	43.5	50	50	100	48.5	84.39	86	649.39
	Reinhard, Kyomi	100	54	50	50	27	100	26.19	97	46.44	550.63
	Roberts, Jase	87	23	43.5	27.5	11.5	55	11.155	84.39	19.78	362.825
	Rosenthaler, Andrew	55	45	27.5	50	22.5	100	21.825	53.35	38.7	413.875
	Rudebusch, Justin	100	67	50	49	33.5	98	32.495	97	57.62	584.615
	Runes, Corri	100	65	50	50	32.5	100	31.525	97	55.9	581.925
	Saario, Courtney	98	89	49	49.5	44.5	99	43.165	95.06	76.54	643.765
	Sabloff, Claire	100	67	50	50	33.5	100	32.495	97	57.62	587.615
	Shiff, Sari	100	89	50	50	44.5	100	43.165	97	76.54	650.205
	Sides, Stacy	100	87	50	50	43.5	100	42.195	97	74.82	644.515
	Silerio, Edgar	99	98	49.5	38.5	49	77	47.53	96.03	84.28	638.84
	Simmer, Lindsey	77	82	38.5	28	41	56	39.77	74.69	70.52	507.48
	Sims, Andrew	56	83	28	43.5	41.5	87	40.255	54.32	71.38	504.955
	Smaiely, Mohammed	87	74	43.5	38.5	37	77	35.89	84.39	63.64	540.92
	Smith, Ryan	100	55	50	50	27.5	100	26.675	97	47.3	553.475
	Snead, Megan	100	100	50	50	50	100	48.5	97	86	681.5
	Spencer, William	75	100	37.5	44.5	50	89	48.5	72.75	86	603.25
	Sun, Anna	77	74	38.5	39	37	78	35.89	74.69	63.64	517.72
	Talbot, Kevin	89	100	44.5	39	50	78	48.5	86.33	86	621.33
	Talley, Katrina	100	98	50	50	49	100	47.53	97	84.28	675.81
	Tiedtke, Victor	78	94	39	49.5	47	99	45.59	75.66	80.84	608.59
	Toomey, Terrance	100	99	50	50	49.5	100	48.015	97	85.14	678.655
	Urfalioglu, Cem	78	100	39	32.5	50	65	48.5	75.66	86	574.66
	Vallina, Roberta	100	77	50	50	38.5	100	37.345	97	66.22	616.065
	Wang, Barry	100	56	50	50	28	100	27.16	97	48.16	556.32
	Wei, Yu-Chen	99	8	49.5	49	4	98	3.88	96.03	6.88	414.29
	Wieland, Christopher	100	87	50	50	43.5	100	42.195	97	74.82	644.515
	Williams, Maria	65	100	32.5	38	50	76	48.5	63.05	86	559.05
	Wilson, Christina	98	48	49	46	24	92	23.28	95.06	41.28	516.62
	Wurzer, Michael	92	74	46	46.5	37	93	35.89	89.24	63.64	577.27
	Yu, Won Sun	93	88	46.5	4	44	8	42.68	90.21	75.68	492.07

Answer 1

Please do the following steps:

1) Find out what is column code for Total Point earned column. (It will be like m or n or l or any alphabet on very first row. I'm assuming column code is 'L')

2) Decide what is the pass marks. (I'm assuming 500)

3) Then go to first empty row of the Pass/Fail column. And write =IF(L2>= 500,"P","F") and press ENTER

4) Now you should see the value of that column either 'P' or "F'

5) Just clik on the cell, now you can see a dot on selected cell border. Select and hold on the dot and drag it down to all the columns

6) Pass/Fail value will be caluculated automatically or all the students.