small macro needed

[Avdberg]

Hi,
Can you please help me to create a small macro to calculate % in the background and then do a summation on the sheet. Let me explain further.

I have 10 pupils and they wrote 3 tests. I must now calculate the results and report on how they did per test. There are 4 results brackets and I need to say how many pupils are within each bracket.

Here is my spreadsheet

[TABLE="width: 274"]
<colgroup><col width="143" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5091;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1678;" span="2"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1706;"> <col width="82" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2901;"> <tbody>[TR]
[TD="width: 143, bgcolor: transparent"]Grade 5
[/TD]
[TD="width: 47, bgcolor: transparent"]Test 1
20 points[/TD]
[TD="width: 47, bgcolor: transparent"]Test 2
10 points[/TD]
[TD="width: 48, bgcolor: transparent"]Test 3
15 points[/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 47, bgcolor: #D9D9D9"]Pupils points[/TD]
[TD="width: 47, bgcolor: #D9D9D9"]Pupils points[/TD]
[TD="width: 48, bgcolor: #D9D9D9"]Pupils points[/TD]
[TD="width: 82, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 1 [/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 2[/TD]
[TD="bgcolor: transparent"]2.00[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 3[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]9.50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 4[/TD]
[TD="bgcolor: transparent"]15.00[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]12.50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 5[/TD]
[TD="bgcolor: transparent"]12.50[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 6[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[TD="bgcolor: transparent"]5.50[/TD]
[TD="bgcolor: transparent"]11.50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 7[/TD]
[TD="bgcolor: transparent"]12.00[/TD]
[TD="bgcolor: transparent"]5.50[/TD]
[TD="bgcolor: transparent"]11.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 8[/TD]
[TD="bgcolor: transparent"]17.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]14.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 9[/TD]
[TD="bgcolor: transparent"]11.00[/TD]
[TD="bgcolor: transparent"]3.50[/TD]
[TD="bgcolor: transparent"]7.50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 10[/TD]
[TD="bgcolor: transparent"]16.00[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]13.00[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B0F0"]below 40[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]from 40 to 60[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]from 60.5 to 80[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: fuchsia"]above 80[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Thank you
Andre

 

[oldbrewer]

is it imperative you need a macro, as helper cells (out of sight) will do this easily ?

 

[oldbrewer]

[TABLE="width: 768"]
<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl23, width: 64, bgcolor: transparent"]Grade 5[/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"]Test 1[/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"]Test 2[/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"]Test 3[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl23, width: 64"]20 points[/TD]
[TD="class: xl23, width: 64"]10 points[/TD]
[TD="class: xl23, width: 64"]15 points[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl25, width: 64"]Pupils points[/TD]
[TD="class: xl25, width: 64"]Pupils points[/TD]
[TD="class: xl25, width: 64"]Pupils points[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 1[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42.22222[/TD]
[TD]40to60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 2[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26.66667[/TD]
[TD]bl40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 3[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]9.5[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]56.66667[/TD]
[TD]40to60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 4[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]12.5[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]74.44444[/TD]
[TD]60to80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 5[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]12.5[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]61.11111[/TD]
[TD]60to80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 6[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]5.5[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]11.5[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]57.77778[/TD]
[TD]40to60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 7[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]5.5[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]63.33333[/TD]
[TD]60to80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 8[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]86.66667[/TD]
[TD]"80plus[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 9[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]3.5[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]7.5[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]48.88889[/TD]
[TD]40to60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Pupil 10[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl22"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD]"80plus[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]easy now to conditionally format to color the results[/TD]
[/TR]
</tbody>[/TABLE]

 

[Avdberg]

Hi, Thank you for the reply. I see I did not explain the requirements very well.

If you look at test 1, the numbers in column B is what each pupil scored out of 20. I must use these values to calculate their % and then count how many pupils are within each bracket. I did it manually however I want to automate the process.

Here is my complete list
[TABLE="width: 179"]
<colgroup><col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3441;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1678;" span="2"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1706;"> <tbody>[TR]
[TD="width: 97, bgcolor: transparent"]Grade 5[/TD]
[TD="width: 47, bgcolor: transparent"]Test 1
20 points[/TD]
[TD="width: 47, bgcolor: transparent"]Test 2
10 points[/TD]
[TD="width: 48, bgcolor: transparent"]Test 3
15 points[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 47, bgcolor: #D9D9D9"]Pupils points[/TD]
[TD="width: 47, bgcolor: #D9D9D9"]Pupils points[/TD]
[TD="width: 48, bgcolor: #D9D9D9"]Pupils points[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 1 [/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 2[/TD]
[TD="bgcolor: transparent"]2.00[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 3[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]9.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 4[/TD]
[TD="bgcolor: transparent"]15.00[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"]12.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 5[/TD]
[TD="bgcolor: transparent"]12.50[/TD]
[TD="bgcolor: transparent"]5.00[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 6[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[TD="bgcolor: transparent"]5.50[/TD]
[TD="bgcolor: transparent"]11.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 7[/TD]
[TD="bgcolor: transparent"]12.00[/TD]
[TD="bgcolor: transparent"]5.50[/TD]
[TD="bgcolor: transparent"]11.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 8[/TD]
[TD="bgcolor: transparent"]17.00[/TD]
[TD="bgcolor: transparent"]8.00[/TD]
[TD="bgcolor: transparent"]14.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 9[/TD]
[TD="bgcolor: transparent"]11.00[/TD]
[TD="bgcolor: transparent"]3.50[/TD]
[TD="bgcolor: transparent"]7.50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Pupil 10[/TD]
[TD="bgcolor: transparent"]16.00[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]13.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00B0F0"]below 40[/TD]
[TD="bgcolor: transparent"]2.00[/TD]
[TD="bgcolor: transparent"]1.00[/TD]
[TD="bgcolor: transparent"]0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]from 40 to 60[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[TD="bgcolor: transparent"]7.00[/TD]
[TD="bgcolor: transparent"]3.00[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]from 60.5 to 80[/TD]
[TD="bgcolor: transparent"]3.00[/TD]
[TD="bgcolor: transparent"]2.00[/TD]
[TD="bgcolor: transparent"]4.00[/TD]
[/TR]
[TR]
[TD="bgcolor: fuchsia"]above 80[/TD]
[TD="bgcolor: transparent"]1.00[/TD]
[TD="bgcolor: transparent"]0.00[/TD]
[TD="bgcolor: transparent"]3.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 1088"]
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl22, width: 64, bgcolor: transparent"]Grade 5[/TD]
[TD="class: xl22, width: 64, bgcolor: transparent"]Test 1[/TD]
[TD="class: xl22, width: 64, bgcolor: transparent"]Test 2[/TD]
[TD="class: xl22, width: 64, bgcolor: transparent"]Test 3[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl22, width: 64"]20 points[/TD]
[TD="class: xl22, width: 64"]10 points[/TD]
[TD="class: xl22, width: 64"]15 points[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl23, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl24, width: 64"]Pupils points[/TD]
[TD="class: xl24, width: 64"]Pupils points[/TD]
[TD="class: xl24, width: 64"]Pupils points[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 1[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]53.33333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 2[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 3[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]9.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]63.33333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 4[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]12.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]83.33333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 5[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]12.5[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]62.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]66.66667[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 6[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]5.5[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]11.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]76.66667[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 7[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]5.5[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]73.33333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 8[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]93.33333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 9[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]3.5[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]7.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl25, width: 64, bgcolor: transparent"]Pupil 10[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]86.66667[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]by percent of class[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl23, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl30"]test1[/TD]
[TD="class: xl30"]test2[/TD]
[TD="class: xl30"]test3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl26, width: 64"]below 40[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD][/TD]
[TD="class: xl25, width: 64, align: right"]0[/TD]
[TD="class: xl25, width: 64, align: right"]40[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl30"]20[/TD]
[TD="class: xl30"]10[/TD]
[TD="class: xl30"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl27, width: 64"]from 40 to 60[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD][/TD]
[TD="class: xl25, width: 64, align: right"]40[/TD]
[TD="class: xl25, width: 64, align: right"]60[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl30"]40[/TD]
[TD="class: xl30"]70[/TD]
[TD="class: xl30"]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl28, width: 64"]from 60.5 to 80[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD][/TD]
[TD="class: xl25, width: 64, align: right"]60[/TD]
[TD="class: xl25, width: 64, align: right"]80[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl30"]30[/TD]
[TD="class: xl30"]20[/TD]
[TD="class: xl30"]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl29, width: 64"]above 80[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD][/TD]
[TD="class: xl25, width: 64, align: right"]80[/TD]
[TD="class: xl25, width: 64, align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl30"]10[/TD]
[TD="class: xl30"]0[/TD]
[TD="class: xl30"]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formulas for[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="colspan: 4"]=SUMPRODUCT((H$4:H$13<40)*1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="colspan: 7"]=SUMPRODUCT((H$4:H$13<60.01)*1)-SUMPRODUCT((H$4:H$13<40)*1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="colspan: 8"]=SUMPRODUCT((H$4:H$13<80.01)*1)-SUMPRODUCT((H$4:H$13<60.01)*1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="colspan: 4"]=SUMPRODUCT((H$4:H$13>80.01)*1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

You can do that with formulae like

Excel 2013/2016
	A	B	C	D
1	Grade 5	Test 1	Test 2	Test 3
2		20 points	10 points	15 points
3		Pupils points	Pupils points	Pupils points
4	Pupil 1	6	5	8
5	Pupil 2	2	4	6
6	Pupil 3	10	6	9.5
7	Pupil 4	15	6	12.5
8	Pupil 5	12.5	5	10
9	Pupil 6	9	5.5	11.5
10	Pupil 7	12	5.5	11
11	Pupil 8	17	8	14
12	Pupil 9	11	3.5	7.5
13	Pupil 10	16	7	13
14
15	below 40	2	1	0
16	from 40 to 60	4	7	3
17	from 60.5 to 80	3	2	4
18	above 80	1	0	3
Cover

Cell Formulas
Range	Formula
B15	=SUMPRODUCT(--((B$4:B$13)/20<0.4))
B16	=SUMPRODUCT(((B$4:B$13)/20<=0.6)*((B$4:B$13)/20>=0.4))
B17	=SUMPRODUCT(((B$4:B$13)/20<=0.8)*((B$4:B$13)/20>0.6))
B18	=SUMPRODUCT(--((B$4:B$13)/20>0.8))
C15	=SUMPRODUCT(--((C$4:C$13)/10<0.4))
C16	=SUMPRODUCT(((C$4:C$13)/10<=0.6)*((C$4:C$13)/10>=0.4))
C17	=SUMPRODUCT(((C$4:C$13)/10<=0.8)*((C$4:C$13)/10>0.6))
C18	=SUMPRODUCT(--((C$4:C$13)/10>0.8))
D15	=SUMPRODUCT(--((D$4:D$13)/15<0.4))
D16	=SUMPRODUCT(((D$4:D$13)/15<=0.6)*((D$4:D$13)/15>=0.4))
D17	=SUMPRODUCT(((D$4:D$13)/15<=0.8)*((D$4:D$13)/15>0.6))
D18	=SUMPRODUCT(--((D$4:D$13)/15>0.8))