# Percentage average

#### DrBob

##### New Member
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### DonkeyOte

##### MrExcel MVP
=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:

#### dafan

##### Well-known Member
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.

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

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

##### MrExcel MVP
Welcome to the Board.

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

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

#### dafan

##### Well-known Member
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!

#### DrBob

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

#### DrBob

##### New Member
An average of the percentages - I think.

#### DrBob

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

#### Andrew Poulsom

##### MrExcel MVP
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

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

this as an array would handle blank denominator & 0 denominator

Replies
3
Views
378
Replies
7
Views
122
Replies
3
Views
189
Replies
1
Views
341
Replies
2
Views
356

1,191,633
Messages
5,987,783
Members
440,110
Latest member
albertod8

### 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.

### Which adblocker are you using?

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

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