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.

One play ..

If you enter it in sequence in A1 down:

A+
A
A-
B+
B
B-
etc

and you have the grades in say, D1:F1 : A, B+, C-

then you could use this in G1:
=SUMPRODUCT(--MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
G1 will return the numeric "average" of: 5

As-is, G1 can be copied down to return correspondingly for grades in D2:F2,
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote:
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.

THANK YOU Max!
that's wonderful! Can you please explain the formula to me so that I can
fully understand it and then replicate it with more scores etc. I used it in
a simple example but am unable to apply it to my already existing markbooks.
Do I have to have the A+, A, A- etc down the A column?
You're a star
Abigail


"Max" wrote:

One play ..

If you enter it in sequence in A1 down:

A+
A
A-
B+
B
B-
etc

and you have the grades in say, D1:F1 : A, B+, C-

then you could use this in G1:
=SUMPRODUCT(--MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
G1 will return the numeric "average" of: 5

As-is, G1 can be copied down to return correspondingly for grades in D2:F2,
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote:
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.

Think the double minus "--(...)" is not necessary here

In G1:
=SUMPRODUCT(MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
will do the job
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks, I took the double minus (--) out, but I still cant' get it to work.
Can you please explain the formula to me because i cannot get it to work.
When I put in the M column (I have A column full of students' names)
A+
A
A- etc
so i subsitute the A in your formula for the m column in my worksheet.
and then have 5 marks on an A-E scale in columns b2:f2
i substitute the B2:f2 where you have d1:f1 in both places.

This is what i substituted in the end:
=SUMPRODUCT(MATCH(B2:F2,M:M,0))/COUNTm(B2:F2)
but it still does not work!
could you please explain each particular part to me then i will understand
it more fully. I am usually good with this stuff and i'm sure it's something
really simple i am doing wrong
Abi


"Max" wrote:

Think the double minus "--(...)" is not necessary here

In G1:
=SUMPRODUCT(MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
will do the job
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

=SUMPRODUCT(MATCH(B2:F2,M:M,0))/COUNTm(B2:F2)

COUNTA is a function, nothing to do with col A <g

Just try changing it to:
=SUMPRODUCT(MATCH(B2:F2,M:M,0))/COUNTA(B2:F2)

Here's some explanations:

Do I have to have the A+, A, A- etc down the A column?

No, if you have it listed in B1 down,
then the formula in G1 would simply be:
=SUMPRODUCT(MATCH(D1:F1,B:B,0))/COUNTA(D1:F1)

But if you start your A+, A, A- etc in a cell other than row1, then the
match array has to be fixed. Eg suppose A+, A, A- etc is entered in B2:B20,
say, then the formula in G1 should be:
=SUMPRODUCT(MATCH(D1:F1,$B$2:$B$20,0))/COUNTA(D1:F1)

Based on what you posted we're just conveniently using the match positions
as the score here. We're not actually doing any lookup on the letters
viz-a-vis their corresponding numeric scores.

MATCH(D1:F1,A:A,0) returns an array of matched positions for the inputs in
D1:F1 within the list in A1 down, ie: {2,4,9}
which nicely corresponds to the equivalent scores for the letters

SUMPRODUCT(MATCH(...)) then adds up the array to return: 2+4+9 = 15

COUNTA(D1:F1) simply counts the filled cells in D1:F1 and returns the
denominator: 3 for the average to be calculated.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote in message
...
Thanks, I took the double minus (--) out, but I still cant' get it to
work.
Can you please explain the formula to me because i cannot get it to work.
When I put in the M column (I have A column full of students' names)
A+
A
A- etc
so i subsitute the A in your formula for the m column in my worksheet.
and then have 5 marks on an A-E scale in columns b2:f2
i substitute the B2:f2 where you have d1:f1 in both places.

This is what i substituted in the end:
=SUMPRODUCT(MATCH(B2:F2,M:M,0))/COUNTm(B2:F2)
but it still does not work!
could you please explain each particular part to me then i will understand
it more fully. I am usually good with this stuff and i'm sure it's
something
really simple i am doing wrong
Abi

Thanks Max
I've got it now. I actually had an error in a cell which was mucking things
up. But thanks for clarifying. It's much easier to reapply when I understand
it.
VERY GRATEFUL!!!
Abi

"Max" wrote:

=SUMPRODUCT(MATCH(B2:F2,M:M,0))/COUNTm(B2:F2)

COUNTA is a function, nothing to do with col A <g

Just try changing it to:
=SUMPRODUCT(MATCH(B2:F2,M:M,0))/COUNTA(B2:F2)

Here's some explanations:

Do I have to have the A+, A, A- etc down the A column?

No, if you have it listed in B1 down,
then the formula in G1 would simply be:
=SUMPRODUCT(MATCH(D1:F1,B:B,0))/COUNTA(D1:F1)

But if you start your A+, A, A- etc in a cell other than row1, then the
match array has to be fixed. Eg suppose A+, A, A- etc is entered in B2:B20,
say, then the formula in G1 should be:
=SUMPRODUCT(MATCH(D1:F1,$B$2:$B$20,0))/COUNTA(D1:F1)

Based on what you posted we're just conveniently using the match positions
as the score here. We're not actually doing any lookup on the letters
viz-a-vis their corresponding numeric scores.

MATCH(D1:F1,A:A,0) returns an array of matched positions for the inputs in
D1:F1 within the list in A1 down, ie: {2,4,9}
which nicely corresponds to the equivalent scores for the letters

SUMPRODUCT(MATCH(...)) then adds up the array to return: 2+4+9 = 15

COUNTA(D1:F1) simply counts the filled cells in D1:F1 and returns the
denominator: 3 for the average to be calculated.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote in message
...
Thanks, I took the double minus (--) out, but I still cant' get it to
work.
Can you please explain the formula to me because i cannot get it to work.
When I put in the M column (I have A column full of students' names)
A+
A
A- etc
so i subsitute the A in your formula for the m column in my worksheet.
and then have 5 marks on an A-E scale in columns b2:f2
i substitute the B2:f2 where you have d1:f1 in both places.

This is what i substituted in the end:
=SUMPRODUCT(MATCH(B2:F2,M:M,0))/COUNTm(B2:F2)
but it still does not work!
could you please explain each particular part to me then i will understand
it more fully. I am usually good with this stuff and i'm sure it's
something
really simple i am doing wrong
Abi

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

tHANKS

"Rick Rothstein (MVP - VB)" wrote:

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

Here is a slightly longer (3 characters) single formula solution, based on
the solution that Max posted, which should be easier to understand compared
to my first formula...

=IF(COUNTA(A1:A25)=0,"",SUMPRODUCT(MATCH(A1:A25&"" ,{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25))

This is **not** an array formula, so commit it by simply pressing the
<Enter key. And as before, change the A1:A25 range I used in my example to
the range containing the scores you want to average. And, again, as before,
the range can contain blank cells.

Rick


"Abigail" wrote in message
...
tHANKS

"Rick Rothstein (MVP - VB)" wrote:

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

And here, using the last formula I posted as a base, is a formula which
returns the average score for the specified range as a letter grade rather
than as a floating point value...

=CHOOSE(IF(COUNTA(A1:A25)=0,"1",1+SUMPRODUCT(MATCH (A1:A25&"",{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25)),"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-")

Note that this rounds fraction values downward. Hence, an average of 6.99
would evaluate to B- as the grade. If you have a different breakpoint, let
us know and someone here will see if the formula can be modified to account
for it.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a slightly longer (3 characters) single formula solution, based on
the solution that Max posted, which should be easier to understand
compared to my first formula...

=IF(COUNTA(A1:A25)=0,"",SUMPRODUCT(MATCH(A1:A25&"" ,{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25))

This is **not** an array formula, so commit it by simply pressing the
<Enter key. And as before, change the A1:A25 range I used in my example
to the range containing the scores you want to average. And, again, as
before, the range can contain blank cells.

Rick


"Abigail" wrote in message
...
tHANKS

"Rick Rothstein (MVP - VB)" wrote:

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

welcome, Abi.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote in message
...
Thanks Max
I've got it now. I actually had an error in a cell which was mucking
things
up. But thanks for clarifying. It's much easier to reapply when I
understand
it.
VERY GRATEFUL!!!
Abi

Thanks again, I'll have a good look at all of those today.
Abi

"Rick Rothstein (MVP - VB)" wrote:

And here, using the last formula I posted as a base, is a formula which
returns the average score for the specified range as a letter grade rather
than as a floating point value...

=CHOOSE(IF(COUNTA(A1:A25)=0,"1",1+SUMPRODUCT(MATCH (A1:A25&"",{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25)),"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-")

Note that this rounds fraction values downward. Hence, an average of 6.99
would evaluate to B- as the grade. If you have a different breakpoint, let
us know and someone here will see if the formula can be modified to account
for it.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a slightly longer (3 characters) single formula solution, based on
the solution that Max posted, which should be easier to understand
compared to my first formula...

=IF(COUNTA(A1:A25)=0,"",SUMPRODUCT(MATCH(A1:A25&"" ,{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25))

This is **not** an array formula, so commit it by simply pressing the
<Enter key. And as before, change the A1:A25 range I used in my example
to the range containing the scores you want to average. And, again, as
before, the range can contain blank cells.

Rick


"Abigail" wrote in message
...
tHANKS

"Rick Rothstein (MVP - VB)" wrote:

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

Dear Max
After your help last time, my marking has been SO much easier. Thanks a
million. I was wondering if I could twig the formula a little, so that as
well as finding the numerical average, it converts teh numbers back to grades
where
A+ = 1
A = 2
A- = 3
etc down to E-
It would save me so much time now that I am doing report cards.
So, to summarise, can I have all the grades average out back into a letter
grade somehow? Or can I add a new column which converts the numerical grade
into a letter grade?
Thanks so much
Abi

"Max" wrote:

One play ..

If you enter it in sequence in A1 down:

A+
A
A-
B+
B
B-
etc

and you have the grades in say, D1:F1 : A, B+, C-

then you could use this in G1:
=SUMPRODUCT(--MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
G1 will return the numeric "average" of: 5

As-is, G1 can be copied down to return correspondingly for grades in D2:F2,
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote:
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.

With the list of letter grades in A1:A15 and the average numeric grade in
G1:

=INDEX(A1:A15,G1)



--
Biff
Microsoft Excel MVP


"Abigail" wrote in message
...
Dear Max
After your help last time, my marking has been SO much easier. Thanks a
million. I was wondering if I could twig the formula a little, so that as
well as finding the numerical average, it converts teh numbers back to
grades
where
A+ = 1
A = 2
A- = 3
etc down to E-
It would save me so much time now that I am doing report cards.
So, to summarise, can I have all the grades average out back into a letter
grade somehow? Or can I add a new column which converts the numerical
grade
into a letter grade?
Thanks so much
Abi

"Max" wrote:

One play ..

If you enter it in sequence in A1 down:

A+
A
A-
B+
B
B-
etc

and you have the grades in say, D1:F1 : A, B+, C-

then you could use this in G1:
=SUMPRODUCT(--MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
G1 will return the numeric "average" of: 5

As-is, G1 can be copied down to return correspondingly for grades in
D2:F2,
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote:
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 guess you didn't see my last posting in response to your original
message... I gave you a formula which did that (although there was a proviso
which you didn't address). Here, again, is the message/formula I posted back
then...

"And here, using the last formula I posted as a base, is a formula which
returns the average score for the specified range as a letter grade rather
than as a floating point value...

=CHOOSE(IF(COUNTA(A1:A25)=0,"1",1+SUMPRODUCT(MATCH (A1:A25&"",{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25)),"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-")

Note that this rounds fraction values downward. Hence, an average of 6.99
would evaluate to B- as the grade. If you have a different breakpoint, let
us know and someone here will see if the formula can be modified to account
for it."

Rick


"Abigail" wrote in message
...
Dear Max
After your help last time, my marking has been SO much easier. Thanks a
million. I was wondering if I could twig the formula a little, so that as
well as finding the numerical average, it converts teh numbers back to
grades
where
A+ = 1
A = 2
A- = 3
etc down to E-
It would save me so much time now that I am doing report cards.
So, to summarise, can I have all the grades average out back into a letter
grade somehow? Or can I add a new column which converts the numerical
grade
into a letter grade?
Thanks so much
Abi

"Max" wrote:

One play ..

If you enter it in sequence in A1 down:

A+
A
A-
B+
B
B-
etc

and you have the grades in say, D1:F1 : A, B+, C-

then you could use this in G1:
=SUMPRODUCT(--MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
G1 will return the numeric "average" of: 5

As-is, G1 can be copied down to return correspondingly for grades in
D2:F2,
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote:
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.

Dear Rick
Thanks for your message. That is SO handy, THANKS!
Can you please explain how each part of it works so that I can understand it
and apply it, and so that I know which parts to change when I paste it into
different worksheets. Also, how do I get a letter average when the data is
in squares that are not adjacent?
Abi

"Rick Rothstein (MVP - VB)" wrote:

I guess you didn't see my last posting in response to your original
message... I gave you a formula which did that (although there was a proviso
which you didn't address). Here, again, is the message/formula I posted back
then...

"And here, using the last formula I posted as a base, is a formula which
returns the average score for the specified range as a letter grade rather
than as a floating point value...

=CHOOSE(IF(COUNTA(A1:A25)=0,"1",1+SUMPRODUCT(MATCH (A1:A25&"",{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25)),"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-")

Note that this rounds fraction values downward. Hence, an average of 6.99
would evaluate to B- as the grade. If you have a different breakpoint, let
us know and someone here will see if the formula can be modified to account
for it."

Rick


"Abigail" wrote in message
...
Dear Max
After your help last time, my marking has been SO much easier. Thanks a
million. I was wondering if I could twig the formula a little, so that as
well as finding the numerical average, it converts teh numbers back to
grades
where
A+ = 1
A = 2
A- = 3
etc down to E-
It would save me so much time now that I am doing report cards.
So, to summarise, can I have all the grades average out back into a letter
grade somehow? Or can I add a new column which converts the numerical
grade
into a letter grade?
Thanks so much
Abi

"Max" wrote:

One play ..

If you enter it in sequence in A1 down:

A+
A
A-
B+
B
B-
etc

and you have the grades in say, D1:F1 : A, B+, C-

then you could use this in G1:
=SUMPRODUCT(--MATCH(D1:F1,A:A,0))/COUNTA(D1:F1)
G1 will return the numeric "average" of: 5

As-is, G1 can be copied down to return correspondingly for grades in
D2:F2,
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abigail" wrote:
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.

Thanks for your message. That is SO handy, THANKS!
Can you please explain how each part of it works so that I can understand
it
and apply it, and so that I know which parts to change when I paste it
into
different worksheets. Also, how do I get a letter average when the data
is
in squares that are not adjacent?

Let's take your last question first. If, by "data is in squares that are not
adjacent", you mean within column A... that is no problem... the formula
will not be fooled by empty cells within the given range (A1:A25). By the
way, you are not restricted to the cells within A1 to A25... as long as you
change each reference to that range, you can make the span of cells cover
any distance within column A that you want (for example, A1:A1000) whether
there is data in them or not.

Okay, now for your main question... there is a lot going on in the formula I
posted, so I will try to explain them in segments. First, the COUNTA
function will give a count of cell that are not empty. We need that count to
find out how many cells have a value (a letter grade) in them so that we can
calculate an average. To get the average, we will need to sum up the grades
within the range. The formula does this summation process using the
SUMPRODUCT function.

Before we can look at the SUMPRODUCT function, we have to talk about the
MATCH function which is used within it. The MATCH function takes a single
value in its first argument and looks for an exact match (the 0 in the third
argument tells it to do this) in the array of elements that are placed in
the second argument (within the curly braces). The return value is the
number of the element (its relative position within the list) that it
matched. Now, we have placed the MATCH function within a SUMPRODUCT and
specified a range (not a single value) for the first argument. This means
the SUMPRODUCT, which is an array-based function, will perform a match
operation for the value contained in *each* cell of the range, one at a
time; it will couple this with any other operations being done to the
results before summing them up.

Okay, let's look at the SUMPRODUCT function part of the formula and see what
is happening.

SUMPRODUCT(MATCH(A1:A25&"",{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)

Okay, so each cell in the range A1:A25 will be fed one at a time into the
MATCH function; but the possibility exists that there could be one or more
non-filled cells in that range (which would force the MATCH function to
error out)... to account for this, I concatenate the empty string ("") onto
the each value. Doing this will have no effect if the cell has a letter
grade in it, but it will give the MATCH function a non-empty string value
(albeit, the empty string) to work with. To compensate for the possibility
of MATCH searching with the empty string, we add the empty string to the
list of strings in the array (the part in the curly braces). Now, since you
wanted A+ to have the value 1, I added the empty string to the beginning of
the list and then subtract 1 from the result returned by the MATCH function.
That means that each cell in the range A1:A25 will return 0 for the empty
string, 1 for A+, 2 for A, 3 for A-, 4 for B+, etc. and that SUMPRODUCT will
add up each of these values. If you look at the formula, you will see we
then divide this sum by the result of the COUNTA function... this gives us
our average for all the grades. Only real grade values will have add
anything to the summation (the empty cells add 0 which means they don't
count for anything) and COUNTA will only count non-empty cells. Hence, the
SUMPRODUCT, as constructed, divided by the COUNTA, as constructed, returns
the numerical average of the equivalent numerical values on your letter
grades.

Okay, so now we have an average value... how do we turn that into a letter
score. For that, we use the CHOOSE function. The way CHOOSE works is that it
takes an integer value, starting at 1, and looks up the value in its first
argument in the array of values that are placed in its second argument.
Well, we have to discuss that IF function call in the formula. To protect
the function against a new student (or a beginning of school year
situation), we need to do something for the condition when all cells in the
range A1:A25 are empty. We use COUNTA again to see if any cells have a value
in them. If not, we have the IF function return 1 and we put the empty
string in the first position of the array of values so that nothing is shown
when no cells have values. The rest of the list is the same as the array
used in the MATCH function, but notice that each letter grade is in a
position that is one greater than its actual numerical equivalent (the empty
string at the beginning, being number 1 in the list, did that), so we add 1
to the average numerical value produced by dividing the SUMPRODUCT function
by the COUNTA function and use that value to find the letter grade
equivalent to the numerical value.

Only one more thing to discuss and we are done. The CHOOSE function uses an
integer value for its look up value (1 corresponds to the first item in the
list, 2 for the second, and so on), but the value produced by adding 1 to
the average value returned by the division is usually going to be a floating
point value. The CHOOSE function handles this by using only the integer
portion of the value and truncating off any decimal digits that may be
there. That is why I gave you the caution about a average value of 6.99
being evaluated as a B- grade because the CHOOSE function will truncate the
7.99 (6.99 average value plus 1 for the offset added by accounting for the
empty string) down to 7 and find B- in the 7th position of the array.

Hopefully, the above is clear enough. I didn't realize how much I was doing
in my head as I constructed the formula or how long it would take me to
explain the process. Please feel free to ask about anything I've said that
may still be unclear to you.

Rick

Rick,
You have been so helpful, but here I am doing senior grades again and I am
having trouble getting the formula to work. it keeps coming up with #N/A. I
have tried substituting the cells (as per example below) F2:L2, for example,
but it still comes up with the #N/A answer and I can't work out why.

I want to average out letter grades on a horizontal line, and I liked that
you were trying to allow me to do it with a letter average instead of the
original number I was looking for.
I am also interested in knowing how to give more weight to some answers than
others. Here is the first student's marks that I am trying to work out:
B D- E E C C+ C C- E- D E-
Firstly, I want to average out F to L column. Then I want to average, C, D,
E and M column, which were harder questions and should have more weighting. I
can move the L column to be next to the C,D or E column if that would make it
easier. So, once I have the average for the easier and harder questions, I
want to add more weight to the harder ones, say, 1.5 or twice as hard, and
then find the letter grade average of those.

Would you please explain to me in step by step detail, as if I am a complete
novice to Excel (which it seems I am) so that I can stop bothering you.
Thanks a million!
Abi

"Rick Rothstein (MVP - VB)" wrote:

And here, using the last formula I posted as a base, is a formula which
returns the average score for the specified range as a letter grade rather
than as a floating point value...

=CHOOSE(IF(COUNTA(A1:A25)=0,"1",1+SUMPRODUCT(MATCH (A1:A25&"",{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25)),"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-")

Note that this rounds fraction values downward. Hence, an average of 6.99
would evaluate to B- as the grade. If you have a different breakpoint, let
us know and someone here will see if the formula can be modified to account
for it.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a slightly longer (3 characters) single formula solution, based on
the solution that Max posted, which should be easier to understand
compared to my first formula...

=IF(COUNTA(A1:A25)=0,"",SUMPRODUCT(MATCH(A1:A25&"" ,{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)/COUNTA(A1:A25))

This is **not** an array formula, so commit it by simply pressing the
<Enter key. And as before, change the A1:A25 range I used in my example
to the range containing the scores you want to average. And, again, as
before, the range can contain blank cells.

Rick


"Abigail" wrote in message
...
tHANKS

"Rick Rothstein (MVP - VB)" wrote:

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