Percentage average

[DrBob]

I have two rows of data representing marks out of different amounts.
E.g.
A1=23, A2=14, A3=50 etc.
B1=12, B2=10, B3=32 etc.

So, the first mark is 12 out of 23, the second is 10 out of 14 and so on. I have a large number of these for at least 250 people. Is there a simple formula that will immediately work out the average percentage for all of the marks above? If so, will it still work if some marks are missing?

 

[DonkeyOte]

=AVERAGE(A2:C2/A1:C1)

commit using CTRL + SHIFT + ENTER

should display as {=AVERAGE(A2:C2/A1:C1)}

this is an array formula.

as for missing scores... it will reduce average if denominator is present, if denominator not present (row 1) will result in error.

Hopefully one of the gurus here can provide a solution to handle that for you.

 

[dafan]

Try this, this will NOT rule out empty marks (ie row B has empty values). You will get 0% on those (ie "" = 0 out of 14 = 0).
It will generate a div/0 if one of row A is empty. There might be a workaround, will try to make on for you now.

Here is your formula to start with:

=SUMPRODUCT((B1:B250)/(A1:A250))/COUNTA(A1:A250)

Change your range as necessary.

You can consider replacing COUNTA by just 250 (or the amount of people) but this way you can add students as you go.
<table style="border-collapse: collapse; width: 48pt;" border="0" cellpadding="0" cellspacing="0" width="64">

</table>

 

[Andrew Poulsom]

Welcome to the Board.

Do you want an overall average rather than an average of the percentages?

=SUM(A2:C2)/SUM(A1:C1)

 

[dafan]

Lasw's formula can be adjusted to not count marks that have not been filled yet by:

=AVERAGE(IF(B1:B3<>"",B1:B3/A1:A3))

If you remove for example the 10 out of B2, this will result in 58% (average of 52% & 64%)

You still have to confirm this with Ctrl-Shift-Enter!

 

[DrBob]

Thank you! Works a treat - and thanks for correcting my rows/columns!

 

[DrBob]

An average of the percentages - I think.

 

[DrBob]

Thanks Guys. This is my first post - had no idea it would be so quick. Problem solved thanks to you. Much obliged.

 

[Andrew Poulsom]

An average of the percentages - I think.

If Person1 scores 16 out of 40 (40%) and Person2 scores 12 out of 20 (60%) is the average percentage 50% ((40%+60%)/2) or 47% (28 out of 60)?

 

[DonkeyOte]

=AVERAGE(IF(0+A1:C1,A2:C2/A1:C1))

this as an array would handle blank denominator & 0 denominator