Average Percentage Query

[Donal28]

Hi All

I'm trying to get an average percentage for a range of percentages in range B1:B9 with 2 scenarios, Paper & Physical which reside in Range A1:A9. Ideally I would like to get 2 average figures for Paper and Physical


<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Paper</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>84%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Physical </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>55%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Paper</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>100%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Physical </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>97%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Physical </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>87%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Paper</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>85%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Paper</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>88%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Paper</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>100%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Paper</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3%</TD></TR></TBODY></TABLE>

Any help on this would be very much appreciated

Regards
Donal

 

[mancemonster]

Try..


=SUMIF(A:A,"PAPER",B:B)/COUNTIF(A:A,"PAPER")
=SUMIF(A:A,"Physical",B:B)/COUNTIF(A:A,"Physical")

Regards


Roger

 

[Zakkaroo]

Just wanted to mention something. You may have already taken this into account, but generally speaking, averaging percentages is a bad idea, unless they are all percentages of the same number.

 

[Donal28]

Thanks for the reply the first part =SUMIF(A:A,"PAPER",B:B)/COUNTIF(A:A,"PAPER")
works fine but the second part is giving me a #DIV/0! error for sme reason even though the cell is formatted the same as for the 1st formula

 

[Donal28]

Thanks for the reply Zakkaroo...the percentages are not all of the same number what approach would you recommend

 

[T. Valko]

Thanks for the reply the first part =SUMIF(A:A,"PAPER",B:B)/COUNTIF(A:A,"PAPER")
works fine but the second part is giving me a #DIV/0! error for sme reason even though the cell is formatted the same as for the 1st formula

What version of Excel are you using?

 

[Zakkaroo]

Thanks for the reply the first part =SUMIF(A:A,"PAPER",B:B)/COUNTIF(A:A,"PAPER")
works fine but the second part is giving me a #DIV/0! error for sme reason even though the cell is formatted the same as for the 1st formula

A #DIV/0! error would mean that the COUNTIF(A:A,"Physical") part is returning a 0. This could be for a number of reasons.

1) Check that you have spelt Physical Correctly in the formula AND in the A column.
2) Ensure there are no Spaces / extra characters anywhere.
3) If in doubt. Try deleting one of the Physicals and re-typing it.

 

[Donal28]

Yess thats worked...there was a space after the Physical. thanks all of you for all your help on this

 

[Zakkaroo]

Thanks for the reply Zakkaroo...the percentages are not all of the same number what approach would you recommend

The reason I say it's bad is the following example:

Lets say A1, which is Paper, is 84%. And lets say it's 84% of 1000
So it's actual value is 840

Lets say A3, which is paper, is 100%. And lets say it's 100% of 9000.
So it's actual value is 9000.

If you take an average of those two percentages, you end up with 92%. (100+84 / 2)
92% of 10,000 is 9200.

But if you work out the actual AVERAGE of the two numbers, it is 4920 or 49.2%. (9000+840 / 2)

So you now have two averages. 92% and 49.2%. 49.2% is really the correct answer.

This is an extreme example, and I don't know if i explained very well, but it shows how an average of two percentages can get a different value than an average of two numbers.

My own personal preference / solution to this is to ALWAYS work out the average of the NUMBERS first and then calculat this as a percentage. Rather than calculate the percentage first and then average it. So, it all depends if your sheet contains the original values that created the percentages. If it doesn't, then you can only do what you are already doing.

But be wary that if the numbers are hugely different, then your % figure won't actually be right.

 

[Donal28]

The reason I say it's bad is the following example:

Lets say A1, which is Paper, is 84%. And lets say it's 84% of 1000
So it's actual value is 840

Lets say A3, which is paper, is 100%. And lets say it's 100% of 9000.
So it's actual value is 9000.

If you take an average of those two percentages, you end up with 92%. (100+84 / 2)
92% of 10,000 is 9200.

But if you work out the actual AVERAGE of the two numbers, it is 4920 or 49.2%. (9000+840 / 2)

So you now have two averages. 92% and 49.2%. 49.2% is really the correct answer.

This is an extreme example, and I don't know if i explained very well, but it shows how an average of two percentages can get a different value than an average of two numbers.

My own personal preference / solution to this is to ALWAYS work out the average of the NUMBERS first and then calculat this as a percentage. Rather than calculate the percentage first and then average it. So, it all depends if your sheet contains the original values that created the percentages. If it doesn't, then you can only do what you are already doing.

But be wary that if the numbers are hugely different, then your % figure won't actually be right.

Thanks for the excellent explanation Zakkaroo what your sying does make alot of sense. Basically all the figures totalled up to create the percentage are 1s and 0s so if there is say 70 values( 1s and 0s) and say 60 of those are 1s then the percentage is 85%. There will be a number of these scenarios and I just want to get the average % for all of them so I think from what you say above then using averages should work in this case.