ATIKA SCHOOL
  • Home
    • JOBS IN KENYA AND ABROAD FOR KENYANS AND BEYOND
    • News and Opinions
    • Atika School Advanced Search
    • How its Done
    • KNEC Portal
    • Academic Environment
    • KNEC KCSE PROJECT INSTRUCTIONS
    • SITEMAP
    • About Us? >
      • About Us
      • Learn more about us
  • OUR PRODUCTS
    • All Categories >
      • Category
      • FREE NOVELS >
        • COURSE BOOKS
        • KCSE KISWAHILI SETBOOKS
        • Backed Up Files and Archives
        • FREE DOCUMENTS
        • PRIMARY 8-4-4 BASED RESOURCES
        • STANDARD 8 RESOURCES
        • HIGH SCHOOL RESOURCES >
          • Biology Paper 3 Exams
          • Secondary Examinations
        • PRIMARY CBC BASED RESOURCES
        • COLLEGE & VARSITY RESOURCES
    • PRIMARY RESOURCES CBC
    • PRIMARY RESOURCES 8-4-4
    • SECONDARY RESOURCES
    • NOTES & TUTORIALS
    • COLLEGE RESOURCES
    • NOVELS and OTHER BOOKS
  • EXAMINATIONS
    • PRIMARY EXAMS >
      • Primary Assignments and Random Exams
      • CBC
      • 8-4-4
      • STANDARD 8 PAST PAPERS 8-4-4 BASED
    • MOCKS AND JOINT EXAMS >
      • FORM 1 QUESTION PAPERS, ANSWERS AND MARKING SCHEMES COLLECTIONS
      • FORM 2 QUESTION PAPERS, ANSWERS AND MARKING SCHEMES COLLECTIONS
      • FORM 3 EXAMINATIONS, QUESTION PAPERS AND ANSWERS
      • MOCKS PAST EXAMS BY REGION
    • KCPE >
      • KCPE BY YEAR >
        • KCPE 2020 RESULTS, REPORTS, QUESTIONS AND ANSWERS
        • KCPE PAST PAPERS 2019 REPORTS, RESULTS, QUESTIONS AND ANSWERS
      • KCPE PAST PAPERS PER SUBJECT
      • KCPE PAST PAPERS AND ANSWERS
      • KCPE MATHEMATICS TOPICAL QUESTIONS FROM 1989 TO LAST YEAR
    • KCSE >
      • KCSE BY YEAR >
        • KNEC KCSE PAST PAPERS, MARKING SCHEMES, QUESTIONS & ANSWERS
      • KCSE BY SUBJECT >
        • KCSE PAST PAPERS BY SUBJECT
        • Free KCSE Past Papers Mathematics
        • Free KCSE Biology Questions and Answers
    • QUESTIONS & ANSWERS >
      • SECONDARY >
        • LANGUAGES >
          • A DOLL'S HOUSE BY HENRIK IBSEN RESOURCES
        • TECHNICALS >
          • COMPUTER STUDIES >
            • COMPUTER STUDIES Q & A
            • kcse computer studies paper 2 AND 3 Questions & Answers
          • BUSINESS STUDIES QUESTIONS & ANSWERS
          • KCSE AGRICULTURE TOPICAL QUESTIONS AND ANSWERS
          • KCSE HOME SCIENCE DIRECT QUESTIONS AND ANSWERS
        • SCIENCES >
          • KCSE Mathematics Topical Questions
          • KCSE Biology Topical Questions and Answers
          • CHEMISTRY TOPICAL QUESTIONS AND ANSWERS
          • KCSE Physics Topical Questions
          • KCSE physics Practical Sample Quiz
        • HUMANITIES >
          • ISLAMIC RELIGIOUS EDUCATION (IRE) QUESTIONS AND ANSWERS
          • KCSE History Topical Questions and Answers
          • GEOGRAPHY TOPICAL QUESTIONS AND ANSWERS
      • PRIMARY >
        • Standard 4-8 Mathematics Topical Questions from Mocks and KCPE
        • Std 6 Mathematics Notes
  • TUTORIALS
    • SECONDARY >
      • SCIENCES >
        • FOCUS MATHEMATICS TUTORIALS AND EXAMS FREE
        • KCSE BIOLOGY NOTES
        • FREE KCSE CHEMISTRY NOTES
        • KCSE PHYSICS NOTES, AUDIOVISUALS AND MORE
      • LANGUAGES >
        • ENGLISH >
          • English KCSE Set Books
          • ENGLISH POETRY QUESTIONS WITH ANSWERS FOR K.C.S.E CANDIDATES
          • ENGLISH GRAMMAR #KCSE
        • CHOZI LA HERI - MWONGOZO
        • KIGOGO - MWONGOZO
        • USHAIRI MASWALI NA MAJIBU
        • MASWALI NA MAJIBU YA TUMBO LISILOSHIBA
        • KCSE Kiswahili Fasihi
      • TECHNICALS >
        • HOME SCIENCE NOTES
        • KCSE BUSINESS STUDIES NOTES
        • COMPUTER STUDIES NOTES LATEST
        • kcse Computer Studies Notes
        • KCSE AGRICULTURE NOTES, SYLLABUS, QUESTIONS, ANSWERS, SCHEMES OF WORK AND OTHERS
      • HUMANITIES >
        • KCSE History Notes Form 1 to 4
        • FREE KCSE CRE NOTES
        • KCSE GEOGRAPHY NOTES
        • IRE NOTES, AUDIOVISUAL, QUESTIONS AND ANSWERS
    • PRIMARY >
      • NOTES >
        • KISWAHILI
        • SOCIAL STUDIES >
          • STANDARD 4: SOCIAL STUDIES
        • SCIENCE
        • ENGLISH >
          • KCPE and Primary Level Compositions
        • MATHEMATICS >
          • KCPE Mathematics Notes
      • EXAMINATIONS >
        • DecaTurbo Online Series Examinations
        • CBC Kiswahili
        • FOCUS A365 SERIES K.C.P.E EXAMINATIONS FOR PRIMARY
        • Standard 8 (std) English Topical Questions
    • Other Supportive Documents >
      • Free Schemes of Work
      • SYLLABUS >
        • Primary 8-4-4 based Syllabus, Objectives and Lessons
        • Primary Mathematics Syllabus
      • MASOMO VIDEOS
  • CURRICULUM
    • NEW CURRICULUM DESIGNS >
      • PRIMARY >
        • PRE PRIMARY ONE CURRICULUM WITH SYLLABUS
        • PRE PRIMARY TWO CURRICULUM WITH SYLLABUS
      • SECONDARY >
        • KNEC KCSE SYLLABUS AND COURSE OUTLINES
      • COLLEGE >
        • Regulations for the TIVET Craft and Diploma Business Examinations
  • HIGH INSTITUTIONS
    • EXAMINATIONS >
      • College and Varsity Past Papers online
    • TUTORIALS >
      • COLLEGE AND UNIVERSITY NOTES AND TUTORIALS
  • CUSTOMER CARE
    • PRICING
    • Contact
    • Help & Support
    • DONATE
    • MPESA FORM
  • BLOGS
    • ENGLISH - EKEGUSII DICTIONARY
  • MEMBERSHIP RESOURCES
    • TERM 3 2021 PRIMARY PAST REVISION PAPERS
    • SECONDARY EXAMINATIONS
  • Archived Pages
    • C.R.E (CRE) QUESTION AND ANSWERS
    • TOP SECONDARY EXAMS ALL SUBJECTS
  • Atika School
  • Membership products

