small macro needed

Avdberg

New Member
Joined
May 11, 2017
Messages
13
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

Grade 5
Test 1
20 points
Test 2
10 points
Test 3
15 points
Pupils pointsPupils pointsPupils points
Pupil 1 6.005.008.00
Pupil 22.004.006.00
Pupil 310.006.009.50
Pupil 415.006.0012.50
Pupil 512.505.0010.00
Pupil 69.005.5011.50
Pupil 712.005.5011.00
Pupil 817.008.0014.00
Pupil 911.003.507.50
Pupil 1016.007.0013.00
below 40
from 40 to 60
from 60.5 to 80
above 80
<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> </tbody>

Thank you
Andre
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
is it imperative you need a macro, as helper cells (out of sight) will do this easily ?
 
Upvote 0
Grade 5Test 1Test 2Test 3
20 points10 points15 points
Pupils pointsPupils pointsPupils points
Pupil 1658 42.2222240to60
Pupil 2246 26.66667bl40
Pupil 31069.5 56.6666740to60
Pupil 415612.5 74.4444460to80
Pupil 512.5510 61.1111160to80
Pupil 695.511.5 57.7777840to60
Pupil 7125.511 63.3333360to80
Pupil 817814 86.66667"80plus
Pupil 9113.57.5 48.8888940to60
Pupil 1016713 80"80plus
easy now to conditionally format to color the results

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
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
Grade 5Test 1
20 points
Test 2
10 points
Test 3
15 points
Pupils pointsPupils pointsPupils points
Pupil 1 6.005.008.00
Pupil 22.004.006.00
Pupil 310.006.009.50
Pupil 415.006.0012.50
Pupil 512.505.0010.00
Pupil 69.005.5011.50
Pupil 712.005.5011.00
Pupil 817.008.0014.00
Pupil 911.003.507.50
Pupil 1016.007.0013.00
below 402.001.000.00
from 40 to 604.007.003.00
from 60.5 to 803.002.004.00
above 801.000.003.00
<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> </tbody>
 
Upvote 0
Grade 5Test 1Test 2Test 3
20 points10 points15 points
Pupils pointsPupils pointsPupils points
Pupil 1658305053.33333
Pupil 2246104040
Pupil 31069.5506063.33333
Pupil 415612.5756083.33333
Pupil 512.551062.55066.66667
Pupil 695.511.5455576.66667
Pupil 7125.511605573.33333
Pupil 817814858093.33333
Pupil 9113.57.5553550
Pupil 1016713807086.66667by percent of class
test1test2test3
below 4021004021020100
from 40 to 604734060473407030
from 60.5 to 803246080324302040
above 801038010010310030
formulas for
2=SUMPRODUCT((H$4:H$13<40)*1)
4=SUMPRODUCT((H$4:H$13<60.01)*1)-SUMPRODUCT((H$4:H$13<40)*1)
3=SUMPRODUCT((H$4:H$13<80.01)*1)-SUMPRODUCT((H$4:H$13<60.01)*1)
1=SUMPRODUCT((H$4:H$13>80.01)*1)

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
You can do that with formulae like


Excel 2013/2016
ABCD
1Grade 5Test 1Test 2Test 3
220 points10 points15 points
3Pupils pointsPupils pointsPupils points
4Pupil 1658
5Pupil 2246
6Pupil 31069.5
7Pupil 415612.5
8Pupil 512.5510
9Pupil 695.511.5
10Pupil 7125.511
11Pupil 817814
12Pupil 9113.57.5
13Pupil 1016713
14
15below 40210
16from 40 to 60473
17from 60.5 to 80324
18above 80103
Cover
Cell Formulas
RangeFormula
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))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top