Test Result Analysis - Guideline For Excel's Begginers

GUIDELINE : TEST RESULT ANALYSIS Let's say : Year 3 Mathematics Test ( April ) RESULTS No. 1 2 3 4 5

Name

Number of Questions = 40 Items ANALYSIS

Score Mark(^%)

Grade

Result

0% 0% 0% 0% 0%

fx =E7/40

fx =VLOOKUP()

Mark(^%) Grade 0% E 20% D 40% C 70% B 80% A

fx=COUNTIF()

No

fx=COUNTIF()

STEP 1

Draw and complete the tables as shown above. Customize the Mark Coloumn ( F7:F11) of the RESULT Table Hightlight all cells ( F7:F11), then in Formatting Toolbar 'click once the % si

STEP 2

Inserting formula in (F7) cell. - Don't use F4 key ( To get exact results in nex Click F7 cell and in the blank Formula Bar click and type = Click E7 cell then in Formula Bar type / 40 ( Since there are only 40 quest Click ENTER. The formula would be =E7/40 and the result in F7 is 0%

STEP 3

Use Dragging Technique to copy and paste formula in (F7) cell into ( F8:F11 Examine this diagram.

'Hightlight F7 cell. Move mouse pointer here and when press and hold the left-click mouse Move the pointer to F11 cell. Release the left-click. The result as shown on the diagram

STEP 4

Inserting formula in Grade (G7) cell. - 'fx =VLOOKUP() Click G7 cell then click fx in Formula Bar Move mouse pointer here and when you see + sign, press and hold the left-click mouse. Move the pointer to F11 cell. Release the left-click. The result as shown on the diagram.

Move mouse pointer here and when you see + sign, press and hold the left-click mouse. Move the pointer to F11 cell. Release the left-click. The result as shown on the diagram.

fx =SUM(E7/40)*100%

VLOOKUP_1

Grade authomatically ) 1. Click cell E15 and the Click fx 2. In Insert Function table, choose VLOOKUP then clik OK (Filling

3. LookUp_Value Table_Array Col_Index_Num

: Click D15 then Click Table_array space

: Click cell H13 (mark of 0%) and drag to the cell I17 (GredA). This refer to : Click this blank space then type

2 (represents the coloumn of Gred I13 to

Click OK and the outcomes displayed. Note : Don't fill anything in Lookup_range space

VLOOKUP_2 (Filling Comment/Result authomatically ) 1. Click cell F14 and the Click fx 2. In Insert Function table, choose VLOOKUP then clik OK

: Click D14 then Click Table_array space : Click cell I20 (mark of 0%) and drag to the cell J24 (Cemerlang). This refe : Click this blank space then type 2 (represents the coloumn of (%) I20 to I2 Col_Index_Num Click OK and the outcomes displayed. Note : Don't fill anything in Lookup_range space

3. LookUp_Value Table_Array

COUNTIF_1 (Counting the numbers of pupils for specifc Grade ) 1. Click cell K16 and the Click fx

2. In Insert Function table, choose 3. Range 4. Creteria Note

COUNTIF then clik OK : Click cell E8 (Gred E) and drag to the cell E12 (Gred A). This refer to the : Type Sangat Lemah. Then Click OK : Range and Cretaria can be replace with any …..

Note: SUM, AVERAGE, COUNTIF Use similar drag technique

0 Items Table_array % 0% 20% 40% 70% 80%

Grade E D C B A

Result Very Poor Poor Good Brilliant Excellent

=COUNTIF()

once the % siign

esults in next Steps )

only 40 questions ) 7 is 0% into ( F8:F11) cells

here and when you see + sign, ft-click mouse. 11 cell.

on the diagram.

dA). This refer to the ANALISA TABURAN MARKAH

n of Gred I13 to I17 )

erlang). This refer to the PERATUS KESELURUHAN n of (%) I20 to I24 )

This refer to the KEPUTUSAN MARKAH

RESULTS No. 1 2 3 4 5

Name

ANALYSIS Score Mark(^%)

fx =SUM(E7/40)*100%

Grade

Result

0% 0% 0% 0% 0%

fx =VLOOKUP()

Mark(^%) Grade 0% E 20% D 40% C 70% B 80% A

fx=COUNTIF()

Table_array No

% 0% 20% 40% 70% 80%

fx=COUNTIF()

Grade E D C B A

Result Sangat Lemah Lemah Baik Kepujian Cemerlang