1 / 16

Excel Grading System Tutorial

Learn how to create a grading system in Excel, including copying data, comparing data, branching, nesting, and counting students' grades.

lynnjohnson
Download Presentation

Excel Grading System Tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 情報基礎BLecture 5 Takeshi TokuyamaTohoku University Graduate School of Information SciencesSystem Information SciencesDesign and Analysis of Information Systems

  2. SPREADSHEET 2

  3. Copying Data • Copy data by entering cell number (Numeric data, character data, formula) • G1 =English • G2 =G1 • H2 =C1 • H3 =E2

  4. Comparing data • Comparison • A15 =10>3 • B15 =10<3 • C15 =(10*2)<3 • D15 =C1>C2 • E16 =C7>E2 • Result is “TRUE” or “FALSE”

  5. Simple Program • Blanching by “TRUE” and ”FALSE” • IF function • IF(criterion, action1, action2) • Proceed action1 when the criterion is true, otherwise proceed action2 • =IF(logical_test, value_if_true, value_if_false) Characters with “” or numbers Logical test or cell number

  6. Simple Program • Operand used in logical test • A=B A is equal to B • A>B A is larger than B • A>=B A=B or A>B • A<>B A is not equal to B • A<B A is smaller than B • A<=B A=B or A<B

  7. Simple Program =IF(logical_test, value_if_true,value_if_false) logical_test TRUE value_if_true FALSE value_if_false =IF(logical_test，1, 0) • A16 =ＩＦ(A15, 1, 0） =IF(logical_test，“string”, “string”) • B16 =ＩＦ(A15, “True”, “False”） • C16 =ＩＦ(C1>C2, “Correct”, “Wrong”） 　

  8. Simple Program • D16 =IF(A1>=80, “Pass”, “Fail”) TRUE >=80 Pass FALSE Fail

  9. Exercise1Simple Program : Grading • Program a grading system on excel which outputs “Pass” or “Fail” • Pass: if an Score of Japanese, English, Math is more than 80 • Fail: otherwise • Add “Result” on G1 • Display “Pass” or “Fail” on G2 to G21

  10. Branching TRUE A >=90 FALSE TRUE B >=80 FALSE TRUE C >=70 FALSE TRUE D >=60 FALSE F

  11. Branching • Nesting “IF” =IF(B2>=90, “A”, ) IF(B2>=80, “B”, [others]) TRUE >=90 A FALSE TRUE >=90 B FALSE [others]

  12. Branching TRUE A >=90 FALSE TRUE B >=80 FALSE TRUE C >=70 FALSE TRUE D >=60 FALSE F

  13. Grading • Grade • A 100 > Score >= 90 • B 90 > Score >= 80 • C 80 > Score >= 70 • D 70 > Score >= 60 • F 60 > Score • =IF(B2>=90, ”A”, • IF(B2>=80, ”B”, • IF(B2>=70, ”C”, • IF(B2>=60, ”D”, “F”))))

  14. Exercise2Simple Program : Grading 2 • Program a grading system on excel which outputs “A”, “B”, “C”, “D” or “F” for each Subject • A 100 > Score >= 90 • B 90 > Score >= 80 • C 80 > Score >= 70 • D 70 > Score >= 60 • F 60 > Score • Add “Japanese”, “English” and “Math” each on G1, H1 and I1 • Display “A”, “B”, “C”, “D” or “F” on G2 to I21

  15. Count • How many student got A on Japanese? =COUNTIF(range, criteria) =COUNTIF(G2:G21, “A”)

  16. Exercise3Simple Program : Counting • Count numbers of student for each grade and subject

More Related