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?

 

[Andrew Poulsom]

Post your formula that's not working.

 

[DrBob]

This formula doesn't return the correct answer:
=AVERAGE(IF(A1:T1,A2:T2/A1:T1))

However,
=AVERAGE(IF(A2:T2<>"",A2:T2/A1:T1)) does seem to work.

Am I missing something?

 

[Andrew Poulsom]

In your second formula why are you testing A2:T2 when you are dividing by A1:T1?

 

[DrBob]

I used this formula from Stefan who thought I had the data in columns and not rows:

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

(My fault from the first post)

Here is what I'm looking at:
Out of: A1=23 B1=14 etc ......T1=40
Marks : A2=23 B2=7 etc .......T2=20

So this seems to work:
=AVERAGE(IF(A2:T2<>"",A2:T2/A1:T1))

Have now used an ISERROR function on this to get rid of #DIV/0! Sorry if I've been unclear.

 

[Andrew Poulsom]

This works for me:

=AVERAGE(IF(A1:T1,A2:T2/A1:T1))

provided that I confirm with Ctrl+Shift+Enter.

 

[DrBob]

Only works for me like this:

=AVERAGE(IF(A2:T2,A2:T2/A1:T1))

provided that I confirm with Ctrl+Shift+Enter.

Anyway, seems to be working.

Thanks Andrew.