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?

 

[dafan]

Andrew as a former student I know teachers love to compare how their students did versus the average. In this case the 'average' of the class will be 50% imo.

 

[dafan]

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

this as an array would handle blank denominator & 0 denominator

Beauty. Can you tell me the difference between:

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

and

{=AVERAGE(IF(0+A1:A3,B1:B3/A1:A3))}

?

They both have the same result.

 

[DrBob]

Good question. I tried to get my head round this one a while ago and had to make a decision. Currently have a separate section working out the percentages for each piece of work and then taking an average of that. I suppose it's OK if consistent throughout the workbook. Am now planning to slim it down using the info. from this thread. Do you know if one is more mathematically correct than the other?

 

[dafan]

All of the formulas in this thread come down to the exact same result, although same have errorhandling by skipping blank cells.

They all result in the same as you would calculate all percentages first, then averaging them.

 

[DonkeyOte]

I have to confess myself that I found it by *error*

I was trying

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

but still worked in excluding the zero denominators without the additional clause

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

I found your solution <> "" would not work if denominator was 0 only if blank so was trying to test result was both numeric and > 0

Hopefully Andrew can shed some light on it ?

(I'm more VB programmer than anything so formulae not my strong point....)

 

[Andrew Poulsom]

Good question. I tried to get my head round this one a while ago and had to make a decision. Currently have a separate section working out the percentages for each piece of work and then taking an average of that. I suppose it's OK if consistent throughout the workbook. Am now planning to slim it down using the info. from this thread. Do you know if one is more mathematically correct than the other?

There is a difference between an average and a weighted average:

http://support.microsoft.com/kb/214049

 

[DonkeyOte]

could extend further and do a double check both for numerator and denomiator where for former you would only want to exclude blanks (as 0 may be valid)

{=AVERAGE(IF((0+A1:C1>0)*(ISNUMBER(A2:C2)),A2:C2/A1:C1))}

 

[Andrew Poulsom]

This works for both zero and "" in A1:C1:

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

More explicitly it's:

=AVERAGE(IF(A1:C1<>0,A2:C2/A1:C1,FALSE))

the AVERAGE function ignores FALSE.

 

[DonkeyOte]

ah, excellent - thanks Andrew.

 

[DrBob]

I might have this completely wrong, but this doesn't seem to work if you have a larger range e.g. A1:T1 instead of A1:C1? However, Stefan's formula does work (<>) but includes #DIV/0! when data is missing.