I need to calculate the average for a row where the entries are text a, b, c or d

Try this array formula:

=CHOOSE(ROUND(AVERAGE(IF(A1:A10<>"",MATCH(A1:A10,{"F","D","C","B","A"},0),"")),0),"F","D","C","B","A")

Enter by pressing CTRL+SHIFT+ENTER.

Kevin

If those values were in A2:A6, you could use:
=CHAR(INT(AVERAGE(CODE(A2:A6))))
which needs to be array-entered by pressing Ctrl+Shit+Enter rather than just Enter.
Regards,
Rory

Assuming your data in range A1:A34, here is your average in letters:
=MID(ADDRESS(1,ROUND((COUNTIF(A1:A34,"A")+COUNTIF(A1:A34,"B")*2+COUNTIF(A1:A34,"C")*3+COUNTIF(A1:A34,"D")*4)/COUNTA(A1:A34),0)),2,1)

Thomas

I was slower and inelegant, what can I say...

Warning with Rory's formula, which is the shortest:
Make sure that all your values are the same case (either lower or upper). Otherwise, you'll get strange results.

Here is a more robust solution that considers "+" and "-" grades:

=CHOOSE(ROUND(AVERAGE(IF(A1:A10<>"",MATCH(A1:A10,{"A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F+","F","F-"},0),"")),0),"A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F+","F","F-")

Still an array formula. The formula assumes that the grades being evaluated are in cells A1:A10. Note that the above formula will also work with just A, B, C, and D grades but will give a more granular average with a + or -.

Kevin

It's past Rory's bedtime.

To adjust Rory's past bedtime formula, you can use
=CHAR(INT(AVERAGE(CODE(UPPER(A2:A34)))))
which is still shorter than Kevin's formula, but doesn't take into account + and -.

Thomas

=CHAR(INT(AVERAGE(CODE(UPPER(A2:A6)))))

and it is well past my bedtime so I will leave you all to it now! :)

Rory

Shorter is not always better. Or is that longer is not always better...dang, being an Excel jock is soooo confusing.

Indeed Dr. DDEJ. Size does not matter in the  Excel zone.

Inside jokes are so much fun.

Experts' fringe benefits.

Question: How many Experts does it take to write a formula?
Answer: Ask a question and then ignore it for a few hours to find out.

TDEJ

Thank you all for the quick replies.
I tried Kevins samples in cell G635 but get a "# value" so I have attached the file.
I want to get the value in column G to reflect the average of the row 3 below labeled flight.
I will copy it into each row needed



Book1.zip

For cell G635:

=CHOOSE(ROUND(AVERAGE(IF(K638:AE638<>"",MATCH(K638:AE638,{"F","D","C","B","A"},0),"")),0),"F","D","C","B","A")

Kevin

It's not the size of the formula, it's how you enter it that counts. ;)

Go to bed Rory.

Oh Gawd! ROTFLMAO!!!

I'm going, I'm going! ;^P

Thank you all for the help and a lesson
I never knew there was a Ctrl Shft Entr for array formulas.

It should make it in any FAQs on array formulas, counting on the badger for that.  

Thanks for the assist, even if I didn't really deserve it, I'm just basking in the light of the true Great Ones.

Thomas

After looking at the posted workbook, it appears that the cells being averaged contain either A, B, C, D or nothing. There is fortunately no need to deal with lower case, +, - or F.

I note that the CHAR function inherently incorporates an INT, and has the result of always rounding down to a lower grade. In other words, CHAR(65.999) is the same as CHAR(65). Assuming that you want to round up or down, all you need to do is add a half to the average, then put the result into the CHAR function.

This suggests the following array formula:
=CHAR(AVERAGE(IF(K6:AE6="","",CODE(K6:AE6)))+1/2)             returns #VALUE! if you don't CTRL + Shift + Enter

The above formula returns an average of B if there are equal numbers of A and B. If you want to round up such a perfectly split average, then change the 1/2 to 0.499
=CHAR(AVERAGE(IF(K6:AE6="","",CODE(K6:AE6)))+.499)             returns #VALUE! if you don't CTRL + Shift + Enter


Brad