+ Reply to Thread
Results 1 to 6 of 6

Grade book average formula

  1. #1
    Will in SF
    Guest

    Grade book average formula

    Hi.

    I need a formula that averages grades with the following values...
    A=4,B=3,C=2,D=1,F=0. I am entering the actual letter grades in the cell.

  2. #2
    Biff
    Guest

    Re: Grade book average formula

    Hi!

    You'd be better off just using a number system instead of using a letter
    system then converting to a number system.

    But anyhow, try this formula entered as an array using the key combo of
    CTRL,SHIFT,ENTER:

    =AVERAGE(IF(A1:J1="A",4,IF(A1:J1="B",3,IF(A1:J1="C",2,IF(A1:J1="D",1,IF(A1:J1="F",0))))))

    Biff

    "Will in SF" <Will in [email protected]> wrote in message
    news:[email protected]...
    > Hi.
    >
    > I need a formula that averages grades with the following values...
    > A=4,B=3,C=2,D=1,F=0. I am entering the actual letter grades in the cell.




  3. #3
    JE McGimpsey
    Guest

    Re: Grade book average formula

    One way:

    =(COUNTIF(A1:Z1,"A")*4+COUNTIF(A1:Z1,"B")*3+COUNTIF(A1:Z1,"C")*2
    +COUNTIF(A1:Z1,"D"))/COUNTA(A1:Z1)

    In article <[email protected]>,
    "Will in SF" <Will in [email protected]> wrote:

    > Hi.
    >
    > I need a formula that averages grades with the following values...
    > A=4,B=3,C=2,D=1,F=0. I am entering the actual letter grades in the cell.


  4. #4
    Dana DeLouis
    Guest

    Re: Grade book average formula

    If you use Data | Validation to ensure that you only have A,B,C,D or F (& no
    blanks), then perhaps this Array formula...

    =AVERAGE(MOD(2530,CODE(A1:A5)-59))

    HTH :>)
    --
    Dana DeLouis
    Win XP & Office 2003


    "Will in SF" <Will in [email protected]> wrote in message
    news:[email protected]...
    > Hi.
    >
    > I need a formula that averages grades with the following values...
    > A=4,B=3,C=2,D=1,F=0. I am entering the actual letter grades in the cell.




  5. #5
    Roger Govier
    Guest

    Re: Grade book average formula

    Hi Dana

    Very neat solution!!
    I had been playing about with
    {=AVERAGE(69-CODE(J1:J5))}
    but the lack of "E" in the range of Grades being used gave me the wrong
    results, as "F ended up as a -1.

    I can't work out how you came up with the 2530 to use as the dividend in
    the formula?

    Regards

    Roger Govier



    Dana DeLouis wrote:

    >If you use Data | Validation to ensure that you only have A,B,C,D or F (& no
    >blanks), then perhaps this Array formula...
    >
    >=AVERAGE(MOD(2530,CODE(A1:A5)-59))
    >
    >HTH :>)
    >
    >


  6. #6
    Will in SF
    Guest

    Re: Grade book average formula



    Thanks to everyone that posted to this question.

    I am going to check the formulas out.

    Biff- I tried yours but it didn't work. I don't know what you meant by
    (array using shift control delete keys.

    So here's how this information is listed on a report that I have to avg. the
    grades for.

    Col. A Col. B Col C. Col. D. Col E Col.
    F Col. g GPA
    Student Name I.D. Grade Grade Grade Grade Grade

    Students have anywhere from 1 class to 5 class, meaning averaging based on
    letter grades advised and based on different number of classes taken.

    Any suggestions as to how I can do this.

    thanks.

    "Dana DeLouis" wrote:

    > If you use Data | Validation to ensure that you only have A,B,C,D or F (& no
    > blanks), then perhaps this Array formula...
    >
    > =AVERAGE(MOD(2530,CODE(A1:A5)-59))
    >
    > HTH :>)
    > --
    > Dana DeLouis
    > Win XP & Office 2003
    >
    >
    > "Will in SF" <Will in [email protected]> wrote in message
    > news:[email protected]...
    > > Hi.
    > >
    > > I need a formula that averages grades with the following values...
    > > A=4,B=3,C=2,D=1,F=0. I am entering the actual letter grades in the cell.

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1