Average

[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.

 

[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.

 

[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.

 

[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.

 

[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.

 

[Khalil Handal]

Thanks a lot.

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.

 

[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!

 

[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+"})

 

[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.

 

[Khalil Handal]

Hi,
Thanks a lot. It worked just fine.

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.

 

[Ragdyer]

You're welcome, and appreciate the feed-back.