+ Reply to Thread
Results 1 to 5 of 5

Increasing a range of grades available

  1. #1
    confused teacher
    Guest

    Increasing a range of grades available

    Hi

    I am trying to get a formula to work that will assign a grade to a cell
    based on a numeric result. As as an example if a student scores anything
    above 84.99 (grade recorded in Cell D8) the grade of HD is assigned by the
    formula listed below.

    =IF(D8>84.99,"HD",IF(D8>74.99,"D",IF(D8>64.99,"C",IF(D8>49.99,"P",IF(D8>42.99,"PC",IF(D8>=0.1,"F"))))))

    I need to be able to split these broad grades into upper and lower ranges,
    for instance a student who scores 96 will get a grade of HD+ but the formula
    above doesn't recognise this. Similarly a student might get 82 which equates
    to a D+.

    I need a formula that will lookup a grade in cell D8 and assign a letter
    grade based on the following:
    HD+ = >95; HD = >=90; HD = >=85; D+ = >=82; D = >=80; D- = >=75; C+ = >=72;
    C = >=70; C- = >=65; P+ = >=60; P = >=55; P- = >=50; PC = >=43; F = <=42

    Any ideas would be greatly appreciated

    Thanks Brian

  2. #2
    MartinW
    Guest

    Re: Increasing a range of grades available

    Hi confused teacher,

    One way would be to put the numbers 1 to 100 in column A
    and in column B put the corresponding grade then use a
    VLOOKUP formula to access it.
    You could hide the lookup table or put it in a separate worksheet.

    HTH
    Martin



  3. #3
    Biff
    Guest

    Re: Increasing a range of grades available

    Hi!

    Create a 2 column table:

    ............A............B
    1.........0............F
    2........43...........PC
    3........50...........P-
    4........55...........P
    5........60...........P+
    6........65..........C-
    ...
    13......95..........HD+

    Then use this formula:

    =IF(D8="","",VLOOKUP(D8,A1:B13,2))

    Biff

    "confused teacher" <[email protected]> wrote in
    message news:[email protected]...
    > Hi
    >
    > I am trying to get a formula to work that will assign a grade to a cell
    > based on a numeric result. As as an example if a student scores anything
    > above 84.99 (grade recorded in Cell D8) the grade of HD is assigned by the
    > formula listed below.
    >
    > =IF(D8>84.99,"HD",IF(D8>74.99,"D",IF(D8>64.99,"C",IF(D8>49.99,"P",IF(D8>42.99,"PC",IF(D8>=0.1,"F"))))))
    >
    > I need to be able to split these broad grades into upper and lower ranges,
    > for instance a student who scores 96 will get a grade of HD+ but the
    > formula
    > above doesn't recognise this. Similarly a student might get 82 which
    > equates
    > to a D+.
    >
    > I need a formula that will lookup a grade in cell D8 and assign a letter
    > grade based on the following:
    > HD+ = >95; HD = >=90; HD = >=85; D+ = >=82; D = >=80; D- = >=75; C+ =
    > >=72;

    > C = >=70; C- = >=65; P+ = >=60; P = >=55; P- = >=50; PC = >=43; F = <=42
    >
    > Any ideas would be greatly appreciated
    >
    > Thanks Brian




  4. #4
    confused teacher
    Guest

    Re: Increasing a range of grades available


    Thanks, This is a great help.

    Brian
    "Biff" wrote:

    > Hi!
    >
    > Create a 2 column table:
    >
    > ............A............B
    > 1.........0............F
    > 2........43...........PC
    > 3........50...........P-
    > 4........55...........P
    > 5........60...........P+
    > 6........65..........C-
    > ...
    > 13......95..........HD+
    >
    > Then use this formula:
    >
    > =IF(D8="","",VLOOKUP(D8,A1:B13,2))
    >
    > Biff
    >
    > "confused teacher" <[email protected]> wrote in
    > message news:[email protected]...
    > > Hi
    > >
    > > I am trying to get a formula to work that will assign a grade to a cell
    > > based on a numeric result. As as an example if a student scores anything
    > > above 84.99 (grade recorded in Cell D8) the grade of HD is assigned by the
    > > formula listed below.
    > >
    > > =IF(D8>84.99,"HD",IF(D8>74.99,"D",IF(D8>64.99,"C",IF(D8>49.99,"P",IF(D8>42.99,"PC",IF(D8>=0.1,"F"))))))
    > >
    > > I need to be able to split these broad grades into upper and lower ranges,
    > > for instance a student who scores 96 will get a grade of HD+ but the
    > > formula
    > > above doesn't recognise this. Similarly a student might get 82 which
    > > equates
    > > to a D+.
    > >
    > > I need a formula that will lookup a grade in cell D8 and assign a letter
    > > grade based on the following:
    > > HD+ = >95; HD = >=90; HD = >=85; D+ = >=82; D = >=80; D- = >=75; C+ =
    > > >=72;

    > > C = >=70; C- = >=65; P+ = >=60; P = >=55; P- = >=50; PC = >=43; F = <=42
    > >
    > > Any ideas would be greatly appreciated
    > >
    > > Thanks Brian

    >
    >
    >


  5. #5
    Biff
    Guest

    Re: Increasing a range of grades available

    You're welcome!

    Biff

    "confused teacher" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks, This is a great help.
    >
    > Brian
    > "Biff" wrote:
    >
    >> Hi!
    >>
    >> Create a 2 column table:
    >>
    >> ............A............B
    >> 1.........0............F
    >> 2........43...........PC
    >> 3........50...........P-
    >> 4........55...........P
    >> 5........60...........P+
    >> 6........65..........C-
    >> ...
    >> 13......95..........HD+
    >>
    >> Then use this formula:
    >>
    >> =IF(D8="","",VLOOKUP(D8,A1:B13,2))
    >>
    >> Biff
    >>
    >> "confused teacher" <[email protected]> wrote in
    >> message news:[email protected]...
    >> > Hi
    >> >
    >> > I am trying to get a formula to work that will assign a grade to a cell
    >> > based on a numeric result. As as an example if a student scores
    >> > anything
    >> > above 84.99 (grade recorded in Cell D8) the grade of HD is assigned by
    >> > the
    >> > formula listed below.
    >> >
    >> > =IF(D8>84.99,"HD",IF(D8>74.99,"D",IF(D8>64.99,"C",IF(D8>49.99,"P",IF(D8>42.99,"PC",IF(D8>=0.1,"F"))))))
    >> >
    >> > I need to be able to split these broad grades into upper and lower
    >> > ranges,
    >> > for instance a student who scores 96 will get a grade of HD+ but the
    >> > formula
    >> > above doesn't recognise this. Similarly a student might get 82 which
    >> > equates
    >> > to a D+.
    >> >
    >> > I need a formula that will lookup a grade in cell D8 and assign a
    >> > letter
    >> > grade based on the following:
    >> > HD+ = >95; HD = >=90; HD = >=85; D+ = >=82; D = >=80; D- = >=75; C+ =
    >> > >=72;
    >> > C = >=70; C- = >=65; P+ = >=60; P = >=55; P- = >=50; PC = >=43; F =
    >> > <=42
    >> >
    >> > Any ideas would be greatly appreciated
    >> >
    >> > Thanks Brian

    >>
    >>
    >>




+ 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