Calculating a Running Average Percentage with Different Raw Scores

Good morning, everyone!

I am sure that I am missing the obvious here, but how do I calculate a running average of test scores as a percentage with differing raw scores? Let's say that a pupil has sat three tests so far this year with the following scores:10/20, 5/15 and 26/30. If I convert each of these to a percentage and then average that, I get an average score of 56.67%. How can I do this in one formula?

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G	Q
2		Week	1	2	3	4	Running
3		Raw Score	20	15	30		Average
4		CG
5	Pupil 1	9	20	15	30		100.00
6	Pupil 2	5	10	5	26		56.67
7	Pupil 3	7	13	Abs	2		35.83
8	Pupil 4	6	12	7	19		56.67

Sheet: Sheet1

Ali, I think that, mathematically, you should sum all actual and possible scores, and work the % out that way.
10+5+26 = 41
20+15+30 = 65

41/65 = 63.08%

from your sample...
=SUM(D5:P5)/SUM($D$3:$P$3)

Thanks, all - I will try these when I get into work.

One observation, and I should have made this clear at the start: the individual tests need to be weighted equally, so it's not just a case of adding everything and dividing by the total points available.

I will report back when I've tried the suggestions.

Thanks again.

Originally Posted by BadlySpelledBuoy

...However, I agree with Ford's approach (albeit with a slight amendment to the formula):

Formula:

Please Login or Register  to view this content.

...

My reason for taking it to the end was to allow for extra weekly data to be added, otherwise the formula needs to be adjusted each week

Ali, what do you mean by "weighted equally"?

How are you doing this manually?

Ford - if you look at the helper columns in red on the far right in the workbook you'll see that each score is being converted to a percentage before the scores are being averaged in column Q. Maybe I needed more coffee this morning, but your outcome does not tally with mine. I want to do this without the helper columns.

Originally Posted by BadlySpelledBuoy

Morning Ali,

This will give your expected results:

Formula:

Please Login or Register  to view this content.

Thanks - this is what I want and need.

Originally Posted by BadlySpelledBuoy

However, I agree with Ford's approach (albeit with a slight amendment to the formula):

Formula:

Please Login or Register  to view this content.

BSB

Unfortunately this approach doesn't give the properly weighted results I want.

Thanks both - problem solved.

Sorry - not solved. It copes with absence (Abs), but I need it to look at the whole range from column D to column P (Ford was right about that).

Classic case of not enough information - sorry!

So, the raw mark may or may not be filled in ahead of the test, but I want it to calculate the average of the scores that have been filled in to date (in the case of the example, up to column F).

Sorry!

One way, array entered... maybe...

=SUMPRODUCT(IFERROR((D5:P5)/($D$3:$P$3),""))/COUNT(D5:P5)

or:

=SUM(IFERROR($D5:$P5/$D$3:$P$3, 0))/COUNT(D5:P5)

array entered

Try with in "Q5"

Formula:

Please Login or Register  to view this content.

Glenn - thanks. I shall try these a little later.

AVK - thanks for trying, but I don't think you read post #9 (see the bottom line).

That works, too, John - thanks!