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


0
Utf
12/11/2009 5:17:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
810 Views

Similar Articles

[PageSpeed] 5

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)))))
> 
> 
0
Utf
12/11/2009 5:25:01 PM
>=if(a1=0,0,average(if(D1:D6<=A1,if(D1:D6<=B1,if(E1: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" <Jennifer@discussions.microsoft.com> wrote in message 
news:F3C71164-B17B-4437-BC0A-543F2E263C6B@microsoft.com...
> 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)))))
>
> 


0
T
12/11/2009 5:26:44 PM
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)))))
> > 
> > 
0
Utf
12/11/2009 5:34:01 PM
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)))))
> > > 
> > > 
0
Utf
12/11/2009 5:49:02 PM
Reply:

Similar Artilces:

how do I attach multiple Word documents?
I need to attach multiple Word documents into one large document. Many have different headers, so I can't just copy the text into one master document. Would appreciate any help on this. Before you Insert Text (2007: Insert > Object > Text from File), be sure each document you're including has a Section Break New Page both before and after it. After you've built your document, go through the headers/footers to be sure the page nos. are sequenced as you want them. Or use the "boilerplate" macro devised by MVP Graham Mayor. On Feb 10, 10:43=A0am, El...

conditional summing of arrays
I would like to sumproduct 2 arrays up to the row where it first exceeds a given number. For example assume 2 arrays are as follows : 1,2,3,4,5 & 2,3,4,5,6 the sumproduct is 2,8,20,40,70. So if my given number was say 30, the answer would be 4. if the number was say 80, the result would be NA. Can anyone help me with this problem? Thank you and kind regards JV ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** This can probably be done easier =INDEX(LARGE(ROW(INDIRECT("1:"&...

Reporting an Average
How do I average the field “Minutes” from the query “qryUrban” and display the average in a report? I would like to find the (mean) average, but I have no experience with the AVG function. Thanks -- Message posted via http://www.accessmonster.com pushrodengine via AccessMonster.com wrote: >How do I average the field �Minutes� from the query �qryUrban� and display >the average in a report? > >I would like to find the (mean) average, but I have no experience with the >AVG function. Without seeing your query, if the query is the report's record source and if the Minute...

Conditional format to cells containing a #DIV/0! error
I would like to apply a conditional format to an entire spreadsheet. In particular, if a cell returns and error such as #DIV/0! I would the selected font color to be white. What do I have to enter in the conditional formatting menu? If I set the value of the cell equal to #DIV/0!, the format does not work. A solution that I found could be to use the GoTo functions to select the cells with errors and then manually apply the color, but since I am creating a template at a later point the empty cells that return the divided by zero error may contain a number. Basically I would like the cells t...

Print multiple items
How can you print multiple items without opening each one and print. Rick wrote: > How can you print multiple items without opening each one and print. Hold down the control key while clicking to select multiple items, just like in Explorer, and then File - Print? -- f.h. Rick, Please enlighten me. Why do so many folks want to print electronic mail? "Rick" <Rick@discussions.microsoft.com> wrote in message news:17BF6FD0-1EA8-4CBE-A54D-4F67C5A957F2@microsoft.com... > How can you print multiple items without opening each one and print. ...

Sending from multiple accounts #3
I have two email accounts, one is POP3 (lets call this Account 1) and the other a MAPI (e.g. Accout 2). My default accout is the POP3. When I download email, the received emails go into the correct folders, that is to say Account 1 email goes in the Busines Folders Inbox and Account 2 mail goes in the Account 2 Inbox folder. No problem there, it works as expect. This issues is this: If I write an email on Account 2 and just click send (remember, account 1 is the POP3 account is default) the email is received as coming from Account 1. If they reply, it comes back to me as an email sent to...

Are Charts with Multiple XY Datasets Possible?
I'd like to be able to build a chart using multiple XY datasets -- say from two different experiments. If I plot the two columns of dataset as XY, then Add Data and try to plot the two columns of dataset B, get an XY plot (A), overlaid by two line plots, one for each column o dataset B. I tried googling but have not found any usefu information. Thanks Steve Shervais -- Message posted from http://www.ExcelForum.com when you add the 2nd range did you check the box saying that the firs column is the x axis values -- Message posted from http://www.ExcelForum.com Steve - There are s...

Multiple Copies of Messages!
In just the last week or so, my Outlook (2002, running in WinXP Pro) has started (1) sending as many as 6 copies to the recipients of some of my emails, and (2) sometimes putting 3-4 copies of incoming messages into my Inbox. It's starting to drive me crazy, and is a cause of constant apologies to friends, clients, etc. Not good. Any ideas anyone? I, too, noticed several copies of sent messages in my Outlook 2002 Sent Items folder dating back a couple of months. I didn't receive any comments from receipents, so I don't know that they received duplicates. It hasn't ...

Installing multiple Notes connector
Hi, I am referring to one of the MS guide, where it says it is possible to install multiple Lotus Connectors for scalability. Well..i tried to install just the connector component along with System manager component; but it refuses to install. My question: Do i need to install whole Exchange server in order to install Lotus COnnector on that server? Or, can i install just the connector component? Thanks, On Fri, 3 Nov 2006 01:01:02 -0800, Jack Dorson <JackDorson@discussions.microsoft.com> wrote: >Hi, > >I am referring to one of the MS guide, where it says it is possibl...

Multiple sorting
I'm trying to sort a report that I have by multiple options: 1st) by DATE and then by NAME, I went to: Advance filter Options I insert: first Date/Ascending;...then Lname/Ascending then apply filter, and is not working it's only filtering by date WHAT'S GOING ON??? Thank you!!! Hernan The Sorting and Grouping dialog exists in Access 2003 (as well as all previous versions of Access) However, if you're seeing Form View under the View menu, it sounds as though you're talking about a form, not a report as you stated in the beginning. -- Doug Steele, Microsoft Access MV...

Recording multiple checks in a single deposit
This seems like it's probably brutally simple, but I can't figure it out: how do I record multiple check numbers in a single deposit? The deposit consists of a couple checks from my primary checking account which I'm depositing, along with some cash, as a single deposit transcation in a different checking account. Using MS Money Deluxe 2003. - sph In microsoft.public.money, hodgdon wrote: >This seems like it's probably brutally simple, but I >can't figure it out: how do I record multiple check >numbers in a single deposit? > >The deposit consist...

Using CRM 4.0 Enterprise for multiple environments (production, te
Are you using CRM 4.0 Enterprise with multiple organizations such that certain organizations are set up for non-production purposes (production, test, training, development, demo, etc.)? For example, have you set it up so that you have: Production: http://crm.mycompany.com Test: http://crmtest.mycompany.com Training: http://crmtraining.mycompany.com Development: http://crmdev.mycompany.com Do you need to purchase user CALS for each organization, or can one user CAL be used for all organizations? Can all of the organizations leverage the same Active Directory organ...

Stacked bar for multiple series + secondary axis
Hello! :confused: Could somebody help me to get 2 or more parallel an distinct stacked bars? In the attached picture you will see that th only solution I got was to modify the bar width. Thank!!!!!!!!!!!!! +------------------------------------------------------------------- |Filename: Stacked bar.bmp |Download: http://www.excelforum.com/attachment.php?postid=3908 +------------------------------------------------------------------- -- gilbertc ----------------------------------------------------------------------- gilbertcn's Profile: http...

Opening Multiple files in separate Excel folders
Can this be done? Hi what do you mean with 'separate Excel folders'? -- Regards Frank Kabel Frankfurt, Germany Paul Dunn wrote: > Can this be done? Yes. Dim aryFiles Dim oFSO Sub LoopFolders() Dim i As Integer Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object D...

Conditional Delete
I want to delete all cells that contain specific text. Any help would be appreciated! I'd select that column and do Data|Filter|autofilter Then use the dropdown and choose custom contains: (type in your value) And delete the visible rows Then data|Filter|autofilter (to remove the filter) ======= You may be able to chose the value you want from the dropdown, too--avoiding the Custom stuff. gsxith3@embarqmail.com wrote: > > I want to delete all cells that contain specific text. Any help would > be appreciated! -- Dave Peterson Edit>Find what: yourtext Find all. ...

Multiple Addresses
If an account has two addresses, one at the main level and one under "More Addresses," how can I add a contact in so that the mapped address is the one under "More Addresses?" Thanks ...

Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data table of a line chart. I've tried creating the chart without the formatting applied to cells with the original data (data source), and then applying the format to the source; as well as creating the chart after applying the format to the source. The data table in the chart will naturally update with changed data since it is linked, but will not incorporate any style formatting. Any thought? Thanks. Data tables in charts are not particularly flexible. They do not, for example, support conditional fo...

multiple emails #4
I am receiving the same email over and over again, multiple times -- I delete it and remove delete folder contents and the next time I hit send/receive they all show up again. They seem to be from one sender -- but I can't find anything on her computer to suggest why this is happening. It is as if the send/receive function on her computer is stuck and it just keep sending the same mail. ???? Hi, I think that the UIDL Cahing is broken. What happens with the New Profile in Outlook created through the MAil applet in the Control Panel ? Also, this could be a problem with the ...

One sender to multiple recipients
Hello, I need to know if there is an option in Exchange 2003 or an addon that spam an email to multiple mailbox. We are receiving important messages from our clients. With the time, those client send directly requests to a user mailbox (user@) instead to a general mailbox (info@). When the user is out of the office (2 or 5 days), nobody is aware about the message sent by the client to my user. So we need to replicate the message sent from particular client to multiple mailbox. Any idea? Thanks Alex >-----Original Message----- >Hello, >I need to know if there is an option in Exc...

Dragging to select multiple rows causes out of control scroll
When I select a number of rows by click and drag method, the rows scroll by too fast to control. Is there a way to slow down this scrolling? "ronlee67" <ronlee67@discussions.microsoft.com> wrote in message news:D2940A38-DDA3-4E4B-B41A-FCEFAA3ADACA@microsoft.com... > When I select a number of rows by click and drag method, the rows scroll > by > too fast to control. Is there a way to slow down this scrolling? Hi Roplee67, You can change the scroll speed of your mouse: Start | Control Panel | Mouse | Wheel | Scrolling Depending on your OS version, access to ...

sends same message multiple times
I have been experiencing the problem that instead of a message just leaving the outbox, I'll receive a message that it timed/out, but if I don't delete that message immediately, it gets sent to the recipient over and over. I had one that went out 99 times before I stopped it by deleting the message from the outbox. These messages never show as sent mail. Any ideas? ...

Comparing data sets with +/-1 tolerance using conditional formatin
Please help! This has been puzzling me for hours!! I am using conditional formating to compare one data set with another to see if there are any discrepancies, using green cells for the same and red cells for different data. However, one set of data is rounded to the whole number, but the other set is to 2 decimal places (and has to remain that way). Therefore I need my conditional formating to compare the two data sets, but with a tolerance of plus or minus 1.00. How do I create this formula using conditional formating? CF/ Formula is: =ABS(A1-B1)<=1 -- David Biddulph ...

Change Multiple Contacts
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange How can I change the details within multiple contacts in a single action. i.e., company ABC changed their name to XYZ and moved to a new address. <br><br>I need to change ~250 cards with this example, and also a few other contact details. <br><br>Thanks to all. ...

Multiple Profiles in Outlook 2002
I have multiple profiles set up in Outlook: one for me, one for my wife, one I use for graduate school, etc. When Outlook starts, I'm prompted for the profile to use. If I have not rebooted the system since the last time I started Outlook, and I use a different profile from the last time, I get the following error message: Microsoft Visual C++ Runtime Library Runtime Error! Program: <path to Outlook.exe> abnormal program termination If I click on OK, and try again, it works fine from there on out ... until I use a different profile without rebooting first. I chec...

multiple inputs into same cell and totaling
I have A as constant. I subtract B from A giving me C. I want to be able to input different values(numbers) into B that accumulate. So 1st input 10 giving C new value. 2nd Input of 5 in B and having B become 15 which than updates C. So A's value 100 Subtract B's value from A. Example 100-10 Giving C's value 100-10+90 Than input new value into B: 5 which adds to B original 10 making it 15 C's value should than become 85. I want to be able to input new value in B as many times as I want. -- Atienne This example uses A1, B1, and C1. Put the following event macro in the w...