Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 385
Default 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)))))


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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)))))


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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)))))




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 385
Default 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)))))


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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)))))


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Average - Multiple Conditions? Ted[_5_] Excel Worksheet Functions 2 August 3rd 09 10:59 PM
Average If with multiple conditions WildWill Excel Discussion (Misc queries) 1 February 10th 09 11:45 AM
AVERAGE IF with multiple conditions WildWill Excel Discussion (Misc queries) 6 February 10th 09 08:52 AM
Average with multiple conditions Dez Excel Worksheet Functions 4 July 11th 07 06:07 PM
AVERAGE with conditions mr_concrete Excel Worksheet Functions 3 February 7th 07 09:23 PM


All times are GMT +1. The time now is 09:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"