need a function to repeat a process

[pzaz]

Hello -
I have a large list of assessments for a school. Each assessment ha
five levels and a count of students for each level - like so:

asmt 1
lvl 1 9
lvl 2 13
lvl 3 24
lvl 4 7
lvl 5 1

asmt 2
lvl 1 7 12% =M12/$M$17
lvl 2 11 19% =M13/$M$17
lvl 3 29 50% =M14/$M$17
lvl 4 9 16% =M15/$M$17
lvl 5 2 3% =M16/$M$17
total 58

for each assessment, I need to total the counts, then determine th
percentage of each count. Easy to do for one but a pain to for th
whole school. If I could write a single function that used ove
again it would save me a ton of time.

Thanks in advance for any suggestions
:

 

[DNF Karran]

The first thing i would suggest is reformatting the data into:

Assessment - level - Students
1 - 1 - 9
1 - 2 - 13
1 - 3 - 24
1 - 4 - 7
1 - 5 - 1
2 - 1 - 7
2 - 2 - 11
etc

Now you are in this format you can use the more powerful excel tools e
pivot tables, subtotals and for what you are trying to do:

Assume the data is in cells A1 to C6

In D1 put =C1/(SUMIF($A$1:$A$6,1,$C$1:$C$6)) then drag it down an
format as %.

Done!

Dunca

 

[pzaz]

Thank you, Duncan for responding so quickly !

I tried your suggestion and it works fine for the data in cells A1 t
C6. But I have 28 assessments. The absolute cell references in you
formula ($a$1:$a$6) are just that - absolute, so if I attempt to re us
your formula again elsewhere in the data, like the 2nd assessment, fo
example, I must first edit it to refer to the new block of data. I wa
trying to avoid having to edit this for every block.

Perhaps I could create a macro that will perform the necessar
calculations - then all I would have to do is run that macro in each o
the appropriate cells.

I'll keep you posted
Davi

 

[Trevor]

David,

Two small modifications will make Duncan's solution work for all
assessments. Assuming, of course, that you added the column, as he wisely
suggested, for the assessment number.

First, change the 6 to the number of the last row of data (the one that
contains level 5 for assessment 28 - which takes you all the way down to at
least row 140).

Second, change the 1 in the center of the function to refer to the
assessment column. Use a relative reference, not an absolute one.

=C1/(SUMIF($A$1:$A$140,A1,$C$1:$C$140))

If you don't have any other numbers in column A, then you could always just
use:
=C1/(SUMIF(A:A,A1,C:C))

If you've kept your format, but also added Duncan's assessment column, then
you'll either have to copy the formula to each of the five rows of each
assessment, or you can add some logic to the formula to hide the answer when
you're on a row without any assessments/levels on it. So if your data looks
like:

Assessment 1
1 - 1 - 9
1 - 2 - 13
1 - 3 - 24
1 - 4 - 7
1 - 5 - 1

Assessment 2
2 - 1 - 7
2 - 2 - 11
2 - 3 - 29
2 - 4 - 9
2 - 5 - 2

Then put the following formula in column D and copy it all the way down to
the bottom, not worrying about your title rows.
=IF(ISNUMBER(C2),C2/(SUMIF($A$2:$A$195,A2,$C$2:$C$195)),"")
or, if you don't have anything other numbers in column A:
=IF(ISNUMBER(C2),C2/(SUMIF(A:A,A2,C:C)),"")