Average

A

Adel Handal

Hi,
In cells G21 and J21 are grades calculated from another sheet in the same
wrokbook.
The grades are NOT numbers but letters: E+, C, D+, ..... (letter system)
In cell K21 i want to have the average of the two cells G21 and J21.
What formula should I use? The regular "average function" is giving an empty
cell.
 
P

Patrick

Hi Adel,
you need to recode your letter grades to numeric grades. e.g. A=4 etc. You
cannot calculate an average on letter grades.
 
R

RagDyeR

Post the formulas in G21 and J21.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hi,
In cells G21 and J21 are grades calculated from another sheet in the same
wrokbook.
The grades are NOT numbers but letters: E+, C, D+, ..... (letter system)
In cell K21 i want to have the average of the two cells G21 and J21.
What formula should I use? The regular "average function" is giving an empty
cell.
 
K

Khalil Handal

Formula in G21 is:
=LOOKUP(IF(ISERR(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Formula in J21 is:
=LOOKUP(IF(ISERR(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Hope this help.
 
R

RagDyeR

This will give the numerical average:

=AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,),),INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,),))

If you want the alpha average, just incorporate it into your existing
formulas.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Formula in G21 is:
=LOOKUP(IF(ISERR(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Formula in J21 is:
=LOOKUP(IF(ISERR(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Hope this help.
 
K

Khalil Handal

Thanks a lot.

RagDyeR said:
This will give the numerical average:

=AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,),),INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,),))

If you want the alpha average, just incorporate it into your existing
formulas.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Formula in G21 is:
=LOOKUP(IF(ISERR(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Formula in J21 is:
=LOOKUP(IF(ISERR(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Hope this help.
 
K

Khalil Handal

Hi,
I encorporate your formula in my existing formula but I think I am missing
something! when either of G21 or J21 shows nothing still I see the result
in K21.
The ISERR part is not wroking! I think it is in the BRACKET!!
Her is how the formula looks like:

=LOOKUP(IF(ISERR(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,),),INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,),))),"",AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,),),INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,),))),{"","";0,"F";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Any suggestions!
 
R

Ragdyer

If I understand what you're asking, you *don't* want to see an average in
K21, unless *both* G21 and J21 contain values.

So, try this:

=IF(AND(G21<>"",J21<>""),LOOKUP(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,),),INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,),)),{0,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"}),"")

I also noticed that your original formulas, in G21 and J21, returned #N/A
errors when the student's name couldn't be found.
Since this threw off my formula, I made some minor revisions to your
original formulas, which will now return blank cells if names are
non-existent:

In G21:

=LOOKUP(IF(ISERROR(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

In J21:

=LOOKUP(IF(ISERROR(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0))),"",(AVERAGE(INDEX(Marks!L:N,MATCH($A$3,Marks!$B:$B,0),0)))),{"","";0,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})
 
R

Ragdyer

You can eliminate those zeroes in the Match() functions of G21 and J21 if
you wish, to make those formulas the same as the first formula.
 
K

Khalil Handal

Hi,
Thanks a lot. It worked just fine.


Ragdyer said:
You can eliminate those zeroes in the Match() functions of G21 and J21 if
you wish, to make those formulas the same as the first formula.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top