Average with multiple conditions

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I'm trying to calcluate the average where multiple conditions have to be met.
I've tried a couple of different formulas based on other postings, but I
continue to get "0".

I'm trying to calculate the average of months open (Column F) if the
following conditions apply:
Date closed is >= A1
Date closed <=B1
Bucket = "12 Month"

Column D Column E Column F
Date Closed Bucket Months Open
1/5/2006 12 Month 11.2
1/1/2004 24 Month 16
2/1/2006 48 Month 52
2/5/2006 12 Month 6.1
1/5/2004 24 Month 27
2/1/2004 48 Month 60

I've tried the following formula, but I get a value of 0:
=if(a1=0,0,average(if(D1:D6<=A1,if(D1:D6<=B1,if(E1:E6="12 Month", F1:F6)))))
 
Try the below (changed the first < sign to > sign). Please note that this is
an array formula. You create array formulas in the same way that you create
other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=IF(A1=0,0,AVERAGE(IF(D1:D6>=A1,IF(D1:D6<=B1,
IF(E1:E6="12 Month",F1:F6)))))
 
=if(a1=0,0,average(if(D1:D6<=A1,if(D1:D6<=B1,if(E1:E6="12 Month",
F1:F6)))))
Click to expand...

You just have the comparison operator for A1 backwards.

Try this...

Array entered** :

=IF(COUNT(A1:B1)=2,AVERAGE(IF(D1:D6>=A1,IF(D1:D6<=B1,IF(E1:E6="12 Month",
F1:F6)))),0)

The COUNT function makes sure there are 2 dates entered in A1:B1.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
That was just a typo on my part and I had entered it as an array. I now
actually get a #DIV/0! when I tried your formula.
 
Make sure '12 Month' is exactly same as ...May be copy one of the 12 Month
from the data to a reference cell C1 and change your formula to refer C1....
 
Back
Top