Question: Why this function, CHAR(), is not working ?

Question

2017.04.18 — Question:  Why this function, CHAR(), is not working ?

Apparently I’m missing something important causing the CHAR() function to work.

The cells of Columns K through Y are formatted “General” to hold exam and homework scores, while column Z is to hold the cumulative grade [Score] and AA is the final grade [Grade].

Before any grades are entered, Z currently posts a zero (“0”) and AA posts an “F”.

I’m trying to make Z and F stay blank or post blank spaces until a score is actually input, anywhere in K thought Y.

Thus far, I’ve tried CHAR (32), CHAR(0), CHAR(), TEXT(CHAR(32)), TEXT(CHAR(0)), and TEXT(CHAR()).

I was thinking one of those would work, but neither did.

I’m apparently not using CHAR or TEXT correctly.

Below are the functions in cells Z and AA.

ISBLANK), SUM, VLOOKUP are working correctly.

“Score” (Cell Z2)  =  IF(ISBLANK(K2:Y2), CHAR(32), SUM(Grading!$J$3:$J$8))

“Grade” Cell AA2  =  IF(ISBLANK(K2:Y2), CHAR(32),VLOOKUP(Z2, Grading!$L$3:$M$15, 2, TRUE))

Worksheet “Roster”                                                       Score      Grade

K          L        M        N         O         P        Q         R        S          T         U        V        W        X         Y         Z       AA

Row	2	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z = 0	AA =F

PS:  My last reply came from Teylyn; excellent response !

Answer 1

Sorry Erica that won't work. You need to include an AND in front of the ISBLANK() to make it work as an array. See images below.

      

Answer 2

Answer:  Why this function, CHAR(), is not working ?

My objective was to include a function that would allow a formula to place either:

1. a number from zero to 100 into a cell, or
2. leave the cell blank.

By combining ideas I got from four replies to my question, I managed to get something useful.

The final result that works is below:

= IFERROR(IF(Y2<0,"",VLOOKUP(Y2,$M$41:$N$53,2,TRUE)),"")

Answer 3

Hi,

Is your concern already resolved? If not, kindly give us a feedback.

Regards.

Answer 4

Hi iwc,

edit your formulas for the Z and AA cells and then press ctrl+shift+enter. This will convert them into array formulas and make the Isblank(array) function work as intented.

Please let me know if this answers your question by marking as answer and if you need further assistance.

Thanks,

EBW

Answer 5

In Z2, try this - not sure why you are using Grading!$J$3:$J$8.....

=IF(COUNTA(K2:Y2)=0, "",SUM(K2:Y2))

In AA2, try

=IF(COUNTA(K2:Y2)=0, "",VLOOKUP(Z2, Grading!$L$3:$M$15, 2, TRUE))