Possible To Average Letters?

I need to average the following row of letters. Is it possible?


A B D C C C C A B D A A A A A = AVERAGE OF THIS ROW (IE B)

If your letters are in A2:P2 and all in upper case try

=CHAR(ROUND(SUMPRODUCT(CODE(A2:P2))/COUNTA(A2:P2),0))

This should work:

=CHAR(ROUND(AVERAGE(IF(A1:Z1<>"",CODE(A1:Z1))),0))

This is an array formula, so once you enter it, hit CTRL + SHIFT + ENTER, rather than just ENTER.

HTH

Jason

Looks like I type too slow. LOL

Hello Jason

faster doesn't always equal better

Your approach copes with blanks whilst mine doesn't

Thank you both for your input. However, i forgot to mention that these cells are retrieving the "Letter Grade" based on another sheet via VLOOKUP command.

So both do not work correctly. But I know you guys will have the answer!

note:
B4:U4 is my range we are dealing with, btw.

Originally Posted by couger77

Thank you both for your input. However, i forgot to mention that these cells are retrieving the "Letter Grade" based on another sheet via VLOOKUP command

That shouldn't make any difference unless some of the cells return #N/A, is that the case? If not what results did you get with the above suggestions?

What are the possible results in B4:U4?

#VALUE! is what I am getting for a return in cell V4.

The range of letters is simply A,B, or C.

It is possible there might be an #N/A (will fix later) or blank in any given cell.

Cheers

If any of the input cells have an error in them, then the formulas that we stated above will also return the same error. So if you fix your lookup errors, the formulas should work.

If you would like to post up a copy of part of your workbook, we can take a look at it.

You should be able to use Jason's suggestion, here modified to ignore #N/A too....

=CHAR(ROUND(AVERAGE(IF(ISTEXT(B4:U4),IF(B4:U4<>"",CODE(B4:U4)))),0))

You need to confirm this with CTRL+SHIFT+ENTER

To do that paste formula into cell, press F2, hold down CTRL and SHIFT keys and at the same time press ENTER. Curly braces like { and } should appear around the formula in formula bar and you should get desired result.....

The blanks are causing the problem. I replace them with a letter and it works.

Anyway we can get it to ignore blank cells?

Thanks so much for your time.

If you copied and pasted the exact formula above posted most recently by daddylonglegs, and the blanks are causing a #VALUE! error, then it sounds like you are not entering it with a CTRL + SHIFT + ENTER.

After you enter the formula, go out of the cell, then go back into it, looking in the formula bar, is the formula enclosed in { } brackets? If not, you are not using CTRL + SHIFT + ENTER as stated above, which is required for an array formula. To do this, select F2 to edit the cell, then while holding down CTRL and SHIFT, hit the ENTER key.

See attached picture. I emulated your situation using VLOOKUP, as well as leaving a blank cell, and the formula is working just fine. If you are still having problems getting it to work, post it up and we can figure out what went wrong.

Ok, here is the workbook sample to help solve this vile thing.

Thanks!

The problem was that you were using daddylonglegs's FIRST suggested formula, as opposed to the last one that properly handles blank and error cells. The formula you should be using is:

=CHAR(ROUND(AVERAGE(IF(ISTEXT(B4:U4),IF(B4:U4<>"", CODE(B4:U4)))),0))

See attached file. It is working.

Jason

Whoohoo! That did it. Thanks again to the BOTH of you!

Good to see it is working for you. Glad to help.