I enter grades into Excel in an A-E format. I would like to create a number
average, where A+=1, A=2, A-=3 etc, so that it picks up an A, B+ and C-
average out to a numerical value of 5, which I could therefore give a B
overall to. Have wasted valuable hours on this.

I realize you have a solution that works, but I thought you might be
interested in seeing a direct formula that will calculate the average you
seek. It is an array formula, so you must commit it by pressing
Ctrl+Shift+<Enter

=IF(COUNTA(A1:A25)=0,"",SUM(IF(A1:A25="",0,3*(CODE (LEFT(A1:A25,1))-64)+IF(MID(A1:A25,2,1)="-",0,IF(MID(A1:A25,2,1)="+",-2,-1))))/COUNTA(A1:A25))

Simply change the 6 occurrences of the range I used for my example (A1:A25)
to the actual range containing the scores you want to average. You can set
this range to encompass current scores and blank cells reserved for future
scores.

Rick