Average with multiple conditions
 

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)))))

Average with multiple conditions
 

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)))))

--
Jacob


"Jennifer" wrote:

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)))))

Average with multiple conditions
 

=if(a1=0,0,average(if(D1:D6<=A1,if(D1:D6<=B1,if(E 1:E6="12 Month",
F1:F6)))))

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.

--
Biff
Microsoft Excel MVP


"Jennifer" wrote in message
...
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)))))

Average with multiple conditions
 

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.

"Jacob Skaria" wrote:

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)))))

--
Jacob


"Jennifer" wrote:

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)))))

Average with multiple conditions
 

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....

--
Jacob


"Jennifer" wrote:

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.

"Jacob Skaria" wrote:

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)))))

--
Jacob


"Jennifer" wrote:

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)))))