Code Function With Blanks

**ninjadan** · 04-28-2010, 02:45 PM

Hello,

I have a function that averages the grades in a column. Part of the function needs to use the code function. It returns #VALUE! whenever I have an empty cell in the range.

For example

CODE(A1:A5)

If A4 is blank, then it will return an error.

How do I make it so that it doesn't count the blanks?

**Shampoo Monkey** · 04-28-2010, 02:54 PM

Hey ninjadan,

The code function is designed to work on a single cell, not a range. If you want it to go over an entire range, you would need to use an array formula or create a column designated for just that purpose.

I don't know why you would want to do that though, can you post an example, or a full formula?

**ninjadan** · 04-28-2010, 02:59 PM

Originally Posted by Shampoo Monkey

Hey ninjadan,

The code function is designed to work on a single cell, not a range. If you want it to go over an entire range, you would need to use an array formula or create a column designated for just that purpose.

I don't know why you would want to do that though, can you post an example, or a full formula?

Here's the entire formula:

=CHAR(INT(SUMPRODUCT(CODE(A1:A5))/COUNTA(A1:A5)))

It works if I have all cells filled with letter grades.

**NBVC** · 04-28-2010, 03:05 PM

Try:

=CHAR(INT(SUM(IF(A1:A5<>"",CODE(A1:A5)))/COUNTA(A1:A5)))

confirmed with CTRL+SHIFT+ENTER

**ChemistB** · 04-28-2010, 03:09 PM

OR
=CHAR(AVERAGE(IF(ISNUMBER(CODE(A1:A5)),CODE(A1:A5),"")))
Also an array function so use CNTRL SHFT ENTER

Note: Your formula (and these formula's) seem flawed as if there is even 1 A and 4 B's, the result is A. Is that an issue?

**Paul** · 04-28-2010, 03:12 PM

I was thinking the same thing, Chemist. I would suggest using ROUND rather than INT. INT will always round down (disregard the decimal, actually) giving a higher average than may be true.

BBBAA would result in 97.6, and should average to a B.
BBAAA would result in 97.4 and should average to an A.

=CHAR(ROUND(SUM(IF(A1:A5<>"",CODE(A1:A5)))/COUNTA(A1:A5),0))