Percentage average

DrBob

New Member
Joined
Jun 25, 2008
Messages
28
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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
=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.
 
Last edited:
Upvote 0
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:
Code:
=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>
 
Upvote 0
Welcome to the Board.

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

=SUM(A2:C2)/SUM(A1:C1)
 
Upvote 0
Lasw's formula can be adjusted to not count marks that have not been filled yet by:

Code:
=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!
 
Upvote 0
Thanks Guys. This is my first post - had no idea it would be so quick. Problem solved thanks to you. Much obliged.
 
Upvote 0
=AVERAGE(IF(0+A1:C1,A2:C2/A1:C1))

this as an array would handle blank denominator & 0 denominator
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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