HOW TO RANK AND GRADE STUDENT MARKS WITH EXCEL OR GOOGLE SHEETS. SIMPLE FORMULAE AND FUNCTIONS

5/12/2021

0 Comments

 
Ranking and grading student marks can sometimes be a tedious and confusing work given the formulae and functions involved. In this this manual, we will illustrate to you how to achieve this.
Consider the Mathematics grading system  below:

Mathematics Grading System

  • 0 - 24       E
  • 25 - 29     D-
  • 30 - 34     D
  • 35 - 39     D+
  • 40 - 44     C-
  • 45 - 49     C
  • 50 - 54     C+
  • 55 - 59     B-
  • ​60 - 64     B
  • 65 - 69     B+
  • 70 - 74     A-
  • 75 - 100   A

BASIC FUNCTIONS AND FORMULA IN EXCEL/SHEETS USED FOR GRADING

USING THE 'NESTED IF' FORMULA

Consider the Excel sheet below
open/download in excel sheet
To grade the average column, we will put this nested if formula in cell G2 and copy it downwards
​=IF(F2>=75,"A",IF(F2>=70,"A-", IF(F2>=65,"B+",IF(F2>=60,"B",IF(F2>=55,"B-",IF(F2>=50,"C+",IF(F2>=45,"C",IF(F2>=40,"C-",IF(F2>=35,"D+",IF(F2>=30,"D",IF(F2>=25,"D-","E")))))))))))
using the formula above, we get this result
using nested if in microsoft excel and google sheets
Sometimes, the above function may give wrong grades due to rounding  errors.
In rounding errors for example, a number such as 29.5 may be rounded off to 30 by the computer as instructed by the user. The user will see 30 but the computer will see 29.5 hence giving grades contrary to the users expectation. When such a challenge happens, use this formula.
Note: the formula references values in cell f3
​=IF(F3>=74.5,"A",IF(F3>=69.5,"A-", IF(F3>=64.5,"B+",IF(F3>=59.5,"B",IF(F3>=54.5,"B-",IF(F3>=49.5,"C+",IF(F3>=44.5,"C",IF(F3>=39.5,"C-",IF(F3>=34.5,"D+",IF(F3>=29.5,"D",IF(F3>=24.5,"D-","E")))))))))))


try it yourself

Using the ranking formula

Paste this ranking formula in cell H2 
​=RANK(F2,$F$2:$F$102,0)
here is the outcome
using the ranking formula/function in excel and google sheets


To arrange students according to their performance, use the sort command on average column

To arrange students according to their performance, use the sort command on average colu
  1. highlight all the values in the average column
  2. Locate sort and filter control of the ribbon
  3. sort z to a or in descending order for the best performing student to start from above.
I hope that you got the answers you were seeking. just incase you have any question in this excel topic, feel free to ask it in the comment section below.

Read More
0 Comments
Print Friendly and PDF


    Submit

    RSS Feed


    Archives

    May 2022
    April 2022
    March 2022
    December 2021
    November 2021
    August 2021
    June 2021
    March 2021
    February 2021
    December 2020
    October 2020
    September 2020
    July 2020
    June 2020
    September 2019
    July 2019
    May 2019
    December 2018
    November 2018
    October 2018
    May 2018
    December 2017
    November 2017
    January 2017
    October 2016
    July 2016
    October 2015


    Categories

    All
    CA
    CARS
    CHECKING STATUS OF YOUR NHIF CONTRIBUTION
    Curriculum Vitae (CV)
    ETHIOPIA BLOCKS SOCIAL MEDIA ACCESS TO AVERT EXAMS RIGGING
    Excel Solutions
    FILLING OF KUCCPS EXCEL APPLICATION TEMPLATE / FORM
    Government Services
    HOW TO ENROL APPLICANTS/STUDENTS FOR PLACEMENT AT KUCCPS
    How To Submit KCSE Project Marks Via KNEC Portal And Download Instructions
    INSURANCE
    KCSE
    Kcse Reports
    KNEC Portal Help
    KRA
    Laptop
    LIFE HACKS
    MPESA
    NEMIS
    NHIF
    NOTES
    PAYMENT OF KUCCPS APPLICATION PROCESSING FEE.
    Phones
    PHONE SECURITY
    READING
    Student
    THE STANDARD: SHOULD CANING BE RE-INTRODUCED IN SCHOOL?
    TPAD
    Twitter Reacts To Examinations Cheating In Kenya
    VELOCITY
    WINGS TO FLY
    WORDPRESS HELP



    ALL SECONDARY RESOURCES (EXAMINATIONS, NOTES, TUTORIALS AND REPORTS)

    NOTES AND TUTORIALS

    HIGH SCHOOL EXAMINATIONS

    Form 1 Term 1 Exams

    FORM 1 TERM 3 EXAMINATIONS

    FORM 2 TERM 3 EXAMINATIONS

    ENGLISH LITERATURE BOOKS & SETBOOKS

    COLLEGE RESOURCES

    KCPE Past Papers, Marking Schemes and Reports

    FREE NOVELS

    PRIMARY SCHOOL RESOURCES 8-4-4

    STANDARD 8 CLASSROOM RESOURCES

    PRIMARY SCHOOL RESOURCES CBC

Print Friendly and PDF

Primary Resources
  • K.C.P.E Past Papers
  • ​Pri - Primary 1 Level
  • Pri  - Primary 2 Level
  • Grade 1
  • Grade 2
  • Grade 3
  • Grade 4
  • Standard 5
  • Standard 6
  • Standard 7
  • Standard 8
  • English
  • Kiswahili
  • Social Studies
  • Science
  • Mathematics
  • Kenya Sign Language
  • I.R.E
  • ​H.R.E
  • Notes
  • Termly Questions
  • Mocks
  • K.C.P.E Past Papers
College Resources
  • E.C.D.E
  • P.T.E
  • D.T.E
  • Technical Diploma
  • Technical Certificate
  • Business Diploma
  • Business Certificate
  • Higher Diploma
  • K.A.S.N.E.B Resources
  • K.M.T.C Resources
  • Varsity Resources
Secondary Resources
  • K.C.S.E Past Papers
  • Form 1
  • Form 2
  • Form 3
  • Form 4
  • Term 1
  • Term 2
  • Term 3
  • English
  • Geography
  • History
  • C.R.E
  • I.R.E
  • ​H.R.E
  • Home Science
  • Computer Studies
  • Business Studies
  • Agriculture
  • Chemistry
  • Biology
  • Physics
  • Mathematics ALT A
  • Mathematics ALT B
  • Kiswahili
  • French
  • Germany
  • Arabic
  • Aviation
  • Art & Design
  • Drawing & Design
  • Building & Construction
  • Metal Works
  • Wood Work
  • Music
  • Kenya Sign Language
  • Electricity
Other Useful Links
  • Academic Environment
  • How its Done
  • News and Opinions
  • Manyam Franchise Support
  • About
  • SITEMAP
  • FOCUS A365 SERIES
  • Membership Details (KCPE/KCSE)
  • Secondary Mocks
  • SYLLABUS
Contact Us
Manyam Franchise
P.O Box 1189 - 40200 Kisii
Tel: 0728 450 424
Tel: 0738 619 279
Tel: 0763 450 425
E-mail - sales@manyamfranchise.com
  • Home
    • JOBS IN KENYA AND ABROAD FOR KENYANS AND BEYOND
    • News and Opinions
    • Atika School Advanced Search
    • How its Done
    • KNEC Portal
    • Academic Environment
    • KNEC KCSE PROJECT INSTRUCTIONS
    • SITEMAP
    • About Us? >
      • About Us
      • Learn more about us
  • OUR PRODUCTS
    • All Categories >
      • Category
      • FREE NOVELS >
        • COURSE BOOKS
        • KCSE KISWAHILI SETBOOKS
        • Backed Up Files and Archives
        • FREE DOCUMENTS
        • PRIMARY 8-4-4 BASED RESOURCES
        • STANDARD 8 RESOURCES
        • HIGH SCHOOL RESOURCES >
          • Biology Paper 3 Exams
          • Secondary Examinations
        • PRIMARY CBC BASED RESOURCES
        • COLLEGE & VARSITY RESOURCES
    • PRIMARY RESOURCES CBC
    • PRIMARY RESOURCES 8-4-4
    • SECONDARY RESOURCES
    • NOTES & TUTORIALS
    • COLLEGE RESOURCES
    • NOVELS and OTHER BOOKS
  • EXAMINATIONS
    • PRIMARY EXAMS >
      • Primary Assignments and Random Exams
      • CBC
      • 8-4-4
      • STANDARD 8 PAST PAPERS 8-4-4 BASED
    • MOCKS AND JOINT EXAMS >
      • FORM 1 QUESTION PAPERS, ANSWERS AND MARKING SCHEMES COLLECTIONS
      • FORM 2 QUESTION PAPERS, ANSWERS AND MARKING SCHEMES COLLECTIONS
      • FORM 3 EXAMINATIONS, QUESTION PAPERS AND ANSWERS
      • MOCKS PAST EXAMS BY REGION
    • KCPE >
      • KCPE BY YEAR >
        • KCPE 2020 RESULTS, REPORTS, QUESTIONS AND ANSWERS
        • KCPE PAST PAPERS 2019 REPORTS, RESULTS, QUESTIONS AND ANSWERS
      • KCPE PAST PAPERS PER SUBJECT
      • KCPE PAST PAPERS AND ANSWERS
      • KCPE MATHEMATICS TOPICAL QUESTIONS FROM 1989 TO LAST YEAR
    • KCSE >
      • KCSE BY YEAR >
        • KNEC KCSE PAST PAPERS, MARKING SCHEMES, QUESTIONS & ANSWERS
      • KCSE BY SUBJECT >
        • KCSE PAST PAPERS BY SUBJECT
        • Free KCSE Past Papers Mathematics
        • Free KCSE Biology Questions and Answers
    • QUESTIONS & ANSWERS >
      • SECONDARY >
        • LANGUAGES >
          • A DOLL'S HOUSE BY HENRIK IBSEN RESOURCES
        • TECHNICALS >
          • COMPUTER STUDIES >
            • COMPUTER STUDIES Q & A
            • kcse computer studies paper 2 AND 3 Questions & Answers
          • BUSINESS STUDIES QUESTIONS & ANSWERS
          • KCSE AGRICULTURE TOPICAL QUESTIONS AND ANSWERS
          • KCSE HOME SCIENCE DIRECT QUESTIONS AND ANSWERS
        • SCIENCES >
          • KCSE Mathematics Topical Questions
          • KCSE Biology Topical Questions and Answers
          • CHEMISTRY TOPICAL QUESTIONS AND ANSWERS
          • KCSE Physics Topical Questions
          • KCSE physics Practical Sample Quiz
        • HUMANITIES >
          • ISLAMIC RELIGIOUS EDUCATION (IRE) QUESTIONS AND ANSWERS
          • KCSE History Topical Questions and Answers
          • GEOGRAPHY TOPICAL QUESTIONS AND ANSWERS
      • PRIMARY >
        • Standard 4-8 Mathematics Topical Questions from Mocks and KCPE
        • Std 6 Mathematics Notes
  • TUTORIALS
    • SECONDARY >
      • SCIENCES >
        • FOCUS MATHEMATICS TUTORIALS AND EXAMS FREE
        • KCSE BIOLOGY NOTES
        • FREE KCSE CHEMISTRY NOTES
        • KCSE PHYSICS NOTES, AUDIOVISUALS AND MORE
      • LANGUAGES >
        • ENGLISH >
          • English KCSE Set Books
          • ENGLISH POETRY QUESTIONS WITH ANSWERS FOR K.C.S.E CANDIDATES
          • ENGLISH GRAMMAR #KCSE
        • CHOZI LA HERI - MWONGOZO
        • KIGOGO - MWONGOZO
        • USHAIRI MASWALI NA MAJIBU
        • MASWALI NA MAJIBU YA TUMBO LISILOSHIBA
        • KCSE Kiswahili Fasihi
      • TECHNICALS >
        • HOME SCIENCE NOTES
        • KCSE BUSINESS STUDIES NOTES
        • COMPUTER STUDIES NOTES LATEST
        • kcse Computer Studies Notes
        • KCSE AGRICULTURE NOTES, SYLLABUS, QUESTIONS, ANSWERS, SCHEMES OF WORK AND OTHERS
      • HUMANITIES >
        • KCSE History Notes Form 1 to 4
        • FREE KCSE CRE NOTES
        • KCSE GEOGRAPHY NOTES
        • IRE NOTES, AUDIOVISUAL, QUESTIONS AND ANSWERS
    • PRIMARY >
      • NOTES >
        • KISWAHILI
        • SOCIAL STUDIES >
          • STANDARD 4: SOCIAL STUDIES
        • SCIENCE
        • ENGLISH >
          • KCPE and Primary Level Compositions
        • MATHEMATICS >
          • KCPE Mathematics Notes
      • EXAMINATIONS >
        • DecaTurbo Online Series Examinations
        • CBC Kiswahili
        • FOCUS A365 SERIES K.C.P.E EXAMINATIONS FOR PRIMARY
        • Standard 8 (std) English Topical Questions
    • Other Supportive Documents >
      • Free Schemes of Work
      • SYLLABUS >
        • Primary 8-4-4 based Syllabus, Objectives and Lessons
        • Primary Mathematics Syllabus
      • MASOMO VIDEOS
  • CURRICULUM
    • NEW CURRICULUM DESIGNS >
      • PRIMARY >
        • PRE PRIMARY ONE CURRICULUM WITH SYLLABUS
        • PRE PRIMARY TWO CURRICULUM WITH SYLLABUS
      • SECONDARY >
        • KNEC KCSE SYLLABUS AND COURSE OUTLINES
      • COLLEGE >
        • Regulations for the TIVET Craft and Diploma Business Examinations
  • HIGH INSTITUTIONS
    • EXAMINATIONS >
      • College and Varsity Past Papers online
    • TUTORIALS >
      • COLLEGE AND UNIVERSITY NOTES AND TUTORIALS
  • CUSTOMER CARE
    • PRICING
    • Contact
    • Help & Support
    • DONATE
    • MPESA FORM
  • BLOGS
    • ENGLISH - EKEGUSII DICTIONARY
  • MEMBERSHIP RESOURCES
    • TERM 3 2021 PRIMARY PAST REVISION PAPERS
    • SECONDARY EXAMINATIONS
  • Archived Pages
    • C.R.E (CRE) QUESTION AND ANSWERS
    • TOP SECONDARY EXAMS ALL SUBJECTS
  • Atika School
  • Membership products