Averaging column %

[johane]

I Have a column with the results in the cells as %, some of the results are 0%. When i try and average the column it returns the answer as 0%.
If all the cells are >0 then it returns a correct average.
When i change the format from % to number the correct average is returned.

 

[anandvarma]

I Have a column with the results in the cells as %, some of the results are 0%. When i try and average the column it returns the answer as 0%.
If all the cells are >0 then it returns a correct average.
When i change the format from % to number the correct average is returned.

Try
Sum/count

 

[Andrew Poulsom]

To exclude zeroes from the average:

=SUMIF(A1:A10,"<>0")/MAX(1,COUNTIF(A1:A10,"<>0"))

I find it difficult to believe your last sentence.

 

[bosco_yip]

Or, this array formula :

{=AVERAGE(IF(A1:A10<>0,A1:A10))}

 

[johane]

Hi Andrew, thanks for the response.
The result in the column comes from a formula =IF($C$1<>E$368,E369,$D$11) sometimes the result is >0 but on occasion the result is 0%. in all the columns with a cell where the result is 0% the average returns as 0%. I tried what you suggested but it still returns 0% as the average

 

[Andrew Poulsom]

Please post some sample data and the formula that doesn't give the expected result.

 

[johane]

<TABLE style="WIDTH: 156pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=208><COLGROUP><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 156pt; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl69 height=17 width=208 align=right>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl69 height=17 align=right>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl69 height=17 align=right>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl69 height=17 align=right>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl69 height=17 align=right>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17 align=right>92%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl69 height=17 align=right>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff66; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17 align=right>0%</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 312pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=416><COLGROUP><COL style="WIDTH: 312pt; mso-width-source: userset; mso-width-alt: 7606" width=416><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 312pt; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68 height=17 width=416>=IF($C$1<>S$368,S392,$D$287)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68 height=17>=IF($C$1<>S$368,S393,$D$299)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68 height=17>=IF($C$1<>S$368,S394,$D$311)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68 height=17>=IF($C$1<>S$368,S395,$D$323)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68 height=17>=IF($C$1<>S$368,S396,$D$335)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>=IF($C$1<>S$368,S397,$D$347)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 12.75pt; COLOR: #9c0006; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68 height=17>=IF($C$1<>S$368,S398,$D$360)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff66; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17>=+SUMIF(S392:S398,"<>0")/MAX(1,COUNTIF(S392:S398,"<>0"))</TD></TR></TBODY></TABLE>

 

[T. Valko]

<TABLE style="WIDTH: 156pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=208 border=0><COLGROUP><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 156pt; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right width=208 height=17>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right height=17>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right height=17>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right height=17>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right height=17>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>92%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right height=17>0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff66" align=right height=17>0%</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 312pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=416 border=0><COLGROUP><COL style="WIDTH: 312pt; mso-width-source: userset; mso-width-alt: 7606" width=416><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 312pt; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=416 height=17>=IF($C$1<>S$368,S392,$D$287)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" height=17>=IF($C$1<>S$368,S393,$D$299)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" height=17>=IF($C$1<>S$368,S394,$D$311)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" height=17>=IF($C$1<>S$368,S395,$D$323)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" height=17>=IF($C$1<>S$368,S396,$D$335)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>=IF($C$1<>S$368,S397,$D$347)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 9pt; BACKGROUND: #ffc7ce; BORDER-LEFT: windowtext 0.5pt solid; COLOR: #9c0006; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" height=17>=IF($C$1<>S$368,S398,$D$360)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff66" height=17>=+SUMIF(S392:S398,"<>0")/MAX(1,COUNTIF(S392:S398,"<>0"))</TD></TR></TBODY></TABLE>

That formula returns the correct result for me.

You don't need the + sign.

=SUMIF(S392:S398,"<>0")/MAX(1,COUNTIF(S392:S398,"<>0"))

If you're using Excel 2007 or later:

=IFERROR(AVERAGEIF(S392:S398,">0"),0)

 

[Andrew Poulsom]

Where are those formulas? You should be referencing that range rather than S392:S398.