GPA's and Blanks

[Stephen_IV]

Currently I am using the formula below. I works great unless I have blank cells. It gives me the #N/A error. I tried to figure it out but could not. The formula in Column J is the result of the formula below. I need the results in column K. So IF Isnumber(F2) or Isnumber(G2) then give me result else leave blank (IF F2 & G2 are blank).Can someone please help me. Thanks in advance Stephen!



{=AVERAGE(CHOOSE(MATCH(F2:G2,{"G","S","N","U"},0),4,3,2,0))}

Book2
	A	B	C	D	E	F	G	H	I	J	K
1						1st MP	2nd MP	3rd MP	4th MP	Formula Results	What I would Like
2						N	U			1	1
3						U	N			1	1
4						G	G			4	4
5						U	U			0	0
6						G	G			4	4
7						S	N			2.5	2.5
8						S	N			2.5	2.5
9						S	G			3.5	3.5
10						G				#N/A	4
11							U			#N/A	0
12						G	G			4	4
13						S	S			3	3
14						G	G			4	4
15						S	U			1.5	1.5
16						S	S			3	3
17						S				#N/A	3
18						G	S			3.5	3.5
19						N	N			2	2
Sheet1

 

[schielrn]

How about this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1						1st MP	2nd MP	3rd MP	4th MP	Formula Results	What I would Like
2						N	U			1	1	1
3						U	N			1	1	1
4						G	G			4	4	4
5						U	U			0	0	0
6						G	G			4	4	4
7						S	N			2.5	2.5	2.5
8						S	N			2.5	2.5	2.5
9						S	G			3.5	3.5	3.5
10						G				#N/A	4	4
11							U			#N/A	0	0
12						G	G			4	4	4
13						S	S			3	3	3
14						G	G			4	4	4
15						S	U			1.5	1.5	1.5
16						S	S			3	3	3
17						S				#N/A	3	3
18						G	S			3.5	3.5	3.5
19						N	N			2	2	2
Sheet2

#VALUE!

Hope that helps.

 

[schielrn]

And I assume you maybe want to include F:I?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1						1st MP	2nd MP	3rd MP	4th MP	Formula Results	What I would Like
2						N	U			1	1	1
3						U	N			1	1	1
4						G	G			4	4	4
5						U	U			0	0	0
6						G	G			4	4	4
7						S	N			2.5	2.5	2.5
8						S	N			2.5	2.5	2.5
9						S	G			3.5	3.5	3.5
10						G				#N/A	4	4
11							U			#N/A	0	0
12						G	G			4	4	4
13						S	S			3	3	3
14						G	G			4	4	4
15						S	U			1.5	1.5	1.5
16						S	S			3	3	3
17						S				#N/A	3	3
18						G	S			3.5	3.5	3.5
19						N	N			2	2	2
Sheet2

#VALUE!

Entered with Ctrl+Shift+Enter

Yopu can change it to put a false part in the if statement and have it return somthing and then use that in teh match part to return the right value from choose, I just chose a blank because average will ifnore blanks and text.

 

[Stephen_IV]

Schielrn,

Perfect! Thank you again!