Average IF two columns (based on content of another cell)

[garwil]

Hi, Michael very kindly assisted me with an average calculation which worked perfectly. I have since altered the structure of the column which now contains some zeros and I am baffled as cannot find a solution to average it correctly.
My table layout is as follows....
Column "D" has dates on a daily basis extending three years down. Column "F" has percentages also extending down for each day in column "D". Cell K3 has a month. I needed to average column "F" on a per month basis and place that figure in cells K4-V4 on a per month basis.
Michael gave me this code which works fine =AVERAGE(IF($D:$D,INT(K3),$F:$F)).
Now since I have modified Column "F" and it contains Zeros the above formula returns 0%. I have tried all sorts of variations that I have found on the web such as averageif, averageifs etc and the closest I can find is this one =AVERAGE(IF($D:$D,INT(O3)),IF($F:$F>0,$F:$F)) which is returning a figure of 2852505% when it should be 70%
I hope someone can help ...

 

[Weaver]

Looks like an array formula.

Does it appear bracketed by {}?

If you've modified it, did you confirm by hitting ctrl+shift+enter?

HTH

 

[garwil]

Hi,
Yes... have confirmed it as an array formula... can't figure it out. Been trying various ways and the closest I got was 29% which was wrong so here I am at the forum after 24hrs or trying...

 

[mgirvin]

Maybe this small example will help:

<TABLE style="WIDTH: 268pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=356 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #002060" width=82 height=20>Dates</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060" width=64>Number</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 51pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060" width=68>Citeria</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060" width=78>Citeria</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/8/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3/9/2011</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3/10/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/9/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><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; BACKGROUND-COLOR: #002060">Average</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060">Average</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/10/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc"></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" align=right>1.66666667</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/11/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/9/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060">Citeria</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060">Citeria</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/10/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3/11/2011</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3/12/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/9/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><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; BACKGROUND-COLOR: #002060">Average</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060">Average</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3/10/2011</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" align=right>2</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc"></TD></TR></TBODY></TABLE>

Using Ctrl + Shift + Enter put formula in cell D4 and copy to E4, D9, E9 (I think that was similar to your original setup):

=IF(SUMIF($A$2:$A$9,D2,$B$2:$B$9),AVERAGE(IF($A$2:$A$9=INT(D2),IF($B$2:$B$9>0,$B$2:$B$9))),"")

or if you have excel 2007 or later:

=IFERROR(AVERAGEIFS($B$2:$B$9,$B$2:$B$9,">0",$A$2:$A$9,INT(D2)),"")

I do not have your original spreadsheet, but maybe this would work:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o>=AVERAGE(IF($F:$F>0,IF($C:$C=INT(H4),$F:$F)))</o>
<o></o>
<o>Just put the condition for the F column in the front of the formula.<o></o>
</o>

 

[mgirvin]

I do not remember the month criteria, but if you are trying to get months from serial dates, maybe:

=AVERAGE(IF($F$7:$F$1043>0,IF(MONTH($C$7:$C$1043)=MONTH(H4),$F$7:$F$1043)))

 

[garwil]

Hi Mike,
Thanks. Am using 2007 so just tried your last three solutions.
The first two for 2007 resulted in:
A.the IfError(averageif.. formula resulted in a blank cell
B. The =AVERAGE(IF($F:$F>0,IF($C:$C=INT(H4),$F:$F))) produced a #div/0! error although I did make sure the columns were correctly set.
Your last solution produced the best result of 68% for one particular month,however when I double checked the average with a standard =Average(,f7,f9,f10...) formula it came out at 70%. I copied the formula to another month and there was a 1% discrepancy between your formula and the standard =Average( formula. Is it possible that conducting an "average" calculation can have a margin of error like this?

 

[garwil]

Just been trying different things with this formula....{=AVERAGE(IF(F6:F1042>0,IF(MONTH($D6:$D1042)=MONTH(K3),$F6:$F1042)))}

Column D has the dates. If I alter the formula to just take in the range for March (F6-F36) then the formula works perfectly and the standard =Average formula and your formula results match exactly... If I use the whole column ($F:$F) or include the whole range (F6-F1043) then there is a discrepancy...

 

[mgirvin]

Don't use the whole column! Use just the range with values. I think the range was C7:C1000 or something like that. Not using the column will speed up calculation time too!

 

[garwil]

Yup.. very weird... I experimented with various ranges until the result changed. I found that after row 377 the result started reducing. I cannot find anything wrong around rows 377/ 378. I'll have work with short ranges but this takes the dynamic quality away... thanks again...

 

[mgirvin]

To get a range that will inlcluded new values (dynamic range) maybe:

1) Instead of highlighting the whole column, maybe highlight F7 to F2000, instead of 1000
2) Convert your table to an Excel Table (Ctrl + T), then as you add new records to the bottom, the ranges will update.
3) Use a Defined Name formula like:

If no blanks in Date column:

=$F$7:INDEX($F$7:$F$2000,COUNT($F$7:$F$2000))

If there may be blanks:

=$F$7:INDEX($F$7:$F$2000,MATCH(9.99999999999999E+307,$F$7:$F$2000))

To create name, Ctrl + F3, New, type name in Name textbox, paste formula in refers to textbox, clcik OK.