#### COUNTIF? Use one column as condition to determine average of another

```This may be beyond the scope of basic excel, but here goes.

C4:C16 contains a list of times, or durations in this case.

In D4:D16 each cell contains either a "yes" or "no"

what I would like to do is average the "yes" times.  So referenc
D4:D16 as a condition to determine which times to average.

I could just manually seperate the "yes" times and compute the average
but there must be a way to do it as described above

--
Message posted from http://www.ExcelForum.com

```
 0
5/8/2004 10:43:38 PM
excel 39879 articles. 2 followers.

1 Replies
512 Views

Similar Articles

[PageSpeed] 49

```On 5/8/04 6:43 PM, in article pacman2k4.15ylio@excelforum-nospam.com,
"pacman2k4 >" <<pacman2k4.15ylio@excelforum-nospam.com> wrote:

> This may be beyond the scope of basic excel, but here goes.
>
> C4:C16 contains a list of times, or durations in this case.
>
> In D4:D16 each cell contains either a "yes" or "no"
>
> what I would like to do is average the "yes" times.  So reference
> D4:D16 as a condition to determine which times to average.
>
> I could just manually seperate the "yes" times and compute the average,
> but there must be a way to do it as described above.
>
> ---
> Message posted from http://www.ExcelForum.com/
>
Hi,

=AVERAGE(IF(D4:D16="yes",C4:C16)), to be entered using Ctrl+Shift+Enter

Why do it manually, when you can have Excel do for you?  :-)

Hope this helps!

```
 0
domenic22 (716)
5/8/2004 11:02:24 PM

Similar Artilces:

loop through pivottables columns and rows
I have a pivot table that have 2 columns that I need to loop through and read both columns and each items of each columns...the pivotitems only allow reading one column...does anyone know how to programmatically read both columns? Thanks You can loop through the column fields and their items: Sub test() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.ColumnFields For Each pi In pf.PivotItems Debug.Print pf.Name & " - " & pi.Name Next pi Next pf End Sub hpham77 wrote: > I have a pivot ta...

I can't back space more than one character or repeat a character,
I was prompted while filing out a form to have duplicate key strokes stopped. I made the mistke of accepting and now I can't type any dupe characters without difficulty (must pause momentarilly) and I can't back space without the same pause. I also cannot find how to disable the thing. This happens in all typing situations, including here. You've probably enabled FilterKeys, one of the Accessibility Options. You can turn it off in Windows Control Panel and at the same time disable the shortcut key that you pressed to enable it. -- Suzanne S. Barnhill Microsoft M...

Am I using the pop3 connector properly?
I currently host mail for udc.net on a machine running mailsite. I want to convert everyone over to exchange, but cannot do everyone at once. I believe that I need to use the pop3 connector. All MX records point to the current box running Mailsite. In exchange, I have created a pop3 connector for my own mailbox, and added the SMTP domain and email address for udc.net (so when I send mail, it will show me@udc.net as the from, and return address) I connector definately downloads mail from the pop3 mailbox. But it gets lost. The messages do not appear in my exchange mailbox. If I remove t...

