GPA's and Blanks

Stephen_IV · Mar 22, 2011

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

3

U

N

1

4

G

4

5

U

0

6

G

4

7

S

N

2.5

8

S

N

2.5

9

S

G

3.5

10

G

#N/A

4

11

U

#N/A

0

12

G

4

13

S

3

14

G

4

15

S

U

1.5

16

S

3

17

S

#N/A

3

18

G

S

3.5

19

N

2

Sheet1

schielrn · Mar 22, 2011

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

3

U

N

1

4

G

4

5

U

0

6

G

4

7

S

N

2.5

8

S

N

2.5

9

S

G

3.5

10

G

#N/A

4

11

U

#N/A

0

12

G

4

13

S

3

14

G

4

15

S

U

1.5

16

S

3

17

S

#N/A

3

18

G

S

3.5

19

N

2

Sheet2

#VALUE!

Hope that helps.

schielrn · Mar 22, 2011

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

3

U

N

1

4

G

4

5

U

0

6

G

4

7

S

N

2.5

8

S

N

2.5

9

S

G

3.5

10

G

#N/A

4

11

U

#N/A

0

12

G

4

13

S

3

14

G

4

15

S

U

1.5

16

S

3

17

S

#N/A

3

18

G

S

3.5

19

N

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 · Mar 23, 2011

Schielrn,

Perfect! Thank you again!

GPA's and Blanks

Stephen_IV

Well-known Member

Excel Facts

schielrn

Well-known Member

schielrn

Well-known Member

Stephen_IV

Well-known Member

Similar threads

Forum statistics

Share this page

GPA's and Blanks

Stephen_IV

Well-known Member

Excel Facts

schielrn

Well-known Member

schielrn

Well-known Member

Stephen_IV

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock