ATIKA SCHOOL
  • Start
    • SITEMAP
    • KNEC Portal
    • ZERAKI HELP AND SUPPORT
    • Academic Environment
    • KNEC KCSE PROJECT INSTRUCTIONS
  • BLOGS
    • About Us? >
      • Learn more about us
    • CUSTOMER CARE >
      • PRICING
      • Help & Support
    • News and Opinions
    • JOBS IN KENYA AND ABROAD FOR KENYANS AND BEYOND
    • How its Done
  • OUR PRODUCTS
    • All Categories >
      • MORE PRODUCTS >
        • SECONDARY CRE NOTES
        • 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 >
      • 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
        • FORM 4 MOCKS PAST EXAMS BY REGION
        • TOP SECONDARY EXAMS ALL SUBJECTS
      • KPSEA NATIONAL EXAMS
      • KCPE >
        • KCPE PAST PAPERS AND ANSWERS >
          • KCPE 2020 RESULTS, REPORTS, QUESTIONS AND ANSWERS
          • KCPE PAST PAPERS 2019 REPORTS, RESULTS, QUESTIONS AND ANSWERS
        • KCPE PAST PAPERS PER SUBJECT
      • KCSE >
        • KCSE PAST PAPERS BY SUBJECT
        • KNEC KCSE PAST PAPERS, MARKING SCHEMES, QUESTIONS & ANSWERS
        • Free KCSE Past Papers Mathematics
        • Free KCSE Biology Questions and Answers
      • QUESTIONS & ANSWERS >
        • SECONDARY >
          • LANGUAGES >
            • Secondary English Questions and Answers
          • 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 >
            • C.R.E (CRE) QUESTION AND ANSWERS
            • 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 >
            • A GUIDE TO SILENT SONG AND OTHER STORIES
            • English KCSE Set Books
            • ENGLISH POETRY QUESTIONS WITH ANSWERS FOR K.C.S.E CANDIDATES
            • ENGLISH GRAMMAR #KCSE
          • KISWAHILI >
            • Bembea ya Maisha
            • CHOZI LA HERI - MWONGOZO
            • KIGOGO - MWONGOZO
            • MASWALI NA MAJIBU YA TUMBO LISILOSHIBA
            • USHAIRI MASWALI NA MAJIBU
            • 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 >
        • STANDARD 4: SOCIAL STUDIES
        • NOTES >
          • KISWAHILI
          • SOCIAL STUDIES
        • EXAMINATIONS
        • DecaTurbo Online Series Examinations
        • KCPE Mathematics Notes
        • KCPE and Primary Level Compositions
      • Other Supportive Documents >
        • 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
  • MEMBERS RESOURCES
    • Primary >
      • CBC AND 8-4-4 LATEST EXAMS
      • CBC PROFFESSIONAL TOOLS
      • CBC NOTES PRIMARY
      • CBC KPSEA EXAMS
      • Standard 8 (std) English Topical Questions
      • KCPE MATHEMATICS TOPICAL QUESTIONS FROM 1989 TO LAST YEAR
      • STANDARD 8 PAST PAPERS 8-4-4 BASED
    • Secondary >
      • FORM 1 EXAMS WITH ANSWERS
      • FORM 2 EXAMS WITH ANSWERS
      • FORM 3 EXAMS WITH ANSWERS
      • FORM 4 EXAMS WITH ANSWERS
      • KCSE 2020 QUESTIONS AND ANSWERS
      • NOTES SECONDARY
      • MOCKS SECONDARY >
        • kcse form 2 mathematics questions
    • Free Schemes of Work
    • FOCUS A365 SERIES K.C.P.E EXAMINATIONS FOR PRIMARY
    • ALL GROUPS PRODUCTS
    • PREMIUM PRODUCTS 2

HOW EVERYTHING IS DONE:

Follow us now to receive the latest updates @schoolatika





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

5/12/2021

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.
Print Friendly and PDF


Follow AtikaSchool.Org on our Social Channels

This gives you an opportunity to get our latest uodates, changes and firsthand information







Comments
comments powered by Disqus


    Submit

    RSS Feed


    Archives

    March 2023
    February 2023
    November 2022
    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
    PRINTERS
    READING
    Student
    THE STANDARD: SHOULD CANING BE RE-INTRODUCED IN SCHOOL?
    TPAD
    Traffic Rules In Kenya
    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



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
  • Start
    • SITEMAP
    • KNEC Portal
    • ZERAKI HELP AND SUPPORT
    • Academic Environment
    • KNEC KCSE PROJECT INSTRUCTIONS
  • BLOGS
    • About Us? >
      • Learn more about us
    • CUSTOMER CARE >
      • PRICING
      • Help & Support
    • News and Opinions
    • JOBS IN KENYA AND ABROAD FOR KENYANS AND BEYOND
    • How its Done
  • OUR PRODUCTS
    • All Categories >
      • MORE PRODUCTS >
        • SECONDARY CRE NOTES
        • 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 >
      • 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
        • FORM 4 MOCKS PAST EXAMS BY REGION
        • TOP SECONDARY EXAMS ALL SUBJECTS
      • KPSEA NATIONAL EXAMS
      • KCPE >
        • KCPE PAST PAPERS AND ANSWERS >
          • KCPE 2020 RESULTS, REPORTS, QUESTIONS AND ANSWERS
          • KCPE PAST PAPERS 2019 REPORTS, RESULTS, QUESTIONS AND ANSWERS
        • KCPE PAST PAPERS PER SUBJECT
      • KCSE >
        • KCSE PAST PAPERS BY SUBJECT
        • KNEC KCSE PAST PAPERS, MARKING SCHEMES, QUESTIONS & ANSWERS
        • Free KCSE Past Papers Mathematics
        • Free KCSE Biology Questions and Answers
      • QUESTIONS & ANSWERS >
        • SECONDARY >
          • LANGUAGES >
            • Secondary English Questions and Answers
          • 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 >
            • C.R.E (CRE) QUESTION AND ANSWERS
            • 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 >
            • A GUIDE TO SILENT SONG AND OTHER STORIES
            • English KCSE Set Books
            • ENGLISH POETRY QUESTIONS WITH ANSWERS FOR K.C.S.E CANDIDATES
            • ENGLISH GRAMMAR #KCSE
          • KISWAHILI >
            • Bembea ya Maisha
            • CHOZI LA HERI - MWONGOZO
            • KIGOGO - MWONGOZO
            • MASWALI NA MAJIBU YA TUMBO LISILOSHIBA
            • USHAIRI MASWALI NA MAJIBU
            • 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 >
        • STANDARD 4: SOCIAL STUDIES
        • NOTES >
          • KISWAHILI
          • SOCIAL STUDIES
        • EXAMINATIONS
        • DecaTurbo Online Series Examinations
        • KCPE Mathematics Notes
        • KCPE and Primary Level Compositions
      • Other Supportive Documents >
        • 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
  • MEMBERS RESOURCES
    • Primary >
      • CBC AND 8-4-4 LATEST EXAMS
      • CBC PROFFESSIONAL TOOLS
      • CBC NOTES PRIMARY
      • CBC KPSEA EXAMS
      • Standard 8 (std) English Topical Questions
      • KCPE MATHEMATICS TOPICAL QUESTIONS FROM 1989 TO LAST YEAR
      • STANDARD 8 PAST PAPERS 8-4-4 BASED
    • Secondary >
      • FORM 1 EXAMS WITH ANSWERS
      • FORM 2 EXAMS WITH ANSWERS
      • FORM 3 EXAMS WITH ANSWERS
      • FORM 4 EXAMS WITH ANSWERS
      • KCSE 2020 QUESTIONS AND ANSWERS
      • NOTES SECONDARY
      • MOCKS SECONDARY >
        • kcse form 2 mathematics questions
    • Free Schemes of Work
    • FOCUS A365 SERIES K.C.P.E EXAMINATIONS FOR PRIMARY
    • ALL GROUPS PRODUCTS
    • PREMIUM PRODUCTS 2