Vlookup not working on entire content of table array

[RafikiRW]

I am attempting to build a small template for school report cards in which the student's GPA is calculated based on the individual grades entered by the teachers.

The individual percentage grades are entered into B7:B15 and averaged in cell B28 using =IF(B7>0,VALUE(FIXED(AVERAGE(B7:B15)))*100).

This result is then referenced by a formula in another cell =VLOOKUP($B$28,$B$39:$C$140,2,FALSE)

The proper GPA is listed until the grade percentage goes below 59%. When it reaches 58%, the resulting answer becomes #N/A.

I have begun with a new spreadsheet and completely reentered the formulas by hand. I have also populated the table array (B39:C140) line by line and know that the cell formats are correct.

What am I missing?

 

[Norie]

Your VLOOKUP is looking for exact matches, are you sure the average in B28 exactly matches a value in B28:C140?

 

[RafikiRW]

Your VLOOKUP is looking for exact matches, are you sure the average in B28 exactly matches a value in B28:C140?

Yes, the numbers are all exact values. I have tried using TRUE instead of FALSE but it made no difference.

 

[Norie]

Can you post some sample data?

 

[RafikiRW]

Is it possible for me to attach the file? If so, how do I do it? It's quite small.

 

[Fluff]

You cannot attach files here, but you can use the XL2BB add-in to post sample data.
See the link in my signature, or the link in the reply window.

 

[RafikiRW]

You cannot attach files here, but you can use the XL2BB add-in to post sample data.
See the link in my signature, or the link in the reply window.

Report Card Template.xlsx
	A	B	C
4	Subject	Term 1	Term 2
5
6	Bible
7	French	85%
8	Genesis Design	98%
9	Health and Hygiene	87%
10	I.C.T.	100%
11	Ikinyarwanda	50%
12	Language Arts	20%
13	Life Science	10%
14	Maths	25%
15	Social Studies/History	52%
16	P.E.
17	Art
18	Drama
19	Music
20	Grade Point Average	0.9
21	Behavior
22	Neatness
23	Politeness
24	Cooperative Attitude
25	Learning Attitude
26	Attentiveness
27	Perseverance
28		59
29
30	Academic Subjects		Non-Academic Subjects and Behavior
31	A = Excellent 90-100%		E = Excellent
32	B = Good 80-89%		S = Satisfactory
33	C = Pass 70-79%		N = Needs Improvement
34	D = Weak 65-69%		U = Unacceptable
35	F = Failing 64% and below
36
37
38		Percentage	GPA
39		100	4.0
40		99	3.9
41		98	3.9
42		97	3.8
43		96	3.8
44		95	3.7
45		94	3.7
46		93	3.6
47		92	3.6
48		91	3.5
49		90	3.5
50		89	3.4
51		88	3.3
52		87	3.2
53		86	3.1
54		85	3.0
55		84	2.9
56		83	2.8
57		82	2.7
58		81	2.6
59		80	2.5
60		79	2.4
61		78	2.3
62		77	2.2
63		76	2.1
64		75	2.0
65		74	1.9
66		73	1.8
67		72	1.7
68		71	1.6
69		70	1.5
70		69	1.4
71		68	1.4
72		67	1.3
73		66	1.3
74		65	1.2
75		64	1.1
76		63	1.1
77		62	1.1
78		61	1.0
79		60	1.0
80		59	0.9
81		58	0.9
82		57	0.9
83		56	0.9
84		55	0.9
85		54	0.9
86		53	0.8
87		52	0.8
88		51	0.8
89		50	0.8
90		49	0.8
91		48	0.7
92		47	0.7
93		46	0.7
94		45	0.7
95		44	0.7
96		43	0.7
97		42	0.7
98		41	0.6
99		40	0.6
100		39	0.6
101		38	0.6
102		37	0.6
103		36	0.6
104		35	0.5
105		34	0.5
106		33	0.5
107		32	0.5
108		31	0.5
109		30	0.5
110		29	0.4
111		28	0.4
112		27	0.4
113		26	0.4
114		25	0.4
115		24	0.4
116		23	0.3
117		22	0.3
118		21	0.3
119		20	0.3
120		19	0.3
121		18	0.3
122		17	0.2
123		16	0.2
124		15	0.2
125		14	0.2
126		13	0.2
127		12	0.2
128		11	0.1
129		10	0.1
130		9	0.1
131		8	0.1
132		7	0.1
133		6	0.1
134		5	0.0
135		4	0.0
136		3	0.0
137		2	0.0
138		1	0.0
139		0	0.0
Sheet1

Cell Formulas
Range	Formula
B20:C20	B20	=IF(B7>0,VLOOKUP(B$28,$B$39:$C$139,2,FALSE),"")
B28	B28	=IF(B7>0,VALUE(FIXED(AVERAGE(B7:B15)))*100,"")
C28	C28	=IF(C7<>0,VALUE(FIXED(AVERAGE(C7:C15)))*100,"")

 

[Fluff]

Thanks for that, it's down to floating errors, if you replace your formula in B20 with

=SUMIFS(C39:C139,B39:B139,B28)

It should work.
Despite the fact you are rounding the values, there is an infinitesimally small remainder as can be seen in C27:C28

+Fluff v2.xlsm
	A	B	C
4	Subject	Term 1	Term 2
5
6	Bible
7	French	0.85
8	Genesis Design	0.98
9	Health and Hygiene	0.87
10	I.C.T.	0.8
11	Ikinyarwanda	0.5
12	Language Arts	0.2
13	Life Science	0.1
14	Maths	0.25
15	Social Studies/History	0.52
16	P.E.
17	Art
18	Drama
19	Music
20	Grade Point Average	0.9
21	Behavior
22	Neatness
23	Politeness
24	Cooperative Attitude
25	Learning Attitude
26	Attentiveness
27	Perseverance	56	-7.10543E-05
28		56	-7.10543E-05
Work

Cell Formulas
Range	Formula
B20	B20	=SUMIFS(C39:C139,B39:B139,B28)
C20	C20	=IF(C7>0,VLOOKUP(C$28,$B$39:$C$139,2,FALSE),"")
B27	B27	=IF(B7>0,ROUND(AVERAGE(B7:B15),2)*100,"")
C27:C28	C27	=(56-B27)*10^10
B28	B28	=IF(B7>0,VALUE(FIXED(AVERAGE(B7:B15)))*100,"")

 

[RafikiRW]

BRILLIANT!!! Thank you so much. It works like a charm.

 

[Fluff]

You're welcome & thanks for the feedback.