using a UDF in place of a cond. format, b/c I need 4, not three
I know this is an age old topic here, and I'm really hoping that I don't just get referred to another web page [that type of help of course has its place and merits]. And if I had the time, I would have more fun going through docs and notes to find out how to do it, but time is not something I can barter. What I'm looking at is: I'm thinking along the lines of an example where I normally set the borders of my columns [except if it's the first row] in a cond. format via something like this: =AND((NOT(ISBLANK(\$A1))),(ROW()>1)) But, if I need that space for a Con...

Using Rules to Respond to Messages
I'm setting up resource scheduling and like some others have noticed, if a user submits a calendar item to a resource but puts the resource in the required or optional field the calendar has to accept or decline the meeting. Well, your resource is setup to automatically accept resource scheduling and no one is going to read the requests submitted as email. I was thinking I could setup a rule that automatically resonds to the user to tell them that they have selected a resource as a person. Please resubmit this request and put the resource in the resource field. PROBLEM: I can'...

Training registration: how to pick multiple entries for one course
A client is currently using a QBF to select an employee to register for a training class. Now, we want to allow for multiple employees to be selected to register them at the same time for one class. Looking for examples, sample code, or even a basic layout of how to approach this change. Thank you in advance for your help, Dawn -- Dawn Bjork Buzbee On Tue, 29 May 2007 09:05:00 -0700, Dawn Bjork Buzbee <DawnBjorkBuzbee@discussions.microsoft.com> wrote: >A client is currently using a QBF to select an employee to register for a >training class. Now, we want to allow for multip...

PDFs do not display in OWA using FE/BE scenario, IE 6, Adobe Reader 7.0.7
Greetings, My organization is currently running Microsoft Exchange Server 2003 SP2 in a FE/BE scenario with the FE serving OWA. We are experiencing problems with our OWA users not being able to open PDF attachments within OWA when they are using IE 6 (fully patched) and Adobe Reader 7.0.7. After some testing, I have discovered the following: When using: Abobe Reader 7.0.7 and IE 6 (fully patched) - PDFs will not display in OWA (no errors given) Adobe Reader 7.0.7 and IE 7 Beta 2 - PDFs will display properly Adobe Reader 7.0.7 and Firefox (fully patched) - PDFs will display properly ...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

Using Automation to Send HTML...
The following code used to work fine in Outlook 2000, now with Outlook 2002 it doesn't. Now it leaves a BLANK body. Can anyone tell me why this no longer works? Also, we try the same thing with an RTF type file as the body (.body property), and it just puts junk in the body and does not interpret it as a RTF file. Thanks. Bob ***************************************************** oOutlook = CREATEOBJECT("outlook.application") oMail = oOutLook.CreateItem(0) WITH m.oMail m.oRecipient = .Recipients.Add("test@test.com") m.oRecipient.type = 1 .Subject = &quo...

Moving mailboxes using Active Directory Users and Computers
This is a multi-part message in MIME format. ------=_NextPart_000_0022_01C61DC8.6FAD4950 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Question: When moving mailboxes using AD Users and Computers from = Exchange server A to Exchange Server B, what will be the effect on users = that currently have their mailbox open in Microsoft Outlook? Some of the = users have Outlook 2003 and some of them have Outlook 2002. Is it best = to do the move when noone is on? but the thing is in this organization, = we have so laptop users that may not be on...

How I can print full text bigger than column, in repeat column
When I print statements with repeat columns in excell which have text bigger than repeat columns, complete text is printed on all sheets except first sheet. How I can overcome this problem? I want to print complete text in repeat columns, which is expanded to other columns which are blank. ...

Link to other slides from table using c#
Hi, I'm looking to create links from a certain slide to another, and I need those links to be from within a table. I need it to be something like this: var pptApp = new PowerPoint.Application(); var presentation = pptApp.Presentations.Add(Office.MsoTriState.msoFalse); var slides = presentation.Slides; slides.InsertFromFile(@"C:\Users\Tng1\Documents\PresentationTemplate.potx" , 0, 1); var titleSlide = slides[1]; titleSlide.Name="TitleSlide" ; var tableSlide = slides[2]; tableSlide.Name = "TableSlide" ; va...

Conditional Formatting dependent on whether cell is populated.
I want to conditionally format cell G2 dependent upon whether there is a value in cell C2 AND G2 Therefore, if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber if cell C2 has a value in it and cell G2 has a value then cell G2 color = Green Any helpful hints or useful suggestions? Thanks Tel Conditional Format of G2, condition 1: =AND(COUNTA(C2)=1,ISBLANK(G2)) format Amber condition 2: =COUNTA(C2,G2)=2 format Green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tel" wrote: &...

What's the best way to diagram a supply chain using Visio?
What's the best way to diagram a supply chain using Visio? ...

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is there a way, in Excel, that I can merge cells, rows, or columns, like I can in Numbers? Many thanks for your help. ...

EXCEL Cell Formatting: Custom or Conditional
At work I have Excel 2002 and I have some cells that I import data in from the mainframe. I have a table where there is a column for GENDER (MALE or FEMALE) that I would like to see shown as "Male" or "Female" (mixed case). Is there a way to change all upper case to mixed case using either CUSTOM formatting or CONDITIONAL formatting? I would prefer not using a macro if there is something simpler. I know that =PROPER() produces the correct result, but I don't see how I can use this unless I'm referencing another cell. I would like to be able to just change what fa...

Sendinf personalised mails to a group using Outlook
Hi All, I would like to know how to send personalised mails to a group (keeping the groups in bcc) using outlook 2003/2007. Please share your thoughts. regards, Jaseel You have stated contradictory points. BCC does not "personalise" it just hides the recipient list. If you want to personalise, ie use data fields like first_name and last_name, you need to do a mailmerge to email. There are many posts here on how to do that. Regards Judy Gleeson MVP Outlook in Canberra, Australia "Jassy" <Jassy@discussions.microsoft.com> wrote in mes...

how can I change the a,b,c, column headers in excel to names
I've tried and can't figure out how to change the column header from a,b,c, etc. to names for each column. Any tips are appreciated You can only show A,B,C...,IV or numbers: 1,2,3,...,256 But you could put your names in Row 1 and then select A1, then select A2 and click on Window|Freeze panes. espray wrote: > > I've tried and can't figure out how to change the column header from a,b,c, > etc. to names for each column. Any tips are appreciated -- Dave Peterson ...

Sort Using Different Columns
I am trying to compile a list of sales by customer and total purchases by that customer in a series of months. I have column a as a master list of customer names. Column B is going to be the charges for that customer in lets say may. Column c is going to be the charges for that customer in June. The problem I am having is that not all customers have charged from us in the same months so when I bring my data into this worksheet it contains the customer name in one column and the charges in another. Doing this does not allow the customer names for one month to line up with the next m...

Variable Column Sort
I need to aIphabetize columns with a set start column, but a variable range for the end column. Thanks, Bern ...

Entering vacation into a recurring batch using HR attendance
Hello, I have a client who maintains recurring batches and keeps track of vacation and sick payouts that way. We recently moved vacation and sick from payroll to HR. Now there is a restriction that does not allow entering these linked vacation/sick pay codes in a recurring batch. Is there around this or is it basic functionality of attendance? -- Greg. It's the way it is. You'd probably be better off with PTO Manager unless you need more than vacation and sick time. If you do, you'd want to look at Enterprise Leave Manager from Integrity. -- Charles Allen, MVP &quo...

Access exchange using IMAP
Hi There. I cannot access my email message using IMAP to exchange 2003. The statement as below: Connection refused Configuration: Account: Staffmail Server: 172.18.3.203 User name: Staff\Taufik Protocol: IMAP Port: 143 Secure(SSL): 0 Code: 800cccd9 any ide how so solve it? thanks Have you started the IMAP service and made sure that IMAP access is enabled for your user account? Exchange 2003 disables IMAP and POP3 services by default. -- Ben Winzenz Exchange MVP MessageOne "Muhamad Taufik Khasim" <Taufik@uniten.edu.my> wrote in message news...

Using VBA
Hello, I have a summary timetable sheet, and each customer has a differen colour. If someone is due to visit them on day x, then the cell i shaded in that particular customer. I then have an individual sheet for each customer and I want to be abl to copy the formatting from the summary to the individual custome sheet without having to cut and paste for each individual customer. This is what I have so far Dim Cell As Object Dim CurrentCell As Object Range("B10:Y200").Select For Each Cell In Selection If Cell.Interior.ColorIndex = 52 Then Cell = CurrentCell Sheets("Cus...

Using "OR" in an IF statement
I am trying to use the command OR in an If statement and I am having no luck. In Lotus 123 I could string commands by using #OR# to produce a common result. For example: =IF(J3="INVEST" #OR# IF(K3 = "INVEST",1,"NO")) I can't seem to make this work in Excel. Can anyone provide me with a correction? Doug, Like this =IF(OR(J3="Invest",K3="Invest"),"The true condition","the false condition") If the true or false condition is numeric then drop the quotes -- Mike When competing hypotheses are other...

Using the 'S' key
PowerPoint 2003, Win XP. A presentation with music running from start to end, using the 'S' key to pause the presentation also stops the music. On pressing again to continue the presentation, the music does not continue. I'm not hopeful, but is there a way to continue the music, even if it means that it starts from the beginning? I have the sound set to 'loop until next sound' on the second slide, the first being an introduction slide. Thanks. Brian. On most systems embedded and linked sound act differently in this respect. If you LINK to the so...